Stored Procedures as a Back End
gnuhost.medium.com
gnuhost.medium.com
If I deploy to the database and something goes wrong, I need to really trust my rollback scripts. If the rollback scripts go haywire, you're in a tough spot. If that happens to code, you can literally just move all the traffic to the same thing that was working before, you don't quite have that luxury with the database.
You can have a bunch of servers, you really only can have one database. This means the database should be very sensitive to changes because it's a single point of failure. I don't like modifying or changing single points of failure without very good and well tested reasons.
Could you version your procs and have the new version of the code call the new procs? Sure, but now you have to manage deployment of both a service, and the database, and have to handle rollover and/or A/B for both. If my logic is in there service, I only have to worry about rolling back the service.
Database logic saves you a little bit of pain in development for a ton of resilience and maintenance costs, and it's not worth it in the long run IMO. Maybe it's because the tooling isn't that mature, but until I can A/B test an atomic database, this doesn't work for a lot of applications.
But I still wouldn’t.
The apps I have been working on are monoliths which run at about 2% CPU on the server, so there is no A/B comparison runs in production (e.g. on January 1, some law goes into effect and you WILL implement it). I would happily see ORM usage curb stomped.
In this sort of setup, changing a db function isn't unlike making a change to db-interfacing code in that it's not changing the underlying data, just the interface to it.
I've built similar systems that instead of making changes to db functions, changes are made to materialized views. The underlying data doesn't change, but its presentation does. No huge need for rollbacks. It works really well!
I think the sense of the comment was "how can I deploy or test anything once it goes in production". You know, were starting again from scratch is not an option ever.
Using the database as a service layer is dumb for non-trivial scenarios were you need garantees that your service keeps running, or when you want to test new code with the production data.
It's interesting, and probably useful to have a set of functions with the database, but there is real problems once your database stops being a dumb store.
Do you think it's a bad idea to use something like PostGIS because it uses functions that live on the database?
I just think that most applications need stuff to be ACID and deal with a lot of writes (or may want to deal with some of those things later) and make this a bad idea.
If they are made to views, you have the problem of how do you roll out/back those changes. You could obviously make that configuration on the server side, but then you're still needing to roll out both the service's configuration changes and the database, when if you put all the logic in the service, you only need to handle that for the service.
I don't mind abstracting the views into another mental layer, but it's a harder sell to convince me that reduces a ton of risk because doing operations on the database (even if you have a perfect permission setup) is risky, because you can, by definition, impact that machine that's serving live customers.
Still, thinking about it has led me to see how we are not really leveraging the capabilities of feature rich RDMSes like Portgres. A judicious use of user defined functions can help, particularly for polyglot companies/microservices where the database can ensure certain routines are standardized across all apps.
Why wouldn’t you have more than one database? What if you’re global?
Why would a database ever be a single point of failure?
Why wouldn’t you be capable of rolling it back, or test in anyway that’s different from what you do with your other code? Is it perhaps because you don’t know the tools to do so?
Why would database logic save you pain in development? Typically utilising stores procedures would make development less straightforward because you split your logic up, but you do it because database servers are a lot better and a lot more efficient at giving you the data you need.
Don’t get me wrong. I only use stored procedures, or even any SQL tooling, when they are the only way to get efficient or persistent data, but it’s not because or any of the reasons you list. It’s because 95% or the developers we hire don’t really know how to use databases.
A good example is an issue one of juniors had recently. We have a lot of data organised in what is essentially a tree structure where the nodes only know their parents. He needed to pick any node and gets its children + related rows from a range of tables in three databases. The task itself was simple enough, except the tree has millions and millions of nodes and even more millions of related rows, and he needed the data fast. He had hacked around, trying different stuff like loading it gradually, caching it between uses and so on, but he couldn’t get it fast enough. We made a recursive stored procedure that was capable of giving him the data almost instantly, because thats what database servers do. In a couple of years, when this needs tweaking, however, it’s very likely that someone like me will be needed to look at it, because recursive SQL isn’t something a lot of our developers do, so in any other case, doing it in C# would’ve been better.
You don't test the ORM, you test your models. If you're writing db procesures you'd test the procedures, not the db's procedure engine.
But yeah, you should be doing your testing in a test database already. The only problem I see is that you might have a tighter coupling between db schema version and software version with procedures (although then again, you could avoid that if you're careful and make it less coupled)
T-SQL only gives you 100 calls deep. Also, having CTEs calling other CTEs can create a very slow query plan, which takes unrolling each CTE into either a table variable or temp table to resolve.
Everyone always asks that in every language at every interview and it is rarely used in practice - ahhh interviews.
"Dolt is a data format and a database. The Dolt database provides a command line interface and a MySQL compatible Server for reading and writing data."
... and TerminusDB [2] for graphs:
"TerminusDB is an open-source knowledge graph database that provides reliable, private & efficient revision control & collaboration."
I don't know if Dolt supports stored procedures, but in any case versioning a database would be an amazingly powerful and convenient thing, if it can be done without sacrificing too much DB performance.
Worth investigating and has the potential of changing the way we write apps.
--
Advanced methods (like blue/green deploys) are invented by people with high-traffic requirements and slowly filter down to everyone else until they are normal practice. 15 years ago server configurations were ordinarily painstakingly handcrafted and somewhat unique.
We can reimagine those techniques around a database without too much difficulty.
> You can have a bunch of servers, you really only can have one database. This means the database should be very sensitive to changes because it's a single point of failure. I don't like modifying or changing single points of failure without very good and well tested reasons.
This is a strong argument; I agree that very good reasons are required. I have seen scenarios where the reasons are IMO quite good, but agree this isn't a widely-applicable technique.
> Could you version your procs and have the new version of the code call the new procs? Sure, but now you have to manage deployment of both a service, and the database, and have to handle rollover and/or A/B for both. If my logic is in there service, I only have to worry about rolling back the service.
It has become common to have (and version/rollback/etc) each of web-frontend, web-backend code, web-backend engine, database schema, database engine.
Cutting that back to web-frontend, database http plugin, database schema & database engine is IMO a plausible win some of the time.
I've been noodling around for awhile with the concept of a precompiler for stored procedures which alters their names to include a hash of their code.
This would give you a safe way to seat multiple versions alongside one another.
* The frontend build process would pull in the hashes so it knew which version to call * A cron-job could delete the old ones once they're (manually marked?) no longer in use - this would be the most hairy bit since it's got to be 100% sure it's not in use anymore.
Rolling back then constitutes putting the old version of the frontend live so it'll refer to the previous stored procedures.
Relying on ORM doesn't make a difference, since you are still sending DDL commands to a live database.
Using stored procedures, views, and triggers makes it easier to add abstraction between your middle ware and the database structure.
But on the whole, I agree it's probably not recommendable to anyone feeling hesitant about it.
In a few years when you need to make some major changes you will see why it was a bad idea, and it will be too late. Have fun modifying hundreds or thousands of sprocs when you need to make a large-scale change to the structure of your data, because SQL won't compose. Have fun modifying dozens of sprocs for each change in business logic, because SQL won't compose. I guarantee you will have mountains of duplicated code because SQL won't compose.
The best argument against sprocs that I've heard is that you really don't want any code running on your database hosts that you don't absolutely need because it steals CPU cycles from those hosts and they don't scale horizontally as well as stateless web servers. This is a completely different argument than the OP's, however.
If someone created a database with the intention of it being a good development framework, it would probably be more pleasurable to code against, but would you trust it with your data?
So should be most of your (micro)services. I have seen more instances of sloppy system design (non-transactional but sold as such) than coherent eventually consistent ones.
On the other hand, does it matter if my social network updoot microservice loses a few transactions? With code running outside of the database, you get to decide how careful you want to be.
Note that what you described is not eventual consistency but rather "certain non-determinism", there is an abyss of difference.
However I believe you CAN tolerate some level of failure and defects in your app code, knowing the more battle hardened database will - for the most part - ensure your data is safe once committed. Yes, there will always probably be bugs and yes some of those bugs may cause data loss in extreme cases, but if you're saying you perform the same level of testing and validation on a product hunt style app as you would on a safety critical system, or as postgres do on their database, I find that extraordinary and very unrepresentative of most application development.
I'm not saying defects are good or tolerated when found, but from an economic perspective you have to weigh up the additional cost of testing and verification against the likely impact these unknown bugs could have. Obviously everyone expects any given service to work correctly - but when is that ever true outside of medical, automotive and aerospace which have notoriously slow development cycles?
Personally I'd pick rapid development over complete reliability in most cases.
Polymorphism is possible, but not really encapsulation. One could argue that arrays/objects would cause consistency problems, and therefore don't belong in a data language.
Someone expecting python-like OO programming will go mad.
The performance argument depends on the whether the database is limited by CPU or bandwidth and what the load looks like. It's shown in benchmarks that the round-trip between db/network/orm/app takes many orders of magnitude longer than the procedures themselves.
You will find you'll need two queries with a huge amount of duplicate logic - the SELECT clauses, even if they're pulling the same columns, will need to be duplicated (don't forget to keep them in sync when the table changes!), because there's no reasonable way to compose the query from a common SELECT clause. The paging logic will be duplicated for the same reason. So although the queries are similar, only sorted and filtered differently, you will need to duplicate logic if you want to avoid pulling the entire data set first and performing filtering and sorting in separate modules.
These problems become significantly worse when you're talking about inserting, updating or upserting data that involves validation and business rules. These rules are not only more difficult to implement in SQL, but they change more often than the structure of data (in most cases) so the duplication becomes a huge issue.
I haven't tried this on a system that uses Postgres functions so I could be way off base here, my experience was pure sprocs in MS SQL.
Write one view for selecting customers, with all the joins, sub-queries and case-when logic.
Then select from this view order by birth date, and select from the same view by sex.
Yes, the limit/offset paging clause will be duplicated, but all the joins, sub-queries and case-when stuff can be DRY'd in a single view.
Likewise the business rule validation could be encapsulated in triggers or in a stored procedure that would validate all changes, much like your C# code would.
I can confirm your experience. There's no doubt that stored procedure/function based business logic requires a certain discipline, knowledge set, and the ability to get a team in marching in the same direction. But if you can achieve the organizational discipline to make it work there are definite advantages, especially in the ERP space.
And that may well be differentiator... those of us working with more traditional business systems have trade-offs that you won't find in a startup. For example, a COTS ERP system is more likely to not really be a single "application", but a bunch of applications sharing data. This means a lot of application servers, integrations, etc., not all from the people that made the ERP, needing access to the ERP data. The easiest integration is often at the database, especially since you can have technology from different decades (and made according to the fashions of their time) needing to access that one common denominator. Since many ERP systems are built on traditional RDBMSs, and talking to those doesn't change much over long periods of time... having the logic be there to make these best of breed systems work sensibly with the ERP can be very helpful. All that said, in this context I'm an advocate of the approach.
Now, take a team that maybe has a high churn, sees the database as some dark and frighting mystical power that can only safely be approached with an ORM, or a team that thinks their "cowboy coding" is their core strength (if perhaps not that thought directly) and database based business logic certainly has many foot-guns. But, there are really few technologies that don't suffer without a good well-rounded, consistent approach.
I want to add that your talent pool will also influence where to create a hardened boundary: inside the database server (if you have lots of SQL people), or inside the application server (if you have lots of C#/Java/Python people).
Back in 2009 I helped to put together a small app in APEX, which then was quite new, in the two days, or day and a half, before the lead developer left for an overseas vacation. I had never before used APEX, she had been introduced to it an an Oracle tech day, but that was the extent of either's experience with it. The application is long gone, but got a lot of use over several months.
PL/SQL is an excellent tool for implementing business logic, and I really like APEX largely because I can call PL/SQL at any of several points.
This also applies to "composable" code written in the application layer outside the database.
At it’s core that’s a very basic change, but the issue with this stuff is how your adapting to such changes without introducing massive technical debt. Databases that elegantly to your data are easy to work with. However, it’s always tempting to use the lazy solution, which is why systems relying on stored procedures tends to age poorly. In effect they tend to corrupt the purity of your schema.
If anything, it seems like it would go the other way around: With stored procedures, you're guaranteed that any application logic is at least consistent with the current database schema, whereas with separate services, you need to maintain that consistency yourself. Not that I'm advocating for stored procedures in general because they come with a mountain of other drawbacks, I just don't buy this particular argument.
Because stored procedures don’t include the ability to do things like use inheritance to easily add extra layers of abstraction and let the compiler detect when you’re messing up. Adding a column is easy enough, but enforcing that it means something and every one of your prior calculations are now meaningless without taking it into account is difficult. Making such changes elegant and enforcing them to avoid future bugs is even harder.
Remember this example is at it’s core a minor difference. If you’re changing something like amounts being measured by volume instead of weight, then basic assumptions no longer hold making things much worse.
But again, I don't see how this is any easier to solve in application code than it is at the DB level. I don't know of any programming language or framework that allows you to encode rules such as "this DB column must be used". And even if such a thing existed, it wouldn't prevent you from making mistakes in using that column.
On the other hand, using stored procedures at least prevents errors in the other direction: referencing columns in a way that no longer makes sense (because the type was changed, it no longer exists, etc).
Stored Procedures have all kinds of other issues, but this is an obvious failure.
The big down side I see is the learning curve. Writing business logic in the database is not easy and not easy to maintain. It can take half a day to grow a large function. Even longer to debug sometimes. But you learn to structure things well.
Oracle was happy to sell database licenses but made zero effort to develop this way of using the database as a first-class capability. They were astonished that it even worked as well as it did, they did not expect people to take it that far.
For some types of applications, something along these lines is likely still a very ergonomic way of writing web apps, if the tooling was polished for this use case.
Just delivered one last month with plenty of T-SQL code in it.
Usually that is one of the things I end up fixing when asked for performance tips, other is getting rid of ORMs and learn SQL.
One of my solutions used Golang. It pulled data, looped a lot, then put results back. It took approximately 4 minutes.
I included a second solution, using SQL. It selected into the results table with a little bit of CTE magic. It took approximately 600ms.
Did you hear back from them with a positive response?
When you see a bottleneck, you could use 2 systems ( eg. EF by default and Dapper when doing optimizations)
There was just so much stuff that you had to manually build you don't have to now. (There are other complexities that we get to worry about now.) One thing I noticed back then is that putting stuff in the database just made things easier. In some instances, it really did act as like an amplifier for getting work done. Need to sort data? The database has things in place to do that and really efficiently too. I remember working with people, and if they had to make things like a linked list or a binary tree, they were lost (Yes, these were CS people too. We can argue about their education, but yes, they did graduate with a CS degree). There was nothing really like Stack Overflow to ask for help. You were really on your own for a large portion of it. Database code really took care of a lot of that for you. (Truthfully, the companies I worked for used MSSQL Server back then, so your results might have been different.)
I've used PostgreSQL this way a few times to great effect. The connection is over local IPC and never touches storage, so the operation throughput is quite high.
Shameless plug: I've written an article to showcase it's philosophy and how easy it is to get started with PostgREST.
[^1]: https://samkhawase.com/blog/postgrest/postgrest_introduction...
Or, as I like frame the issue, there's a difference between a datastore and a database. Datastores/persistence engines store your data in manner that you can get it back later. Think of a key-value store for example. A database, however, assists you with the management of your data, including helping to ensure correctness of the data, tracking changes, among other things. For most systems lot of work is already managed in-database whether you like it or not.
I guess the one true thing in software dev is the cycle/pendulum keeps rotating/swinging. Often without people realizing it's swinging back instead of brand new!
In my past, I built one application that was fully database driven, and at the time (2004-2005) it would have been difficult to pull off without stored procedures and table driven logic - fully maximizing the power of SQL - especially in the timeframe I had to do it (<3 months). I mean, I pushed the technology HARD (expert system for fraud detection that worked hand-in-hand with basic machine learning).
I will never forget how I was derided by people for that choice - even though, that system is still running today and working well, in the bowels of an acquirer. I mean, literally, I was derided to the point of getting imposter syndrome for feeling that I made a choice that others regarded as so limiting.
The truth is, I learned everything else about distributed applications and databases because of being derided in that way. Ultimately, I now know how to architect things many different ways and can choose when I feel it is appropriate. I also know not to let the negativity of others prevent success.
I am not sure the moral of this story. If you can build a system, and it serves its purpose well and for a long time, and it works and provides the needed value.. it really may not matter. But, you can choose sometimes, and if there is one truth it must be that there is not always only one way to do something. Try to pick the right tool for the job as best as you are able.
Don't think you're dumb just because other people don't like your idea. Keep your mind open, and be willing to learn, but if you can make it work, and prove it works, you are just as right as anyone else.
Maybe that's the moral of the story.
We rely on this to be able to bring updates quickly to our customers when their needs change.
We also found the tooling lacking for our database server.
On the other hand, we don't use ORMs, instead mostly relying on handwritten select queries, with "dumb" insert/updates being handled by library and others by hand. So we normally don't pull more data over the wire than we need.
Then again, I guess we're a bit old fashioned.
I found that if you are 'happy' with the style of lots of stored procs and data lifting on the server side you usually have a decent source delivery system in place. If you do not have that you will fight every step of the way changing the schema and procs as this monolithic glob that no one dares to touch.
Also tooling around SQL, i.e. refactoring tools and debuggers, is not great - if even available at all.
how would you debug HTML, for example? Open it in browser, right? same for sql: run it and see if it works as you wanted
same goes for composability: you can compose SQL same way you can compose HTML, but you still need to understand the big picture
explicit control flow in SQL (cursors, for loops and stuff) is absolutely an antipattern. You need to think in terms of relational algebra and functional programming in order to write clean SQL
I think the one-liner would be better as
for i in [0-8]*/*.sql; do psql -U <user> -h localhost -d <dbname> -f $i ; done
find . -name "*.sql" -exec psql -U <user> -h localhost -d <dbname> -f {} \;
[0]: https://sqitch.org/
I do sometimes wonder if Liquibase or Flyway would be easier, but I love the encouragement to write _migration tests_, which do not always look like the unit tests you might write with pg_unit.
Something we struggle with is naming our migrations; the name you assign a migration is the only organizational control you have over it, and it becomes essentially impossible to introspect the current state of the database through the migrations alone.
For that, you really want independent access controls and CI tooling.
Of course, you can't separate much if you are a 1 or 2 people team at early stages of some project. And it may help you move faster.
But:
> "Rebuilding the database is as simple as executing one line bash loop (I do it every few minutes)"
This denounces a very development-centric worldview where operations and maintenance don't even appear. You can never rebuild a database, and starting with the delusion that you will harm you on the future.
That seems like a silly assumption. Who's to say you can't use databases that you can rebuild from scratch whenever you want? What about append-only logs built with CRDTs and the other ways?
You cannot do that with databases because the data needs to come from somewhere.
I'm sure you could implement some kind of echo server in SQL somehow and yeah that is a "database" that could be rebuilt from scratch with no loss but that's obviously not the kind of thing we are talking about here.
Safe to assume views go in the “code” schema with procs/funcs/packages, leaving just tables and sequences (if needed) in the “data” schema?
Considering building a side project using this kind of approach...
There are plenty of interesting problems with it. I would prefer to completely separate code from data if it wouldn't impact elsewhere. As it is, separating them would severely harm us, so it's kept this way. It brings a lot of productivity, but we have a very small team working on it, and completely separated procedures for them.
Generally when releasing new code you want to do a gradual release so that a bad release is mitigated. It would be possible by creating multiple functions during the migration and somehow dispatching between them in PostgREST but I would be interested to see what they do.
The other obvious concern is scaling which was only briefly mentioned. In general the database is the hardest component of a stack to scale, and if you start doing it do more of the computation you are just adding more load. Not to mention that you may have trouble scaling CPU+RAM+Disk separately with them all being on a single machine.
Make each stored procedure not depend on any other procedure. If you change one procedure, make sure that every existing procedure can work with both the current and the future version. Once compatible, start upgrading procedures one by one. Add in monitoring and automatic checking of the values during deployment, and you have yourself a system that can safely roll out changes slowly.
I'm not advising anyone to do this, it sounds horribly inefficient to me, but it's probably possible with the right tooling.
As a heuristic, moving computation to the data is almost always much more scalable and performant than moving the data to the computation. The root cause of most scalability issues is excessive and unnecessary data motion.
I actually can't remember a single time in 15 years where I've ever seen that. Maybe it's happened, but I don't remember, but I can easily recount tons of poorly performing SQL queries though. Sub-selects, too many joins, missing indexes, dead-locks, missing foreign keys...
I'm not saying it wouldn't cause a problem, I'm saying I've never, ever seen production code where someone dumped tons of data out and then processed it.
I have seen people try and make extremely complex ORM calls that were fixed by hand-coded SQL, but that's a different problem.
We've just seen a lower-level example of this principle in action, with Apple's M1 processor and its Unified Memory Architecture.
1. You put your stored procedures in git.
2. You write tests for your stored procedures and have them run as part of your CI.
3. You put your stored procedures in separate schema(s) and deploy them by dropping and recreating the schema(s). You never log into the server and change things by hand.
Then when new version is being deployed, the tool compares the database with the XML, and generates SQL to alter the database.
For stored procs etc it compares the text of them, so those that weren't changed are ignored.
It's a simple homebrew tool but it gets the job done.
what you probably wanted to mention is table statistics - but they are cleared only if you truncate your table, but then again - once you populate your table - the engine will recalc statistics by itself.
overall RDBMS does a lot of stuff behind the scenes for you, and you should take advantage of it, and instead think about more important atuff - business logic, data modeling, schema evolution, etc
* custom data types and domains
* use schema and namespaces
* foreign key constraints
* check constraints
* default values
* views
* functions (not stored procedures)
* triggers
In my experience, you can get a clean, efficient, easily-understood data model and application with those ingredients, without ever having to touch a stored procedure, a looping construct, or a conditional statement.
That's a strange claim, in my work it's always been 100% data ops.
> “Letting people connect directly to the database is a madness” — yes it is.
why?
> Killing database by multiple connections from single network port is simply impossible in real life scenario. You will long run out of free ports
Depends entirely on the workload. And it's very possible. All too entirely so.
while true:
select count(*) from huge_table
2. always hide postgREST behind API gateway/load balancer/waf/ids+ips/rate limiter and you will be more secure from stuff lile this
I really liked the mixed approach of have the DB do everything that it can and have a light backend that can handle http requests, api callbacks, image manipulation and whatever else.
I worked on a project that made moderate use of this. Worked alright; biggest problem was convincing DBAs/IT to enable it.
Probably integrating ML model inference, written in ML.NET could be use case, but we have SQL with ML Server with r/python support now, so.
The problem with CLR is that you need to know and understand sql engine internals in order to write good C# code for CLR integration, otherwise your clr code will be blocking the sql engine
I wouldn't build this into the database were I to build it today.
[1] blocket.se/leboncoin.fr/segundamano.(es,mx)
I've never written so little backend code for such high performance in so little time. If I were starting today, I'd probably use Supabase for the API as well (which is PostgREST under the hood).
Source: migrated code from oracle to mysql to avoid insane licensing fees once upon a time. Luckily we only had limited stored procedures at the time, most of which I could ignore.
Many large Banks (including Bank of New York where I worked in) and ERPs have stored procedures as their life and blood, have invested and built huge tooling over it – namespacing, dependency tracing of who's using what, monitoring, etc. It's excellent at having a tightly integrated, closed system where the Database is the source for authorization, module ownership, etc.
But there were reasons why it was not successful:
* Severe vendor restrictions. e.g. For a long time, Oracle could not do distributed compile-time PL/SQL checks. If you changed a function definition, other procedures that depended on it would break. But if you change a procedure's definition, it wouldn't tell you anything until you deploy it at runtime and something somewhere will fail, or recompile every other procedure. I don't know if it's fixed yet.
* Testing, or the severe lack of tooling that we have taken for granted – code coverage, easy local machine unit tests, etc
* Lack of modules (availability of an ecosystem or community of modules to solve recurring problems), and the subsequent dependency management and versioning overhead that comes with it
* Debugging, i.e. putting breakpoints, remote debugging, logging, good exception handling, etc
* Change management, graceful rollouts, rollbacks, etc. There is no so-called "Router" for stored procedures, they're all tightly coupled with each other, so there is no easy way to rollout and rollback new versions quickly without recompiling each time
* Connection limits. There is a tradeoff on a database being good at all the above – and how many concurrent connections it can support. It was impossible to simply "pass through" entire web traffic over to the underlying connection, without investing in quite heavy tooling
Overall, if you already have put all your eggs and tooling into one giant, well designed, well governed database – then PSPs are a natural evolution forward.
Have a customer with a 25 y/o Oracle PL/SQL system that basically operates their business and they can barely iterate on it. Every year a new consultancy comes in with their flavour of microservices and tries to subsume some functionality and they choke on the monolith to end all monoliths.
It can be done properly, but you need your entire development team to have a good understanding of declarative programming, and you need to have as much business logic embedded in the stored procedures as possible.
Instead, we had developers who continued to think imperatively. Whenever they were implementing a new feature, they would write several small stored procedures to obtain data, and then process it iteratively inside the application server.
The ensuing result was that there was far too much chatter between the application server and the database server than was actually necessary; loading a new web page in the application would take seconds with one single concurrent user, and this was a product that was supposed to scale to tens of thousands of concurrent users!
The real question is whether you are going to ship your code to your data or your data to your code.
I actually had to complete a dBase project for my last year in high-school (it was math/comp-sci oriented, so it made sense), but man I did not like it at all :) - I was all about Turbo Pascal then!
I have also implemented a message queue using the same design/infrastructure.
One of the reason is that my team knows only SQL things, they don't have time to install other programming tools on their machine.
So, just install postgresql and code the logic you want !
Of course, many programming languages also do not have a debugging quality-of-life compared to Visual Studio.
https://dbeaver.com/docs/wiki/PGDebugger/
https://www2.navicat.com/manual/online_manual/en/navicat/mac...
https://www.cybertec-postgresql.com/en/debugging-pl-pgsql-ge...
Other platforms have options as well:
https://docs.microsoft.com/en-us/sql/ssms/scripting/transact...
You certainly can and do add logging, this can be useful.
One exception is having access to that data because I was working ON the prod server. Read that again. It was deeply uncomfortable to work like this. Very worrying every day.
Better I can even optimize to native code, and still debug them.
But I'm sure there is a valid argument contrasting simplicity with a managed nature of a tech stack, not to mention a cost comparison.
The issue with this statement is that you're comparing things that works on different layers. PostgREST is for someone to host the database without any backend, serverless as you describe it is just a SaaS service you use. If there was a SaaS that exposed PostgREST as a service, it'll be a similar experience.
So unless you're comparing running a serverless platform yourself with running PostgREST yourself (which would be pretty obvious which one is the easiest), I feel like you missed with your argument here.
The OP’s goal was to simplify their stack, which they did do. However, they cannot run this stack without managing a database, as the database and REST API are run in the same container. My point is that another way to lower a project’s required effort is to offload database management.
Sure, OP now only has two layers in the stack instead of three. But now they are locked into managing the database entirely, including updates, backups, and scaling.
a) Did database management for you, a la Amazon RDS (perhaps later supporting BYO Cloud Database) b) Provided a software programming layer that simplified coding, versioning, deployment and rollbacks of Stored Procedures c) Perhaps provided a language environment that lets you program with an SDK in your language of choice, and then "lifted" that into stored procedures
My first thought when faced with this is, how do I do application monitoring, logging, exception handling, paging? Perhaps this is a solved problem, but I'm curious.
Could you send out Content-Type text/html and react on FORM-Posts?
I still develop webapplications with only additional javascript. The webapp on itself is completely usable with javascript turned off (the way it should be!)
As others stated, my concerns are primarily that SQL lacks a lot of general-purpose language features (modules, namespacing, composability, no easy way to debug, etc.) which seem to be ideal for writing applications.
I would be interested to see a project written with PL/Python + PL/SQL stored functions in Postgres. PL/Python functions would be the interface between API calls and the database, and SQL would do what it is currently designed to do: straight-forward data manipulation.
https://github.com/Delibrium/delibrium-postgrest/blob/master...
Projects like PostgREST (and PostGraphile) use schema introspection to generate the API. When the schema changes it's automatically reflected in the API. Sure one has to keep the changes synced on the frontend but the premise is you don't want to decouple.
I take this further by having an `api` schema per major version (e.g. `api_v2`). The downside is it becomes impossible to maintain more than 2 major versions at once, so you have to get everyone from v1->v2 before you can make much progress on v3, but the upside is that you should really try to avoid hard breaking changes in the DB very often.
I'm CTO at a company who's product makes heavy use of stored procedures for business logic. It's constantly the cause of all the biggest headaches. I inherited this architecture, I didn't design it myself. I believe the original rationale behind the design boils down to 'SQL is better at the type of set based operations this product does a lot'. i.e. If you've got an array of 100k values, and you want to perform an operation against a second array of 100k operands, you can do that much faster in SQL with a join than you can by loading them all into memory/code, looping over to do the operations, and saving them again. This is kind of true in the simple cases, but once it grows to require lots of logic and conditionals in those operations, and then chained operations you start to lose the benefit.
Upgrades and versioning are generally a bit awkward, we've got it fairly smooth now, but it still causes pain reasonably frequently when something in the upgrade process breaks. It was worse when I started as many upgrades were a bodged together folder of scripts with lots of 'if exists' type checks in them. Now at least we use a mostly automated diff based upgrade process for the database. Some types of changes still require manual upgrade scripts though. The articles solution of a folders of numbered scripts doesn't really look viable if you need to manage upgrades from different versions to the current latest version. Re-creating customer databases doesn't really go down well when they tend to like to keep their data.
Debugging is awkward. There are tools, but none of them compare to code debuggers.
SQL isn't easily composable, we have repeated SQL all over the place (or nearly repeated with small changes which is kind of worse because you don't spot the differences). Finding a bug in one of these repeated blocks means spending the next few hours hunting down any similar SQL to check over the the same bug.
Performance is unpredictable and all over the place. Stored procs that run fine one release will suddenly start performing like a dog the next release. We often never discover the actual 'cause', the thing that changed that made it slow down. We just end up finding a way to make it fast again by adding a new index, or changing the way a join is composed, or splitting something up. I've not yet got evidence, but I'm convinced that we've made performance improvements in one release only to reverse the exact changes several releases later also as a 'performance improvement'. It feels like playing wack-a-mole. Because of query plan caching, parameter sniffing and other optimizations the DB we have had scenarios where the performance of feature x, depends on if you used feature y before hand in the same session or not. We have some exact duplicates of stored procedures that are only there to ensure that two different code paths that use them don't share the same plan cache because when they do we get problems. Performance characteristics are often very different between dev setup and customer setup. Performance characteristics are different for customers that choose to host on cloud servers compared to customers that host on physical hardware. I don't just mean cloud is slower, I mean it's different. Some things are faster on cloud servers, but it's never predictable what will be what. It makes testing for performance very hard.
The articles statement about 'a database spends 96% of it's time logging and locking' is totally irrelevant. So what if that's what it spends 96% of it's time on. It's still spending that time. And as soon as your database has multiple users all those locks are going to start getting in the way of each other and causing delays or deadlocks.
It doesn't scale at all. Our DB severs are powerful and we can't realistically go much bigger (CPU & RAM wise), yet better performance is probably one of our customers biggest requests.
Deadlocks are not uncommon, hard to defend against, hard to fix, and half the time introduce other deadlocks in other places.
Maybe it's good in some scenarios, but once you have a growing evolving product being built by a team, it's far far harder to manage if a large chunk of the logic is in SQL.
What is wrong with cursor?
This is all completely outside the realm of things I’ll ever get into haha. IMO so what if the current app/db server boundary is “slow” or “adds too many moving parts” I already spent my entire career learning it, there is value in that to me.