18 ms·

Stored Procedures as a Back End

209 points by steve-chavez 6y ago

corytheboyd 6y ago
How does this work with database replication? Does all “application logic” have to execute on the master database?
- kemiller2002 6y ago
  Not necessarily. You can make it so that cross replicated databases can function independently and then sync the data together. This of course assumes that you don't have something like an ordering system and you absolutely need to make sure that you don't sell more than your inventory.
  - corytheboyd 6y ago
    That makes sense, yeah. I admittedly haven’t studied the pieces of the stack, but maybe something like PostgREST allows routing certain requests to master vs any replica, where you could make the atomic transactions needed for inventory based updates. 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.
mrits 6y ago
I'm not old enough to remember the first one but I know at least Oracle beat you to this idea in the early 90s.
- milesvp 6y ago
  Oracle really pushed this idea too, because they knew that it would mean more load in the database so you’d need a bigger box with with more cpus. Oracle charged per tiered cpu so their incentives were not aligned with the dev’s. IMHO this is pretty damning reason to generally avoid using the db for this reason. It may be true that a db has lots of spare cycles to utilize but so do your hot spare servers, and I’d hope that you’re not trying to wring performance out of those without thinking long and hard as to what hot spare means. 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.
kemiller2002 6y ago
Really anything can be a good or a bad idea depending on the problem domain and how it's implemented. He doesn't really go into how he made it work too closely, so it's easy to say it was a success. The real interesting thing to understand would be, "Why was it a success versus other approaches?" What really made it a better choice vs other approaches? What was the team make up? He says that it made the code easy to understand. To whom? Interesting the team made it work, but not enough to say, "Sure let's try it without a lot more forethought."
- loldot_ 6y ago
  Exactly! If you're writing a C compiler, it would probably be a bad idea to have that logic in a database, though it could be fun to try. On the other hand if your application is summarizing a lot data by different dimensions, it would probably be a bad idea to transfer all that data over the network only to summarize it in an application layer. Most applications have a little bit of both though and so will need both an application layer and database layer.
nwsm 6y ago
Nice work; PostgREST seems neat. However, the aim of this stack is simplicity, but it is incompatible with serverless products, which I now vastly prefer for ease of use and lower effort- traits I would consider part of "simplicity". 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.
- steve-chavez 6y ago
  If you like serverless, Supabase[1] will handle all the PostgreSQL and PostgREST management for you :) [1]: https://supabase.io/beta https://supabase.io/beta
- capableweb 6y ago
  > However, the aim of this stack is simplicity, but it is incompatible with serverless products, which I now vastly prefer for ease of use and lower effort- traits 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.
  - nwsm 6y ago
    I’m talking about the stack as a whole, which happens to be built on PostgREST. 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.
    - nvader 6y ago
FlyingSnake 6y ago
Using a database as a backend comes up time and again and developers are sharpely divided over this concept. I feel that developers who started their careers before the advent of NoSQL DBs/MEAN stack/etc had to go through the rigor of fine tuning their SQL queries and witness the powers (and quirks) of a performant SQL DB. These devs are usually a fan of using DB-as-a-backend. Personally, I am a big fan of PostgREST and I apply it whenever I find a use case that suits it. I find it quite pragmatic and it just shines when it plays to it's strength. Pragmatic developers should took a pragmatic look at such approaches and try to use the powerhouse that are SQL DB engines. 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/ https://samkhawase.com/blog/postgrest/postgrest_introduction...
- pjmlp 6y ago
  I am on the stored procedure side, and see no value in wasting network bandwidth and client resources for what the RDMS should do itself.
- CoreFailure 6y ago
  This is a wonderfully in depth tutorial, thank you for taking the time to write this up! I’ve debated using PostgREST for projects before and I think this will push me to use it on my next one.
kevincox 6y ago
The primary issue I have seen with stored procedures is how you update them. I would be curious how they manage that. 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.
- capableweb 6y ago
  > Generally when releasing new code you want to do a gradual release so that a bad release is mitigated 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.
- jandrewrogers 6y ago
  Many scaling problems in databases are because the computation is not done in the database. Outside of exotic cases, good database engines are rarely computation-bound in a competently designed system. By pushing computation outside of the database, it tends to increase the consumption of database resources that actually are bottlenecks, typically effective bandwidth (both memory and I/O). 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.
  - mattmanser 6y ago
    That's not been my experience at all. Quite the opposite. 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.
marcosdumay 6y ago
The one problem this technique has is that data and software normally have two very different speed and correctness requirements. That means that data and software should evolve following different procedures, what heavily implies that you want different people working at them, or at least you want people to look at them at different times. 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.
- capableweb 6y ago
  > 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?
  - shuntress 6y ago
    "rebuilt from scratch" means "as if the production server just fell into a woodchipper and I'm taking a new one out of the crate now" 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.
- taffer 6y ago
  For this reason you should always separate data and code and put them into separate schemas. In such a setup, deploying new code essentially means dropping and re-creating the code schema in a single transaction.
  - 6y ago
njkleiner 6y ago
Related: https://sive.rs/pg https://sive.rs/pg
_benj 6y ago
In my experience I actually got A LOT more from this approach using postgraphile/Hasura in front of my DB and later moved to dosco/super-graph in order to add custom functionality that was kind of a pain to do on the DB. 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.
- BenjieGillam 6y ago
  Out of interest (as the PostGraphile maintainer) did you look into https://www.graphile.org/postgraphile/make-extend-schema-plugin/ https://www.graphile.org/postgraphile/make-extend-schema-plu... for extending the PostGraphile schema to do whatever you need, or were you specifically looking to implement the extensions in Go?
  - _benj 6y ago
    First, thanks! PostGraphile is an AWESOME piece of software! But yeah, the reason was simply because I wanted to implement custom logic in Go.
    - BenjieGillam 6y ago
      Thanks! Makes sense; best of luck with your future projects!
