dbmigrate

README

dbmigrate

dbmigrate is of beta quality and may have it APIs or functionality changed without notice.

dbmigrate is a CLI utility and library for migrating relational database schemas, inspired partly by RedGate's freemium product FlyWay. dbmigrate does not aim to be a replacement for FlyWay, but rather a simple alternative with a limited set of feature that covers limited use cases.

Why Database Migrations are Necessary

A database migration is one or more SQL schema transformations that change a relational database's schema into a different state. When new information needs to be stored in a database, a migration changes or creates the SQL data structures for storing the data. A migration tool might seem like a convenience for a small or fixed number of database instances. Connecting to a small number of database instances and running a SQL query is simple and easy. However, when the number of database instances is large or variable, the manual process increases overhead, causes bugs, and increase risk that a simple mistake causes data loss.

Moreover, because any manual process is more error-prone than an automated process, all manually migrated database instances may not have the same schema, which becomes a problem for creating a new database instance. For a new database instance to be of any use, it must be initialized with a schema and static data. The schema of the new instanced, regardless of whether its creation is automated, is effectively manually applied to the database because it is derived from a manual process. This new database instance may receive a copy of a schema, but the schema does not exist. There is no authoritative version of the schema that can be relied on. In this scenario the problem can be resolved with a production copy of the schema and static data, which still does not solve other issues. This manual process also is impossible for software systems where the software engineer does not have access to the database.

A database schema migration tool provides a way to incrementally apply database changes over the course of development with automation. SQL scripts capture the database changes and are run on database instances where they have not already been applied. New database instances have all the migrations applied or if there is a baseline the migrations are applied after the most current baseline. There is no need to track which database instances have which schema changes. It is the application code that houses the migrations, which are applied either as a separate application deployment step or as part of the application bootstrap process. The schema changes are just normal code changes that are reviewed and checked into a version control system.

Usage

dbmigrate can be used either as a CLI utility or a library.

Command Line Utility

dbmigrate [flags] <dbname> <db-connection-str> <migration-dir>

See dbmigrate -h for a complete list of flags.

Using the CLI version instead of the library, means one does not have to include migration logic in deployed services. Useful in cases where migrations are designed as a separate step in the application deployment process.

Library

dbmigrate can be used as a library, which has the benefit of more control over the database connection used for performing the migration. There might be a need to include database migration in the application bootstrapping process.

Writing Database Migrations

Database migrations are essentially a transformation of a database schema from one state to another. Migrations can either go forward, moving a migration from an older version to a newer version or can be reverted, going to an older version. dbmigrate does not support reverting migrations, but rather a new migration should be made that is effectively reverts a migration.

Migrations are only SQL scripts that are applied in order. The files must be named with the following format:

B|V<number>__<string>.sql

where B denotes a baseline and V is a regular migration.

Examples:

B1__initial_schema.sql
V2__user_table.sql
V3__roles_table.sql
..
[1000 more migrations..]
B10003_new_baseline.sql

Creating Baseline Migrations

A baseline migration is a migration that includes all the migrations up to the current version of the database schema. dbmigrate does not create baseline migrations, but will apply the most current baseline for new databases. Existing databases cannot receive baselines because baselines are intended to include all schema changes up to a particular version.

So long as no migrations are made that use data that unique to an instance of the database, a baseline may be created by using the database's schema export tool after running all migrations on a new instance of the database. The output of the schema export may be good enough for baselines migration, but may need additional modifications to add static data that the migrations added.

Migration Construction Advice

1. Create baselines to save development time when the migrations becomes slow.

2. Do NOT write migrations that performs insertions based on data that may be specific to a particular instance of the database. This kind of migration is not a migration, but rather should be a hotfix because changing application data means there was a logic bug that should be fixed via a change in the application code.

3. Do NOT delete migrations unless one of these statements are true:

   • A baseline has been made and the migration to be deleted is before the baseline and there are no existing instances of the database that were created before the baseline. New databases use the baseline while older databases ignore the baseline. Existing database need the entire migration history to enforce failures on migrations that were already executed, but were changed.

   • The migration has not made it to a production system, and it is okay to re-build other systems. If the migration only exists in non-production systems then the migration can be deleted so long as these other systems can be re-built with the set of migrations with the missing migration.

4. Prefer SQL scripts than code because the goal is to keep migration scripts simple. There is nothing more simple for a database migration than an SQL script. dbmigrate only supports sql scripts. Avoid generating these scripts.

5. Do NOT modify previous migrations that might have already been applied to existing databases. Migrations will fail if an already applied migration has changed.

Limitations and Design Decisions

• Migrations are only for moving forward. There is no intention to implement backward migration.
• Relational databases are only supported.
• A limited set of relational databases are supported, adding more is fairly simple.
• The schema of the migration history table does not use date data types because the schema must also be valid for database types where date data types do not exist, such as SQLite. Multiple schemas could be written to use better choices for data types, but currently simplicity is more important.
• Testing currently only covers SQLite databases.
• Speed is a high priority, but a database migration is by nature a linear process. The overhead of migration should not significantly add time to the SQL statements.
• Migrations should be ideally run when no other database connections are present. Database locking is not performed because locking is implementation dependent.
• Migrations via the CLI are only support with SQL scripts. Other sources can be added by writing scripts that provide the migrations via the filesystem and then running the CLI tool.
• Migration scripts are not parsed or examined.

License

dbmigrate is licensed under the AGPL.