Creating and Changing Tables: Migrations
Migrations are numbered SQL scripts that create and change an API's tables, run in order when the app starts, with SQLite's PRAGMA user_version remembering how many a database has had, so every copy of the database gets each change exactly once.
Journeyman · FastAPI · REST API · Updated
After this lesson you can
- Create and change an API's tables with numbered migration scripts, and never edit a script that has already run
- Run only the migrations a database hasn't had yet, in order, using PRAGMA user_version, when the app starts
Key takeaways
- Editing a CREATE TABLE IF NOT EXISTS statement changes nothing in a database that already has the table, so new code meets an old table.
- A migration is one numbered SQL script, such as 0003_add_set_notes.sql, that makes one change to the tables.
- PRAGMA user_version is a number SQLite keeps inside the database file. It records the last migration that ran, so each script runs once on each database.
- The app runs the migrations it hasn't had yet every time it starts, in number order.
- Never edit a migration that has run somewhere. Write a new one, because other copies of the database already ran the old one.
An API's tables change as long as people use it: the gym wants notes on each set, a shop wants a discount column, a school splits one name column into two. Each change has to reach your laptop's database, your teammate's, the test databases and the one online, without losing a row. This lesson shows the way most teams do it.
The example is Rep Counter's workout log, whose members now want a note on each set, such as "felt easy".
The table you can't change by editing it
A first try keeps a CREATE TABLE IF NOT EXISTS statement for each table and runs it when the app starts. IF NOT EXISTS means "only if there's no table by that name yet". To add notes, you add a notes column to the statement. Here's what happens to a database that already had the table, with Monday's and Tuesday's startups in one script:
import sqlite3
conn = sqlite3.connect(":memory:")
# Monday's app makes the table when it starts.
conn.execute("CREATE TABLE IF NOT EXISTS sets (id INTEGER PRIMARY KEY, exercise TEXT NOT NULL)")
# Tuesday's app has notes added to the same statement, and starts on the same database.
conn.execute("CREATE TABLE IF NOT EXISTS sets (id INTEGER PRIMARY KEY, exercise TEXT NOT NULL, notes TEXT)")
try:
conn.execute("INSERT INTO sets (exercise, notes) VALUES (?, ?)", ("squat", "felt easy"))
except sqlite3.OperationalError as error:
print(error)
table sets has no column named notes
The table was already there, so Tuesday's statement did nothing. Deleting the database file would bring the column, and every member's workouts would go with it.
Numbered scripts: migrations
Builders don't knock a house down to add a porch: they follow a list of changes, in order, and tick each off. A migration is one change to a database's tables, written as a numbered SQL script. Rep Counter keeps them in a migrations folder:
migrations/
├── 0001_create_workouts.sql
├── 0002_create_sets.sql
└── 0003_add_set_notes.sql
The first two make the tables; the third is the new change:
-- Migration 3: a note on each set, such as "felt easy". Sets saved before it get an empty note.
ALTER TABLE sets ADD COLUMN notes TEXT NOT NULL DEFAULT '';
ALTER TABLE ... ADD COLUMN adds a column to a table that already has rows, and DEFAULT '' gives every one of those rows an empty note. The tables and columns a database has, all together, are its schema; migrations are how a schema grows.
PRAGMA user_version remembers
Each database has to know which scripts it has had. SQLite keeps a whole number inside every database file for you to use, user_version, which starts at 0. A PRAGMA statement reads or changes a setting like that: PRAGMA user_version reads it, and PRAGMA user_version = 3 sets it. Rep Counter's migrate function, in database.py, runs every script whose number is higher, in order:
from pathlib import Path
MIGRATIONS = Path(__file__).parent / "migrations"
def migrate(conn):
version = conn.execute("PRAGMA user_version").fetchone()[0]
ran = []
for path in sorted(MIGRATIONS.glob("*.sql")):
number = int(path.name[:4])
if number > version:
conn.executescript(path.read_text(encoding="utf-8"))
conn.execute(f"PRAGMA user_version = {number}")
conn.commit()
ran.append(number)
return ran
sorted(...glob("*.sql"))lists the scripts by name, and the four-digit numbers keep that list in order:0010comes after0009.int(path.name[:4])reads the number from the file's name.executescriptruns everything in the script.PRAGMAtakes no?placeholders, so this is the one place the course puts a value into SQL's text. It's safe here becausenumberis anintmade from your own file's name, never anything a request sent.
Before 0003_add_set_notes.sql existed, the first start on a new database ran [1, 2] and the next ran []. With the new script in the folder, the next start runs only [3], and the start after that runs [] again. In the file itself, python -m sqlite3 rep_counter.db shows the number:
sqlite> PRAGMA user_version;
(3,)
Running them at startup, and the rules
FastAPI runs a lifespan function once: the code before its yield when the app starts, and the code after it when the app stops. Rep Counter's main.py migrates there:
@asynccontextmanager
async def lifespan(app: FastAPI):
conn = sqlite3.connect(DATABASE_PATH)
migrate(conn)
conn.close()
yield
app = FastAPI(title="Rep Counter", lifespan=lifespan)
A lifespan is written with async def and @asynccontextmanager (from contextlib); copy the pattern as it is for now, and Startup, Shutdown and a Health Check explains both. Two rules keep migrations safe:
- Never edit a script that has run anywhere. Your teammate's database already ran
0003_add_set_notes.sql, and won't run it again. A fix is a new script, such as0004_fix_set_notes.sql. - One change per script, numbered in the order it was written.
Projects that use SQLAlchemy, which SQLAlchemy and SQLModel: When a Project Uses an ORM shows, usually use a tool named Alembic for the same job: numbered changes, a version kept in the database, run in order.
Do it: run only the new migrations
Write a version of migrate that takes the scripts as a list instead of reading a folder.
conn: an open connection to a SQLite database.scripts: a list of(number, sql)pairs, in number order, such as(1, "CREATE TABLE workouts (...);").sqlmay hold more than one statement.
Your job: write migrate(conn, scripts), which:
- reads the database's
PRAGMA user_version; - runs, in order, the
sqlof every script whose number is higher than it, and after each one setsPRAGMA user_versionto that script's number and commits; - returns a list of the numbers it ran, in order, or
[]when the database is up to date.
Worked through with the three scripts in the tests, SCRIPTS: on a new database, user_version is 0, so migrate(conn, SCRIPTS) runs all three and returns [1, 2, 3], and user_version is then 3. Run again, it returns [].
Practiced live on the lesson page: write the Python right there and check it.
Check yourself
2 questions to try on the lesson page.
So what
Numbered migrations and a version number inside the database let every copy of it, yours, your team's and the one online, get each change once, in order, with every row kept.