flerchin 6y ago
Good luck debugging! Seriously. You can't attach a debugger to your DB.
- vsareto 6y ago
  DBs can have debuggers and breakpoints, but they do not come anywhere close to something like Visual Studio debugging C# code. Of course, many programming languages also do not have a debugging quality-of-life compared to Visual Studio.
  - TedDallas 6y ago
    Er ... SQL Server Management Studio is based on Visual Studio shell, and it does a fine job debugging stored procedures. I also code in C# and agree it also works well for that.
- bsg75 6y ago
  https://www.pgadmin.org/docs/pgadmin4/development/debugger.html https://www.pgadmin.org/docs/pgadmin4/development/debugger.h... https://dbeaver.com/docs/wiki/PGDebugger/ https://dbeaver.com/docs/wiki/PGDebugger/ https://www2.navicat.com/manual/online_manual/en/navicat/mac_manual/DebuggerPGSQL.html https://www2.navicat.com/manual/online_manual/en/navicat/mac... https://www.cybertec-postgresql.com/en/debugging-pl-pgsql-get-stacked-diagnostics/ 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-sql-debugger?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/ssms/scripting/transact...
- throwaway_pdp09 6y ago
  IME I've never needed one. You get a copy of prod and test against that. SPs are typically short, even when they're long their functionality tends to be well defined, it's never been a problem for me. You certainly can and do add logging, this can be useful.
  - mdoms 6y ago
    I don't know what industry you are in or what kind of data your users entrust in you, but "just get a copy of prod" is not an idea that would EVER fly in any organisation I have worked in. Developers do not get access to production customers' financial records, medical data or PII, ever.
