SQLite DB Migrations with PRAGMA user_version

| programming | databases |

This blog used a simple homegrown blogging engine that I wrote backed by a SQLite database. I have a function in the flask app that performs database migrations. My current approach has been to keep a folder full of migrations and run them sequentially whenever the app starts.

This works well for the case of adding and removing tables since SQLite has the handy IF NOT EXISTS option. However, when you are altering an existing table, this entire model falls apart since IF NOT EXISTS no longer works.

Practically, this means that outside of a fresh install my database migrations are useless.

I am still being stubborn and not using a well written solution like Alembic (which I would highly recommend for a “serious” project) for this blog. Instead, I discovered that SQLite comes with a built in mechanism to keep track of the user schema. This is the pragmastatement, and specifically user_version.

Using PRAGMA user_data for DB Migrations

My migrations folder structure looks like this: 
.
├── blog.db
├── blog.py
├── __init__.py
├── migrations
│   ├── 0001_initial_schema.sql
│   ├── 0002_add_unique_index_to_posts_tags.sql
│   ├── 0003_add_fts.sql
│   ├── 0004_add_column_to_post.sql
│   ├── 0005_add_comments_table.sql
│   └── 0006_add_admin_flag_to_comments.sql
As you can see the naming convention is 000N_migration_description.sql. Each migration file has the following statement in it: 
PRAGMA user_version=N; (where N is the 000"N" part of the file name)
This steps the current user_version to be equal to the current version as defined by the file name.

The code to do stuff with the database is shown below:

def connect_db():
    """Connects to Database."""
    rv = sqlite3.connect(
        app.config['DATABASE'],
        detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
    rv.row_factory = sqlite3.Row
    return rv


def get_db():
    """Opens new db connection if there is not an
    existing one for the current app ctx.
    """
    if not hasattr(g, 'sqlite_db'):
        g.sqlite_db = connect_db()
    return g.sqlite_db


def migrate_db():
    """Run database migrations."""

    def get_script_version(path):
        return int(path.split('_')[0].split('/')[1])

    db = get_db()
    current_version = db.cursor().execute('pragma user_version').fetchone()[0]

    directory = os.path.dirname(__file__)
    migrations_path = os.path.join(directory, 'migrations/')
    migration_files = list(os.listdir(migrations_path))
    for migration in sorted(migration_files):
        path = "migrations/{0}".format(migration)
        migration_version = get_script_version(path)

        if migration_version > current_version:
            print("applying migration {0}".format(migration_version))
            with app.open_resource(path, mode='r') as f:
                 db.cursor().executescript(f.read())
                 print("database now at version {0}".format(migration_version))
        else:
            print("migration {0} already applied".format(migration_version))

The relevant part to this blog post is the migrate_db() function. Two things are happening.

  1. The get_script_version() helper function extracts the integer from the migration name.
  2. current_version gets the current value of user_version of your database.
  3. We iterate over each migration file in the migrations folder and perform a simple check. If the migration version is larger than the current_version we run the migration, otherwise it gets skipped.
This solves for most cases and allows for a smooth upgrade path if anyone ever decides to start using this blogging engine for themselves. I am still pretty happy with this approach because this is essentially a fully functional migration system in just a handful of lines of python.

Thank you for reading! Share your thoughts with me on bluesky, mastodon, or via email.

Check out some more stuff to read down below.

Most popular posts this month

Recent Favorite Blog Posts

This is a collection of the last 8 posts that I bookmarked.

Articles from blogs I follow around the net

Bandcamp is Taking Slop Head On with Unequivocal Ban, to the Cheers of Music Lovers Everywhere

’Tis But Some Quick News for January 16, 2026 Bandcamp read the room. It is the hero we need in our hour of darkness. Amidst an onslaught of horrendous AI-generated slop pretending to be music (Spotify is rife with this garbage, though they keep claiming …

via The Internet Review January 16, 2026

Pluralistic: Catch this! (16 Jan 2026)

Today's links Catch this! Email is good, actually. Hey look at this: Delights to delectate. Object permanence: LDS excommunication; King Foundation v "I Have a Dream"; "Lat-stage capitalism" v "Christ, what an asshole"; Pelosi …

via Pluralistic: Daily links from Cory Doctorow January 16, 2026

Liberating the ASUS CX1100CN Chromebook with OpenBSD

Liberating the ASUS CX1100CN Chromebook with OpenBSD 2026-01-16 I’ve always enjoyed the idea of having a portable, lightweight, 11 inch laptop for my personal use for around the house and small trips. A device that I wouldn’t have to be concerned about just…

via btxx.org RSS Feed January 16, 2026

Generated by openring