Step 8: Database Setup with Migrations

Database migrations let you version control your database schema. Instead of manually running SQL commands, you create migration files that can be applied and rolled back consistently across environments. This means:

• Team collaboration: Everyone has the same database structure
• Deployment safety: Changes are applied in order, every time
• Rollback capability: Undo changes if something goes wrong
• History tracking: See when and what changed in your schema

Here's an overview of the database configuration and migration files you'll create. Click on files in the tree to explore. Files marked with NEW are the ones you'll create.

{
  "database": "sqlite",
  "sqlite": { "database": "database/db.sqlite" },
  "migration_folder": "migrations"
}

Create a proper.json configuration file in your project root:

{
  "database": "sqlite",
  "sqlite": { "database": "database/db.sqlite" },
  "migration_folder": "migrations"
}

This tells Proper:

• database - Which database driver to use (sqlite)
• sqlite.database - Path to the SQLite database file
• migration_folder - Where migration files are stored

First, create the database directory, then generate a migration:

mkdir -p database
npx proper create create_todos_table

This creates two files in your migrations/ folder:

• YYYYMMDDHHMMSS-create_todos_table.up.sql - Applied when migrating up
• YYYYMMDDHHMMSS-create_todos_table.down.sql - Applied when rolling back

The up migration creates your table:

CREATE TABLE todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    completed INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

Key SQLite patterns:

• INTEGER PRIMARY KEY AUTOINCREMENT - Auto-incrementing ID
• TEXT NOT NULL - Required string field
• INTEGER DEFAULT 0 - Boolean stored as 0/1
• datetime('now') - SQLite function for current timestamp

The down migration reverses the up migration:

DROP TABLE IF EXISTS todos;

Always use IF EXISTS to avoid errors when the table doesn't exist.

Apply all pending migrations:

npx proper up

Rollback the last migration:

npx proper down

Check migration status:

npx proper status

• One change per migration: Keep migrations focused and atomic
• Always write down migrations: Every up needs a reversible down
• Never edit applied migrations: Create new migrations for changes
• Use descriptive names: Name migrations after what they do
• Test rollbacks: Verify your down migrations work correctly

With our database set up and migrations in place, our backend is complete! In the next step, we'll build a frontend to interact with our API.