throwaway_pdp09 6y ago
> Also typical database uses 96% of the time for logging, locking etc. and 4% percent for actual data operations 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.
- taffer 6y ago
  > why? while true: select count(*) from huge_table thats why.
  - somurzakov 6y ago
    1.if that table is a materialized view with columnstore index then you can run it as many times as you want. 2. always hide postgREST behind API gateway/load balancer/waf/ids+ips/rate limiter and you will be more secure from stuff lile this
  - ruslan_talpa 6y ago
    You can setup a view (accessed by postgrest) that returns an error when there are no (correct) filters in the query so that takes care of select *... problem (also you can have the same logic in the proxy (nginx) layer
tacticaldev 6y ago
I'm currently part of a team trying to UNDO this very concept. It sounded great in the beginning, but after years of headaches and wasted cash; we're building the backend in something else. I wasn't part of the original team. Nor was I part of the decision to migrate off of the system. I just know that for my employer, it was a bad decision those many years ago...
- gnat 6y ago
  Could you be more specific about the problems you're hoping to fix/benefits you're hoping to gain by dragging the backend logic out of the database?
  - magicalhippo 6y ago
    We're staying away from stored procs as far as possible. For us the main driver is that it would make it harder to run older versions of our program alongside newer versions. 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.
- pjmlp 6y ago
  Sure, because wasting network bandwith for going through needless data on the client is such a clever idea.
  - sumtechguy 6y ago
    This takes a balance to get right. For some data yes drag it over and keep it on the 'client'. But for other data just join it out and let the SQL handle it. However, for some reason SQL seems to scare a lot of people. So they end up making lots of microservices/functions/methods that just drag the data together and mash it together in a language they understand. That comes at a cost of network bandwidth. 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.
jrochkind1 6y ago
Everything old is new again. This was a common way to write client-server software in the pre-web world, clients talking directly to a central rdbms. 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!
- codeulike 6y ago
  Yes its funny to see this idea come back. It just needs a snappy name. MoSQL instead of NoSQL, something like that.
- sixdimensional 6y ago
  I almost can't believe we are talking about this now. I mean that in the sense of, I have built applications every possible way - when you work in a big company and inherit one of everything, you have to do that often. 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.
loldot_ 6y ago
What I've found being the problem with this approach is that sql does not lend itself very well to composability. Sure, you can make functions, views and stored procedures and compose them. But when you start actually composing queries of these parts the could lead to different execution plans having wildly different performance characteristics. Also tooling around SQL, i.e. refactoring tools and debuggers, is not great - if even available at all.
- somurzakov 6y ago
  sql is not a code, like c#, you dont debug it, you inspect results, or inspect execution plan/traces. 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
gnat 6y ago
I'm very interested in this, as I come from the web world yet find myself in a company that has a 30-year legacy of putting business logic into the database. It served them really well, and that they run quite lean in terms of hardware. But they're built on Microsoft tech ($$$), and moving things to a traditional database=datastore cloud architecture would require a substantial change. Does anyone have resources they can point me to on the wisdom or folly of keeping this logic-in-database approach? It seems like the tradeoffs in the different approaches are all about where the smarts are, and whether you can horizontally scale those smarts. Does that sound accurate?
- taffer 6y ago
  > It seems like the tradeoffs in the different approaches are all about where the smarts are, and whether you can horizontally scale those smarts. Does that sound accurate? The real question is whether you are going to ship your code to your data or your data to your code.
- mattmanser 6y ago
  Honestly, I find SQL Server worth the $$$, it's certainly not Oracle $$$$$$$$$ as far as I understand it.
jandrewrogers 6y ago
Back in the mid-90s, this was how many of the high-end web apps were built, using Oracle. Despite its limitations, and Oracle license fees (but back then you were paying for Sun servers anyway), it worked surprisingly well and was a reasonable model for many database-driven web applications. Some of these systems were used for mission-critical global operations handling enormous amounts of money and complex business. It had a lot of advantages versus other web app stacks at the time (commonly a mountain of Perl). 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.
- pjmlp 6y ago
  I can assure you that still is how many high-end web apps are built today. Just delivered one last month with plenty of T-SQL code in it.
  - kemiller2002 6y ago
    Not surprising. Databases are built to be really efficient, and with enough hardware, they can forgive a lot. Mediocre developers can produce a lot of "working" code if they stand on the shoulders of giants. (Not saying you or your team was mediocre, I'm just saying that I've seen really bad code run a lot of business processes. At the end of the day, it worked and the company made money.)
    - pjmlp 6y ago
      Just like I keep seeing plenty of mediocre teams doing for loops over GB of downloaded data and pegging Webserver CPUs for stuff that should have stayed on the DB. 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.
      - 6y ago
mdoms 6y ago
I have worked on an application that heavily used sprocs for business logic. I would not wish it on anyone. It will seem fine (and actually it will seem better - faster and easier to manage) while your project is small. 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.
- rishav_sharan 6y ago
  Can you elaborate a bit more on duplicated code/composition issue? I would think that functions would ensure that there isn't much duplicated code.
  - kqr 6y ago
    I agree. To me this sounds more like bad programming (high coupling and low cohesion) and like it would be a problem even if a separate application acted as the backend.
    - aaomidi 6y ago
      Well some systems make good programming really difficult. Stored procedures, especially back then.... Yeah.
      - throwaway894345 6y ago
        I believe this in the abstract, but would be very interested in some concrete explanation for why sprocs compose worse than other programming languages. How would the OP's situation have improved if instead of sprocs he had a similarly complex webapp and had to deal with the schema changes there? I'm not challenging the OP; I don't have enough experience with sprocs to have an informed opinion, but this information is necessary to make sense of the OP's criticism. 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.
aszen 6y ago
I'm very curious about this pattern, but does the tooling exist to make this feasible across a team. One of the big problems with SQL views is their lack of discoverability. The lack of tooling also plagues SQL based systems, I would miss goto definition, find all references and sensible autocompletion. I am curious why no one has built suitable tooling around all this, because it is a great idea for lot's of scenarios
- sql_server_fan 6y ago
  Redgate software produce tools for SQL Server that solve some of these problems
notJim 6y ago
See also https://www.slideshare.net/beamrider9/scaling-etsy-what-went-wrong-what-went-right https://www.slideshare.net/beamrider9/scaling-etsy-what-went... https://www.youtube.com/watch?v=eenrfm50mXw https://www.youtube.com/watch?v=eenrfm50mXw
lflux 6y ago
At a previous job I was at 2007-2014, we were doing classified ads[1] we had a lot of logic in stored procedures in our Postgres database written in pl/pgsql. It made sense at the time, but after a while these got really unwieldy and deploying things got tricky, since you we needed to update both appserver code and database stored procs at the same time. There also wasn't a great way to update stored procs at the time - we built this into an RPM that just ran a postinstall script to do an atomic swap. I wouldn't build this into the database were I to build it today. [1] blocket.se/leboncoin.fr/segundamano.(es,mx)
- BareNakedCoder 6y ago
  At a previous job I was at, we put each release of the sprocs into a different schema. Upgraded app servers called sprocs in schema "r102" while the not-yet-upgraded app servers continued to call sprocs in schema "r101" until they were eventually upgraded.
Brentward 6y ago
Not that this is an important part of the post, but in general parsing the output of ls like the author does in the for loop is a bad idea[1]. 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 or even better as something like find . -name "*.sql" -exec psql -U <user> -h localhost -d <dbname> -f {} \; [1] http://mywiki.wooledge.org/ParsingLs http://mywiki.wooledge.org/ParsingLs
8note 6y ago
How do you handle decoupling your API model from your database model?
- ellimilial 6y ago
  This won't answer this question in full but perhaps some part of it. 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.
- claytonjy 6y ago
  Use as many "base" schemas (namespaces) as needed, and only one `api` schema which has only views & functions you want to expose. 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.
jdmoreira 6y ago
Oh I feel like it's 2007 again and I'm a Junior developer writing a frontend in Delphi and the business logic in PL/SQL. Those were the days.
alphanumeric0 6y ago
I personally would not make this choice for new projects. I only use technology that is appropriate for the domain I'm using it in. Although, I am hoping this trend encourages better tooling and more modernization of SQL. 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.
- steve-chavez 6y ago
  Here's an OSS project that makes heavy use of plpython3u: https://github.com/Delibrium/delibrium-postgrest/blob/master/database/rpc/add_school.sql https://github.com/Delibrium/delibrium-postgrest/blob/master...
dang 6y ago
jgrahamc wrote a fascinating comment about how Cloudflare started this way: https://news.ycombinator.com/item?id=22883548 https://news.ycombinator.com/item?id=22883548
Raidion 6y ago
If I make the change in code, I can roll it out, test it in prod, and then slowly migrate traffic over in very standard and well defined ways. 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.
- ComputerGuru 6y ago
  I fully agree and treat my databases the same way. However, there’s no reason a stored procedure couldn’t be treated the same way with a random weight determining which version of a stored procedure is run. But I still wouldn’t.
- Roboprog 6y ago
  Presumably you can revert to a previous tag/label in source control and reload the previous procs/funcs to roll back versions. 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.
- chaps 6y ago
  Not sure I follow in the context of the post. The post is about a database that can be wiped and effectively recreated from scratch. There wouldn't really be a need for rollback scripts when you can just rerun the entire workflow from scratch. Even easier if you have a hot/warm/cold setup. 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!
LukeShu 6y ago
That's the approach that https://crt.sh https://crt.sh uses. https://www.lukeshu.com/blog/crt-sh-architecture.html https://www.lukeshu.com/blog/crt-sh-architecture.html
revskill 6y ago
I'm using this pattern on my projects for my customers. 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 !
- nvader 6y ago
  When the only tool you have is a hammer, you end up living in a house made out of hammers.
siscia 6y ago
> Performance hint: developers using cursors should have their hands cut off. This should do the trick. What is wrong with cursor?
- unklefolk 6y ago
  Performance basically. As SQL is a set based language you can often replace cursors (which are basically loops) with joins and that is orders of magnitude quicker for larger workloads.
  - tehjoker 6y ago
    Somehow I managed to avoid learning to use cursors and I have never seemed to need them. It seems like this was some kind of grace.
laudable-logic 6y ago
Maybe sorta off-topic, but does anybody have any stories to tell about successes/fails with the .NET CLR in MSSQL? I worked on a project that made moderate use of this. Worked alright; biggest problem was convincing DBAs/IT to enable it. https://en.m.wikipedia.org/wiki/SQL_CLR https://en.m.wikipedia.org/wiki/SQL_CLR
- somurzakov 6y ago
  I can't think of an example where CLR integration would be beneficial. 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
g0rbongler 6y ago
No.
chrismsimpson 6y ago
Love it. I am working on a similar but different concept for a prototype I am working on. The project requires many document/NoSQL style micro services, and I had the idea to do this directly with the file system and JSON serialisation, with thread locking on appropriate read/write calls (some calls that are read only don’t require locks). It is extremely fast and performant, and given the way I’ve delineated the block storage that the file system runs on, my hope is it will also scale reasonably well. I have also implemented a message queue using the same design/infrastructure.
astine 6y ago
"“Database should store data not logic!” — well, no. This is called data-base, not data-bag. Databases are specialized engines to deal with, you guessed it, data. And they are extremely efficient at it. Not to mention - majority of the job backends do, is dealing with data. This is good for prototyping. Unfortunately more often than not — prototypes lands on production with updated connection string and debug=False." 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.
- RussianCow 6y ago
  To add to this, if you need your data to be consistent at all times, it probably makes sense to let the database take care of that instead of managing it at a higher level (and hoping you don't make mistakes). This is where keeping your data-related logic in-DB is really advantageous.
- scarmig 6y ago
  And even the most conservative use of databases usually relies on them enforcing particular application logic. E.g., foreign keys or non null constraints. Or, to be even more radical, maintaining structured data itself, with separate tables comprised of different columns with different data types.
  - richardwhiuk 6y ago
    When you highly scale a database, you normally find you can't rely on the database to maintain foreign key constraints.
overcast 6y ago
Managing business logic in applications is a nightmare to control. That's why things like constraints and unique keys exist database side. If I have triggers that manipulate the data and update another table, you'll know that's always going to happen regardless of what application is writing to it.
RussianCow 6y ago
For those who looked at list item #9 ("no code versioning") and thought the author's makeshift solution to migrations seems awful, I highly recommend sqitch[0], an extremely simple but well thought out migrations tool that is platform- and database-agnostic. I'm using it on a side project with success, and haven't run into any major hurdles yet. [0]: https://sqitch.org/ https://sqitch.org/
- claytonjy 6y ago
  I'm using this in production for our core Postgres database and am quite happy with it. We've used it to "devops the database" in that all migrations are deployed via CI jobs triggered by git commits. 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.
snicker7 6y ago
This is basically how Q/kdb+ applications work in finance. Data and application exists on the same server. Of course, the programming model is much richer than pure SQL.
hartror 6y ago
Everything old is new again. 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.
dblohm7 6y ago
My previous employer did this, but it was fraught with problems. 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!
qpiox 6y ago
The first web application I did, that went in production 22 years ago, was in it's entirety based on stored procedures. This was the main idea of Oracle's web application services at that time. But, you have a very big tradeoff - you will be stuck with that database. Hardly portable.
cafard 6y ago
I would add that if you have SQL Server, and you want to use it other than in SQL Injection Mode, you more or less have to use sprocs. Anyway, you used to have to--I haven't checked in lately.
- sk5t 6y ago
  No. Prepared statements since forever.
stmw 6y ago
Is it just me or is this literally the worst architecture idea that's popped up here? I've seen this done in a few large projects, including some extremely famous products, and it produced endless grief for the engineering team - and ultimately the organization. Among other things, it makes performance scaling much harder to achieve, makes software deployments really scary, and makes it almost impossible to migrate to a different database vendor. It is true that it can be done. But I really don't think you want to.
malkia 6y ago
Anyone remembers dBase, Clipper, FoxPro, etc.? 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!
throwaway346346 6y ago
Using a throwaway because I don't want anyone to guess the product/company. 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.
wcpines 6y ago
Derek Sivers did something similar. Related discussion: https://news.ycombinator.com/item?id=21362190 https://news.ycombinator.com/item?id=21362190
aryehof 6y ago
Well it’s come full circle for me. 40 years ago I started as a professional programmer. The big issue at the time was how to represent business logic, other than as stored procedures.
- jpcooper 6y ago
  Why was it an issue back then, and what were the alternatives and why?
rdsubhas 6y ago
This was a trend in 2002. PSP (PLSQL Server Pages), just like ASP and PHP. https://oracle-base.com/articles/9i/plsql-server-pages-9i https://oracle-base.com/articles/9i/plsql-server-pages-9i 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.
dventimi 6y ago
Many commentators seem to be getting hung up on "stored procedures." That's understandable given the title of the original post, but it's also unfortunate because it mixes independent concerns: business-logic in the database, and using stored procedures. You can do the former without the latter, and in my experience it works better: * 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.
bengali 6y ago
How do you guys unit test your business logic located in the SQL code?
sdze 6y ago
What about web applications without using javascript? 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!)
cpursley 6y ago
I'm basically doing the same thing with Hasura, but instead of using their Events webhooks, listening to the WAL for changes with Elixir (I stole the listening code from Supabase realtime: https://github.com/supabase/realtime https://github.com/supabase/realtime). 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).

209 points by steve-chavez 6y ago

corytheboyd 6y ago

How does this work with database replication? Does all “application logic” have to execute on the master database?

kemiller2002 6y ago
Not necessarily. You can make it so that cross replicated databases can function independently and then sync the data together. This of course assumes that you don't have something like an ordering system and you absolutely need to make sure that you don't sell more than your inventory.
- corytheboyd 6y ago
  That makes sense, yeah. I admittedly haven’t studied the pieces of the stack, but maybe something like PostgREST allows routing certain requests to master vs any replica, where you could make the atomic transactions needed for inventory based updates. 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.

mrits 6y ago

I'm not old enough to remember the first one but I know at least Oracle beat you to this idea in the early 90s.

milesvp 6y ago
Oracle really pushed this idea too, because they knew that it would mean more load in the database so you’d need a bigger box with with more cpus. Oracle charged per tiered cpu so their incentives were not aligned with the dev’s. IMHO this is pretty damning reason to generally avoid using the db for this reason. It may be true that a db has lots of spare cycles to utilize but so do your hot spare servers, and I’d hope that you’re not trying to wring performance out of those without thinking long and hard as to what hot spare means. 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.

kemiller2002 6y ago

Really anything can be a good or a bad idea depending on the problem domain and how it's implemented. He doesn't really go into how he made it work too closely, so it's easy to say it was a success. The real interesting thing to understand would be, "Why was it a success versus other approaches?" What really made it a better choice vs other approaches? What was the team make up? He says that it made the code easy to understand. To whom? Interesting the team made it work, but not enough to say, "Sure let's try it without a lot more forethought."

loldot_ 6y ago
Exactly! If you're writing a C compiler, it would probably be a bad idea to have that logic in a database, though it could be fun to try. On the other hand if your application is summarizing a lot data by different dimensions, it would probably be a bad idea to transfer all that data over the network only to summarize it in an application layer. Most applications have a little bit of both though and so will need both an application layer and database layer.

nwsm 6y ago

Nice work; PostgREST seems neat. However, the aim of this stack is simplicity, but it is incompatible with serverless products, which I now vastly prefer for ease of use and lower effort- traits I would consider part of "simplicity". 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.

steve-chavez 6y ago
If you like serverless, Supabase[1] will handle all the PostgreSQL and PostgREST management for you :) [1]: https://supabase.io/beta https://supabase.io/beta
capableweb 6y ago
> However, the aim of this stack is simplicity, but it is incompatible with serverless products, which I now vastly prefer for ease of use and lower effort- traits 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.
- nwsm 6y ago
  I’m talking about the stack as a whole, which happens to be built on PostgREST. 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.
  - nvader 6y ago

