Pretty much how we built client-server applications on Oracle RDBMS 25 years ago.

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.

I think the more accurate translation into modern Postgresql would be to develop as an extension. This should more or less cover same functionalities.

One downside is that you need to be careful around upgrade procedures and backup because postgresql extensions weren't initially developed for persistent data. So you have to declare clearly in the extension definition which table should be backed up and kept when updating the extension.

Also if you manage your repo wisely extensions are a great way to version control. I'm currently toying around with a mostly sql app delivered as an extension and so far it's working pretty nicely. But it's a side project and it's not really ready for prime time.

This was my introduction to server-side web programming as a kid; I was helping develop a very early online store (Australia, 94/95) for a local tabletop gaming retailer in Oracle 7 (IIRC). As well as being my first foray into web programming, it was the first time I got my hands on a grown-up RDBMS, Windows NT (still 3.5.1), and Java (I wrote a tetris knockoff as an applet for the site also). I was very surprised to find that my experience writing ADA was actually going to be useful out in the real world :)

Now I feel old :)

Hey Derek. For tests, have you tried pgTAP[1]?

Having all the db tests in SQL is very convenient and I think pgTAP could simplify your API tests.

Here are some examples of how testing with pgTAP looks like:

https://github.com/steve-chavez/socnet/blob/master/tests/fri...

(Loading schema/fixtures can be done with a `\i file.sql`)

[1]: https://pgtap.org/

I read this a few days ago. I was waiting for it to show on HN because I have a few questions.

I want to do the same, but with big databases (the TXID wraparound is a problem every few months). For now, the queries are stored in the code. They rarely evolve.

How do you manage versioning? Queries in code mean you can ensure everything match.

How easily other people interact with that? Not many developers know SQL well. Poor SQL skills can slow down a server for other users.

What did you gain in practice? It is nice, like CI/CD or version control, but I see little gains for something that does not change very often.

Currently, most of the queries are materialized views that can be refreshed separately. It seems simpler to me.

In practice, how is this different from refreshing materialized views?

Versioning: sorry I'm not sure what you mean. Same as if you had queries in an ORM and views using the results, you'd need to branch and make changes, then merge the branch together at once. I just do the same. If the database structure changes, I run the ALTER TABLE commands, when switching to that branch.

Other people : I guess like anyone choosing a language, you're excluding those who don't know it. I'm assuming more know SQL than Rust or Elixir or whatever. And more should.

Gain in practice : Two best improvements were:

(1) Having all data and data-logic (which might also be business-logic) in the same place. No worrying that some external code somewhere might have old data/business logic in it. One definitive source. Like Rich Hickey's classic "Simple/Complex Hard/Easy" talk - https://www.youtube.com/watch?v=rI8tNMsozo0 - I like that this is simple, un-complex. The data and external code don't need to be braided/complected together to work.

(2) The freedom do switch my external code from Ruby to Go or Elixir or whatever, and not have to rewrite all that functionality. It's all just simple API calls.

Sorry I haven't looked into materialized views yet, so I don't know how this compares.

Thanks a lot for your reply!

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.

Unless it changed very recently be aware that you can't update sql definition of a materialized as easily as you would update a view. There is currently no "CREATE OR REPLACE" option so any dependencie build onto a materialized view can quickly become a real pain (been there). Sometime, it might be easier to stick to the old trick of a table updated by a refresher function (possibly called as a trigger).

Dependencies are a general problem, like changing the type of a table that has dependent views. It's a good idea if your database update scripts/migration software can handle something like this. Other databases don't tend to be as strict as Postgres here (I only found out that some rarely-accessed views never quite worked after migrating from Oracle)

I've had good luck with these functions: https://gist.github.com/mateuszwenus/11187288

Allows you to save-and-drop dependent views (materialized or regular) and then restore them after your updates.

Deeply agree about dependencies being a general problem.

Thanks for the script that look pretty clever #bookmarking. Like particularly the approach of "drop what you saved, no more no less". DROP CASCADE is simpler but can have undetected side effect, if this script fail to backup all dependency, logically you will get an error when attempting to delete target and that rocks.

Personally I have update scripts for 'version control' of the MV and its dependancies, it is not very painful to do:

begin; drop ... cascade; create ...; commit;

You just talked about TXID wraparound, materialized views and then talking about not many developers know SQL well. You're all over the place with your questions.

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.

im curious - why not insert from table value list instead of multiple insert statements in your fixtures population? Does this not work well in Postgres?

Only because sometimes I edit them directly, and remove columns or data there with vim.