Better Database Migrations in Postgres

I'm using knex too, and enjoy the workflow. The built-in primitives for creating and modifying tables work nicely. It gets a little messy when you need to go off-script and `CREATE TRIGGER`s, etc, but I think that knex's approach is still predictable and reliable.

You can read the explanation: https://www.postgresql.org/docs/9.5/static/sql-createindex.h...

(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.

I'm one of those who disliked pgAdmin3, at least on Mac. It crashed reliably if the database didn't disconnect cleanly and you tried to continue using the program. It crashed randomly when using SSH tunneling. It crashed randomly when resuming from sleep. It crashed reliably when you clicked on objects that no longer exist (ie something outside of pgAdmin deleted a table, then you click on that table in pgAdmin). The window would be lost in the nether if I moved it to a secondary monitor and then unplugged said monitor. The query editor was pretty mediocre. That's just the ones I can remember.

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.