FlyingSnake 6y ago

Using a database as a backend comes up time and again and developers are sharpely divided over this concept. I feel that developers who started their careers before the advent of NoSQL DBs/MEAN stack/etc had to go through the rigor of fine tuning their SQL queries and witness the powers (and quirks) of a performant SQL DB. These devs are usually a fan of using DB-as-a-backend. Personally, I am a big fan of PostgREST and I apply it whenever I find a use case that suits it. I find it quite pragmatic and it just shines when it plays to it's strength. Pragmatic developers should took a pragmatic look at such approaches and try to use the powerhouse that are SQL DB engines. 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/ https://samkhawase.com/blog/postgrest/postgrest_introduction...

pjmlp 6y ago
I am on the stored procedure side, and see no value in wasting network bandwidth and client resources for what the RDMS should do itself.
CoreFailure 6y ago
This is a wonderfully in depth tutorial, thank you for taking the time to write this up! I’ve debated using PostgREST for projects before and I think this will push me to use it on my next one.

kevincox 6y ago

The primary issue I have seen with stored procedures is how you update them. I would be curious how they manage that. 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.

capableweb 6y ago
> Generally when releasing new code you want to do a gradual release so that a bad release is mitigated 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.
jandrewrogers 6y ago
Many scaling problems in databases are because the computation is not done in the database. Outside of exotic cases, good database engines are rarely computation-bound in a competently designed system. By pushing computation outside of the database, it tends to increase the consumption of database resources that actually are bottlenecks, typically effective bandwidth (both memory and I/O). 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.
- mattmanser 6y ago
  That's not been my experience at all. Quite the opposite. 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.

