PostgreSQL example of self-contained stored procedures

This is very simple, you wrap all your logic as functions/stored procedures, have the DB do the work. This will work for 99% of businesses out there. How many people really wraparound ID? How many people really need to shard DB? Those 1% will know how to tackle the problem.

For most people by moving all these into the DB, your code is much simpler. Something like this should be encouraged more, it's far easier to implement something like this than deal with ORM.

I wrote some boilerplate tools for easily loading schemata and extensions. This is a must have for being able to write isolated tests.

I keep all data definitions in schemata, which can be managed by standard migration scripts. I keep function definitions in extensions, which do not create any data stores but expose functions with well defined interfaces for querying the underlying data. I rely on standard Postgres extension loading to manage migrations.

I really like this pattern, but there is a degree of unapproachability to it. It takes a lot of effort to make sure tests work well - but it’s not so bad. Since your DB functions do everything, the app code is just a shell around those functions (few lines of code to call each function), so by testing those functions you are exercising their Postgres counterparts.

With tools like postgraphile and Postgrest available, this is becoming a more appealing option. What we need is more tooling for managing deployment, installation, migration, verification of and communication with the stored procedures from within application code. Combined with Postgres PL libraries like plv8, it should be possible to write JavaScript code within your application toolchain that you can test locally, but deploy to run in Postgres context.

In practice though, the logic your application uses is going to generate the same queries anyway. So I’m skeptical that using stored procedures increases the workload of the database.

If you need to scale your database, you can do it with any standard replication strategy, and just make sure to bring the functions to the replica too. If you package them in extensions, this is easy.

To be clear, you’re not answering web requests from the database. There is still an application in between; it’s just much thinner, or can be some abstraction like postgrest or postgraphile. You scale that too.

It seems the main gain for your is to move between languages and allow initial iterations/bugfixes without touching the application code.

The database has more maintenance issues (like rolling txids) than the application code. I am not sure I want to add more complexity and potential issues to the database.

FYI, a materialized view is a potentially long or complex query whose results are cached, so you can say 'select * from complex_query_result' to get them, and refresh the complex_query_result whenever you feel like it. You can also update the query that generates complex_query_result.

In practice, MV can give you speed (as you can refresh the MV when you need/want, while keeping the results) and also put the data-logic inside the database (as the MV is defined initially, and can later be updated) if you don't need super fresh results. If you do, use a regular view.

In either case, you can use the view approach when parameters are needed, iff you can reduce you query to where parameter=something on the view. Otherwise, you need to use languages like pl sql.

As the materialized views queries just return the results to be processed, and I have very little extra to do, your approach seems overkill for my use case.

When your application grows, you'll have a large number of stored procedures. Oracle has packages which significantly eases the way you work with a large amount of stored procedures. Packages enable you to group related stored procedures into one object.

Packages have an interface (stored procedures and custom types available outside the package), and an implementation (package body). You can manage security at the package level, and you can hide the implementation of the stored procedures in the package body.

The postgres alternative, to use schema, isn't very good: a schema is more like a package in java, an oracle package like a class. Doing java with only packages, and no classes feels very limited.