Migrations

Migrations

Mantis uses Diesel for database migrations, providing versioned schema management.

Overview

How Migrations Work

Migrations are SQL files that:

• Apply changes in order (up migrations)
• Can be reversed if needed (down migrations)
• Track applied state in __diesel_schema_migrations table

Migration Location

instinct/
└── migrations/
    ├── 00000000000000_diesel_initial_setup/
    │   └── up.sql
    ├── 2025-12-01-004132_create_users/
    │   ├── up.sql
    │   └── down.sql
    └── 2025-12-01-010016_create_tenants/
        ├── up.sql
        └── down.sql

Running Migrations

Using Diesel CLI

The recommended approach:

# Install Diesel CLI
cargo install diesel_cli --no-default-features --features postgres

# Run migrations
cd instinct
diesel migration run

# Check status
diesel migration list

Migration Status

Check Applied Migrations

-- Connect to database
psql -U mantis -d mantis

-- View migrations
SELECT version, run_on
FROM __diesel_schema_migrations
ORDER BY run_on DESC;

Example output:

       version        |          run_on
----------------------+---------------------------
 2024010300000        | 2024-01-03 10:00:00.000000
 2024010200000        | 2024-01-02 10:00:00.000000
 2024010100000        | 2024-01-01 10:00:00.000000

Pending Migrations

# Check for pending migrations
diesel migration pending

# List all migrations with status
diesel migration list

Creating Migrations

Generate Migration

cd instinct
diesel migration generate create_new_table

Creates a directory named with a YYYY-MM-DD-HHMMSS timestamp prefix (14 digits with hyphens) followed by the migration name:

migrations/
└── 2026-06-22-163256_create_new_table/
    ├── up.sql
    └── down.sql

Write Up Migration

-- migrations/2026-06-22-163256_create_new_table/up.sql

