# Migration

> 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.

- Skill: `go-vikunja/migration` (Agent Skill)
- Install (CLI): `npx skillmds@latest add go-vikunja/migration`
- Raw SKILL.md: https://api.skillmd.com/api/skills/go-vikunja/migration/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: go-vikunja (https://skillmd.com/u/go-vikunja)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/go-vikunja/migration

---


# 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.

```go
// 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.

```go
// 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`).