Whenever I'm starting a new database-backed project, I usually default to using SQLite instead of networked databases like MySQL or Postgres.

SQLite is a flat file masquerading as a database, so it's very easy to get up and running with. It's part of the Python standard library. And contrary to popular belief, it's highly scaleable if you understand its limitations.

If your site or app is getting less than 500k hits/day with ~100k of those touching the database, you're well within SQLite's capabilities.

Migrations still suck, though.

I feel like they suck in any relational database model and they're the bane of most back-end developer's existence. A lot can go wrong and the database is a critical component of most applications. Migrations and backup restores are always a high-pressure situation.

To make matters worse, SQLite has some... quirks that require a bit of leg-work before updating certain schema types.

We're going to go over a few of those quirks here in the hopes that it makes your life a bit easier.

Migrating an SQLite Database

I usually use Miguel Grinberg's excellent Flask-Migrate library to handle migrations for me. It's mostly just an Alembic wrapper that abstracts out a lot of the complexity of that library.

Usually that's a good thing, but sometimes it's not.

Especially when you're dealing with SQLite's quirkiness. SQLite, for example, has no native boolean data type. You're stuck using either 0s or 1s as an INT. Weird, but manageable.

SQLite also allows unnamed foreign key constraints. To my knowledge, no other database operates this way. It makes using migration software that targets "normal" databases a real headache, as libraries like Flask-Migrate and Alembic will be looking for these constraints.

Take my advice: Name your foreign key constraints in advance if you're using SQLite.

Otherwise, you'll have to drop your unnamed constraints and re-add them before you can run a successful migration. This requires heavy modification of an Alembic script, which defeats the purpose of using excellent abstraction tools like Flask-Migrate.

It's pretty easy to do so - you'll need to import the MetaData class from the vanilla SQLAlchemy library, instantiate it somewhere in your __init__.py file (if you're using the app factory pattern), and explicitly pass it to your database object:

from sqlalchemy import MetaData
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy

#Explicit naming convention and metadata required for SQLite
convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}

metadata = MetaData(naming_convention=convention)

db = SQLAlchemy(metadata=metadata)

Another issue with migrations in SQLite is its implementation of ALTER TABLE statements. SQLite only supports adding or renaming columns - any other operations when migrating your database are going to cause headaches.

Save yourself these headaches in advance by enabling batch mode in Flask-Migrate. It's a simple one-liner somewhere in your application factory:

#Render_as_batch required for SQLite
def create_app():
    migrate.init_app(app, db, render_as_batch=True)

Instead of running ALTER TABLE commands, batch mode introduces a drop-and-replace alteration method, cloning your old tables into new ones that abide by your updated schema. A hacky solution, to be sure, but it works. It also doesn't seem to affect other database types if you decide to switch to a networked database later on.

And don't forget: SQLite is just a flat .db file, so feel free to experiment! You can always rsync the entire database from your server as a backup, break things with confidence, and simply re-upload your .db file to revert your application to a working state if you screw up in spectacular fashion.

Try doing that with a production MySQL database.