PostgreSQL example of self-contained stored procedures

Sql and stored procedures are just a textual source code. You can put it into version control, do code reviews, or build CICD pipelines for sql and stored procedures just like for any other language...

The only challenge is the deployment environment, as databases offer a stateful runtime engine, so you'll want to consider it's state. But state can also be managed in CICD pipelines, with scripting, sql-loader, backups, or similar.

I could see how you could get that impression if you've worked on a project with poorly planned functions, but our experience has actually been the opposite of that. On the contrary, using PostgreSQL functions has allowed our company to be more consistent and declarative with our return data and has helped us eliminate repetitive SQL code. Particularly so when used in conjunction with our RESTful APIs.

Don't forget that in Postgres you can define custom composite return types (i.e., a return type that includes another return type). You can have a Postgres function that returns nested objects like this (I'm using JSON for clarity):

[ "name":"Bob" ,"age":13 ,"address":"123 Main St" ,"cars":[ { "make":"Toyota" "model":"Camry" "license":"NF98549" }, { "make":"Toyota" "model":"Corolla" "license":"NF5649" }, ] ]

The ability to enforce that kind of return type for a given function is incredibly helpful

> It basically takes SQL's biggest issue, query composability, and makes it even worse than it already is.

I think that the bigger problems are ORMs like SQL Alchemy. Where it not only introduces a new layer of abstraction but also empowers engineers to request data from the database in a completely ad hoc "unregulated" manner. In large projects without a strong lead or code review process, it can quickly turn into a spaghetti mess that seriously complicates database re-factors.

When you use functions, you have clearly defined inputs and outputs, better re-usability and much more consistent interactions between the application layer and database layer.

Working there as a software engineer introduced me to a lot of dysfunctional things (as is true of all big tech cos) but pushing as much into the database as possible is one thing that worked beautifully and I fell in love with it. Since leaving Salesforce, I've embraced that pattern in three other projects and I've never regretted it.

It turns out that the database is the best place to work with data. It's verbose but has primitives that your application server simply can't have. And it's beautiful how querying data vs reading a variable have the same amount of cognitive overhead.

That is, the lines between what's in memory and what's coming from disk start to disappear. You just work with data, insert it when you're ready, and don't think about it again.

And the performance gains of not having to serialize data back and forth across the wire unnecessarily is huge.

I'd also be remiss if I didn't point out that if you are a fan of pub/sub patterns then triggers should be your favorite feature to use. They're just transactional pub/sub.

YNAB's web app (YouNeedABudget.com) is architected in a similar manner, in that a lot of logic is performed in Postgres Functions. Rails is the API layer, (which lets Rails do authentication, authorization, and a number of other things that Rails does well), but many of the performance-sensitive calculations and sync algorithm are implemented as Postgres functions.

The SQL is version controlled of course, and the functions are DROPped/Redeclared when they change, so other than being written in PL/pgSQL the development experience is similar to any other deployed web app. We chose to do it not because we expect the wrapping language to change from Rails, but because our data is very relational, our calculation and sync logic is more "easily" and accurately expressed in PL/pgSQL, and we don't have to pay a performance penalty to bring a lot of data across the wire to Ruby, run logic on it, and then ship a lot of data back to the database.

I'm not advocating this as an approach for most web apps, but it's well worked for us. It's important to get your tooling right from the get-go so that the development experience is a good one.

> I've been considering moving some things directly into the database, but I'm nervous about it because the app runs well and I like the data/logic division. Also go seems easier to learn for new developers than SQL, and it's a lot easier to deploy a new app server than it is to reconfigure a database server.

Those are really valid concerns! It's difficult to find developers who are comfortable with _advanced_ SQL, so I believe that is especially true for new developers as you point out. I think it's easier to reason about higher level languages like Go than it is to perform advanced logic in a declarative language like SQL, but at least PL/PGSQL makes that easier with constructs like loops, "if" statements, etc. Although I don't regret the decision in this case, I would have to be presented with a _very_ similar problem to architect a future web app the same way. Otherwise, I'd stick with a language/framework the team is more comfortable with.

I don't understand. Multiple SQL statements executed inside stored procedures are not run any more atomically than multiple SQL statements executed from application layer.