Hacker News Reader

> Making matters worse, changes to stored procedures don't show up in git, and can break some regression-testing tools, making it very difficult to track changes and hunt down bugs.

If stores procedures become part of the system, they belong into the SCM as well. The deployment process has to include the stored procedures as well. Different to tables and other constructs that have state, stored procedures can be easily migrated.

In general I agree that stored procedures massively underdeliver in many aspects, but not properly treating the database as the application server it has become is part of the problem.

Every horror you've just described can happen up in the app layer as well.

    The problem is SQL is not a programming language and the only way to 
    accomplish some things directly in the database is by creating solutions 
    with O(n^2) or worse running-time. Our system has queries with O(n^4) 
    time complexity.

Two observations here.

1. The approach being espoused is not, "Move everything down into the database layer at all costs, no exceptions." It's more like, "Many things are simpler and faster at the database layer."

2. These O(n^2) (and n^3, n^4, whatever) horrors are at least as common at the app layer. I have dealt with my fair share at every possible level of the stack.

    I had to untangle a query that was over 3,100 lines long with 18 sub-queries

I just want to say: ouch. I am sorry!

You say "could happen" he says "must happen". SQL won't be able to lower the complexity needed. But in the app level it is just a matter of being good programmer or under good review process.

Datomic solves this tension by letting you upload Clojure code into the database to run as (basically) a stored procedure. So there isn’t a tension between programming language and query language, there’s no network between them, they compose directly and are interchangeable. Do what makes your code simplest. Program your database with the usual data structures, if-statements, function composition, the standard library you’re used to, and share library functions between your backend and database at will (even compile them to ClojureScript and run them in the UI!)

Yup. Same with Neo4j. You can write your stored procedures in Java ( or any JVM) language. Import any library you want, go crazy.

Same with MSSQL. You can write stored procedures in C#

I wasn't clear enough. in Datomic, you upload your entire application code (web server and all) into the database and run it from the same JVM cluster as the database in the same memory space as the database. There is no opportunity for dichotomy between application logic and database logic because the database is serving the application (or the application is serving the database...this is now just a matter of perspective)

Yes, PostgreSQL does this too, you can use JVM/Python/Node etc. But it's not really geared towards being your app server like Datomic is.

If you could refactor the code into smaller and faster SQL, that means the code was badly written, not that SQL (and stored procedure language) itself is bad.

Also almost every algorithm can be written in PLSQL if needed, so yuu can surely improve on the O(n^4) time complexity.

I agree with the procedure change tracking, that's much harder, but server side programs can be extremely responsive (low latency) and powerful.

He never said SQL was bad, he said use it to query data because that's what it is good at.

I was using it and PL/pgSQL together to modify data and although I had to think differently, working in the relational space even with modifying data is awsome (of course you have to use cursors).

It always helps to consider the context. This is a personal website for Derek. You can do all kinds of crazy stuff and get away with it in this context, because you don't have to work with others.

If you're working on a big project which requires a team of people (or may grow into a team), then yeah, don't do this. Keep your systems as idiomatic, unsurprising, and consistent as possible, unless you have a very good reason not to. The less cognitive load, the easier and faster it is for others to help without making mistakes.

I do this and I rarely have a stored procedure that's bigger than 25 LOC. Most are around ~5 LOC and look like this: https://gist.github.com/ericbets/a6a82041a9fd9181ee52ac0d346...

> Making matters worse, changes to stored procedures don't show up in git

This is not necessarily true. It depends on your environment. You can track changes to db with the right tools.

Rest of your points I completely agree. Don't put unnecessary complexity in your db. It's a database, not a code repo.

I don't like migrations, it's like doing version control with dated copies of files.

I wish there were (perhaps I'll be told there is) a migration system that used a single file per table, and was git-aware so that when the live schema didn't match the checked out schema file, it would determine a migration from the historical version that it does match.

Alas, I don't think it's possible, because such a file would describe a state, not, as migration files do, transitions between states. It's tempting to imagine that it's possible to robustly infer transitions from one to another, but I've used Django.

- Liquibase expects the scemea to be modeled in its schema language. This makes it possible to compute changes between the database and the new schema and apply those changes, or to just create old-fashioned migration scripts.

- Th. Doctrine and Hibernate ORMs can do similar things.

- Flyway expects developers to write migrations by themselves, either in Java or in SQL, and just executes them and records state information. It's not too bad to write migrations yourself because the data you work with is usually valuable.

Yes, they can do similar things. However, there are several cases which you cannot handle automatically, due to incomplete information:

- The current schema has a column A and the new version does not have A but a similar column B. Is this a new column? Or should A be renamed into B? (same for renaming tables) - You add a new table A which contains columns from B, which are also removed from B. Should this data be copied? - You change the structure of a table, and have some of it split off into different tables. How does the schema tool know how to rearrange the current data?

There are probably some more cases, but these are the ones I could think of quickly.

While I think what you want is understandable - it's also extremely dangerous. You'd no longer be absolutely certain you're executing the same migration in production as on the testing environments.

That's exactly why I think Django's migrations are insanely good. Simple ones are autogenerated and everything else is possible as well, with either python code or direct sql queries

Stored procedures MUST be stored in the VCS. On every upgrade, the older set of procedures MUST be fully deleted and the new set of procedures MUST be installed.

Otherwise, you have just created a huge hole in your buuild reproducibility. SQL (including pl/SQL) is a programming language, therefore treat it like a programming language - keep all of it in a versioning system and ensure that you can trace every piece of code that runs inside your database to a piece of code in your VCS.

Making matters worse, changes to stored procedures don't show up in git...

They do if you use database migrations.

Isn't PL/SQL a full fledged programming language?