Database Migration Guide

This guide explains how to create and manage database migrations in the Mulm application.

Overview

Mulm uses SQL-based migrations that run automatically on application startup. The migration system is built on the sqlite npm package's migration functionality.

Migration Location: db/migrations/

Migration Format: Sequential SQL files with "up" and "down" sections

Execution: Migrations run automatically when the application starts, applying only migrations that haven't been run yet.

Migration System Architecture

How Migrations Work

flowchart TD
    A[Application Starts] --> B[Open Admin Connection]
    B --> C{Migrations Table Exists?}
    C -->|No| D[Create migrations Table]
    C -->|Yes| E[Read Applied Migrations]
    D --> E
    E --> F[Scan db/migrations/ Directory]
    F --> G{Find New Migrations?}
    G -->|Yes| H[Apply Migrations in Order]
    G -->|No| I[Close Admin Connection]
    H --> J[Record Migration in Table]
    J --> K{More Migrations?}
    K -->|Yes| H
    K -->|No| I
    I --> L[Open Read/Write Connections]
    L --> M[Application Ready]

Migration Tracking

Migrations are tracked in the migrations table:

CREATE TABLE IF NOT EXISTS "migrations" (
  id   INTEGER PRIMARY KEY,
  name TEXT    NOT NULL,  -- Migration filename (e.g., "001-init.sql")
  up   TEXT    NOT NULL,  -- SQL for "up" migration
  down TEXT    NOT NULL   -- SQL for "down" migration (rollback)
);

Key Points:

• Migrations are applied in alphabetical/numeric order
• Each migration is recorded when successfully applied
• Failed migrations prevent application startup
• Migrations are idempotent (safe to run multiple times)

Connection Management

The migration system uses a separate admin connection:

// src/db/conn.ts (simplified)
(async () => {
  // 1. Open admin connection with CREATE privileges
  const adminConn = await open({
    filename: config.databaseFile,
    driver: sqlite3.Database,
    mode: sqlite3.OPEN_CREATE | sqlite3.OPEN_READWRITE,
  });

  // 2. Run migrations
  await adminConn.migrate({
    migrationsPath: './db/migrations',
  });

  // 3. Close admin connection
  await adminConn.close();

  // 4. Open read-only and write connections for application use
  await init();
})();

Why separate connections?

• Admin connection has CREATE/ALTER privileges (needed for schema changes)
• Application connections use READ_ONLY (queries) and READ_WRITE (data mutations)
• Principle of least privilege: application code cannot alter schema

Migration File Format

Naming Convention

NNN-descriptive-name.sql

• NNN: Zero-padded sequential number (001, 002, 003, etc.)
• descriptive-name: Kebab-case description of the migration
• .sql: SQL file extension

Examples:

• 001-init.sql - Initial schema
• 002-add-tank-presets.sql - Add tank_presets table
• 015-add-submission-notes.sql - Add submission_notes table

Ordering Rules:

• Migrations are applied in lexicographic order (alphabetically)
• Always use sequential numbering to ensure correct order
• Never reuse or skip numbers

File Structure

Each migration file has two sections separated by comments:

-- Up
-- SQL statements to apply the migration
CREATE TABLE example (...);
ALTER TABLE other_table ADD COLUMN new_field TEXT;

-- Down
-- SQL statements to rollback the migration
DROP TABLE IF EXISTS example;
-- Note: SQLite doesn't support DROP COLUMN
-- Rollback instructions may be limited or commented

Section Markers:

• -- Up - Marks the beginning of migration SQL
• -- Down - Marks the beginning of rollback SQL

Important Notes:

• SQLite has limited ALTER TABLE support (no DROP COLUMN)
• Down migrations may not be fully reversible
• Some migrations include comments explaining rollback limitations

Creating a New Migration

Step-by-Step Process

1. Determine the next migration number

ls db/migrations/ | tail -1
# Output: 015-add-submission-notes.sql
# Next number: 016

2. Create the migration file

touch db/migrations/016-add-field-name.sql

3. Write the migration SQL

-- Up

-- Add new column with proper constraints
ALTER TABLE submissions ADD COLUMN new_field TEXT DEFAULT NULL;

