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.

## 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 don't have dedicated unit tests, but 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`).