Skip to main content
Geni runs all migrations within database transactions by default, ensuring that migrations either complete fully or are rolled back entirely if an error occurs. This prevents partial migrations from leaving your database in an inconsistent state.

​
Default Transaction Behavior

When you run a migration, Geni automatically wraps it in a transaction: 
-- Your migration file: 1234567890_create_users.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE INDEX idx_users_name ON users(name);
Geni executes this as: 
BEGIN;
    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL
    );
    CREATE INDEX idx_users_name ON users(name);
COMMIT;
If any statement fails, the entire transaction is rolled back, leaving your database unchanged.

​
How Transactions Work

From the PostgreSQL driver implementation (src/lib/database_drivers/postgres.rs:70-96): 
fn execute<'a>(
    &'a mut self,
    query: &'a str,
    run_in_transaction: bool,
) -> Pin<Box<dyn Future<Output = Result<(), anyhow::Error>> + '_>> {
    let fut = async move {
        if run_in_transaction {
            let mut tx = self.db.begin().await?;
            match tx.execute(query).await {
                Ok(_) => {
                    tx.commit().await?;
                }
                Err(e) => {
                    tx.rollback().await?;
                    bail!(e)
                }
            }
            return Ok(());
        } else {
            self.db.execute(query).await?;
        }

        Ok(())
    };

    Box::pin(fut)
}
Transactions ensure atomicity - either all statements in a migration succeed, or none of them do. This is critical for maintaining database consistency.

​
Disabling Transactions

Some database operations cannot run inside a transaction. For these cases, use the transaction:no directive as the first line of your migration file.

​
The transaction:no Directive

Add -- transaction:no (or -- transaction: no with a space) as the very first line: 
-- transaction:no
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
Geni detects this directive and runs the migration without a transaction wrapper.

​
Detection Logic

From the source code (src/lib/utils.rs:46-58): 
pub fn should_run_in_transaction(query: &str) -> bool {
    let first_line = query.split_once('\n').unwrap_or(("", "")).0;

    if first_line.contains("transaction: no") {
        return false;
    }

    if first_line.contains("transaction:no") {
        return false;
    }

    true
}
The directive must be on the first line. If it appears anywhere else in the file, it will be ignored and the migration will run in a transaction.

​
When to Disable Transactions

Certain database operations require running outside of transactions. Here are common scenarios:

​
PostgreSQL: Concurrent Index Creation

Creating indexes concurrently locks the table less but cannot run in a transaction: 
-- transaction:no
CREATE INDEX CONCURRENTLY idx_products_name ON products(name);
CREATE INDEX CONCURRENTLY idx_products_category ON products(category_id);
Without CONCURRENTLY, index creation locks the table for writes. With CONCURRENTLY, the index builds without blocking writes, but requires running outside a transaction.

​
PostgreSQL: Database-Level Operations

Operations that affect the entire database: 
-- transaction:no
ALTER DATABASE myapp SET timezone TO 'UTC';

​
PostgreSQL: Changing Enum Types

Adding values to existing enum types: 
-- transaction:no
ALTER TYPE user_role ADD VALUE 'moderator';

​
MySQL: Certain DDL Statements

MySQL implicitly commits some DDL statements: 
-- transaction:no
ALTER TABLE users ENGINE=InnoDB;

​
Transaction Support by Database

Transaction behavior varies across database systems:
DatabaseDefault TransactionNotes
PostgreSQL✅ Full supportMost DDL can run in transactions
MySQL⚠️ Partial supportSome DDL auto-commits
MariaDB⚠️ Partial supportSome DDL auto-commits
SQLite✅ Full supportAll DDL transactional
LibSQL✅ Full supportAll DDL transactional

​
PostgreSQL Example

From the driver implementation: 
if run_in_transaction {
    let mut tx = self.db.begin().await?;
    match tx.execute(query).await {
        Ok(_) => {
            tx.commit().await?;
        }
        Err(e) => {
            tx.rollback().await?;
            bail!(e)
        }
    }
    return Ok(());
}

​
SQLite/LibSQL Example

From the SQLite driver (src/lib/database_drivers/sqlite.rs:52-68): 
fn execute<'a>(
    &'a mut self,
    query: &'a str,
    run_in_transaction: bool,
) -> Pin<Box<dyn Future<Output = Result<(), anyhow::Error>> + '_>> {
    let fut = async move {
        if run_in_transaction {
            self.db.execute_transactional_batch(query).await?;
        } else {
            self.db.execute_batch(query).await?;
        }

        Ok(())
    };

    Box::pin(fut)
}
SQLite and LibSQL use execute_transactional_batch() for transactional migrations and execute_batch() for non-transactional ones.

