Database Migrations

Yama provides a complete migration system to manage your database schema changes safely.

Overview

Migrations are versioned SQL files that track changes to your database schema. Yama generates migrations from your yama.yaml entities and applies them in order.

Generate a Migration

When you modify entities in yama.yaml, generate a migration:

yama migration:generate --name add_users_table

This creates a migration file in migrations/:

migrations/
└── 20231126120000_add_users_table.sql

Migration File Format

-- Migration: add_users_table
-- Created: 2023-11-26T12:00:00Z

-- Up
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

-- Down
DROP TABLE IF EXISTS users;

Apply Migrations

Apply pending migrations to your database:

yama migration:apply

For a specific environment:

yama migration:apply --env production

Check Migration Status

See which migrations have been applied:

yama migration:status

Output:

Migration Status
================
✓ 20231126120000_add_users_table
✓ 20231126130000_add_posts_table
○ 20231126140000_add_comments_table (pending)

View Migration History

See the complete migration history:

yama migration:history

Rollback Migrations

Roll back the last migration:

yama migration:rollback

Roll back multiple migrations:

yama migration:rollback --steps 3

Migration Sync Check

Check if your database is in sync with yama.yaml:

yama migration:check

Scaffold Common Changes

Quickly scaffold common schema changes:

# Add a new table
yama schema:scaffold add-table posts

# Add a column
yama schema:scaffold add-column users avatar_url

# Add an index
yama schema:scaffold add-index users email

Best Practices

1. Always Review Generated Migrations

Before applying, review the generated SQL:

cat migrations/20231126120000_add_users_table.sql

2. Test Migrations Locally First

# Apply to development database
yama migration:apply --env development

# Verify the changes
yama db inspect users

# Then apply to production
yama migration:apply --env production

3. Use Transactions

Wrap complex migrations in transactions:

-- Up
BEGIN;

ALTER TABLE users ADD COLUMN role VARCHAR(50) DEFAULT 'user';
UPDATE users SET role = 'admin' WHERE email LIKE '%@company.com';

COMMIT;

-- Down
BEGIN;

ALTER TABLE users DROP COLUMN role;

COMMIT;

4. Handle Data Migrations

For data migrations, use separate files:

-- Migration: backfill_user_roles
-- Type: data

-- Up
UPDATE users 
SET role = CASE 
  WHEN email LIKE '%@admin.com' THEN 'admin'
  ELSE 'user'
END
WHERE role IS NULL;

-- Down
-- Data migrations typically aren't reversible

5. Version Control

Always commit migrations to version control:

git add migrations/
git commit -m "Add users table migration"

Environment-Specific Migrations

Apply migrations to different environments:

# Development
yama migration:apply --env development

# Staging
yama migration:apply --env staging

# Production
yama migration:apply --env production

Each environment uses its own database URL from the corresponding .env file.

Troubleshooting

Migration Failed

If a migration fails:

Check the error message
Fix the SQL in the migration file
Re-run yama migration:apply

Out of Sync

If your database is out of sync:

# Check current state
yama migration:status

# Force sync (use with caution)
yama migration:apply --force

Conflicting Migrations

If you have conflicting migrations from different branches:

Merge the branches
Resolve conflicts in migration files
Run yama migration:apply