Evolving a database schema with DB migrations
- TODO: Align the narrative with the concept of simplicity & consistency
Managing database schemas can be a pain:
Many projects still rely on manually applied sql scripts. And sometimes not even that (a quick sql statement here or there to fix a problem). And soon, many questions arise:
- What state is the database in on this machine?
- Has this script already been applied or not?
- Has the quick fix in production been applied in test afterwards?
- How do you set up a new database instance?
More often than not, the answer to these questions is: We don't know.
Flyway (Why database migrations?)
You can resolve such questions with a database migration tool. The tool expects a list of schema updates, called migrations. When you run the tool, it applies the migrations1 to the specified target database.
There are tens of migration tools to choose from3. In API-based tools, you write migrations against a language-specific API. In SQL-based tools you write migrations in plain SQL. Some tools offer advanced features, such as repeatable transactions and placeholders. When choosing, consider database-specific edge-cases and tool's maturity.
- Applying migrations is an idempotent operation, that is, you can run them repeatedly regardless of the current version of the schema.↩
- The dev/prod parity factor, the build-release-run factor , and the one-off admin process factor.↩
- For example, for Python there's alembic; for NodeJS there's node-sqlite, knexjs, pg-migrate, db-migrate, and umzug; for Ruby there's active record, for Java there's flyway. The SQL-based family includes flyway, liquibase, and sqitch.↩
- Flyway has a rich feature set to cover edge cases. For complex applications, follow recommended practices for designing migrations.↩
If you need help with building the tech products get in touch.