Best Practices

Migration Design

Write Reversible Migrations

Every .up.sql migration should have a corresponding .down.sql that completely reverses its changes.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

Always drop in reverse order: indexes first, then tables. This prevents dependency errors.

Keep Migrations Small and Focused

Each migration should do one logical thing. This makes them easier to:

Review and understand
Debug when issues occur
Rollback independently

-- 1709123456_create_users_table.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

Use Descriptive Migration Names

Generate migrations with clear, action-oriented names:

# Good names
geni new create_users_table
geni new add_email_index_to_users
geni new remove_deprecated_status_column
geni new alter_posts_add_published_at

# Avoid vague names
geni new update_schema
geni new fix_bug
geni new changes

The timestamp provides uniqueness, so focus the name on what the migration does.

Transaction Control

Default: Use Transactions

Geni runs migrations in transactions by default, which provides:

Atomicity: All changes succeed or all fail
Safety: Failed migrations don’t leave partial changes
Rollback: Automatic rollback on errors

migrations/1709123456_create_tables.up.sql

-- Runs in a transaction by default
CREATE TABLE users (id SERIAL PRIMARY KEY);
CREATE TABLE posts (id SERIAL PRIMARY KEY);
-- If either fails, both are rolled back

When to Disable Transactions

Some operations can’t run in transactions. Add -- transaction:no as the first line:

-- transaction:no
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

When disabling transactions, ensure the migration can be safely re-run if it fails partway through. Use IF NOT EXISTS and IF EXISTS clauses.

Data Safety

Test Migrations Locally First

Always test the complete migration cycle before production:

# 1. Create a test database
DATABASE_URL="postgres://localhost:5432/test_app" geni create

# 2. Run migrations up
DATABASE_URL="postgres://localhost:5432/test_app" geni up

# 3. Verify the schema
psql postgres://localhost:5432/test_app -c "\dt"

# 4. Test rollback
DATABASE_URL="postgres://localhost:5432/test_app" geni down -a 1

# 5. Verify rollback worked
psql postgres://localhost:5432/test_app -c "\dt"

# 6. Run up again to test idempotency
DATABASE_URL="postgres://localhost:5432/test_app" geni up

Use IF EXISTS and IF NOT EXISTS

Make migrations idempotent where possible:

Safe Migration Pattern

-- Up migration
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY
);

ALTER TABLE users ADD COLUMN IF NOT EXISTS email VARCHAR(255);

-- Down migration  
DROP TABLE IF EXISTS users;

This prevents errors if a migration is accidentally run twice.

Handle Data Migrations Carefully

When migrating data, use multiple steps:

Add the new column (nullable)

1709123456_add_full_name_column.up.sql

ALTER TABLE users ADD COLUMN full_name VARCHAR(500);

Populate the new column

1709123457_populate_full_name.up.sql

UPDATE users 
SET full_name = CONCAT(first_name, ' ', last_name)
WHERE full_name IS NULL;

Make the column non-nullable (if needed)

1709123458_make_full_name_required.up.sql

ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

Remove old columns (after verification)

1709123459_remove_name_columns.up.sql

ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

Never drop columns with data in the same migration that creates their replacement. Deploy the new column, verify data integrity, then remove old columns in a later migration.

Schema Management

Version Control Schema Files

Enable schema dumping to track your database structure:

# Automatically dump schema after migrations
DATABASE_URL="your_url" geni up

# This creates/updates schema.sql in your repo

Commit the schema.sql file to version control:

Provides a snapshot of your current schema
Helps reviewers understand structural changes
Enables schema comparison across branches

Customize Schema File Location

# Use a custom schema file name
DATABASE_SCHEMA_FILE="db/schema.sql" geni up

# Disable schema dumping in CI
DATABASE_NO_DUMP_SCHEMA=true geni up

Environment Management

Use Environment Variables

Store database credentials in environment variables, never in code:

.env.example

DATABASE_URL=postgres://localhost:5432/development
DATABASE_MIGRATIONS_FOLDER=./migrations
DATABASE_MIGRATIONS_TABLE=schema_migrations
DATABASE_SCHEMA_FILE=schema.sql
DATABASE_WAIT_TIMEOUT=30

