Sqldef: Idempotent schema management tool for MySQL, PostgreSQL, SQLite (opens in new tab)

I built this last summer and thought it was fairly complete. And it turns out I was naive. 6 months later, I have resolved ~100 issues reported by the users.

`ALTER TABLE books ADD CONSTRAINT fk_books_author FOREIGN KEY (author_id) REFERENCES authors (id)`

Which is not valid in SQLite (https://www.sqlite.org/lang_altertable.html)

For example add temporarily nullable column to a large table, deploy new code which starts writing to the new column, in background populate that column for existing rows in batches and finally alter column to be mandatory non-nullable.

Another example of non-trivial schema management case is to make schema change after new version rollout completes: simple migration at the start of the container can't do that.

It must be a solved problem, but I didn't see a good tool for it which would allow expressing these imperative changes in a declarative way which can be comitted and reviewed and tested along the app code. It is always bunch of adhoc ugly scripts on a side and some hand waving deployment instructions.

Also means I can stop with my hobby project that was supposed to do the same. Wasn't far along and haven't worked on it in months anyway.

So I'll spend my time on another hobby project then that also solves something that is already solved 100 times over, but I don't like the other solutions (simple log monitoring including systemd and sending error emails if something is found).

When using Postgres, I just try to keep my schema script idempotent and also work with any version of the DB. Like I start with a CREATE TABLE IF NOT EXISTS, then if I add a new col, it goes into there and also a separate ALTER. But at some point it gets cluttered and I delete the ALTERs once things are stable. Maybe something could hit the fan, I have to restore an old backup from before the last purge, and this tool helps me make it compatible quickly.

I get the declarative nature of this, but migration of schemas is not purely declarative, particular on large production databases. An ideal schema manager would understand the costs of particular migrations (perhaps by using the table stats and EXPLAIN) and apply that back to the migration strategies so that downtime is minimized/eliminated.

Adding or remove columns or indexes can trigger major database table scans and other problems, especially when partitioning conditions change.

We used Sybase SQLAnywhere, and a complication there was that if you had materialized views against a table, you had to drop and recreate that view when adding or removing columns in the table. And when you recreate it, you of course have to remember to recreate the indexes on that materialized view.

Tracking this in case you have multiple materialized views touching multiple tables became a bit tricky, and you don't want to do the "dumb" thing to just always drop and recreate, or per-column, since some of them might take an hour or so to recreate and reindex.

The tool has some built-in safeties like never dropping columns if it's missing (have to add explicit element for that in the XML), and only performs safe column definition changes, ie integer to varchar(50) is safe, integer to varchar(3) is not, etc.

It really made database migrations very painless for us, great since we've have hundreds of on-premise installations. Just modify the XML, and let the tool do its job.

And since its our tool, we can add functionality when we need to.

Schema management is only a small part of the problem, and I don’t think this tool handles data migrations. E.g. if I reshape a JSONB column into something more structured, I don’t think it would be able to handle that. Or if I drop a column the backwards migration it generates ADD COLUMN … NOT NULL, which is obviously unusable if the table has any data in it already.

But anyway, from the example for sqlite, I added "x integer not null," to the original table, and was greeted with "-- Skipped: ALTER TABLE books DROP COLUMN x". Then ticked the "Enable DROP" and got the same, except with the line uncommented.

Such a shame, this is one thing that would make a lot of difference. FWIW, the standard way of doing this is creating a temporary table with the new data, dropping the original table, and recreating the new table, copying the data across and deleting the temporary. It's kind of a shame that it doesn't automate that, and this is one of those things that's incredibly easy to mess up if you're not careful, especially if there are constraints involved.

If it just does the easy stuff, you might as well just do it by hand.

https://david.rothlis.net/declarative-schema-migration-for-s...

Isn't it much quicker to write a one line migration vs copying the DDL, then adapting it to the desired state and then getting getting the migrations from that tool? Or am I misunderstanding something?

i like the following about it 1. database schema is regular code 2. make schema change declaratively 3. packaging (.daspac) and deployement script

most open source tools , seem to be after the fact tools, that do diffs ms db project, handle the code from the start in a declarative, source code managed way

Found shmig and it’s been really fantastic.

https://github.com/mbucc/shmig

https://github.com/sqg-dev/sqg/

e.g. CREATE TABLE books2 (LIKE book INCLUDING ALL);

So I assume it is not Progresql DDL it uses but "something close".