-- Note: UUIDs are generated by application code using crate::uuid::new_v8()
-- and passed in at insert time. Do not use DEFAULT gen_random_uuid() for
-- primary keys — application-generated UUIDv8 ensures sortable identifiers.
CREATE TABLE new_table (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_new_table_tenant ON new_table(tenant_id);

Write Down Migration

-- migrations/2026-06-22-163256_create_new_table/down.sql

DROP TABLE IF EXISTS new_table;

Test Migration

# Apply migration
diesel migration run

# Verify it works
psql -U mantis -c "\dt new_table"

# Test rollback
diesel migration revert

# Re-apply
diesel migration run

Migration Best Practices

1. Make Migrations Reversible

Always write down migrations:

-- up.sql
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT false;

-- down.sql
ALTER TABLE users DROP COLUMN email_verified;

2. Use Transactions

Wrap complex migrations in transactions:

-- up.sql
BEGIN;

ALTER TABLE users ADD COLUMN status VARCHAR(20);
UPDATE users SET status = 'active';
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

COMMIT;

3. Avoid Data Loss

Rename instead of dropping:

-- up.sql (safe)
ALTER TABLE users RENAME COLUMN old_name TO new_name;

-- up.sql (risky - data loss)
-- ALTER TABLE users DROP COLUMN old_name;
-- ALTER TABLE users ADD COLUMN new_name VARCHAR(255);

4. Add Indexes Concurrently

For large tables, avoid locking:

-- up.sql
CREATE INDEX CONCURRENTLY idx_dh_status ON deployment_history(status);

Caution

CREATE INDEX CONCURRENTLY cannot run inside a transaction.

5. Handle NOT NULL Additions

Add NOT NULL constraints safely:

-- up.sql
-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN role_id UUID;

-- Step 2: Populate existing rows
UPDATE users SET role_id = (SELECT id FROM roles WHERE name = 'default');

-- Step 3: Add constraint
ALTER TABLE users ALTER COLUMN role_id SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT fk_users_role
    FOREIGN KEY (role_id) REFERENCES roles(id);

Rolling Back Migrations

Revert Last Migration

diesel migration revert

Revert Multiple Migrations

# Revert the last 3 migrations
diesel migration revert -n 3

# Revert all applied migrations
diesel migration revert --all

Manual Rollback

If automated rollback fails:

-- 1. Execute down.sql manually
-- 2. Remove from tracking table
DELETE FROM __diesel_schema_migrations
WHERE version = '2024010400000';

Troubleshooting

Migration Failed

Error: migration 2024010400000 failed
Caused by: duplicate key value violates unique constraint

Resolution:

1. Check error message for specifics
2. Fix the issue in your migration
3. If partially applied, manually clean up
4. Re-run migration

Schema Out of Sync

# Regenerate schema file from database (run from within instinct/)
diesel print-schema > src/database/schema.rs

Alternatively, running diesel migration run automatically updates src/database/schema.rs per the diesel.toml configuration.

Locked Tables

-- Check for locks
SELECT * FROM pg_locks WHERE NOT granted;

-- Identify blocking query
SELECT pid, usename, query, state
FROM pg_stat_activity
WHERE pid IN (
    SELECT blocking_pid
    FROM pg_locks
    WHERE NOT granted
);

-- Terminate blocking query (use with caution)
SELECT pg_terminate_backend(<pid>);

Connection Limit Reached

Error: too many connections for database "mantis"

Resolution:

1. Check connection count
2. Increase max_connections in postgresql.conf
3. Use connection pooling
4. Close idle connections

CI/CD Integration

Pre-Deployment Check

scripts/check-migrations.sh

#!/bin/bash
# Check for pending migrations
PENDING=$(diesel migration pending 2>&1)
if [ -n "$PENDING" ]; then
    echo "Pending migrations found:"
    echo "$PENDING"
    exit 1
fi

echo "All migrations are up to date"

Deployment Pipeline

.github/workflows/deploy.yml

jobs:
  deploy:
    steps:
      - name: Run migrations
        run: |
          cd instinct
          diesel migration run
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

      - name: Deploy application
        run: |
          docker compose up -d

Kubernetes Job

apiVersion: batch/v1
kind: Job
metadata:
  name: mantis-migration
spec:
  template:
    spec:
      containers:
        - name: migration
          image: <registry>/mantis-mandible:latest
          command: ['mandible', '--migrate-only']
          env:
            - name: DATABASE_URL
              valueFrom:
                secretKeyRef:
                  name: mantis-database
                  key: url
      restartPolicy: OnFailure

Multi-Environment Management

Environment-Specific Migrations

# Development
DATABASE_URL="postgres://localhost/mantis_dev" diesel migration run

# Staging
DATABASE_URL="postgres://staging-db/mantis" diesel migration run

# Production
DATABASE_URL="postgres://prod-db/mantis" diesel migration run

Migration Testing

# Create test database
createdb mantis_migration_test

# Copy production schema
pg_dump -s mantis_prod | psql mantis_migration_test

# Test new migrations
DATABASE_URL="postgres://localhost/mantis_migration_test" diesel migration run

# Verify success
psql mantis_migration_test -c "\dt"

# Clean up
dropdb mantis_migration_test

Data Migrations

Large Data Updates

For large tables, batch updates:

-- up.sql
DO $$
DECLARE
    batch_size INTEGER := 1000;
    affected_rows INTEGER;
BEGIN
    LOOP
        UPDATE users
        SET status = 'active'
        WHERE status IS NULL
        AND id IN (
            SELECT id FROM users
            WHERE status IS NULL
            LIMIT batch_size
        );

        GET DIAGNOSTICS affected_rows = ROW_COUNT;
        EXIT WHEN affected_rows = 0;

        PERFORM pg_sleep(0.1);  -- Brief pause to reduce load
    END LOOP;
END $$;

Backfilling Data

-- up.sql
-- Add column
ALTER TABLE deployment_history ADD COLUMN duration_seconds INTEGER;

-- Backfill from existing data
UPDATE deployment_history
SET duration_seconds = EXTRACT(EPOCH FROM (completed_at - started_at))
WHERE completed_at IS NOT NULL AND started_at IS NOT NULL;

Next Steps

• Connection Pooling - Optimize connections
• Maintenance - Database maintenance
• Backup & Recovery - Backup procedures