migration

name	migration
description	Use when creating or editing files in pkg/migration/. Covers cross-DB type safety across MySQL/PostgreSQL/SQLite, DDL error handling, time-column conventions, and path sanitization.
user-invocable	true

Database Migrations

Migrations are irreversible in production. Vikunja supports MySQL, PostgreSQL, and SQLite — every migration must work on all three.

Before writing

1. Generate the skeleton: mage dev:make-migration <StructName>.
2. The migration struct must mirror the model in pkg/models/ exactly (field names, types, xorm tags).
3. Use time.Time for time columns. Never use string, varchar, or text for times.
4. For renames or type changes, verify the conversion is safe on all three DBs:
   • MySQL will silently coerce VARCHAR → BIGINT during ALTER. Don't rely on that — migrate data explicitly.
   • SQLite has limited ALTER TABLE; prefer xorm migration helpers over raw SQL when possible.
   • PostgreSQL is strict about types; explicit casts are often required.

Syncing structs — never plain tx.Sync on an existing table

xorm's Sync drops every index and unique constraint the synced struct doesn't declare. Partial-struct migrations (the usual "add a column" pattern) therefore silently wipe all other indexes on the table — this destroyed the users and tasks indexes of every upgraded install in v2.4.0 (issue #3244). On pgloader-converted Postgres DBs it even aborts the migration with 2BP01 because the PK index has a name xorm doesn't recognize (idx_<oid>_primary) and tries to drop.

// WRONG — drops every index on users the struct doesn't declare
return tx.Sync(users20260405194817{})

// RIGHT — adds the column and its plain indexes, drops nothing
return partialSync(tx, users20260405194817{})

• partialSync (in migration.go) is mandatory for any Sync into an existing table.
• Because it sets IgnoreConstrains, partialSync does not create unique indexes — add a new unique index on an existing table explicitly (CREATE UNIQUE INDEX per dialect), and check for pre-existing duplicate values first so the migration fails with an actionable message instead of a raw constraint error.
• Plain tx.Sync is fine only for brand-new tables (migrations whose Rollback drops the table).

Error handling on DDL

Every error from tx.Exec, session.Exec, or xorm calls must be handled. Silent discards are the most commonly flagged bug in migration reviews.

// WRONG — silently drops errors; migration reports success even on failure
_, _ = tx.Exec("CREATE INDEX idx_foo ON bar(baz)")

// RIGHT — error is returned so the migration rolls back cleanly
if _, err := tx.Exec("CREATE INDEX idx_foo ON bar(baz)"); err != nil {
    return err
}

If you must discard a DB error (e.g., idempotent best-effort cleanup where the index might already exist), write a one-line comment explaining why. No comment = reviewer will flag it.

Path and user input

If the migration touches user-supplied paths, filenames, or import blobs (restore, dump, import modules under pkg/modules/migration/), sanitize before use. Never filepath.Join raw input. Watch for .. traversal in archive entry names.

Model and frontend sync

• If the migration adds or changes a field, update the struct in pkg/models/ with matching xorm tags.
• Update the TypeScript interface in frontend/src/modelTypes/ to match the Go struct shape. Frontend services must match backend model structure exactly.

Testing

• Migrations can have dedicated _test.go files next to them using db.CreateTestEngine() (see pkg/migration/20260720120000_test.go); run with mage test:filter <TestName>. Otherwise, the model's feature tests must pass against the new schema — run mage test:feature (uses SQLite by default).
• If you suspect DB-specific behavior, flag it in the PR description so reviewers know to verify against MySQL/PostgreSQL.

Related

• Existing examples: browse pkg/migration/ for patterns; recent files are usually the cleanest references.
• Never edit pkg/swagger/ (generated).
• Never commit config.yml.sample (generated by mage generate:config-yaml).