Migrations

TrailBase provides a schema migration system to coordinate changes across different environments. For example, adding a new table or column may need to happen consistently across multiple environments like dev, CI, pre-prod and production.

API Implications

Schema changes to TABLEs or VIEWs exposed as record APIs, will also change the API definition, which requires special attention and may require a well orchestrated rollout.

For example, adding a new NOT NULL column without DEFAULT, will require all clients to start providing a corresponding value during record creation. Similarly, removing a NOT NULL column will break clients that critically expect a corresponding value when reading records.

Creating a Migration

On the surface, migrations are simply files containing SQL statements in traildepot/migrations/ following a naming scheme of U<timestamp>__<name>.sql. The quickest way to create a new empty migration is: trail migration, which will create an empty file with the current timestamp. Afterwards you can rename the file and add your SQL, e.g.: CREATE TABLE new_table (id INTEGER PRIMARY KEY) STRICT. Once satisfied, migrations can be applied either by restarting TrailBase or sending a SIGHUP signal to a running instance.

Alternatively, altering the schema via the table explorer in the admin UI will generate migrations for you and instantly apply them.

Append Only

Migrations represent a strict progression of the schema evolution. Meaning, once a migration has been applied, the migration file may no longer be removed w/o invalidating the database state. This ensures that all environments will eventually have the same progression. Think of migrations as append-only.

If you’re playing around with migrations in your local dev environment, the quickest way to get rid of a failed experiment is to stop TrailBase and delete traildepot/data/main.db. The migration state is tracked in the main._schema_history table.

Rolling Out Schema Changes to Clients

Schema changes to TABLES or VIEWs exposed as APIs will automatically change the API definition and thus require special diligence. Especially client-server divergence over columns with NOT NULL constraints can lead to breaking API changes. For example, adding a new NOT NULL column w/o a DEFAULT requires all clients going forward to provide said value, otherwise the server will reject the call. Consequently, some API toolkits like protobuf strongly advice against or even disallow required fields. In other words, adding and removing nullable columns is a lot simpler and safer.

It is your responsibility to ensure that all clients are compatible with both the old and new version of the API at the time of the rollout. For changes with required fields, it will often be easier to add a new version of the API - potentially using VIEWs to emulate the old behavior - move your clients over, and ensure that no more out-of-date clients exist that we’re not ok with breaking before removing the old API.

Reverting Migrations vs Backups

Some schema migration systems allow reverting or undoing migrations. In practice, they are inverse follow-up migration. While this allows reverting schemas to a prior state, it doesn’t allow restoring the actual database state. For example, dropping a table is a destructive operation that results in all of the table’s records being deleted. Reverting the schema will bring back an empty table only.

TrailBase does not provide a builtin revert. Schema migrations are append only and move strictly forward in time. They should always be conducted with great care. If a destructive operations has been rolled out all the way to prod, the data is gone. You can still undo the schema change by manually applying an inverse forward migration, i.e. addin a column or table back, but properly undoing the database state will require a restore from backups.

Backups

By default, TrailBase backs up your database periodically to traildepot/backups/backup.db.