SQLite DB Migrations with PRAGMA user_version
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
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)
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.
- The
get_script_version()
helper function extracts the integer from the migration name. current_version
gets the current value ofuser_version
of your database.- 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.
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
- 2024
- Reinstalling Windows at 1am
- SQLite DB Migrations with PRAGMA user_version
- My Custom Miniflux CSS Theme
- How to Disable Wayland in Debian Testing
Recent Favorite Blog Posts
This is a collection of the last 8 posts that I bookmarked.
- Future Fonts from Blog – Brad Frost
- 21st Century C++ from Communications of the ACM
- Submarines DevCon 2025 Keynote Speech from JoshHaines.com
- How I Use AI: Meet My Promptly Hired Model Intern from Armin Ronacher's Thoughts and Writings
- DeepSeek from Maggie Appleton
- Digital Reality Digital Shock from Christopher Butler
- 10 habits to help becoming a Debian Maintainer from Optimized by Otto
- Tiny corners from Manuel Moreale RSS Feed
Articles from blogs I follow around the net
Open Up, Episode 1: layoffs, purpose, and embracing your full self
Last week we held our first episode of Open Up, a new advice show I’m hosting with my fantastic co-host Geoff Graham and my good friends at SmashingConf. We had a wonderful inaugural episode, with over 80 people joining us […]
via Blog – Brad Frost March 18, 2025A General Update and Titanic Discovery
I probably won’t usually do this… but I wanted to sorta self-bookmark what’s happening with me right now, so I thought I’d turn my ‘now’ update into a post. And since I don’t save my ‘now’ page… here I am. What I’m Doing: Designing, researching, producing…
via HouseRat March 18, 2025A Lucky Lamy Listing
A got extremely lucky finding this listing for a bunch of Lamy pens for basically nothing
via Robb Knight • Posts • Atom Feed March 18, 2025Generated by openring