Comment by gregmac | Hacker News Reader

Comment by gregmac | Hacker News Reader

I really just want a deterministic (or sync-based) migration tool. The only two I'm aware of are Innovartis DBGhost and RedGate SQL Compare (though RedGate requires a license everywhere it runs, whereas DBGhost only requires a license to compile the package).

This stems from my work years ago with databases in on-premise products. Customers would modify the database schema, causing migration "up" scripts to fail, and it would be very difficult (and manually intensive) to baseline everything again and get the database back to a working state. Even though modifying the schema was clearly laid out as "not supported", it would still be something we'd have to fix, because ultimately they (and their account reps, etc) still need the product to work.

We used DBGhost, and then had custom pre- and post-deployment scripts to do certain types of changes, such as renaming a column (`if exists old_column { add new column; copy old to new; drop old_column; }`), or adding expensive indexes. One of the best parts is all it stores in source control is all the CREATE scripts for database objects (and the custom pre/post deployment scripts). Pull requests would let you trivially see a new column or index being added.

Compared to the pain of creating and maintaining up/down scripts and the long-term problem where deploying a new instance takes thousands of migration steps (or risking the inital CREATE scripts not matching what happens after all migrations), doing a schema sync was significantly simpler in nearly every respect.

I've been looking for something similar for Postgres and MySQL/MariaDB without any luck, and it really surprises me there's not more interest in doing migrations this way.

At Facebook we discovered this is especially important for large sharded environments. As you add more shards, the chances that a migration will fail on at least one of them (simply due to hardware failure) increases. A declarative approach, where you have a repo of CREATE statements that your tools can diff/sync automatically, is much easier to manage. You can have automation just retry in a loop until the shard converges on the desired final state.

> I've been looking for something similar for Postgres and MySQL/MariaDB without any luck

For MySQL, I wrote an open source tool to do this: http://github.com/skeema/skeema -- it gives you a repo-of-CREATE-statements approach to schema management, like you describe.

Skeema's CLI is inspired by Git (in terms of paradigm for subcommands) and the MySQL client (in terms of option-handling), so if you know how to use those tools, it's a cinch to learn. Basically you `skeema init` to initially populate your CREATE statements (one file per table) from a db instance. You can then change those files locally and run `skeema diff` to view auto-generated DDL, or `skeema push` to actually execute it. Or if you make "out of band" changes directly to the db -- such as renames, or just someone doing something manually for whatever reason -- then you can `skeema pull` to update the files accordingly.

Skeema's configuration supports online schema change tools, service discovery, sharding, various safety options, etc. I also hope to add integration with GitHub API at some point. That can provide a really nice "self service" model for schema management at scale.

(We should coin a term for this. I propose: "idempotent database updates".)

I'm also a strong proponent of idempotent database updates, and prefer those over classic migrations wherever possible.

Some experience from PostgreSQL (with several years of experience in various applications):

While this approach works pretty well for idempotent changes such as "add column if not exists", it is more tricky when data content is changed by a migration. Although seldom, this alone justified classic migrations, which I always had to use in addition to idempotent upgrades. But I try to keep that part as small as possible.

However, the latter issue might be solved by disciplined usage of names. That is, never reuse or "clean up" column names, table names, index names, view names, function names.

A nice fit into idempotent upgrades is "create or replace function" for database functions. However, there is a caveat that you can't replace it if you change the return type. (Changing the argument types is mostly safe, because then it is a different function for PostgreSQL.) You might be tempted to solve this via "drop function if exists" followed by "create function", but then you need "drop ... cascade", which destroys all views (and perhaps indexes!) that depend on it. Again, the correct solution here is to create a new function with a different name. (And drop old one only at the very end, when everything else is switched to the new one.)

One final note: Always put each migration into a database transaction. And for idempotent updates, put the whole thing into a huge transaction. So when anything goes wrong, nothing happened. You can fix your script and simply try again, without having to cleanup any intermediate mess. This is obviously important on production systems, but also very, very handy during development. For the same reason, while writing a classic migration, always put a "ROLLBACK" at the end. Remove it only when you are fully satisfied with the results.

PostgreSQL is especially strong here, because all DDL actions (alter table, etc.) are transaction safe and can easily be rolled back.