marcosdumay 6y ago

The one problem this technique has is that data and software normally have two very different speed and correctness requirements. That means that data and software should evolve following different procedures, what heavily implies that you want different people working at them, or at least you want people to look at them at different times. 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.

capableweb 6y ago
> 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?
- shuntress 6y ago
  "rebuilt from scratch" means "as if the production server just fell into a woodchipper and I'm taking a new one out of the crate now" 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.
taffer 6y ago
For this reason you should always separate data and code and put them into separate schemas. In such a setup, deploying new code essentially means dropping and re-creating the code schema in a single transaction.
- 6y ago

njkleiner 6y ago

Related: https://sive.rs/pg https://sive.rs/pg

_benj 6y ago

In my experience I actually got A LOT more from this approach using postgraphile/Hasura in front of my DB and later moved to dosco/super-graph in order to add custom functionality that was kind of a pain to do on the DB. 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.

BenjieGillam 6y ago
Out of interest (as the PostGraphile maintainer) did you look into https://www.graphile.org/postgraphile/make-extend-schema-plugin/ https://www.graphile.org/postgraphile/make-extend-schema-plu... for extending the PostGraphile schema to do whatever you need, or were you specifically looking to implement the extensions in Go?
- _benj 6y ago
  First, thanks! PostGraphile is an AWESOME piece of software! But yeah, the reason was simply because I wanted to implement custom logic in Go.
  - BenjieGillam 6y ago
    Thanks! Makes sense; best of luck with your future projects!

