Keep track of migration state in SQlite
2 min. read

Keeping track of database migrations in SQlite DB files can generally be done in the same way as with any other database. The most common approach is to provide a separate database table for maintaining the history of all migrations that have already been applied.

In SQlite, there is a more subtle alternative to this, which is to use the user_version PRAGMA:

The user-version is an integer that is available to applications to use however they want.

That allows you to store the migration state as single integer value in the header section of the SQlite file.

-- Query the `user_version` value
PRAGMA user_version

-- Set the `user_version` value to 12
PRAGMA user_version=12

The prerequisite for this to work in a robust manner is to have one authoritative and immutable source of ordered migration procedures, e.g.:

For such a list of SQL migrations, the user_version would represent the ordinal number of the migration that was applied last. So the user_version being set to 2 means that the first two migrations have already been executed. Therefore, you’d have to run 3 to bring the DB schema up to date.

One thing to note: you should make sure that executing the migration and incrementing the user_version counter is one atomic operation, which you can use transactions for. While it appears that SQlite provides automated rollbacks for the user_version PRAGMA, this doesn’t seem to be officially documented.

My e-mail is:
Copy to Clipboard