db-migrate

name

db-migrate

description

Use this skill whenever the user wants to run, apply, or check database migrations in the jishono-backend repo. Trigger for requests like "run migrations", "apply pending migrations", "create a new migration", "roll back migration", "check migration status", "what migrations are pending", or "migrate the database". Also trigger when a new feature requires schema changes and the user wants help creating a migration file.

Database Migrations — jishono-backend

This project uses node-pg-migrate. Migration files live in migrations/ and are plain SQL with -- Up Migration and -- Down Migration sections.

1. List existing migrations

Before doing anything, show the user what's there:

ls -1 migrations/

2. Apply pending migrations

Run locally:

npm run migrate:up

Or inside Docker (if the user is working via Docker):

docker compose exec jishono-api npm run migrate:up

When to use Docker: if the user mentions Docker, or if there's a running container. Otherwise default to local.

3. Create a new migration

Ask the user for a short descriptive name (snake_case, e.g. add_tags_to_oppslag), then run:

npm run migrate:create -- <name>

This creates migrations/<timestamp>_<name>.sql where the timestamp is the real Unix millisecond clock at creation time. Read the file and show the user the path, then tell them to fill in SQL under:

• -- Up Migration — the forward change
• -- Down Migration — how to reverse it

Remind them: commit the migration in the same PR as the code that depends on it. Never edit a migration after it's applied to production.

4. Roll back the last migration

Always confirm before rolling back — this is destructive and may cause data loss.

Say something like: "Rolling back will reverse the last migration. This can cause data loss. Shall I proceed?"

If the user confirms:

npm run migrate:down

Or inside Docker:

docker compose exec jishono-api npm run migrate:down

5. Check migration status

node-pg-migrate doesn't have a built-in status command, but you can:

1. Show migration files: ls -1 migrations/
2. Query the DB for applied migrations (if DB access available):

SELECT name, run_on FROM pgmigrations ORDER BY run_on DESC LIMIT 20;

Environment note

DATABASE_URL must be set for migrations to work. In Docker Compose, it's auto-constructed from other env vars. For local runs outside Docker, it must be set in .env:

DATABASE_URL=postgres://<user>:<pass>@<host>:<port>/<db>

Migration file naming

Migration filenames use a real Unix millisecond timestamp — never a hand-crafted or guessed number. Always generate the timestamp with:

node -e "console.log(Date.now())"

Use that value as the prefix. Migrations with incorrect timestamps (e.g. in the past relative to already-run migrations) will fail with "Not run migration X is preceding already run migration Y".

Rules

• Never edit a migration file that has been applied to production — create a new one instead.
• relaterte_oppslag is managed by a weekly cron job — do not create migrations for it.
• Commit migration files in the same PR as the code that depends on them.
• Never run migrations after creating them. After writing a migration file, stop and show the user the file contents. Let the user inspect and explicitly ask you to apply it.
• Migrations run automatically on app startup (via node-pg-migrate called from the entrypoint) — so a migration created while the app is running will be applied on the next restart.
• Before creating a migration, run docker compose down to prevent it from being auto-applied before the user has had a chance to inspect it.