When your code and database disagree
You push a new version of your Go service that expects a user_preferences table. The old version is still running on half your servers. The new version starts, tries to query the table, and crashes. Or worse, it silently drops a column that the old version still needs. Database schema changes are one of the few places where your code and your data live in different time zones. You need a way to evolve the database structure without breaking running instances or losing data.
Go leaves database schema management out of the standard library. The database/sql package handles connections and queries, but it deliberately stays away from schema evolution. That design choice keeps the standard library small and database-agnostic. It also means you have to pick a strategy. The industry standard is a versioned migration system. You write SQL files that describe how to move from version N to N+1. A tracking table in the database remembers which versions have already run. On startup, your application reads that table, finds the missing versions, and applies them in order.
The tracking table is usually called schema_migrations or migrations. It holds two columns: a version identifier and a checksum or hash of the SQL file. The hash prevents replay attacks and catches cases where someone edits a migration file after it has already run. When your application starts, it queries the table, compares the results against the available migration files, and executes the missing ones sequentially.
Migrations are historical records. Treat them as append-only logs, not reversible experiments.
Manual implementation
Here is the simplest manual implementation. It creates the tracking table and loads the history of applied versions into memory.
// InitTrackingTable ensures the version log exists and returns applied versions.
func InitTrackingTable(db *sql.DB) (map[string]bool, error) {
// Create the tracking table if it does not exist yet.
_, err := db.Exec(`
CREATE TABLE IF NOT EXISTS schema_migrations (
version TEXT PRIMARY KEY,
checksum TEXT NOT NULL
)
`)
if err != nil {
return nil, fmt.Errorf("create tracking table: %w", err)
}
// Fetch already applied versions to skip them later.
rows, err := db.Query("SELECT version FROM schema_migrations")
if err != nil {
return nil, fmt.Errorf("query applied versions: %w", err)
}
defer rows.Close()
applied := make(map[string]bool)
for rows.Next() {
var version string
if err := rows.Scan(&version); err != nil {
return nil, fmt.Errorf("scan version: %w", err)
}
applied[version] = true
}
return applied, nil
}
The second half of the runner iterates through your migration definitions, skips anything already in the map, and applies the rest inside transactions.
// ApplyPending runs unapplied migrations sequentially.
func ApplyPending(db *sql.DB, applied map[string]bool, migrations []Migration) error {
for _, m := range migrations {
// Skip versions that have already run successfully.
if applied[m.Version] {
continue
}
// Execute the migration inside a transaction for safety.
tx, err := db.Begin()
if err != nil {
return fmt.Errorf("begin transaction: %w", err)
}
_, err = tx.Exec(m.SQL)
if err != nil {
tx.Rollback()
return fmt.Errorf("apply migration %s: %w", m.Version, err)
}
// Record the version and checksum before committing.
_, err = tx.Exec(
"INSERT INTO schema_migrations (version, checksum) VALUES ($1, $2)",
m.Version, m.Checksum,
)
if err != nil {
tx.Rollback()
return fmt.Errorf("record migration: %w", err)
}
if err := tx.Commit(); err != nil {
return fmt.Errorf("commit migration: %w", err)
}
}
return nil
}
How the execution loop works
The code above follows a strict startup sequence. First, it guarantees the tracking table exists. Then it loads the history of applied versions into a map. The loop iterates through your migration definitions, skips anything already in the map, and runs the rest. Each migration runs inside its own transaction. If the SQL fails, the transaction rolls back, leaving the database exactly as it was before the attempt. The version record only gets inserted after the SQL succeeds, so a failed migration will retry on the next startup.
This pattern works because it treats schema changes as data. The database itself becomes the source of truth for what has been applied. You do not need external state files or environment variables to track progress. The only requirement is that your migration files are ordered and immutable once deployed.
Go conventions apply to migrations too. Always pass context.Context as the first parameter to your migration runner, even if you only use it for cancellation during long-running backfills. Name your migration files with zero-padded numbers so 01 sorts before 10. Keep migration files idempotent when possible, but rely on the version tracker for strict ordering. The community accepts the verbosity of if err != nil { return fmt.Errorf("...: %w", err) } because it makes the failure path visible during startup. Do not swallow migration errors. A failed migration should block the application from starting.
If you forget to handle an error from db.Exec, the compiler will not stop you, but your application will silently skip the migration. The next query will fail with pq: relation "users" does not exist or a similar driver-specific message. Always wrap migration execution in explicit error checks. The compiler complains with assignment mismatch: 2 variables but db.Exec returns 1 value if you try to capture a result set from a statement that does not return rows, so use db.Query for SELECT and db.Exec for everything else.
Track the hash. Verify the order. Let the database tell you what has run.
The zero-downtime deployment pattern
Running migrations sounds straightforward until you hit production. The first trap is concurrent execution. If you deploy to three servers at once, all three will try to run the same migration simultaneously. Most migration tools solve this with a database-level advisory lock or a dedicated lock table. Without it, you get duplicate key errors or half-applied schemas.
The second trap is DDL transaction support. PostgreSQL wraps CREATE TABLE and ALTER TABLE in transactions, so a failed migration rolls back cleanly. MySQL historically ran DDL statements outside transactions, meaning a failed migration could leave behind empty tables or partial indexes. SQLite has its own limitations around schema changes inside transactions. Always check your database engine documentation before assuming atomicity.
The third trap is zero-downtime deployments. Adding a new column is usually safe. Renaming or dropping a column is not. If you drop a column in your migration, the old version of your code will crash the moment it tries to read it. The safe pattern is a four-step process. First, add the new column in a migration. Second, backfill data from the old column to the new one. Third, update your Go code to read and write both columns. Fourth, deploy the new code everywhere, then drop the old column in a separate migration.
Database schema changes are not code changes. Treat them as historical records, not reversible experiments.
When to switch to a library
Manual tracking works for small projects, but it quickly becomes tedious. You have to parse files, calculate checksums, handle directory walking, and manage rollback logic. Most teams switch to a dedicated library like golang-migrate. The library handles file discovery, version sorting, and execution. You just provide the SQL files and a connection string.
Here is how you integrate it into a Go application startup routine.
// StartDB runs pending migrations and returns a ready database connection.
func StartDB(dsn string) (*sql.DB, error) {
// Initialize the migrate driver for PostgreSQL.
m, err := migrate.New("file://./migrations", dsn)
if err != nil {
return nil, fmt.Errorf("init migrate: %w", err)
}
// Apply all pending up migrations.
if err := m.Up(); err != nil && err != migrate.ErrNoChange {
return nil, fmt.Errorf("run migrations: %w", err)
}
// Open the actual database connection for the application.
db, err := sql.Open("postgres", dsn)
if err != nil {
return nil, fmt.Errorf("open db: %w", err)
}
// Verify the connection is alive before returning.
if err := db.Ping(); err != nil {
return nil, fmt.Errorf("ping db: %w", err)
}
return db, nil
}
The migration files live in a directory named migrations. Each file follows a strict naming convention: 001_create_users.up.sql and 001_create_users.down.sql. The number prefixes ensure correct ordering. The .up suffix contains the forward migration, and the .down suffix contains the rollback. The library reads the directory, sorts by number, and applies only the versions not yet recorded in the internal schema_migrations table that it creates automatically.
Lock the migration process. Let one instance apply the changes while the others wait.
Decision matrix
Use a manual tracking table when you want zero dependencies and full control over the execution flow. Use a dedicated migration library when your team needs standardized file naming, automatic version parsing, and built-in rollback support. Use an ORM migration generator when your application already relies heavily on code-first schema definitions and you prefer Go structs to drive database structure. Use raw SQL scripts run by a CI/CD pipeline when your database is managed by a separate platform team and your application should never touch DDL statements.