Migrations with python - Kenny's Blog

Migrations are used in production to change a database schema as time goes by - let me explain a little with an example. Consider we want to create a pretty simple table in postgres (or some other database like sqlite3:

CREATE TABLE weather_node (
    time_received TIMESTAMP NOT NULL,
    schema INTEGER,
    node_addr INTEGER,
    uptime_ms BIGINT,
    batt_mv INTEGER
)

Let’s say we want to add another column - we’d want to run a command like this:

ALTER TABLE weather_node ADD COLUMN temp_c INTEGER We could run these commands manually, or we could codify both using a migration. There’s a library called yoyo migrations that we can use. Here’s what some of the code looks like:

# migrate.py
import yoyo
 
backend = yoyo.get_backend('sqlite://./test.db')

# execute the migrations in the ./migrations folder
migrations = yoyo.read_migrations('./migrations')
backend.apply_migrations(backend.to_apply(migrations))
This is what the rest of the directory looks like:

$ tree .
.
├── migrate.py
├── migrations
│   ├── 0001.create.py
│   └── 0002.alter-table.py

You’ll notice there are two files here - each containing a migration corresponding to the changes we want to make above:

$ cat 0001.create.py
from yoyo import step

steps = [
   step(
       """
CREATE TABLE heartbeat (
    time_received TIMESTAMP NOT NULL,
    schema INTEGER,
    node_addr INTEGER,
    uptime_ms BIGINT,
    batt_mv INTEGER
)
       """
   )
]
$ cat 0002.alter-table.py
from yoyo import step

steps = [
   step(
       """
ALTER TABLE heartbeat ADD COLUMN temp_c INTEGER
       """
   )
]

Running migrate.py executes both steps automatically, and also creates some extra tables to keep track of the state of the migrations, letting us not have to worry about the current schema.

Other notes

Declarative schemas are an interesting alternative to migrations. Instead of specifying steps like we did above, you can specify a declaration of the scehma and the system will try to make your database look like that schema. A couple of systems at work for me use such a system like that.

Maybe I’ll post something someday about it - for now, here are some HN threads about them: