Database Migrations

ButterRobot uses a simple database migration system to manage database schema changes. This document explains how the migration system works and how to extend it.

Automatic Migrations

Migrations in ButterRobot are applied automatically when the application starts. This ensures your database schema is always up to date without requiring manual intervention.

The migration system:

Checks which migrations have been applied
Applies any pending migrations in sequential order
Records each successful migration in the schema_migrations table

Initial State

The initial migration (version 1) sets up the database with the following:

channels table for chat platforms
channel_plugin table for plugins associated with channels
users table for admin users with bcrypt password hashing
Default admin user with username "admin" and password "admin"

This migration represents the current state of the database schema. It is not backwards compatible with previous versions of ButterRobot.

Creating New Migrations

To add a new migration, follow these steps:

Open /internal/migration/migrations.go
Add a new migration version in the init() function:

Register(2, "Add example table", migrateAddExampleTableUp, migrateAddExampleTableDown)

Implement the up and down functions for your migration:

// Migration to add example table - version 2
func migrateAddExampleTableUp(db *sql.DB) error {
    _, err := db.Exec(`
        CREATE TABLE IF NOT EXISTS example (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
    `)
    return err
}

func migrateAddExampleTableDown(db *sql.DB) error {
    _, err := db.Exec(`DROP TABLE IF EXISTS example`)
    return err
}

Migration Guidelines

Incremental Changes: Each migration should make a small, focused change to the database schema.
Backward Compatibility: Ensure migrations are backward compatible with existing code when possible.
Test Thoroughly: Test both up and down migrations before deploying.
Document Changes: Add comments explaining the purpose of each migration.
Version Numbers: Use sequential version numbers for migrations.

How Migrations Work

The migration system tracks applied migrations in a schema_migrations table. When you run migrations, the system:

Checks which migrations have been applied
Applies any pending migrations in order
Records each successful migration in the schema_migrations table

When rolling back, it performs the down migrations in reverse order.

In Code Usage

The application automatically runs pending migrations when starting up. This is done in the initDatabase function.

You can also programmatically work with migrations:

// Get database instance
database, err := db.New(cfg.DatabasePath)
if err != nil {
    // Handle error
}
defer database.Close()

// Run migrations
if err := database.MigrateUp(); err != nil {
    // Handle error
}

// Check migration status
applied, pending, err := database.MigrationStatus()
if err != nil {
    // Handle error
}

3.1 KiB Raw Permalink Blame History