flerchin 6y ago

Good luck debugging! Seriously. You can't attach a debugger to your DB.

vsareto 6y ago
DBs can have debuggers and breakpoints, but they do not come anywhere close to something like Visual Studio debugging C# code. Of course, many programming languages also do not have a debugging quality-of-life compared to Visual Studio.
- TedDallas 6y ago
  Er ... SQL Server Management Studio is based on Visual Studio shell, and it does a fine job debugging stored procedures. I also code in C# and agree it also works well for that.
bsg75 6y ago
https://www.pgadmin.org/docs/pgadmin4/development/debugger.html https://www.pgadmin.org/docs/pgadmin4/development/debugger.h... https://dbeaver.com/docs/wiki/PGDebugger/ https://dbeaver.com/docs/wiki/PGDebugger/ https://www2.navicat.com/manual/online_manual/en/navicat/mac_manual/DebuggerPGSQL.html https://www2.navicat.com/manual/online_manual/en/navicat/mac... https://www.cybertec-postgresql.com/en/debugging-pl-pgsql-get-stacked-diagnostics/ 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-sql-debugger?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/ssms/scripting/transact...
throwaway_pdp09 6y ago
IME I've never needed one. You get a copy of prod and test against that. SPs are typically short, even when they're long their functionality tends to be well defined, it's never been a problem for me. You certainly can and do add logging, this can be useful.
- mdoms 6y ago
  I don't know what industry you are in or what kind of data your users entrust in you, but "just get a copy of prod" is not an idea that would EVER fly in any organisation I have worked in. Developers do not get access to production customers' financial records, medical data or PII, ever.

throwaway_pdp09 6y ago

> Also typical database uses 96% of the time for logging, locking etc. and 4% percent for actual data operations 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.

taffer 6y ago
> why? while true: select count(*) from huge_table thats why.
- somurzakov 6y ago
  1.if that table is a materialized view with columnstore index then you can run it as many times as you want. 2. always hide postgREST behind API gateway/load balancer/waf/ids+ips/rate limiter and you will be more secure from stuff lile this
