Database migrations

Summary: This page explains StartER's approach to database evolution: declarative schema editing during prototyping, and forward-only migration scripts for production deployment.

Two modes, one source of truth

StartER is a prototyping framework. Most of the time, your application lives on localhost in development mode. When you're ready to deploy, you opt into a different workflow, but schema.sql remains the foundation in both cases.

	Development	Production
Command	npm run database:reset	npm run database:migrate
Behavior	Drop everything, replay all SQL files	Apply only un-applied SQL files
schema.sql	Edit freely	Frozen after first deployment
seeder.sql	Loaded (test data)	Ignored
Migration scripts	Replayed from scratch	Applied forward-only
Data	Lost (intentional: fresh start)	Preserved

During prototyping: edit schema.sql freely

In development, schema.sql is your playground. Add tables, rename columns, change types, etc. When you want to see the result:

npm run database:reset

This drops all tables and replays every SQL file from scratch: schema.sql first, then any migration scripts in src/database/migrations/, then seeder.sql. You always get a clean database reflecting the current state of your files.

Tip

This is the workflow you'll use 99% of the time. No migration scripts needed, no mental overhead.

When you deploy: forward-only migrations

If you choose to deploy your application to production, the rules change. Production databases contain real data that cannot be dropped and recreated.

Once schema.sql has been applied to production (on first deployment), it becomes frozen. All subsequent schema changes must go through migration scripts in src/database/migrations/.

Writing a migration script

Migration scripts are plain .sql files placed in src/database/migrations/. The framework imposes no naming convention: files are executed in alphabetical (filesystem) order. A common practice is to use a date or sequence prefix:

src/database/migrations/
  2026-07-10_add_category_table.sql
  2026-07-15_add_status_to_item.sql

Example migration (adding a category table):

-- src/database/migrations/2026-07-10_add_category_table.sql

CREATE TABLE category (
  id INTEGER PRIMARY KEY NOT NULL,
  name VARCHAR(255) NOT NULL
);

ALTER TABLE item ADD COLUMN category_id INTEGER REFERENCES category(id);

Applying migrations

npm run database:migrate

This command:

1. Backs up the database file (temporary, deleted after completion)
2. Scans schema.sql + all files in migrations/ (alphabetical order)
3. Skips already-applied files (tracked in a _migrations table)
4. Applies new files inside a transaction (all-or-nothing)
5. Cleans up the backup file

On first deployment, schema.sql and all existing migration scripts are applied. On subsequent deployments, only new migration scripts are applied.

Tip

Use the -n flag for non-interactive mode in CI/CD:

npm run database:migrate -- -n

What happens if you edit an already-applied file?

If you modify schema.sql or a migration script after it has been applied, the next database:migrate will warn you:

⚠️  'schema.sql' was modified since it was applied.
    These changes will NOT take effect.
    Revert your change or write a new migration script.

The migration system uses checksums to detect modifications. This is a pedagogical signal: it explains why your change isn't showing up and tells you what to do instead.

To fix it: revert the modification to the applied file, and write a new migration script in src/database/migrations/ that applies the change you intended.

The _migrations table

Both database:reset and database:migrate maintain a _migrations table in the database:

CREATE TABLE _migrations (
  filename TEXT PRIMARY KEY,
  checksum TEXT NOT NULL,
  applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

• database:reset drops it and recreates it (recording all replayed files)
• database:migrate reads it to determine which files have already been applied

This means you can test the production migration workflow locally: run database:reset to start fresh, add a new migration script, then run database:migrate to verify it applies correctly.

Best practices and use cases

Before deployment

• Edit schema.sql freely.
• Use database:reset.

After first deployment

• Freeze schema.sql and migration scripts that have already been applied.
• Write new migration scripts in src/database/migrations/.
• Use database:migrate (forward-only).

See also

• Database
• CLI scripts
• Deployment