Back

Migrations

Kyrux includes a migration system based on numbered .sql files, with automatic tracking of which ones have already been applied.

How it works

.sql files live in database/migrations/
The name follows the NNNN_description.sql pattern (e.g. 0001_create_users.sql)
The kyrux_migrations table tracks what's already been applied — created automatically on the first migrate
Each migration is applied exactly once (idempotent)
If the table already exists in the database even though the file was removed, it just records the migration without re-running the SQL

Migration file format

Each .sql file can contain two sections separated by the -- down marker:

-- up (implicit — everything before "-- down")
CREATE TABLE IF NOT EXISTS posts (
    id         BIGSERIAL    PRIMARY KEY,
    titulo     VARCHAR(200) NOT NULL DEFAULT '',
    publicado  BOOLEAN      NOT NULL DEFAULT FALSE
);

CREATE UNIQUE INDEX IF NOT EXISTS posts_titulo_idx ON posts (titulo);

-- down
DROP INDEX IF EXISTS posts_titulo_idx;
DROP TABLE IF EXISTS posts;

The up section is applied by migrate. The down section is run by removemigration all to safely undo the schema before removing the record.

Automatically generating migrations (`makemigrations`)

The command reads every struct with kyrux:"pk" in apps/*/models/*.go, core/security/auth/*.go, and core/admin/*.go (ignoring *_test.go — a test fixture is never a real model), compares it against the schema of existing migrations, and generates SQL with the up and down sections automatically:

New table → a full CREATE TABLE (+ unique/fk indexes).
New field on an existing model → ALTER TABLE ... ADD COLUMN (autodetected).
Field removed from the model → the removal is not generated (data loss); the command warns and writes the -- ALTER TABLE ... DROP COLUMN ...; suggestion commented out in the migration, for you to uncomment after review.
Renaming a field or changing its type → manual migration (the detector would see an ADD plus a removed-column warning).

go run main.go makemigrations

Review the generated file before applying it — composite indexes, constraints, and custom defaults must be adjusted manually.

Applying migrations (`migrate`)

go run main.go migrate

Applies the up section of every .sql file not yet recorded in kyrux_migrations. Multiple SQL statements per file are supported (separated by ;).

Smart behavior:

If the SQL file still exists: runs it normally and records it
If the file was removed but the table exists in the database: just records it (avoids "table already exists" errors)
If it's already recorded: skips it (status ~)

Removing a migration (`removemigration`)

# Removes it from disk only (allows regenerating the migration)
go run main.go removemigration 0001

# Runs the down section, removes the database record, and deletes the file
go run main.go removemigration 0001 all

removemigration all runs the following steps in order:

Reads the file and extracts the -- down section
Runs the reversal SQL against the database (e.g. DROP TABLE IF EXISTS ...)
Removes the record from kyrux_migrations
Deletes the file from disk

If the file has no -- down section, the command aborts with a message indicating what to add — it never silently removes without undoing the schema.

Generated SQL types

Go type	PostgreSQL	MySQL / SQLite
`string` (no `size`)	`TEXT`	`TEXT`
`string` + `kyrux:"size:N"`	`VARCHAR(N)`	`VARCHAR(N)`
`int`, `int32`	`INTEGER`	`INTEGER`
`int64`	`BIGINT`	`INTEGER`
`float32`, `float64`	`DECIMAL`	`DECIMAL`
`bool`	`BOOLEAN`	`BOOLEAN`
`time.Time`	`TIMESTAMPTZ`	`DATETIME`
`kyrux:"pk"` field	`BIGSERIAL PRIMARY KEY`	`INTEGER PRIMARY KEY`
`kyrux:"unique"` field	`CREATE UNIQUE INDEX`	`CREATE UNIQUE INDEX`
`kyrux:"fk:table"` field	`REFERENCES table(id)` + `CREATE INDEX`	same

For fk:, the referenced table must exist beforehand — declare the referenced model first, or in an earlier migration.

Pointer fields (*string, *int) are generated without NOT NULL. Non-pointer fields get NOT NULL DEFAULT <zero>.

Manual migrations

For changes makemigrations doesn't cover (ALTER TABLE, composite indexes, seed data), always include the down section:

-- database/migrations/0003_add_slug_to_posts.sql
ALTER TABLE posts ADD COLUMN IF NOT EXISTS slug VARCHAR(200) NOT NULL DEFAULT '';

CREATE UNIQUE INDEX IF NOT EXISTS posts_slug_idx ON posts (slug);

-- down
DROP INDEX IF EXISTS posts_slug_idx;
ALTER TABLE posts DROP COLUMN IF EXISTS slug;

Run it with go run main.go migrate.

Directory structure

database/
└── migrations/
    ├── 0001_create_users.sql    ← included by the framework (auth.User)
    ├── 0002_auto.sql            ← generated by makemigrations
    └── 0003_add_slug.sql        ← written manually

makemigrations does not generate ALTER TABLE migrations — it only creates new tables. For changes to existing tables, write the migration manually.