DB Migration Scripts
Developmentdb-migration-scriptsDB 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.
How to use this skill
Bring this guide into your coding agent with a prompt tailored to the tool you use.
- Open your project in Codex.
- Copy and paste the prompt below.
- Review files and risks before approving any action.
Inspect the Agent Skill at https://github.com/golemcloud/golem/blob/HEAD/.agents/skills/db-migration-scripts/SKILL.md and all supporting files. Report provenance, permissions, scripts, dependencies, and security risks. Propose an exact installation into .agents/skills/db-migration-scripts, but wait for my approval before writing files or running scripts.
Copying this prompt does not install or run the skill. Review third-party files before use.
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.sqldb/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>
_idxfor regular indexes_ukfor unique indexes
Examples:
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: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