Migration Examples

These examples demonstrate database migration patterns and SQL file organization.

Referenced by: migrations.md, schema-organization.md

Example 1: Add Table Migration

-- db/316_graph_my_feature.sql

-- Purpose: Add my_feature to graph
-- Dependencies: 303_graph_node.sql

BEGIN;

-- Add new table
CREATE TABLE graph.my_feature (
    id BIGSERIAL PRIMARY KEY,
    node_id BIGINT NOT NULL REFERENCES graph.node(id),
    feature_data JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add index
CREATE INDEX idx_my_feature_node 
    ON graph.my_feature(node_id);

-- Add function
CREATE FUNCTION graph.get_my_feature(
    p_node_id BIGINT
) RETURNS TABLE (
    id BIGINT,
    feature_data JSONB
) AS $$
BEGIN
    RETURN QUERY
    SELECT id, feature_data
    FROM graph.my_feature
    WHERE node_id = p_node_id;
END;
$$ LANGUAGE plpgsql;

COMMIT;

Example 2: Add Column Migration

-- Add column with default
ALTER TABLE schema.existing_table
ADD COLUMN new_column TEXT DEFAULT 'default_value';

-- Update existing rows if needed
UPDATE schema.existing_table
SET new_column = 'computed_value'
WHERE new_column IS NULL;

-- Make NOT NULL after data populated
ALTER TABLE schema.existing_table
ALTER COLUMN new_column SET NOT NULL;

Example 3: Add Function Migration

CREATE OR REPLACE FUNCTION schema.new_function(
    p_param1 TEXT,
    p_param2 INT DEFAULT 10
) RETURNS TABLE (
    result_col1 TEXT,
    result_col2 INT
) AS $$
BEGIN
    RETURN QUERY
    SELECT col1, col2
    FROM schema.table
    WHERE col1 = p_param1
    LIMIT p_param2;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION schema.new_function IS
    'Function description and usage';

Example 4: Add Index Migration

-- Regular index
CREATE INDEX idx_table_column 
    ON schema.table(column);

-- Composite index
CREATE INDEX idx_table_multi 
    ON schema.table(col1, col2);

-- Partial index
CREATE INDEX idx_table_active 
    ON schema.table(column)
    WHERE status = 'active';

-- Check if exists first
CREATE INDEX IF NOT EXISTS idx_table_column
    ON schema.table(column);

Example 5: Data Migration with Verification

-- Wrap in transaction
BEGIN;

-- Transform data
UPDATE schema.table
SET new_format = 
    (old_format::jsonb || '{"version": 2}')::jsonb
WHERE old_format IS NOT NULL;

-- Verify
DO $$
DECLARE
    unmigrated_count INT;
BEGIN
    SELECT COUNT(*) INTO unmigrated_count
    FROM schema.table
    WHERE old_format IS NOT NULL 
        AND new_format IS NULL;
        
    IF unmigrated_count > 0 THEN
        RAISE EXCEPTION 'Migration incomplete: % rows', unmigrated_count;
    END IF;
END $$;

COMMIT;

Example 6: Rollback Migration

-- Create reverse migration file
-- db/316_rollback_my_feature.sql

BEGIN;

DROP FUNCTION IF EXISTS graph.get_my_feature(BIGINT);
DROP TABLE IF EXISTS graph.my_feature CASCADE;

COMMIT;

Example 7: Test Migration Success

import { test } from '@/test-utils-integration/config/database.context';

test('migration adds table', async ({ newDbClient }) => {
    const result = await newDbClient.query(`
        SELECT EXISTS (
            SELECT FROM pg_tables
            WHERE schemaname = 'graph'
            AND tablename = 'my_feature'
        ) as exists
    `);
    
    expect(result.rows[0].exists).toBe(true);
});

Example 8: Test Data Migration

import { test } from '@/test-utils-integration/config/database.context';

test('migration transforms data', async ({ newDbClient }) => {
    // Insert test data
    await newDbClient.query(`
        INSERT INTO schema.table (old_format)
        VALUES ('{"version": 1}')
    `);
    
    // Run migration
    await newDbClient.query(migrationSQL);
    
    // Verify transformation
    const result = await newDbClient.query(`
        SELECT new_format
        FROM schema.table
        WHERE old_format IS NOT NULL
    `);
    
    expect(result.rows[0].new_format.version).toBe(2);
});