Load them in your shell:

source .env
geni up

Add .env to .gitignore to prevent committing secrets.

Separate Configurations by Environment

DATABASE_URL=postgres://localhost:5432/app_development
DATABASE_WAIT_TIMEOUT=30

Performance Optimization

Create Indexes Concurrently (PostgreSQL)

For production databases with existing data:

migrations/1709123456_add_email_index.up.sql

-- transaction:no
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);

Concurrent index creation:

Doesn’t lock the table
Allows reads and writes during creation
Takes longer but doesn’t block production traffic

Schedule Large Migrations Carefully

For migrations that:

Modify large tables (millions of rows)
Create indexes on large datasets
Perform data transformations

Run during low-traffic periods:

# Example: Scheduled migration
0 2 * * * cd /app && DATABASE_URL=$PROD_DB geni up

Batch Large Data Updates

For updating many rows, batch the operations:

Batch Updates

-- Instead of one huge UPDATE
UPDATE users SET status = 'active' WHERE status IS NULL;

-- Use batches
UPDATE users 
SET status = 'active' 
WHERE status IS NULL 
AND id BETWEEN 1 AND 10000;

-- Repeat for each batch
-- Add LIMIT for other databases

CI/CD Integration

Automated Migration Testing

name: Test Migrations
on: [pull_request]

jobs:
  test-migrations:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_PASSWORD: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      - uses: actions/checkout@v3
      
      - name: Run migrations up
        env:
          DATABASE_URL: postgres://postgres:postgres@localhost:5432/test
        run: |
          # Install geni
          curl -fsSL -o geni https://github.com/emilpriver/geni/releases/latest/download/geni-linux-amd64
          chmod +x geni
          
          # Test migrations
          ./geni create
          ./geni up
          ./geni status
      
      - name: Test rollback
        env:
          DATABASE_URL: postgres://postgres:postgres@localhost:5432/test
        run: |
          ./geni down -a 1
          ./geni up

Production Deployment

Deploy Workflow

deploy:
  runs-on: ubuntu-latest
  steps:
    - uses: actions/checkout@v3
    
    - name: Run migrations
      env:
        DATABASE_URL: ${{ secrets.PRODUCTION_DATABASE_URL }}
        DATABASE_TOKEN: ${{ secrets.DATABASE_TOKEN }}
      run: |
        curl -fsSL -o geni https://github.com/emilpriver/geni/releases/latest/download/geni-linux-amd64
        chmod +x geni
        ./geni up
    
    - name: Deploy application
      run: |
        # Your deployment steps

Run migrations before deploying application code to ensure the database schema is ready for new features.

Library Usage

When using Geni as a Rust library:

src/main.rs

use geni;

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    // Run migrations on application startup
    geni::migrate_database(
        std::env::var("DATABASE_URL")?,
        None,                           // Token (for LibSQL)
        "schema_migrations".to_string(),
        "./migrations".to_string(),
        "schema.sql".to_string(),
        Some(30),                       // Wait timeout
        false,                          // Skip schema dump in production
    )
    .await?;

    // Your application code
    Ok(())
}

Benefits:

Automatic migrations on startup
No separate migration step in deployment
Guaranteed schema version matches code

For production, consider running migrations as a separate step before starting multiple application instances to avoid race conditions.

Common Anti-Patterns to Avoid

❌ Modifying Applied Migrations

Don’t do this:

Editing migration files that have been applied to production
Changing timestamps of existing migrations

Why it’s bad:

Migrations are already recorded as applied
Changes won’t be applied to existing databases
Creates inconsistency between environments

Do this instead:

Create a new migration to fix issues
Keep historical migrations as-is

❌ Deleting Migrations

Don’t do this:

Removing migration files from the migrations folder
Cleaning up “old” migrations

Why it’s bad:

Breaks rollback capability
Causes errors when setting up new environments
Loses historical context

Do this instead:

Keep all migrations in version control
Use migrations as documentation of schema evolution

❌ Deploying Code Before Migrations

Don’t do this:

