I was wondering when there would be a technical article here. Dope. :)

Thanks for sharing Derek. I like the idea. I've used something similar before...sort of a domain-specific dialect of SQL. I'm curious to see how it works out for you. I'm guessing it'll work well. And it's great that you get the higher level language constructs from any environment that interacts with Pg or ODBC. It strikes me that this was pretty common early on in my career...but, towards the end, this sort of logic was always in an ORM within a specific language (though I've run into some language-agnostic ORMs along the way as well.)

I like your approach. Wondering how you are handling testing....should that live in the database layer or in whatever impl language you use? Would be nice to keep that in-DB as well.
There are two unit test suites in there. Any language will do to call the database function and assert the response you expect. Then inbetween each test, drop and rebuild the fixtures. Works great for me. I've been doing all my projects this way for 5+ years now, and love it. — Derek

I've some something similar to this with SQL Server. I really like how it turned out.

I have always been interested in PostgresQL but the tooling isn't as nice (like static typing, SSDT, etc) so that has been a hurdle for me. Do you use any tools with Postgres?

Piggybacking on [3] Jon's question - do you use a graphical front end? I am currently using HeidiSQL with MariaDB and - as much as I love the command line - I find it much more productive to have a nice GUI interface for DB work.

Have you checked out https://hasura.io?

It gives you automatic Rest and GraphQL CRUD functionality out of the box on top of Postgres. Additionally, Hasura has has hooks for database events and GraphQL Subscriptions (websockets). Really powerful stuff, as it allows you to leverage Postgres without having to write boilerplate API application level code.
Thanks for posting that. — Derek

This is great thanks for sharing. I know you mentioned people might think this is stupid, I certainly don’t. Many people advocate ORMs as a way of generating database schemas and then keep all the logic in the application layer and think it best practice.

It certainly works, and if you have a shop with a lot of application developers and no one who knows SQL then it makes sense... at first, but there are compromises.

The compromises are that nobody understands the database or how to tune it for performance and then people treat the database like a black magic box and wrap it in cotton to protect it. Performance issues are bound to happen, the most common in ORM environments is the N+1 query issue.

What you’ve done is the complete reverse. With stored procedures and full tests you can verify performance and correctness at the database layer, and while that implies some future extending working in that same layer it means reaping the rewards of stored procedures, namely less IO, compiled execution plan reuse, and an abstraction layer to the application layer that is database-first allowing for better performance. incidentally have you tried pg-tap, it’s a great testing framework for Postgres. I’ve used it with the Postgres docker. That docket includes a hook to run whatever you like, I used it to generate mock Data and launch the tests which means your database CI can be self contained.
Yes! Well-put. Thanks Davos. And yeah, to me, the database is the real core of everything. All other code may come and go but the database is the most important. (As for pg-tap, yep!) — Derek

There's a lot of auto rest services out there for Postgres. Here's another one that I've been following for a long time:

https://postgrest.org/

Once again. The main thing that has hindered me from using it is not understanding the tooling in the Postgres world.

well done. nice to read. a question - why not using named parameters in stored procedures instead using $1, ...
I tried it but just preferred $1, $2, etc — Derek

Your use case is simple enough so that it may be a good idea, but for anything a little more complex:

- writing logic in an imperative language (node/php/ruby...) is easier.
- I would rather debug ruby than a stored procedure / function.

I read only a few of your blogs and I m mighty impressed.