One caveat regarding "Always put each migration into a database transaction": I've found that for very large database tables on a live system, it becomes impractical to e.g. create a new index inside a transaction, because the entire table would need to be locked for the duration of the operation.

Is this not what PostgreSQL's MVCC was created to help mitigate?

https://www.postgresql.org/docs/current/static/mvcc-intro.ht...

That's because this entire approach is broken: if one cannot afford to bring database down to do alter table migration, one should always go a lazy migration either via application stack using write-to-new only, read from new on miss, read from old, followed by a backfill or using triggers.

I've found Liquibase [1] to be really good for this, although the docs don't give much of a hint about how to structure your change sets.

If you get it right, it's really easy to manage the DB schema structure and commits to your source repository are very legible (because there's one file per entity being modified), much better than interleaving all changes in a single stream IMO.

Particular highlights:

- Their annotated SQL format allows you to create a file per table that contains a first "changeset" to create the table and subsequent changesets for each alteration

- The "runOnChange" option allows idempotent, non-data-destroying parts of your schema such as view and function definitions to be kept as individual files and modified in place (Liquibase uses a hash of the definition to decide whether to rerun it when migrating)

- The "includeAll" tag lets you collect together the scripts in directories ("/views", "/tables", "/functions", etc.) and have them automatically picked up and run from a single "schema.xml" file in the root.

[1] http://www.liquibase.org

> I've been looking for something similar for Postgres and MySQL/MariaDB without any luck, and it really surprises me there's not more interest in doing migrations this way.

skeema (suggested above by evanelias) and square/shift are two management tools for MySQL/MariaDB.

On the lower level, I'm authoring GitHub's gh-ost, an online schema migration tool, and we have Ruby on Rails + scripts automation around that. Described in https://www.percona.com/live/17/sessions/automating-schema-c... .

I suspect no matter what solution you'd use, you will always need to break migrations into parts that are not fully automated. e.g. when dropping a column you'd first deploy code that ignores said column, only then run the migration to actually drop the column.

Strongly agree that the sync-based approach to schema migrations is much better.

I presented on this exact topic last week at PostgresOpen 2017, and have written a schema diff tool for Postgres that supports this approach (https://github.com/djrobstep/migra).

I'd be curious to know if this is what you are getting at, and if it would solve your problem?

Mysql Workbench has had a GUI ability to do data modeling, forward and reverse engineer it with a live database, and sync your model with your database (showing you deltas and letting you make changes to the model or the database).

I also have used (with mysql) liquibase with preconditions to script db migrations with conditional logic so that deployment can deal with variations in target db environments, conditionally apply ddl, fail midscript while allowing restart and rollback gracefully.

We used thisbat a previous job. I loved it. https://github.com/dbsteward/dbsteward

You define you're schema and it figures out what tonsonbased onbyour current schema.

Can it handle stored procs / UDFs? Was unable to find this in the docs...

Yes. I know I've done it for triggers, let me see if I can find that project.

https://github.com/dbsteward/dbsteward/blob/master/xml/somea... is an example with a function and trigger.

Sql Server with Management Studio can do something like it with the dacpak format. It calculates differences and what needs to be done.

We have a project at work using this dacpak stuff and tbh it's a nightmare to work with since the dacpaks themselves have to manually generated and distributed.

It also appears to make the projects (in visual studio) unbelievably slow.

The way I did was adding a MSBuild script that re-generates the dacpacs for each supported MSSQL version when you compile using a special configuration in Visual Studio (so the dropdown is Debug / Release / RebuildDacpacs).

Then the dacpacs get shipped with the application, along with a zipped SqlPackage.exe (and accompanying libraries) to apply them.

It's been a lot of work and we've had to handle a lot of corner cases through pre-deployment scripts and SqlPackage CLI options, but I haven't seen a 'why does this customer have an [Address] column that's the wrong length and set to nullable???' or 'this report is super slow -> index is missing' ticket since.

I'm with you that it makes the solution slower, but you can just selectively not load it, or use the new light loading feature.

You most certainly do not have to make them manually, that can be delegated to a build server. You can also use the dacpac to automatically run integration test and it can run some rudimentary upgrade tests vs previous versions of the dacpac.

I think Navicat will do that as well

Reply on news.ycombinator.com