Database Migrations

Platforma provides a built-in migration system that allows you to version control your database schema changes using SQL files with special markers.

Overview

The migration system is built into the database.Database type and automatically discovers migrations from registered repositories. Migrations are executed in order based on their filenames, and the system tracks which migrations have been applied.

Migration File Format

Migration files use special comment markers to define up and down migrations:

-- +migrate Up
CREATE TABLE IF NOT EXISTS users (
	id VARCHAR(255) PRIMARY KEY,
	username VARCHAR(255) UNIQUE,
	password TEXT,
	salt TEXT,
	created TIMESTAMP,
	updated TIMESTAMP,
	status VARCHAR(50)
);

-- +migrate Down
DROP TABLE users;

Migration Markers

-- +migrate Up: Required marker that indicates the beginning of the forward migration
-- +migrate Down: Optional marker for rollback SQL (placed after the Up section)
-- +migrate ID: <custom_id>: Optional ID override (must be the first marker if used)

Migration ID

By default, the migration ID is derived from the filename without the .sql extension. For example, 1770095236_init.sql has an ID of 1770095236_init. You can override this by adding an ID marker as the first line:

-- +migrate ID: custom_migration_id
-- +migrate Up
CREATE TABLE ...

Embedding Migrations

Use Go’s embed package to bundle migration files with your repository:

package auth

import (
	"embed"
	"io/fs"
)

//go:embed migrations/*.sql
var migrations embed.FS

type Repository struct {
	db db
}

func (r *Repository) Migrations() fs.FS {
	m, _ := fs.Sub(migrations, "migrations")
	return m
}

The Migrations() method must return an fs.FS containing your SQL files. Place your migration files in a migrations/ subdirectory next to your repository code.

Registering Repositories

db, err := database.New(connectionString)
if err != nil {
	return err
}

authRepo := auth.NewRepository(db.Connection())
db.RegisterRepository("auth", authRepo)

The RegisterRepository method automatically detects if your repository implements the migrator interface (has a Migrations() method) and registers it for migration.

Running Migrations

Run all pending migrations with the Migrate method:

err := db.Migrate(ctx)
if err != nil {
	log.ErrorContext(ctx, "migration failed", "error", err)
	return err
}

The migration system:

Creates a migrations tracking table if it doesn’t exist
Reads all migration logs to determine which migrations have been applied
Collects migrations from all registered repositories
Applies pending migrations in lexicographic order by filename
Records successfully applied migrations

Best Practices

Use timestamps in filenames

Name migration files with a timestamp prefix to ensure correct ordering:

migrations/
├── 1770095236_init.sql
├── 1770095300_add_email_column.sql
└── 1770095450_create_index.sql

Make migrations idempotent

Use IF NOT EXISTS and IF EXISTS clauses to make migrations safe to re-run:

-- +migrate Up
CREATE TABLE IF NOT EXISTS users (...);
CREATE INDEX IF NOT EXISTS idx_username ON users(username);

-- +migrate Down
DROP INDEX IF EXISTS idx_username;
DROP TABLE IF EXISTS users;

Test both Up and Down migrations

Always provide a Down migration and test that it correctly reverses the Up migration. This ensures you can roll back changes if needed.

One logical change per migration

Keep migrations focused on a single logical change to make them easier to understand and roll back if necessary.

Example: Complete Migration Setup

Create migration directory

Create a migrations/ directory in your repository package:

mkdir -p myrepo/migrations

Write migration file

Create a migration file with timestamp prefix:

myrepo/migrations/1770095236_init.sql

-- +migrate Up
CREATE TABLE IF NOT EXISTS items (
  id VARCHAR(255) PRIMARY KEY,
  name TEXT NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- +migrate Down
DROP TABLE IF EXISTS items;

Embed migrations in repository

Add the embed directive and Migrations method:

myrepo/repository.go

package myrepo

import (
  "embed"
  "io/fs"
)

//go:embed migrations/*.sql
var migrations embed.FS

type Repository struct {
  db db
}

func (r *Repository) Migrations() fs.FS {
  m, _ := fs.Sub(migrations, "migrations")
  return m
}

db, err := database.New(connectionString)
if err != nil {
  return err
}

repo := myrepo.NewRepository(db.Connection())
db.RegisterRepository("myrepo", repo)

if err := db.Migrate(ctx); err != nil {
  return err
}

Migration Tracking

The system tracks applied migrations in a migrations table with columns:

repository: The name used when calling RegisterRepository
id: The migration ID (filename without extension or custom ID)
timestamp: When the migration was applied

This ensures migrations are only applied once per repository.

Get Started

Core Concepts

Guides

Database Migrations

Overview

Migration File Format

Migration Markers

Migration ID

Embedding Migrations

Registering Repositories

Running Migrations

Best Practices

Example: Complete Migration Setup

Migration Tracking

​Overview

​Migration File Format

​Migration Markers

​Migration ID

​Embedding Migrations

​Registering Repositories

​Running Migrations

​Best Practices

​Example: Complete Migration Setup

​Migration Tracking

Overview

Migration File Format

Migration Markers

Migration ID

Embedding Migrations

Registering Repositories

Running Migrations

Best Practices

Example: Complete Migration Setup

Migration Tracking