Enrolling an existing db

Enroll an existing db

Here’s how to add an existing db to be managed by fastmigrate.

Back up your database, of course.
Run fastmigrate_enroll_db --db /path/to/database.db against your current database.

This will modify your database, marking it as version 1 by adding a _meta table.

It will also generate an initial migration script, 0001-initialize.sql, which is for creating an empty “version 1” database with the same schema as your current database.
Move your current database to somewhere safe, update your application’s db setup code to use fastmigrate, and run it.
```
import fastmigrate
fastmigrate.create_db("/path/to/database.db")
fastmigrate.run_migrations("/path/to/database.db")
# application code continues from here
```
Since you moved your real db, fastmigrate will create a new db at that path based on the initial migration script.
Check your app, to see if it is working fine.

If it is, your initialization script is correct, and your can move your real database back into place.

If not, then you will need to edit that initialization script, so that it produces a database which is equivalent to your current database.

The reason for this procedure

As long as you use fastmigrate with valid migration scripts, fastmigrate can guarantee whch version of your database it presented to your application code. This is the value of managed migrations.

However, to provide this guarantee, you need your database to be managed by fastmigrate – that is, to be explicitly marked with a version, in its _meta table. If you created the database with fastmigrate (using create_db or the fastmigrate_create_db CLI command), then it is managed.

But what if you are starting with an application built outside of fastmigrate, and you want to enroll the database in fastmigrate?

To recap the basic idea of what migrations are, the fundamental guarantee which we need to maintain is this: any database which has a fastmigrate version number (like 1, or like 3) is in the state which would be produced by the migration script with that version number (like by 0001-initialize.sql or 0003-unify-users.sql).

So when enrolling an existing db, you need to assign a version to the db you already have. But since that version number takes its meaning from the migration script which would produce it, you also need to create a migration script which would produce a database like yours. That script is also practically useful. If you ever want to deploy a new instance of your database, or run fresh instancs for debugging, you need that initialization script to create the initial, empty state of a db for your application to use.

fastmigrate_enroll_db is merely a helper for those tasks. It marks your database, and generates an initialization migration script.

One reason enrollment needs manual inspection

Why is this not 100% automatic?

The tool generates the migration script based on the schema of your existing database. In many cases, that is all that matters for defining the version of a database, because the schema is all that the application code depends on.

However, this will not be enough if you application requires not only a particular table schema, but also certain initial data values to be present. In that case you will need to add code to your the initialization script which not only creates the necessary tables but also inserts those values.

For instance, if your application code merely required a user table which tracked settings, you would expect a line like this:

CREATE TABLE user (id INTEGER, settings TEXT);

But if your application code also required that the database start with one row in that table, defining a user with an ID of 1 and settings which were an empty pair of brances, then you would also add a line like so:

INSERT INTO user VALUES (1, 0, '{}');

This subltety is a reason why it is not strictly accurate to say migrations version exist only to track schema schema. In fact, they define versions which should track what application code expects, which likely includes versions but not only versions.