-- Add index for the new field (if needed)
CREATE INDEX idx_submissions_new_field ON submissions(new_field);

-- Update existing data (if needed)
UPDATE submissions SET new_field = 'default_value' WHERE condition;

-- Down

-- Rollback: SQLite doesn't support DROP COLUMN
-- To fully reverse this migration, you would need to:
-- 1. Create a new table without the column
-- 2. Copy data from old table to new table
-- 3. Drop old table
-- 4. Rename new table to old name
-- This is complex and typically not implemented for SQLite

4. Test the migration

# Development database
npm run dev
# Check logs for migration success

# Or manually test with sqlite3
sqlite3 src/database/database.db
sqlite> SELECT name FROM migrations WHERE name = '016-add-field-name.sql';

5. Verify the schema

sqlite3 src/database/database.db
sqlite> .schema submissions
# Verify new column exists

Migration Templates

Adding a Table

-- Up

CREATE TABLE new_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    member_id INTEGER NOT NULL
        REFERENCES members(id)
        ON DELETE CASCADE,
    field_name TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_new_table_member ON new_table(member_id);
CREATE INDEX idx_new_table_created ON new_table(created_at DESC);

-- Down

DROP TABLE IF EXISTS new_table;

Adding a Column

-- Up

ALTER TABLE existing_table ADD COLUMN new_column TEXT DEFAULT NULL;

-- Add check constraint if needed (must be done during column creation)
-- ALTER TABLE existing_table ADD COLUMN status TEXT
--     CHECK (status IN ('pending', 'approved', 'denied'))
--     DEFAULT 'pending';

-- Down

-- SQLite doesn't support DROP COLUMN
-- Full rollback would require table recreation

Adding an Index

-- Up

CREATE INDEX idx_table_column ON table_name(column_name);

-- Composite index example
CREATE INDEX idx_table_multi ON table_name(column1, column2 DESC);

-- Down

DROP INDEX IF EXISTS idx_table_column;

Adding a Foreign Key

Important: SQLite requires foreign keys to be defined during table creation. You cannot add foreign keys to existing tables without recreating the table.

-- Up

-- Option 1: Add column with foreign key (if table has no foreign key yet)
ALTER TABLE existing_table ADD COLUMN new_fk_id INTEGER
    REFERENCES other_table(id) ON DELETE CASCADE;

-- Option 2: Recreate table with foreign key (complex)
-- This requires:
-- 1. Creating new table with foreign key
-- 2. Copying all data
-- 3. Dropping old table
-- 4. Renaming new table

-- Down

-- Removing foreign key requires table recreation

Updating Data

-- Up

-- Update existing records with new default values
UPDATE submissions
SET witness_verification_status = 'pending'
WHERE witnessed_by IS NOT NULL
  AND witness_verification_status IS NULL;

-- Backfill data from related tables
UPDATE awards
SET award_type = 'species'
WHERE award_name IN (
    'Catfish Specialist',
    'Killifish Specialist'
);

-- Down

-- Reverting data updates may not be possible without backups
-- Consider whether rollback is necessary for data migrations

SQLite Limitations

Operations NOT Supported

SQLite has limited ALTER TABLE support. The following operations are NOT supported:

❌ DROP COLUMN - Cannot remove columns ❌ ALTER COLUMN - Cannot modify column definitions ❌ ADD CONSTRAINT - Cannot add constraints to existing columns ❌ DROP CONSTRAINT - Cannot remove constraints

Workaround: Table Recreation

To make structural changes, you must recreate the table:

-- Up

-- 1. Create new table with desired schema
CREATE TABLE submissions_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    -- ... all columns with modifications ...
    new_column TEXT DEFAULT NULL,
    -- removed_column is omitted
);

-- 2. Copy data from old table
INSERT INTO submissions_new (id, column1, column2, ...)
SELECT id, column1, column2, ... FROM submissions;

-- 3. Drop old table
DROP TABLE submissions;

-- 4. Rename new table
ALTER TABLE submissions_new RENAME TO submissions;