Deploying application code that requires new schema
Running migrations after the application starts

Why it’s bad:

Application errors until migrations complete
Downtime during migration window

Do this instead:

Run migrations first
Deploy backward-compatible changes
Use feature flags for multi-step rollouts

❌ Manual Schema Changes

Don’t do this:

Running SQL directly in production
“Quick fixes” that bypass migrations

Why it’s bad:

Changes not tracked in version control
Environments drift out of sync
Can’t reproduce or rollback

Do this instead:

Always create a migration
Test it locally
Deploy through normal process

Quick Reference

Essential Commands

# Create a new migration
geni new migration_name

# Check pending migrations
geni status

# Apply all pending migrations
geni up

# Rollback last migration
geni down

# Rollback multiple migrations
geni down -a 3

# Create database
geni create

# Dump current schema
geni dump

Environment Variables

Variable	Required	Default	Purpose
`DATABASE_URL`	Yes	-	Database connection string
`DATABASE_TOKEN`	No	-	LibSQL/Turso auth token
`DATABASE_MIGRATIONS_FOLDER`	No	`./migrations`	Migration files location
`DATABASE_MIGRATIONS_TABLE`	No	`schema_migrations`	Tracking table name
`DATABASE_SCHEMA_FILE`	No	`schema.sql`	Schema dump filename
`DATABASE_WAIT_TIMEOUT`	No	`30`	Connection timeout (seconds)
`DATABASE_NO_DUMP_SCHEMA`	No	`false`	Disable schema dumping

Get Started

Core Concepts

Commands

Configuration

Database Support

Integration

Guides

Best Practices

Migration Design

Write Reversible Migrations

Keep Migrations Small and Focused

Use Descriptive Migration Names

Transaction Control

Default: Use Transactions

When to Disable Transactions

Data Safety

Test Migrations Locally First

Use IF EXISTS and IF NOT EXISTS

Handle Data Migrations Carefully

Schema Management

Version Control Schema Files

Customize Schema File Location

Environment Management

Use Environment Variables

Separate Configurations by Environment

Performance Optimization

Create Indexes Concurrently (PostgreSQL)

Schedule Large Migrations Carefully

Batch Large Data Updates

CI/CD Integration

Automated Migration Testing

Production Deployment

Library Usage

Common Anti-Patterns to Avoid

Quick Reference

Essential Commands

Environment Variables

Build docs developers (and LLMs) love

Get Started

Core Concepts

Commands

Configuration

Database Support

Integration

Guides

​Migration Design

​Write Reversible Migrations

​Keep Migrations Small and Focused

​Use Descriptive Migration Names

​Transaction Control

​Default: Use Transactions

​When to Disable Transactions

​Data Safety

​Test Migrations Locally First

​Use IF EXISTS and IF NOT EXISTS

​Handle Data Migrations Carefully

​Schema Management

​Version Control Schema Files

​Customize Schema File Location

​Environment Management

​Use Environment Variables

​Separate Configurations by Environment

​Performance Optimization

​Create Indexes Concurrently (PostgreSQL)

​Schedule Large Migrations Carefully

​Batch Large Data Updates

​CI/CD Integration

​Automated Migration Testing

​Production Deployment

​Library Usage

​Common Anti-Patterns to Avoid

​Quick Reference

​Essential Commands

​Environment Variables

Build docs developers (and LLMs) love

Migration Design

Write Reversible Migrations

Keep Migrations Small and Focused

Use Descriptive Migration Names

Transaction Control

Default: Use Transactions

When to Disable Transactions

Data Safety

Test Migrations Locally First

Use IF EXISTS and IF NOT EXISTS

Handle Data Migrations Carefully

Schema Management

Version Control Schema Files

Customize Schema File Location

Environment Management

Use Environment Variables

Separate Configurations by Environment

Performance Optimization

Create Indexes Concurrently (PostgreSQL)

Schedule Large Migrations Carefully

Batch Large Data Updates

CI/CD Integration

Automated Migration Testing

Production Deployment

Library Usage

Common Anti-Patterns to Avoid

Quick Reference

Essential Commands

Environment Variables