ComputeSphereLearn
Console
Learning paths / Application foundations / Databases in an app

Try it: run a migration locally

Reading · 15 min · Module 3, lesson 5 of 620 min left in this module

Module 3 · Databases in an appLesson 5 of 6

Goal: Apply two migrations to a local database, roll the last one back, and read the record the runner keeps.

You need Node 22.13 or later (Node 24 LTS recommended), Git and a terminal. Nothing else to install.

Checking your Node version

Run node --version. If it prints something older than v22.13.0, install the current LTS from nodejs.org or with your version manager.

The sample uses the SQLite database built into Node, so there are no packages to install and no database server to run. Some Node versions print an ExperimentalWarning about SQLite the first time; it's harmless.

  1. Get the sample

    git clone https://github.com/computesphere-samples/learn.git
cd learn/labs/migrations
ls migrations

    Cloned learn already in lesson 2.2.4? Don't clone again: cd into that learn folder, run git pull to fetch the latest, then cd labs/migrations.

    You should seeTwo migrations, each with an up and a down file.

  2. Check what has run

    node migrate.js status

    This creates the database, a local file called app.db, and the schema_migrations table that records what has run.

    You should see[ ] 001_create_tasks and [ ] 002_add_due_date: nothing has run yet.

  3. Read one migration

    cat migrations/002_add_due_date.up.sql migrations/002_add_due_date.down.sql

    On Windows, open the two files in your editor instead.

    You should seeThe up adds a due_date column to tasks; the down drops it.

  4. Apply them

    node migrate.js up

    They run in order, because 002 changes the table that 001 creates.

    You should seeApplied 001_create_tasks, then Applied 002_add_due_date.

  5. See the schema

    node migrate.js schema

    SQLite has no separate true-or-false or date types, so done is stored as an INTEGER (0 or 1) and due_date as TEXT.

    Check yourself

    What happens if you run node migrate.js up again now?

    You should seeA table of four columns: id, title, done and due_date.

  6. Roll back the last one

    node migrate.js down
node migrate.js schema
node migrate.js status

    Down undoes only the most recent migration. Any dates in due_date would be gone now.

    You should seeRolled back 002_add_due_date. schema shows three columns; status shows [x] 001_create_tasks and [ ] 002_add_due_date.

  7. Apply it again

    node migrate.js up
node migrate.js schema

    To start over at any point, delete app.db.

    You should seeApplied 002_add_due_date, and due_date is back in schema.

What the runner does

migrate.js is about 50 lines. On up it lists the *.up.sql files in order, skips the versions already in schema_migrations, and runs each new one in a transaction together with the insert that records it. If the SQL fails, both roll back, so the record never claims a change that didn't happen.

Real migration tools add locking, so two copies can't run at once, and checksums that catch an edited migration. The idea is the same.