# DB Migration Scripts

> DB Migration Scripts is an Agent Skill for development published in the golemcloud/golem repository on GitHub: a SKILL.md instruction file an AI agent can load to do this task.

Writing database migration SQL scripts. Use when creating or modifying migration files under db/migration/ directories, adding tables, indexes, or columns.

- Skill name: `db-migration-scripts`
- Category: [Development](https://www.openparable.com/skill/development/1)
- Source: https://github.com/golemcloud/golem/blob/HEAD/.agents/skills/db-migration-scripts/SKILL.md
- Repository: golemcloud/golem (1,605 GitHub stars when collected)

## SKILL.md

# Database Migration Scripts

## Dual-Database Support

Every migration must be written for **both** PostgreSQL and SQLite. Migration files live in parallel directories:
- `db/migration/postgres/NNN_description.sql`
- `db/migration/sqlite/NNN_description.sql`

Both files must have the same numbered prefix and name. Use the appropriate SQL dialect for each (e.g., `BIGSERIAL` vs `INTEGER PRIMARY KEY AUTOINCREMENT`, `UUID` vs `TEXT`, `TIMESTAMPTZ` vs `TEXT`).

## File Naming

Migration files are numbered sequentially with zero-padded three-digit prefixes:
```
001_init.sql
002_code_first_routes.sql
003_wasi_config.sql
```

Check existing files to determine the next number.

## Index Naming Convention

Index names follow the format: `<table>_<column(s)>_<idx|uk>`

- `_idx` for regular indexes
- `_uk` for unique indexes

Examples:
```sql
CREATE INDEX accounts_deleted_at_idx ON accounts (deleted_at);
CREATE UNIQUE INDEX accounts_email_uk ON accounts (email) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX plugins_name_version_uk ON plugins (account_id, name, version);
```

## Primary Keys

- **Do not create indexes on primary key columns.** Both PostgreSQL and SQLite automatically create an index for primary key columns.
- Name primary key constraints as `<table>_pk`:
  ```sql
  CONSTRAINT accounts_pk PRIMARY KEY (account_id)
  ```

## Column Types

Use the same column types in PostgreSQL and SQLite whenever possible, to make it easier to write queries that work on both databases. Only use database-specific types (e.g., `BIGSERIAL` vs `INTEGER PRIMARY KEY AUTOINCREMENT`, `UUID` vs `TEXT`, `TIMESTAMPTZ` vs `TEXT`, `TEXT[]` vs `TEXT`) when there is no common alternative.

## Table Style

- Use uppercase SQL keywords (`CREATE TABLE`, `NOT NULL`, `PRIMARY KEY`)
- Column definitions are indented and aligned
- Primary key constraints are defined inline or as named table constraints

---

Source: https://www.openparable.com/skill/development/db-migration-scripts (OpenParable). Cite the canonical URL above.
