Database Migrations

CityRP's migration system provides a structured way to evolve your database schema over time while maintaining consistency across different environments. This system ensures that database changes are applied in the correct order and only once per environment.

Overview

The migration system consists of:

• Migration Registry: Tracks which migrations have been executed
• Execution Engine: Runs migrations in chronological order
• File-based Migrations: Timestamped Lua files containing schema changes
• Plugin Integration: Automatic loading from plugin directories
• Rollback Support: Optional down migrations for reverting changes

How It Works

Migration Table

The system uses a single migrations table with two fields:

• migration_id: Unique identifier for the migration
• created_at: Timestamp when the migration was executed

This table is automatically created and is the only table that uses "IF NOT EXISTS" logic.

File Structure

Migration files follow a strict naming convention:

sv_<unix_timestamp>_<descriptive-name>.lua

Examples:

• sv_1687120203_create-inventory-table.lua
• sv_1703448501_add-character-system.lua
• sv_1699211263_update-player-constraints.lua

File Locations

Core Migrations:

gamemode/core/migrations/sv_*.lua

Plugin Migrations:

plugins/<plugin_name>/migrations/sv_*.lua

Execution Order

1. Core migrations are loaded first
2. Plugin migrations are loaded via the DoPluginIncludes hook
3. All migrations are sorted by timestamp (oldest first)
4. Only unexecuted migrations are run
5. Each migration must call done() to mark completion

Basic Migration Structure

Every migration file must call cityrp.migrations.register() with an object containing up and down functions:

cityrp.migrations.register({
    up = function(done)
        -- Forward migration logic
        print("Applying migration changes")
        done() -- MUST call done() when complete
    end,
    down = function(done)
        -- Rollback logic (optional but recommended)
        print("Reverting migration changes")
        done() -- MUST call done() when complete
    end
})

Asynchronous Operations

For operations that use callbacks (like database queries):

cityrp.migrations.register({
    up = function(done)
        print("Starting database migration")
        mysql:RawQuery([[
            CREATE TABLE example (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(255) NOT NULL
            );
        ]], function(results)
            print("Migration completed successfully")
            done() -- Call done() in the callback
        end)
    end,
    down = function(done)
        mysql:RawQuery("DROP TABLE example;", done)
    end
})

Common Migration Patterns

Table Creation

cityrp.migrations.register({
    up = function(done)
        mysql:RawQuery([[
            CREATE TABLE characters (
                character_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                player_id VARCHAR(17) NOT NULL,
                first_name VARCHAR(64) NOT NULL,
                last_name VARCHAR(64) NOT NULL,
                face VARCHAR(32) NOT NULL,
                type VARCHAR(32) NOT NULL DEFAULT 'Citizen',
                data JSON,
                created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                deleted TIMESTAMP NULL,
                PRIMARY KEY (character_id),
                FOREIGN KEY (player_id) REFERENCES players(_SteamID64),
                INDEX idx_player_active (player_id, deleted)
            );
        ]], done)
    end,
    down = function(done)
        mysql:RawQuery("DROP TABLE characters;", done)
    end
})

Column Addition

cityrp.migrations.register({
    up = function(done)
        mysql:RawQuery([[
            ALTER TABLE players
            ADD COLUMN _BankID INT UNSIGNED NULL
            AFTER _Inventory;
        ]], done)
    end,
    down = function(done)
        mysql:RawQuery([[
            ALTER TABLE players
            DROP COLUMN _BankID;
        ]], done)
    end
})

Index Creation

cityrp.migrations.register({
    up = function(done)
        mysql:RawQuery([[
            CREATE INDEX idx_players_steamid
            ON players(_SteamID64);
        ]], done)
    end,
    down = function(done)
        mysql:RawQuery([[
            DROP INDEX idx_players_steamid
            ON players;
        ]], done)
    end
})

Foreign Key Management

cityrp.migrations.register({
    up = function(done)
        -- Get database name dynamically
        mysql:RawQuery("SET @database_name = '" .. cityrpserver["MySQL Database"] .. "'; " .. [[
            -- Get existing constraint name
            SELECT CONSTRAINT_NAME INTO @constraint_name
            FROM information_schema.KEY_COLUMN_USAGE
            WHERE TABLE_SCHEMA = @database_name
              AND TABLE_NAME = 'players'
              AND COLUMN_NAME = '_InventoryID';

            -- Drop old constraint
            SET @sql = CONCAT('ALTER TABLE players DROP FOREIGN KEY ', @constraint_name);
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            -- Add new constraint with ON DELETE behavior
            ALTER TABLE players
            ADD CONSTRAINT players_inventory_fk
            FOREIGN KEY (_InventoryID) REFERENCES inventory(inventory_id)
            ON DELETE SET NULL;
        ]], done)
    end,
    down = function(done)
        mysql:RawQuery([[
            ALTER TABLE players
            DROP FOREIGN KEY players_inventory_fk;
        ]], done)
    end
})

Plugin Migration Patterns

Plugin Table Creation

-- plugins/myplugin/migrations/sv_1234567890_create-plugin-tables.lua
cityrp.migrations.register({
    up = function(done)
        mysql:RawQuery([[
            CREATE TABLE plugin_data (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                player_id VARCHAR(17) NOT NULL,
                plugin_value TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                FOREIGN KEY (player_id) REFERENCES players(_SteamID64) ON DELETE CASCADE,
                INDEX idx_player_id (player_id)
            );
        ]], done)
    end,
    down = function(done)
        mysql:RawQuery("DROP TABLE plugin_data;", done)
    end
})

Plugin Configuration Migration

