Database versioning best practices
I mostly agree with this but with one (and a half) modifications and one addition.<p>Instead of storing a version as an integer, I strongly prefer <i>naming</i> each migration and storing the applied migrations in a database table.<p>Rather than relying on a single integer, I can simply write code that applies all the migrations which haven't yet been applied. This makes it much easier for the database to be modified in multiple concurrent branches without any merge pain.<p>At work we follow this practice and name our migrations something like "1-add-foo-table". If another dev makes a branch with a migration named "1-add-bar-table" then there's no conflict.<p>We also store our migrations as a directory rather than a single script. This lets us split things up into multiple files if needed. We also allow for both SQL and Perl (our main language) migration scripts in the directory, which is handy.<p>Finally, all migrations must be idempotent. In theory no migration should ever be run against the same database twice, but making our migrations idempotent is a little extra insurance.<p>For the curious, I wrote some Perl modules that help manage a system of migrations like what I just described:<p>* <a href="https://metacpan.org/release/Database-Migrator" rel="nofollow">https://metacpan.org/release/Database-Migrator</a><p>* <a href="https://metacpan.org/release/Database-Migrator-mysql" rel="nofollow">https://metacpan.org/release/Database-Migrator-mysql</a><p>* <a href="https://metacpan.org/release/Database-Migrator-Pg" rel="nofollow">https://metacpan.org/release/Database-Migrator-Pg</a><p>The system is designed to be extensible so you can build on top of it, rather than a fully encapsulated tool.<p>There's also Sqitch (<a href="http://sqitch.org/" rel="nofollow">http://sqitch.org/</a>). It's written in Perl but is entirely language-agnostic to the best of my knowledge.