Comment by evanelias | Hacker News Reader

Comment by evanelias | Hacker News Reader

Parent Full thread

While I agree with the overall negative opinion of overzealous stored procedure usage, to echo your last sentence, I've come to realize many of these problems are completely solvable with tooling. A personal anecdote, and please excuse the self-plug:

I'm the author of an infrastructure-as-code tool for MySQL/MariaDB schema management, Skeema [1]. Basically it allows you to store CREATE statements in a repo, and you can diff/push/pull between the repo and live DB environments. Originally the tool only supported tables, as my personal MySQL experience skews towards social networking companies that banned stored procs outright.

Earlier this year a company generously sponsored development of stored proc/func support in Skeema. And although I've long been a stored proc skeptic, I have to say my opinion has softened considerably after building this functionality. It essentially solves the first 3 bullets you've listed here: allowing storage of CREATE PROCEDURE statements in a git repo; ability to diff the current state of the repo against any live db environment; ability to push the current repo state to any live db environment. And the 4th bullet just depends on your IDE's support for different SQL / T-SQL / PLSQL dialects.

I still have some scalability and maintainability concerns around extensive use of stored procs (especially in MySQL/MariaDB), but nonetheless found this experience to be unexpectedly eye-opening. My perspective went from "stored procs are an operational nightmare, avoid" to "huh these are actually quite useful and totally manageable given a solid development/deployment story."

[1] https://www.skeema.io

Intriguing product - and amazing work! How do you handle column renames and more complicated data migrations (e.g. split "full_name" on whitespace)? Django's migration framework handles these by having you check into your VCS each "diff" in the migration DAG, and providing enough metadata to know how to go forward and back, but this also means that to change VCS branches you need to roll back migrations in a highly counterintuitive way. If there was a way to have a checked-in declarative schema, with metadata to indicate how data migrations should work so you can just `git checkout; skeema push`, it would be a gamechanger.

Thanks! Great questions -- unfortunately the short answer to both currently is "not supported yet" :) But there's reasoning behind it being on the back-burner in both cases; in some older threads I've discussed the lack of renames [1] and data migrations [2][3].

[1] https://news.ycombinator.com/item?id=19882611

[2] https://news.ycombinator.com/item?id=20873608

[3] https://news.ycombinator.com/item?id=19882862

Tooling on databases has been traditionally awful, and I think that has a lot to do with folks' negative experiences with the database taking up more architectural space.

Schema migration is hard. Skeema looks cool. Aquameta's idea with schema migration is to treat the schema as data, so that each table is a meta.table table, each view is a row in the meta.view table, each column... etc. So when version control checks out a new version of the project, if columns say were added, they're just inserted at runtime. Checkout prioritizes meta entities so they happen first and in a sensible order [1] so the migration happens first. This does technically work for all scenarios (I think!), but in the case of say a column rename, it would naively delete all the values and then have to re-set them per row, which could be a big inefficiency. It would still work just fine for smaller tables though. Alternately some kind of optional migration script per commit was another idea.

We’ve built something very similar for PL/pgSQL. It integrates with Gradle so we even have transitive dependencies on different schemas. It has completely changed our approach to software development - development is faster and the application is literally an order of magnitude faster.

The key was to build the tool up front. Without the tool we couldn’t have done it.

Reply on news.ycombinator.com