- ruslan_talpa 6y ago
  You can setup a view (accessed by postgrest) that returns an error when there are no (correct) filters in the query so that takes care of select *... problem (also you can have the same logic in the proxy (nginx) layer

tacticaldev 6y ago

I'm currently part of a team trying to UNDO this very concept. It sounded great in the beginning, but after years of headaches and wasted cash; we're building the backend in something else. I wasn't part of the original team. Nor was I part of the decision to migrate off of the system. I just know that for my employer, it was a bad decision those many years ago...

gnat 6y ago
Could you be more specific about the problems you're hoping to fix/benefits you're hoping to gain by dragging the backend logic out of the database?
- magicalhippo 6y ago
  We're staying away from stored procs as far as possible. For us the main driver is that it would make it harder to run older versions of our program alongside newer versions. 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.
pjmlp 6y ago
Sure, because wasting network bandwith for going through needless data on the client is such a clever idea.
- sumtechguy 6y ago
  This takes a balance to get right. For some data yes drag it over and keep it on the 'client'. But for other data just join it out and let the SQL handle it. However, for some reason SQL seems to scare a lot of people. So they end up making lots of microservices/functions/methods that just drag the data together and mash it together in a language they understand. That comes at a cost of network bandwidth. 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.

jrochkind1 6y ago

Everything old is new again. This was a common way to write client-server software in the pre-web world, clients talking directly to a central rdbms. 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!

codeulike 6y ago
Yes its funny to see this idea come back. It just needs a snappy name. MoSQL instead of NoSQL, something like that.
sixdimensional 6y ago
I almost can't believe we are talking about this now. I mean that in the sense of, I have built applications every possible way - when you work in a big company and inherit one of everything, you have to do that often. 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.

loldot_ 6y ago

What I've found being the problem with this approach is that sql does not lend itself very well to composability. Sure, you can make functions, views and stored procedures and compose them. But when you start actually composing queries of these parts the could lead to different execution plans having wildly different performance characteristics. Also tooling around SQL, i.e. refactoring tools and debuggers, is not great - if even available at all.

somurzakov 6y ago
sql is not a code, like c#, you dont debug it, you inspect results, or inspect execution plan/traces. 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

gnat 6y ago

I'm very interested in this, as I come from the web world yet find myself in a company that has a 30-year legacy of putting business logic into the database. It served them really well, and that they run quite lean in terms of hardware. But they're built on Microsoft tech ($$$), and moving things to a traditional database=datastore cloud architecture would require a substantial change. Does anyone have resources they can point me to on the wisdom or folly of keeping this logic-in-database approach? It seems like the tradeoffs in the different approaches are all about where the smarts are, and whether you can horizontally scale those smarts. Does that sound accurate?

taffer 6y ago
> It seems like the tradeoffs in the different approaches are all about where the smarts are, and whether you can horizontally scale those smarts. Does that sound accurate? The real question is whether you are going to ship your code to your data or your data to your code.
mattmanser 6y ago
Honestly, I find SQL Server worth the $$$, it's certainly not Oracle $$$$$$$$$ as far as I understand it.

jandrewrogers 6y ago

Back in the mid-90s, this was how many of the high-end web apps were built, using Oracle. Despite its limitations, and Oracle license fees (but back then you were paying for Sun servers anyway), it worked surprisingly well and was a reasonable model for many database-driven web applications. Some of these systems were used for mission-critical global operations handling enormous amounts of money and complex business. It had a lot of advantages versus other web app stacks at the time (commonly a mountain of Perl). 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.

pjmlp 6y ago
I can assure you that still is how many high-end web apps are built today. Just delivered one last month with plenty of T-SQL code in it.
- kemiller2002 6y ago
  Not surprising. Databases are built to be really efficient, and with enough hardware, they can forgive a lot. Mediocre developers can produce a lot of "working" code if they stand on the shoulders of giants. (Not saying you or your team was mediocre, I'm just saying that I've seen really bad code run a lot of business processes. At the end of the day, it worked and the company made money.)
  - pjmlp 6y ago
    Just like I keep seeing plenty of mediocre teams doing for loops over GB of downloaded data and pegging Webserver CPUs for stuff that should have stayed on the DB. 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.
    - 6y ago

mdoms 6y ago

I have worked on an application that heavily used sprocs for business logic. I would not wish it on anyone. It will seem fine (and actually it will seem better - faster and easier to manage) while your project is small. 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.

rishav_sharan 6y ago
Can you elaborate a bit more on duplicated code/composition issue? I would think that functions would ensure that there isn't much duplicated code.
- kqr 6y ago
  I agree. To me this sounds more like bad programming (high coupling and low cohesion) and like it would be a problem even if a separate application acted as the backend.
  - aaomidi 6y ago
    Well some systems make good programming really difficult. Stored procedures, especially back then.... Yeah.
    - throwaway894345 6y ago
      I believe this in the abstract, but would be very interested in some concrete explanation for why sprocs compose worse than other programming languages. How would the OP's situation have improved if instead of sprocs he had a similarly complex webapp and had to deal with the schema changes there? I'm not challenging the OP; I don't have enough experience with sprocs to have an informed opinion, but this information is necessary to make sense of the OP's criticism. 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.

aszen 6y ago

I'm very curious about this pattern, but does the tooling exist to make this feasible across a team. One of the big problems with SQL views is their lack of discoverability. The lack of tooling also plagues SQL based systems, I would miss goto definition, find all references and sensible autocompletion. I am curious why no one has built suitable tooling around all this, because it is a great idea for lot's of scenarios

sql_server_fan 6y ago
Redgate software produce tools for SQL Server that solve some of these problems

notJim 6y ago

See also https://www.slideshare.net/beamrider9/scaling-etsy-what-went-wrong-what-went-right https://www.slideshare.net/beamrider9/scaling-etsy-what-went... https://www.youtube.com/watch?v=eenrfm50mXw https://www.youtube.com/watch?v=eenrfm50mXw

lflux 6y ago

At a previous job I was at 2007-2014, we were doing classified ads[1] we had a lot of logic in stored procedures in our Postgres database written in pl/pgsql. It made sense at the time, but after a while these got really unwieldy and deploying things got tricky, since you we needed to update both appserver code and database stored procs at the same time. There also wasn't a great way to update stored procs at the time - we built this into an RPM that just ran a postinstall script to do an atomic swap. I wouldn't build this into the database were I to build it today. [1] blocket.se/leboncoin.fr/segundamano.(es,mx)

BareNakedCoder 6y ago
At a previous job I was at, we put each release of the sprocs into a different schema. Upgraded app servers called sprocs in schema "r102" while the not-yet-upgraded app servers continued to call sprocs in schema "r101" until they were eventually upgraded.

Brentward 6y ago

Not that this is an important part of the post, but in general parsing the output of ls like the author does in the for loop is a bad idea[1]. 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 or even better as something like find . -name "*.sql" -exec psql -U <user> -h localhost -d <dbname> -f {} \; [1] http://mywiki.wooledge.org/ParsingLs http://mywiki.wooledge.org/ParsingLs

8note 6y ago

How do you handle decoupling your API model from your database model?

ellimilial 6y ago
This won't answer this question in full but perhaps some part of it. 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.
claytonjy 6y ago
Use as many "base" schemas (namespaces) as needed, and only one `api` schema which has only views & functions you want to expose. 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.

jdmoreira 6y ago

Oh I feel like it's 2007 again and I'm a Junior developer writing a frontend in Delphi and the business logic in PL/SQL. Those were the days.

alphanumeric0 6y ago

I personally would not make this choice for new projects. I only use technology that is appropriate for the domain I'm using it in. Although, I am hoping this trend encourages better tooling and more modernization of SQL. 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.

steve-chavez 6y ago
Here's an OSS project that makes heavy use of plpython3u: https://github.com/Delibrium/delibrium-postgrest/blob/master/database/rpc/add_school.sql https://github.com/Delibrium/delibrium-postgrest/blob/master...

dang 6y ago

jgrahamc wrote a fascinating comment about how Cloudflare started this way: https://news.ycombinator.com/item?id=22883548 https://news.ycombinator.com/item?id=22883548

Raidion 6y ago

If I make the change in code, I can roll it out, test it in prod, and then slowly migrate traffic over in very standard and well defined ways. 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.

ComputerGuru 6y ago
I fully agree and treat my databases the same way. However, there’s no reason a stored procedure couldn’t be treated the same way with a random weight determining which version of a stored procedure is run. But I still wouldn’t.
Roboprog 6y ago
Presumably you can revert to a previous tag/label in source control and reload the previous procs/funcs to roll back versions. 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.
chaps 6y ago
Not sure I follow in the context of the post. The post is about a database that can be wiped and effectively recreated from scratch. There wouldn't really be a need for rollback scripts when you can just rerun the entire workflow from scratch. Even easier if you have a hot/warm/cold setup. 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!

LukeShu 6y ago

That's the approach that https://crt.sh https://crt.sh uses. https://www.lukeshu.com/blog/crt-sh-architecture.html https://www.lukeshu.com/blog/crt-sh-architecture.html

revskill 6y ago

I'm using this pattern on my projects for my customers. 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 !

nvader 6y ago
When the only tool you have is a hammer, you end up living in a house made out of hammers.

siscia 6y ago

> Performance hint: developers using cursors should have their hands cut off. This should do the trick. What is wrong with cursor?

unklefolk 6y ago
Performance basically. As SQL is a set based language you can often replace cursors (which are basically loops) with joins and that is orders of magnitude quicker for larger workloads.
- tehjoker 6y ago
  Somehow I managed to avoid learning to use cursors and I have never seemed to need them. It seems like this was some kind of grace.

laudable-logic 6y ago

Maybe sorta off-topic, but does anybody have any stories to tell about successes/fails with the .NET CLR in MSSQL? I worked on a project that made moderate use of this. Worked alright; biggest problem was convincing DBAs/IT to enable it. https://en.m.wikipedia.org/wiki/SQL_CLR https://en.m.wikipedia.org/wiki/SQL_CLR

somurzakov 6y ago
I can't think of an example where CLR integration would be beneficial. 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

g0rbongler 6y ago

No.

chrismsimpson 6y ago

Love it. I am working on a similar but different concept for a prototype I am working on. The project requires many document/NoSQL style micro services, and I had the idea to do this directly with the file system and JSON serialisation, with thread locking on appropriate read/write calls (some calls that are read only don’t require locks). It is extremely fast and performant, and given the way I’ve delineated the block storage that the file system runs on, my hope is it will also scale reasonably well. I have also implemented a message queue using the same design/infrastructure.

astine 6y ago

"“Database should store data not logic!” — well, no. This is called data-base, not data-bag. Databases are specialized engines to deal with, you guessed it, data. And they are extremely efficient at it. Not to mention - majority of the job backends do, is dealing with data. This is good for prototyping. Unfortunately more often than not — prototypes lands on production with updated connection string and debug=False." 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.

RussianCow 6y ago
To add to this, if you need your data to be consistent at all times, it probably makes sense to let the database take care of that instead of managing it at a higher level (and hoping you don't make mistakes). This is where keeping your data-related logic in-DB is really advantageous.
scarmig 6y ago
And even the most conservative use of databases usually relies on them enforcing particular application logic. E.g., foreign keys or non null constraints. Or, to be even more radical, maintaining structured data itself, with separate tables comprised of different columns with different data types.
- richardwhiuk 6y ago
  When you highly scale a database, you normally find you can't rely on the database to maintain foreign key constraints.

overcast 6y ago

Managing business logic in applications is a nightmare to control. That's why things like constraints and unique keys exist database side. If I have triggers that manipulate the data and update another table, you'll know that's always going to happen regardless of what application is writing to it.

RussianCow 6y ago

For those who looked at list item #9 ("no code versioning") and thought the author's makeshift solution to migrations seems awful, I highly recommend sqitch[0], an extremely simple but well thought out migrations tool that is platform- and database-agnostic. I'm using it on a side project with success, and haven't run into any major hurdles yet. [0]: https://sqitch.org/ https://sqitch.org/

claytonjy 6y ago
I'm using this in production for our core Postgres database and am quite happy with it. We've used it to "devops the database" in that all migrations are deployed via CI jobs triggered by git commits. 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.

snicker7 6y ago

This is basically how Q/kdb+ applications work in finance. Data and application exists on the same server. Of course, the programming model is much richer than pure SQL.

hartror 6y ago

Everything old is new again. 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.

dblohm7 6y ago

My previous employer did this, but it was fraught with problems. 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!

qpiox 6y ago

The first web application I did, that went in production 22 years ago, was in it's entirety based on stored procedures. This was the main idea of Oracle's web application services at that time. But, you have a very big tradeoff - you will be stuck with that database. Hardly portable.

cafard 6y ago

I would add that if you have SQL Server, and you want to use it other than in SQL Injection Mode, you more or less have to use sprocs. Anyway, you used to have to--I haven't checked in lately.

sk5t 6y ago
No. Prepared statements since forever.

stmw 6y ago

Is it just me or is this literally the worst architecture idea that's popped up here? I've seen this done in a few large projects, including some extremely famous products, and it produced endless grief for the engineering team - and ultimately the organization. Among other things, it makes performance scaling much harder to achieve, makes software deployments really scary, and makes it almost impossible to migrate to a different database vendor. It is true that it can be done. But I really don't think you want to.

malkia 6y ago

Anyone remembers dBase, Clipper, FoxPro, etc.? 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!

throwaway346346 6y ago

Using a throwaway because I don't want anyone to guess the product/company. 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.

wcpines 6y ago

Derek Sivers did something similar. Related discussion: https://news.ycombinator.com/item?id=21362190 https://news.ycombinator.com/item?id=21362190

aryehof 6y ago

Well it’s come full circle for me. 40 years ago I started as a professional programmer. The big issue at the time was how to represent business logic, other than as stored procedures.

jpcooper 6y ago
Why was it an issue back then, and what were the alternatives and why?

rdsubhas 6y ago

This was a trend in 2002. PSP (PLSQL Server Pages), just like ASP and PHP. https://oracle-base.com/articles/9i/plsql-server-pages-9i https://oracle-base.com/articles/9i/plsql-server-pages-9i 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.

dventimi 6y ago

Many commentators seem to be getting hung up on "stored procedures." That's understandable given the title of the original post, but it's also unfortunate because it mixes independent concerns: business-logic in the database, and using stored procedures. You can do the former without the latter, and in my experience it works better: * 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.

bengali 6y ago

How do you guys unit test your business logic located in the SQL code?

sdze 6y ago

What about web applications without using javascript? 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!)

cpursley 6y ago

I'm basically doing the same thing with Hasura, but instead of using their Events webhooks, listening to the WAL for changes with Elixir (I stole the listening code from Supabase realtime: https://github.com/supabase/realtime https://github.com/supabase/realtime). 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).