I'm building an SQLite-powered application and I need a tool to control database migrations (eg.: control the evolution of the database schema, so multiple developers can collaborate on it).

I'm imagining it must be a common task for most people working with SQLite databases, so I decided to ask here. :) I'm using Python – and I know there's SQLAlchemy, but I'm trying to use raw SQL to write these migrations.

I used Go's migrate in the past, but unfortunately, they've drop support for SQLite.

So, what y'all have been using to develop your database schemas? :)

In the past I've used sqitch to manage database migrations with good results. No relationship to them other than a satisfied user in the past.

(Edited by admin: s/squitch/sqitch/)

Cool! I didn't know Sqitch, I'm definitely giving it a try. Thanks!

SQLite has an application_id pragma that can be used to know the database belongs to your app, and a user_version pragma that makes versioning the schema easy. I do it like this on connection to a database in pseudo-code:

if user_version == 0:
   CREATE TABLE ....
   CREATE INDEX ....
   pragma user_version = 1
if user_version == 1:
   CREATE TABLE another
   ALTER TABLE ...
   pragma user_version = 2
if user_version == 2:
   CREATE VIEW ...
   DELETE ....
   pragma user_version = 3

This will upgrade your database schema correctly, providing you ensure each block is a single transaction. It doesn't handle opening a database with a newer user_version than your code knows about, but in that case you can choose what to do.

You can see this expressed in Python in the APSW docs

I'll just post it here should someone land this thread about SQLite migration tools somehow (eg.: search results):

I've been successfully using yoyo-migrations, a pretty decent Python migration library and CLI. Considering that Python is pretty much omnipresent (macOS, Linux, BSDs, and pretty much all UNIX-like systems come with some Python version pre-installed), installing and using it shouldn't be hard.

I would recommend reading the project website for installation and usage instructions, etc.: https://pypi.org/project/yoyo-migrations/

A good text editor works wonders.

I do not understand why people think they need to "saran=wrap" everything. It only makes the saran-wrap maker rich and provides no actual benefit (actually, using saran wrap detracts from utility because everyone is now forced to deal with only the saran-wrap constructs, which were probably designed by a moron.

I use the application of ogranic power (organic means has a carbon chain backbone) to conceive a sequence of commands which, when executed, will transition the state of the database from State A to State B. Then use this modern invention called a "TEXT EDITOR" to put those commands in another new-fangled invention called a "file".

When I determine that the database needs to be transitioned from State A to State B, I simply cause the commands recorded for that purpose to be executed.

No "frameworkds" or other saran-wrapping required.

My app uses a number of SQLite databases; the user can choose to add as many more as they like. The user is unaware that these are SQLite databases; I define the schemas as required to implement the facilities the app is providing. The app looks for some of these databases as it starts up, and if they are missing, it creates them.

From time to time I may add a new feature, which may require extra columns in one table or another. To effect this, I modify the code for creating a given table to add any extra fields. I also have functions that check the version number of the app, and compare it with a version number in each database. It's a simple matter to add code to these which updates the schemas as necessary, as well as the version number in the database. By doing this, any version of the app can update to its own level the set of databases created by any earlier version.

Hi Tim (@Clothears),

Interesting approach. Would you mind sharing some snippets of this in-database version-checking you're doing to control the schema version of your databases? :-)

I'm imagining it's something similar to what Roger (Roger Binns; @rogerbinns) suggested: using a version variable (either the application_id PRAGMA or a value in a custom table) to store the schema version and manually checking it when performing migrations.

I personally don't see much value in handling migrations by hand when there are tools to do it – but since SQLite databases are so "cheap" (no bloated files, creating new databases is easy and cheap, and tasks like moving databases around are not much of a problem), I realize that apps using SQLite as storage can have multiple databases at once (eg.: one general database to the app itself, and one database per user) – and it could even make a lot of sense, depending on which scenario you're dealing with.

A scenario I'm imagining is to support concurrent writes. SQLite allows only one writing process per database – but if there's one database per user, the impact of this limitation is heavily reduced since you'll be able to have one concurrent write per user.

I mean, If I would work with SQLite databases this way, I might want to bring the database schema management to my own codebase instead of using an external tool like Migrate or yoyo-migrations.

I'm new to SQLite, coming from n-tier databases like MySQL and Postgres. I'm used to thinking of databases as "one per app, backups etc. as very expensive ops that should be handled carefully, massive write concurrency w/ MVCC." SQLite puts a lot of the pre-defined assumptions about databases in check.

All databases generated by my app have a globals table one of whose columns is a version number. This gets bumped if any improvement I make to the app requires schema changes. The app will then update any schemas as required and also the version number.

I don't understand "handling migrations by hand when there are tools to do it", unless "by hand" you refer to the code I add to the app to effect the changes. That is obviously done "by hand". And why would an external tool be involved when the app does the job by itself with no issues?

Tim Streater @Clothears,

Yep, you're right. It seems that managing the schema lifetime is something SQLite devs just write it as part of their own software's source code.

"And why would an external tool be involved when the app does the job by itself with no issues?"

By "external tool" I meant a CLI-based migration library. The reason to use it is just to have a featureful CLI to do the job. Being able to apply and/or rollback migrations progressively, etc., without having to write it yourself.

But I get the point that it's a small task that shouldn't take much effort to do it yourself. It seems to be a matter of convenience, and maybe personal preferences too.

Thank you for your comments!

The way we do schema migrations, is we define the schema in a single place (a text file with CREATE TABLE statements etc).

When our application starts up, we create a new in-memory database and execute the schema to create the "desired" version of the database.

Then we query sqlite’s internal sqlite_schema table to get the tables from both the “desired” and “actual” databases; from that information we can work out the "CREATE TABLE" / "ALTER TABLE" statements we need to run.

More information, including the Python script that does all of this, is here: https://david.rothlis.net/declarative-schema-migration-for-sqlite/