-- 5. Recreate indexes
CREATE INDEX idx_member_id ON submissions(member_id);
CREATE INDEX idx_date_approved ON submissions(approved_on);
-- ... all other indexes ...

-- Down

-- Reversing table recreation is very complex
-- Generally not implemented unless critical

⚠️ Warning: Table recreation is risky and should be avoided in production when possible.

Best Practices

Migration Safety

✅ DO:

• Test migrations on development database first
• Make migrations idempotent when possible
• Use IF NOT EXISTS for CREATE statements
• Use IF EXISTS for DROP statements
• Include comments explaining complex migrations
• Keep migrations small and focused (one logical change per migration)
• Add indexes in the same migration as the column

❌ DON'T:

• Modify existing migration files after they've been applied
• Delete migration files from the migrations directory
• Reorder migration numbers
• Make destructive changes without backups
• Combine unrelated schema changes in one migration

Performance Considerations

When adding indexes or updating large datasets:

-- Up

-- For large tables, consider adding index AFTER data is populated
CREATE INDEX idx_submissions_new_field ON submissions(new_field);

-- For large updates, use WHERE clause to limit scope
UPDATE submissions
SET new_field = 'value'
WHERE created_on >= '2024-01-01'  -- Only recent records
  AND new_field IS NULL;           -- Only records needing update

-- Down

DROP INDEX IF EXISTS idx_submissions_new_field;

Database Locks:

• SQLite locks the entire database during writes
• Large migrations can block application startup
• Consider breaking large data migrations into smaller chunks

Schema Consistency

Ensure migrations maintain referential integrity:

-- Up

-- When adding a foreign key column, ensure referenced table exists
-- Bad: Adding member_id when members table might not exist
-- Good: Check dependencies or add in correct order

ALTER TABLE new_table ADD COLUMN member_id INTEGER
    REFERENCES members(id)  -- members table must exist
    ON DELETE CASCADE;

-- Add NOT NULL constraint carefully
-- Bad: ALTER TABLE ADD COLUMN required_field TEXT NOT NULL;
-- Good: Add as NULL first, populate, then consider constraints
ALTER TABLE new_table ADD COLUMN required_field TEXT DEFAULT NULL;
UPDATE new_table SET required_field = 'default';
-- SQLite doesn't allow adding NOT NULL after, so plan ahead

-- Down

-- Document the dependency chain for rollbacks

Documentation

Include helpful comments in migrations:

-- Up

-- Migration 016: Add submission denial tracking
-- Adds fields to track when admins deny submissions and why
-- Related to GitHub issue #123

ALTER TABLE submissions ADD COLUMN denied_on DATETIME DEFAULT NULL;
ALTER TABLE submissions ADD COLUMN denied_by INTEGER DEFAULT NULL
    REFERENCES members(id) ON DELETE RESTRICT;
ALTER TABLE submissions ADD COLUMN denied_reason TEXT DEFAULT NULL;

-- Add indexes for efficient querying of denied submissions
CREATE INDEX idx_submissions_denied_on ON submissions(denied_on);
CREATE INDEX idx_submissions_denied_by ON submissions(denied_by);

-- Business rule: denied_on and denied_by should be set together
-- This cannot be enforced in SQLite with CHECK constraints on existing tables

-- Down

-- Removing denial fields would lose data
-- Consider exporting denial records before rollback if needed
DROP INDEX IF EXISTS idx_submissions_denied_on;
DROP INDEX IF EXISTS idx_submissions_denied_by;
-- Note: Cannot DROP COLUMN in SQLite

Migration Workflow

Development Workflow

flowchart LR
    A[Identify Schema Need] --> B[Create Migration File]
    B --> C[Write Up Migration]
    C --> D[Write Down Migration]
    D --> E[Test Locally]
    E --> F{Migration Works?}
    F -->|No| G[Fix Migration]
    G --> E
    F -->|Yes| H[Commit Migration]
    H --> I[Push to Repository]
    I --> J[Deploy to Production]
    J --> K[Migration Auto-Applies]

Testing Strategy

1. Test in Development

# Start development server (runs migrations)
npm run dev

# Check migration was applied
sqlite3 src/database/database.db
sqlite> SELECT * FROM migrations WHERE name = '016-new-migration.sql';

