# Listmonk's Database Migrations Approach: Pure Go Schema Management

> Discover Listmonks pure Go database migrations approach. Learn how sequential functions manage schema changes idempotently against PostgreSQL without external tools.

- Repository: [Kailash Nadh/listmonk](https://github.com/knadh/listmonk)
- Tags: internals
- Published: 2026-05-19

---

**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](https://github.com/knadh/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`](https://github.com/knadh/listmonk/blob/main/v0.4.0.go), [`v0.7.0.go`](https://github.com/knadh/listmonk/blob/main/v0.7.0.go), or [`v6.2.0.go`](https://github.com/knadh/listmonk/blob/main/v6.2.0.go).

### Versioned Function Signatures

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

```go
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`](https://github.com/knadh/listmonk/blob/main/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:

```go
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`](https://github.com/knadh/listmonk/blob/main/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`](https://github.com/knadh/listmonk/blob/main/internal/migrations/migrations.go) implements a sequential walker:

```go
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`](https://github.com/knadh/listmonk/blob/main/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.