​
Real-World Examples

​
Example 1: Standard Migration (With Transaction)

-- 1709123456_add_user_roles.up.sql
CREATE TABLE user_roles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    role VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);

INSERT INTO user_roles (user_id, role)
SELECT id, 'user' FROM users WHERE role IS NULL;
This runs in a transaction - if the INSERT fails, the table and index are rolled back.

​
Example 2: Concurrent Index (No Transaction)

-- transaction:no
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at);
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);
Runs without a transaction to allow concurrent index creation.

​
Example 3: Mixed Operations (No Transaction)

-- transaction:no
-- Add new enum value (requires no transaction in PostgreSQL)
ALTER TYPE order_status ADD VALUE 'refunded';

-- Then use it
UPDATE orders SET status = 'refunded' WHERE refund_processed = true;
Risks of Disabling TransactionsWhen using transaction:no:
  • If the migration fails partway through, you’ll have partial changes
  • You may need to manually fix the database state
  • Consider breaking complex migrations into smaller, safer pieces

​
Rollback Transactions

The same transaction logic applies to rollbacks (geni down). Down migrations also run in transactions by default: 
-- 1709123456_add_user_roles.down.sql
DROP INDEX IF EXISTS idx_user_roles_user_id;
DROP TABLE IF EXISTS user_roles;
To disable transactions for rollbacks: 
-- transaction:no
DROP INDEX CONCURRENTLY IF EXISTS idx_products_name;

​
Best Practices

​
Keep Transactions When Possible

Only use transaction:no when absolutely necessary: 
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL
);

CREATE INDEX idx_orders_user_id ON orders(user_id);

​
Test Non-Transactional Migrations Carefully

Before running in production: 
# Test in a staging environment
DATABASE_URL="postgres://staging/myapp" geni up

# If it fails, you may need to manually clean up
# Fix the migration and try again

​
Document Why Transactions Are Disabled

Always add a comment explaining why: 
-- transaction:no
-- Required: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
-- This allows the table to remain writable during index creation
CREATE INDEX CONCURRENTLY idx_large_table_column ON large_table(column);

​
Consider Breaking Up Large Migrations

Instead of one large migration without transactions: 
-- Avoid: One large non-transactional migration
-- transaction:no
CREATE INDEX CONCURRENTLY idx_a ON table_a(col);
CREATE INDEX CONCURRENTLY idx_b ON table_b(col);
CREATE INDEX CONCURRENTLY idx_c ON table_c(col);
ALTER TYPE status ADD VALUE 'new_status';
Create multiple smaller migrations: 
geni new create_index_table_a  # transaction:no for concurrent index
geni new create_index_table_b  # transaction:no for concurrent index  
geni new create_index_table_c  # transaction:no for concurrent index
geni new add_new_status_value  # transaction:no for enum change

​
Testing Transaction Behavior

You can verify transaction behavior by deliberately introducing an error: 
-- Test with transaction (default)
CREATE TABLE test_table (id SERIAL PRIMARY KEY);
INSERT INTO test_table (id) VALUES (1);
THIS_WILL_CAUSE_AN_ERROR;

-- After migration fails, check: table should NOT exist

-- transaction:no
-- Test without transaction
CREATE TABLE test_table (id SERIAL PRIMARY KEY);
INSERT INTO test_table (id) VALUES (1);
THIS_WILL_CAUSE_AN_ERROR;

-- After migration fails, check: table WILL exist with one row
Without transactions, a failed migration could leave your database in an inconsistent state:
  1. With Transaction: Migration fails → entire migration rolls back → database unchanged → fix and retry
  2. Without Transaction: Migration fails → partial changes remain → manual cleanup required → risk of data corruption
Transactions provide a safety net, especially in production environments where database consistency is critical.

​
Environment-Specific Considerations

​
Development

Transactions help you iterate quickly: 
# Migration fails - no problem, database is unchanged
geni up

# Fix the migration file
vim migrations/1234567890_create_users.up.sql

# Try again - clean slate
geni up

​
Production

Transactions prevent partial deployments: 
# In CI/CD pipeline
geni up  # Either fully succeeds or fully fails
          # Never leaves database in half-migrated state

​
Staging

Test non-transactional migrations here first: 
# Test concurrent index creation on production-sized data
DATABASE_URL="$STAGING_URL" geni up

# Monitor performance and locking
# Verify the migration completes successfully
# Then deploy to production with confidence

Build docs developers (and LLMs) love

Get started for freeTalk to us