Need way to show merged status of schema

[stephen-kruger]

trafficstars

We recently had a nasty issue where someone dropped a prod table because they ran the migration file directly (another discussion) instead of using sql-migrate.

But when it came time to re-create the table, they simply found a migration that had the CREATE TABLE and used that schema. Obviously there is no guarantee that later migrations don't further update that schema. I couldn't find an easy way to suggest how to identify what the schema should look like, despite having spent months on this versioning work.

So, would there be any support for introducing a new option like : sql-migrate --show

which would print out a valid schema for a particular table at some point in the versioning life-cycle?

I.t.o. implementing such a function, I imagine it would require creating a tmp table&db and running against that, and then extracting the final schema, cleaning it up and printing the output.

Any thoughts appreciated.

[rubenv]

Creating temporary databases/tables etc is hard (you need the right permissions, which you generally don't want to give in production). Also you'd need to rewrite all queries to work against those.

That's going to be a lot of work, so my guess (though I welcome other opinions) is that this will be slightly out of scope for this library, which I like to keep simple.

But it doesn't have to be part of sql-migrate to have it: it's perfectly possible to write this as a separate tool, which uses sql-migrate.

My best advice is to lock down production as much as possible. Sadly there's always going to be a case of someone doing this. Enforcing the process (through migrations) is the only way to guarantee correctness.