# Verify schema changes
sqlite> .schema table_name

# Test application functionality
# ... manual testing or automated tests ...

2. Test with Fresh Database

# Backup current database
cp src/database/database.db src/database/database.db.backup

# Delete database to test from scratch
rm src/database/database.db

# Start application (applies all migrations)
npm run dev

# Verify all migrations applied in order
sqlite3 src/database/database.db
sqlite> SELECT id, name FROM migrations ORDER BY id;

3. Test in Automated Tests

// src/__tests__/mytest.test.ts
import { overrideConnection } from '@/db/conn';
import { setupTestDatabase } from '@/__tests__/testDbHelper.helper';

describe('My Feature', () => {
  let db: Database;

  beforeEach(async () => {
    // Creates fresh in-memory database with all migrations applied
    db = await setupTestDatabase();
    overrideConnection(db);
  });

  it('should work with new schema', async () => {
    // Test code here
    // All migrations are applied automatically
  });
});

Production Deployment

Pre-Deployment Checklist:

• Migration tested in development
• Migration tested with fresh database
• Automated tests passing with new schema
• Database backup created
• Rollback plan documented (if possible)

Deployment Process:

# 1. Create production database backup FIRST
ssh BAP "sqlite3 /mnt/basny-data/app/database/database.db '.backup /tmp/backup-$(date +%Y%m%d-%H%M%S).db'"

# 2. Deploy code with migration
ssh BAP "cd /opt/basny && git pull && sudo docker-compose -f docker-compose.prod.yml up -d --build"

# 3. Monitor application logs
ssh BAP "sudo docker logs basny-app --tail 100 -f"
# Look for: "Migration 016-new-migration.sql applied successfully"

# 4. Verify migration applied
ssh BAP "sqlite3 /mnt/basny-data/app/database/database.db 'SELECT name FROM migrations ORDER BY id DESC LIMIT 5;'"

# 5. Test application functionality
curl https://bap.basny.org/health
# Manual testing of affected features

If Migration Fails:

# 1. Check application logs
ssh BAP "sudo docker logs basny-app --tail 200"

# 2. Check database integrity
ssh BAP "sqlite3 /mnt/basny-data/app/database/database.db 'PRAGMA integrity_check;'"

# 3. Restore from backup if needed
ssh BAP "cd /opt/basny && sudo docker-compose -f docker-compose.prod.yml down"
ssh BAP "cp /tmp/backup-TIMESTAMP.db /mnt/basny-data/app/database/database.db"
ssh BAP "cd /opt/basny && git reset --hard HEAD~1"  # Revert to previous commit
ssh BAP "cd /opt/basny && sudo docker-compose -f docker-compose.prod.yml up -d --build"

Common Migration Scenarios

Scenario 1: Adding Activity Tracking

Use Case: Add table to track user activity for display on homepage.

-- db/migrations/008-add-activity-feed.sql
-- Up