-- plugins/economy/migrations/sv_1234567891_add-economy-config.lua
cityrp.migrations.register({
    up = function(done)
        mysql:RawQuery([[
            CREATE TABLE economy_config (
                config_key VARCHAR(64) NOT NULL,
                config_value TEXT NOT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (config_key)
            );
        ]], function()
            -- Insert default configuration
            mysql:RawQuery([[
                INSERT INTO economy_config (config_key, config_value) VALUES
                ('starting_money', '5000'),
                ('salary_interval', '300'),
                ('max_bank_interest', '0.05');
            ]], done)
        end)
    end,
    down = function(done)
        mysql:RawQuery("DROP TABLE economy_config;", done)
    end
})

Data Migration

-- Migrate existing data to new format
cityrp.migrations.register({
    up = function(done)
        -- First, create new table
        mysql:RawQuery([[
            CREATE TABLE inventory_v2 (
                inventory_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                owner_id VARCHAR(17) NOT NULL,
                inventory_type VARCHAR(32) NOT NULL DEFAULT 'player',
                max_size INT UNSIGNED NOT NULL DEFAULT 50,
                data JSON,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (inventory_id),
                INDEX idx_owner (owner_id, inventory_type)
            );
        ]], function()
            -- Migrate existing inventory data
            mysql:RawQuery([[
                INSERT INTO inventory_v2 (owner_id, inventory_type, max_size, data)
                SELECT _SteamID64, 'player', 50,
                       JSON_OBJECT('legacy_inventory', _Inventory)
                FROM players
                WHERE _Inventory IS NOT NULL;
            ]], done)
        end)
    end,
    down = function(done)
        mysql:RawQuery("DROP TABLE inventory_v2;", done)
    end
})

Best Practices

Migration Design

Do:

• Use descriptive migration names that explain the change
• Include rollback logic in down functions
• Use proper foreign key constraints with appropriate ON DELETE/UPDATE behavior
• Test migrations on development database before deployment
• Use dynamic constraint name detection for complex operations
• Always call done() even for synchronous operations

Don't:

• Modify existing migration files after they've been deployed
• Use hardcoded constraint names (they may vary between environments)
• Forget to call done() callback
• Create migrations that depend on application code
• Use manual transactions (the system handles this)

Performance Considerations

-- For large data migrations, process in batches
cityrp.migrations.register({
    up = function(done)
        local function ProcessBatch(offset)
            mysql:RawQuery(string.format([[
                UPDATE large_table
                SET new_column = CONCAT('prefix_', old_column)
                WHERE id > %d AND id <= %d;
            ]], offset, offset + 1000), function()
                -- Check if more rows need processing
                mysql:RawQuery(string.format([[
                    SELECT COUNT(*) as count
                    FROM large_table
                    WHERE id > %d AND new_column IS NULL;
                ]], offset + 1000), function(results)
                    if results[1].count > 0 then
                        ProcessBatch(offset + 1000)
                    else
                        done()
                    end
                end)
            end)
        end

        ProcessBatch(0)
    end,
    down = function(done)
        mysql:RawQuery("UPDATE large_table SET new_column = NULL;", done)
    end
})

Error Handling

cityrp.migrations.register({
    up = function(done)
        mysql:RawQuery([[
            CREATE TABLE example (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(255) NOT NULL
            );
        ]], function(results, error)
            if error then
                ErrorNoHalt("Migration failed: " .. error)
                -- Still call done() to prevent hanging
                done()
                return
            end

            print("Migration completed successfully")
            done()
        end)
    end,
    down = function(done)
        mysql:RawQuery("DROP TABLE example;", done)
    end
})

Troubleshooting

Common Issues

Migration not running:

• Check file naming convention (sv_timestamp_name.lua)
• Verify file is in correct location
• Ensure migration isn't already in migrations table

Migration hanging:

• Verify done() is called in all code paths
• Check for syntax errors in SQL
• Ensure database connection is active

Foreign key errors:

• Use dynamic constraint name detection
• Check that referenced tables exist
• Verify column types match between tables

Debug Tips

-- Add logging to migrations
cityrp.migrations.register({
    up = function(done)
        print("Starting migration: create_example_table")
        mysql:RawQuery([[
            CREATE TABLE example (id INT PRIMARY KEY);
        ]], function(results, error)
            if error then
                print("Migration error: " .. error)
            else
                print("Migration completed successfully")
            end
            done()
        end)
    end,
    down = function(done)
        print("Rolling back migration: create_example_table")
        mysql:RawQuery("DROP TABLE example;", done)
    end
})

Manual Migration Management

-- Check migration status
SELECT * FROM migrations ORDER BY created_at DESC;

-- Manually mark migration as complete (use with caution)
INSERT INTO migrations (migration_id, created_at)
VALUES ('1234567890_example-migration', NOW());

-- Remove migration record to re-run (dangerous)
DELETE FROM migrations WHERE migration_id = '1234567890_example-migration';

Hooks and Events

Available Hooks

-- Called when all migrations complete
hook.Add("MigrationsCompleted", "MyPlugin.PostMigration", function()
    print("All migrations completed, initializing plugin data")
    -- Perform post-migration setup
end)

-- Called during plugin loading (used internally)
hook.Add("DoPluginIncludes", "Migrations.LoadPluginMigrations", function()
    -- Plugin migrations are loaded here
end)

Custom Migration Hooks

-- Add custom logic after specific migrations
hook.Add("MigrationsCompleted", "CustomSetup", function()
    -- Check if specific migration has run
    mysql:RawQuery([[
        SELECT 1 FROM migrations
        WHERE migration_id = '1234567890_character-system'
    ]], function(results)
        if #results > 0 then
            -- Character system migration has run, set up defaults
            print("Setting up default character data")
        end
    end)
end)