Listmonk's Database Migrations Approach: Pure Go Schema Management

Listmonk implements a lightweight, version-controlled migration system in pure Go using sequential functions stored in internal/migrations that execute idempotent SQL against PostgreSQL at startup, tracking state in the settings table without external tooling.

Listmonk is a high-performance, self-hosted newsletter and mailing list manager written in Go. Understanding listmonk's database migrations approach is essential for operators upgrading instances or contributors modifying the schema, as the system eschews external tools in favor of a tightly integrated, version-driven orchestrator.

Migration Architecture Overview

Instead of relying on third-party tools like Flyway or Goose, listmonk embeds its entire migration logic within the application binary. The system lives under internal/migrations and follows a strict naming convention where each schema change resides in a version-specific file such as v0.4.0.go, v0.7.0.go, or v6.2.0.go.

Versioned Function Signatures

Each migration is encapsulated as a self-contained Go function following the uniform signature:

func Vx_y_z(db *sqlx.DB, fs stuffbin.FileSystem, ko *koanf.Koanf, lo *log.Logger) error

This design makes every migration type-checked at compile time and allows access to the database connection (sqlx.DB), embedded filesystem assets (stuffbin), configuration (koanf), and structured logging. For example, the V6_2_0 migration in internal/migrations/v6.2.0.go handles JSONB transformations on SMTP settings.

Idempotent SQL Execution

Listmonk writes all migration SQL to be idempotent—safe to run multiple times without side effects. This safety mechanism prevents failures if a migration is interrupted and re-executed.

Consider the V6_2_0 implementation that adds msg_retry_delay to SMTP configurations:

func V6_2_0(db *sqlx.DB, fs stuffbin.FileSystem, ko *koanf.Koanf, lo *log.Logger) error {
    _, err := db.Exec(`
        UPDATE settings SET value = s.updated
        FROM (
            SELECT JSONB_AGG(
                CASE WHEN v ? 'msg_retry_delay' THEN v
                     ELSE JSONB_SET(v, '{msg_retry_delay}', '"10ms"'::JSONB)
                END
            ) AS updated FROM settings, JSONB_ARRAY_ELEMENTS(value) v WHERE key = 'smtp'
        ) s WHERE key = 'smtp'
        AND EXISTS (
            SELECT 1 FROM JSONB_ARRAY_ELEMENTS(value) v WHERE NOT (v ? 'msg_retry_delay')
        );
    `)
    return err
}

The WHERE EXISTS clause ensures the update only runs when necessary, making the operation inherently idempotent.

The Migration Runner Orchestration

The migration lifecycle begins in cmd/main.go during application initialization. The process flow is:

1. Establish the database connection using sqlx.Open
2. Ensure the settings table exists via ensureSettingsTable
3. Invoke migrations.Run() to execute pending changes

Sequential Version Detection

The migrations.Run() function in internal/migrations/migrations.go implements a sequential walker:

func Run(db *sqlx.DB, fs stuffbin.FileSystem, ko *koanf.Koanf, lo *log.Logger) error {
    cur := getCurrentVersion(db) // Reads from settings table
    
    migrations := []struct {
        ver string
        fn  func(*sqlx.DB, stuffbin.FileSystem, *koanf.Koanf, *log.Logger) error
    }{
        {"0.4.0", V0_4_0},
        {"0.7.0", V0_7_0},
        // ... additional versions ...
        {"6.2.0", V6_2_0},
    }

    for _, m := range migrations {
        if versionLessThan(cur, m.ver) {
            lo.Printf("Applying migration %s → %s", cur, m.ver)
            if err := m.fn(db, fs, ko, lo); err != nil {
                return fmt.Errorf("migration %s failed: %w", m.ver, err)
            }
            // Update schema version atomically
            _, err := db.Exec(`
                UPDATE settings SET value = $1 WHERE key = 'schema_version'`, m.ver)
            if err != nil {
                return err
            }
            cur = m.ver
        }
    }
    return nil
}

This approach guarantees ordered execution and automatic skipping of already-applied versions.

Embedded Assets and Dependencies

For migrations requiring supplemental assets—such as large seed scripts or static data—the stuffbin.FileSystem parameter provides access to files compiled directly into the binary. This eliminates external file dependencies during deployment, keeping the migration footprint minimal and self-contained within the listmonk executable.

Version Tracking and State Management

Listmonk persists the current schema version in the settings table under the key schema_version. This internal state management eliminates the need for separate migration metadata tables common in external tools.

When getCurrentVersion queries the database, it defaults to "0.0.0" if no version record exists, ensuring fresh installations execute the complete migration chain while existing installations skip to the appropriate delta.

Summary

• Listmonk's database migrations approach uses pure Go functions in internal/migrations rather than external CLI tools.
• Each migration follows a strict Vx_y_z naming convention with standardized parameters for database connections, embedded files, configuration, and logging.
• SQL statements are written to be idempotent, typically using WHERE EXISTS or ON CONFLICT clauses to prevent duplicate execution errors.
• The migrations.Run() dispatcher in internal/migrations/migrations.go sequentially executes pending versions by comparing the stored schema_version against the target migration list.
• State persistence uses the existing settings table, requiring no additional migration infrastructure or tables.
• Embedded assets via stuffbin.FileSystem allow complex migrations to include compiled-in SQL files without external dependencies.

Frequently Asked Questions

How does listmonk track which schema migrations have run?

Listmonk stores the current schema version in the settings table under the key schema_version. During startup, the migrations.Run() function compares this stored version against its internal ordered list of migration functions. Only migrations with version numbers greater than the stored value execute, and upon successful completion, the function updates the schema_version record to match the newly applied version.

What makes listmonk's migration SQL idempotent?

Each migration uses defensive SQL patterns such as WHERE EXISTS subqueries or ON CONFLICT ... DO UPDATE clauses to ensure operations only modify data when necessary. For instance, the V6_2_0 migration checks for the absence of the msg_retry_delay key before attempting JSONB transformations, allowing the function to run safely multiple times without corrupting data or throwing duplicate-key errors.

Why doesn't listmonk use external migration tools like Flyway or Goose?

By implementing migrations in pure Go within internal/migrations, listmonk maintains zero external dependencies for database schema management. This approach uses the same sqlx connection pool as the application code, reduces deployment complexity by eliminating additional binaries, and allows migrations to leverage Go's type system, compile-time checking, and native error handling rather than managing external tool installations and shell scripts.

Can I manually execute a specific listmonk migration for testing?

Yes, individual migration functions are exported from their versioned files (e.g., migrations.V6_2_0) and can be invoked directly in test code or manual scripts. You must provide the standard parameters: a *sqlx.DB connection, stuffbin.FileSystem, *koanf.Koanf configuration, and *log.Logger. This enables isolated testing of specific schema changes without running the full migration chain.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}