CREATE TABLE activity_feed (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    activity_type TEXT NOT NULL CHECK (activity_type IN ('submission_approved', 'award_granted')),
    member_id INTEGER NOT NULL
        REFERENCES members(id)
        ON DELETE CASCADE,
    related_id TEXT NOT NULL,  -- submission_id for approvals, award_name for grants
    activity_data TEXT,        -- JSON data specific to activity type
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_activity_created_at ON activity_feed (created_at DESC);
CREATE INDEX idx_activity_type ON activity_feed (activity_type);
CREATE INDEX idx_activity_member ON activity_feed (member_id);

-- Down

DROP TABLE IF EXISTS activity_feed;

Scenario 2: Adding Witness Verification

Use Case: Add witness confirmation workflow to submissions.

-- db/migrations/009-add-witness-fields.sql
-- Up

-- Add witness fields to submissions table
ALTER TABLE submissions ADD COLUMN witnessed_by INTEGER
    REFERENCES members(id) ON DELETE SET NULL;
ALTER TABLE submissions ADD COLUMN witnessed_on DATETIME;
ALTER TABLE submissions ADD COLUMN witness_verification_status TEXT
    CHECK (witness_verification_status IN ('pending', 'confirmed', 'declined'))
    DEFAULT 'pending';

-- Add indexes for witness queries
CREATE INDEX idx_submissions_witness_status ON submissions (witness_verification_status);
CREATE INDEX idx_submissions_witnessed_by ON submissions (witnessed_by);

-- Down

DROP INDEX IF EXISTS idx_submissions_witness_status;
DROP INDEX IF EXISTS idx_submissions_witnessed_by;
-- Note: Cannot DROP COLUMN in SQLite
-- Full rollback would require table recreation

Scenario 3: Backfilling Data

Use Case: Categorize existing awards by type.

-- db/migrations/007-add-award-type.sql
-- Up

-- Add award_type column with default
ALTER TABLE awards ADD COLUMN award_type TEXT DEFAULT 'species'
    CHECK (award_type IN ('species', 'meta_species', 'manual'));

-- Update existing awards based on their names
UPDATE awards
SET award_type = 'meta_species'
WHERE award_name IN ('Senior Specialist Award', 'Expert Specialist Award');

UPDATE awards
SET award_type = 'species'
WHERE award_name IN (
    'Anabantoids Specialist',
    'Catfish Specialist',
    'Killifish Specialist'
    -- ... etc
);

-- Down

-- Note: SQLite doesn't support DROP COLUMN
-- Data updates cannot be easily reversed without backup

Troubleshooting

Migration Didn't Apply

Symptoms:

• Application starts without error
• Schema changes not visible

Diagnosis:

# Check if migration is in migrations table
sqlite3 database.db "SELECT * FROM migrations WHERE name = 'XXX-migration-name.sql';"

# Check migration file exists
ls db/migrations/ | grep XXX-migration-name.sql

# Check migration file format
head -20 db/migrations/XXX-migration-name.sql
# Should start with "-- Up"

Solution:

• Ensure migration file is in db/migrations/ directory
• Verify filename follows naming convention
• Check "-- Up" section marker exists
• Restart application to retry

Migration Failed with Error

Symptoms:

• Application fails to start
• Error message in logs: "Migration failed: ..."

Diagnosis:

# Check application logs
npm run dev 2>&1 | grep -A 10 "Migration"

# Check database integrity
sqlite3 database.db "PRAGMA integrity_check;"

# Try migration SQL manually
sqlite3 database.db
sqlite> -- Copy "Up" section SQL here

Common Errors:

Error	Cause	Solution
table X already exists	Migration already partially applied	Remove from migrations table or use IF NOT EXISTS
no such table: X	Migration depends on table not yet created	Check migration order, ensure dependencies run first
foreign key constraint failed	Referenced table doesn't exist	Ensure referenced table is created in earlier migration
syntax error	Invalid SQL	Test SQL manually in sqlite3 CLI

Recovery:

# Option 1: Fix migration and retry (if not in production)
# Edit migration file to fix SQL
# Remove migration from tracking table
sqlite3 database.db "DELETE FROM migrations WHERE name = 'XXX-bad-migration.sql';"
# Restart application

# Option 2: Rollback (if database backup exists)
cp database.db.backup database.db
# Fix migration file
# Restart application

Schema Out of Sync

Symptoms:

• Different environments have different schemas
• Production works but development doesn't (or vice versa)

Diagnosis:

# Compare migrations applied
# Development:
sqlite3 src/database/database.db "SELECT id, name FROM migrations ORDER BY id;"

# Production:
ssh BAP "sqlite3 /mnt/basny-data/app/database/database.db 'SELECT id, name FROM migrations ORDER BY id;'"

# Compare schema
sqlite3 src/database/database.db ".schema submissions" > dev-schema.sql
ssh BAP "sqlite3 /mnt/basny-data/app/database/database.db '.schema submissions'" > prod-schema.sql
diff dev-schema.sql prod-schema.sql

Solution:

• Ensure both environments have same migration files
• Apply missing migrations (restart application)
• If schemas diverged significantly, restore from backup and reapply migrations

Related Documentation

• Database Schema - Complete schema documentation
• Production Deployment - Deployment procedures
• CLAUDE.md - Database patterns and architecture