Create a new database migration with up/down SQL files
Generate new migration files for database schema changes.
make migrate-create NAME=add_user_preferences
Replace add_user_preferences with a descriptive name for your migration.
Two files in migrations/ directory:
Up migration: YYYYMMDDHHMMSS_add_user_preferences.up.sql
Down migration: YYYYMMDDHHMMSS_add_user_preferences.down.sql
Use descriptive names that indicate the change:
✅ Good names:
add_email_to_userscreate_categories_tableadd_index_on_transactions_dateremove_deprecated_columns❌ Bad names:
migration1updatefixchanges*_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);
*_add_user_preferences.down.sql)-- Remove index
DROP INDEX IF EXISTS idx_users_preferences;
-- Remove column
ALTER TABLE users DROP COLUMN preferences;
/db-backup before applying migrationsMigrations run automatically:
make run-local and make docker-upCreate migration files:
make migrate-create NAME=add_budget_categories
Edit up migration:
Add SQL to apply changes in migrations/*_add_budget_categories.up.sql
Edit down migration:
Add SQL to revert changes in migrations/*_add_budget_categories.down.sql
Test locally:
make run-local # Applies migrations automatically
Verify in database:
make sqlite-shell
sqlite> .schema # Check schema changes
Commit to version control:
git add migrations/
git commit -m "Add budget categories migration"
SQLite has some limitations compared to PostgreSQL:
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;
make docker-logs or console output/db-backupMigrations are tracked in schema_migrations table. Don't modify applied migrations.
Currently, down migrations are not automatically executed. To rollback:
/db-backup/db-shellschema_migrations/db-shell - Execute SQL directly/db-backup - Backup before migrationsmake sqlite-stats - View database statistics