Migrate Create

Migration Naming Conventions

Use descriptive names that indicate the change:

✅ Good names:

• add_email_to_users
• create_categories_table
• add_index_on_transactions_date
• remove_deprecated_columns

❌ Bad names:

• migration1
• update
• fix
• changes

Example Migration Content

Up Migration (*_add_user_preferences.up.sql)

-- Add user preferences column
ALTER TABLE users ADD COLUMN preferences TEXT DEFAULT '{}';

-- Create index for faster lookups
CREATE INDEX idx_users_preferences ON users(preferences);

Down Migration (*_add_user_preferences.down.sql)

-- Remove index
DROP INDEX IF EXISTS idx_users_preferences;

-- Remove column
ALTER TABLE users DROP COLUMN preferences;

Migration Best Practices

1. Atomic changes: One logical change per migration
2. Always reversible: Write down migration that undoes up migration
3. Test both directions: Verify up and down migrations work
4. Safe operations: Consider impact on existing data
5. Backup first: Use /db-backup before applying migrations

Automatic Execution

Migrations run automatically:

• On app startup: Both make run-local and make docker-up
• In order: Sorted by timestamp prefix
• Once only: Tracks applied migrations to avoid re-running

Migration Workflow

1. Create migration files:

   make migrate-create NAME=add_budget_categories
   

2. Edit up migration: Add SQL to apply changes in migrations/*_add_budget_categories.up.sql

3. Edit down migration: Add SQL to revert changes in migrations/*_add_budget_categories.down.sql

4. Test locally:

   make run-local  # Applies migrations automatically
   

5. Verify in database:

   make sqlite-shell
   sqlite> .schema  # Check schema changes
   

6. Commit to version control:

   git add migrations/
   git commit -m "Add budget categories migration"
   

SQLite-Specific Considerations

SQLite has some limitations compared to PostgreSQL:

• No DROP COLUMN: Use table recreation pattern
• Limited ALTER TABLE: Some operations require table rebuild
• No concurrent DDL: Migrations lock the database

Example table recreation pattern:

-- Create new table with desired schema
CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    -- new column
    preferences TEXT DEFAULT '{}'
);

-- Copy data
INSERT INTO users_new (id, email)
SELECT id, email FROM users;

-- Swap tables
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

Troubleshooting

Migration fails

1. Check SQL syntax in migration files
2. Verify SQLite compatibility
3. Check logs: make docker-logs or console output
4. Restore from backup: /db-backup

Migration already applied

Migrations are tracked in schema_migrations table. Don't modify applied migrations.

Need to rollback

Currently, down migrations are not automatically executed. To rollback:

1. Backup: /db-backup
2. Manually run down SQL in /db-shell
3. Remove migration record from schema_migrations

See Also

• /db-shell - Execute SQL directly
• /db-backup - Backup before migrations
• make sqlite-stats - View database statistics