The Complete Production SOP: How to Deploy and Run golang-migrate CLI & Programmatic Database Migrations in Autonomous AI Agents
Production SOP to install and run golang-migrate in Codex, Claude Code, Cursor, and Windsurf. Resolve dirty database states and automate schema migrations.
Technical Overview & Architectural Context
Autonomous coding agents frequently corrupt schema migrations when operating without strict procedural guardrails. Typical failure modes include generating non-paired or misnumbered SQL files, writing irreversible DDL statements lacking counterpart DOWN scripts, and compounding failed executions into a deadlocked 'dirty' schema state (schema_migrations table dirty=true). When faced with a dirty state, LLMs frequently attempt to run additional ad-hoc ALTER TABLE commands instead of executing deterministic force-reconciliation steps. The golang-migrate CLI & Programmatic Database Migrations skill enforces strict boundaries around database schema mutations. By establishing declarative triggers ('run golang-migrate up', 'create migration golang-migrate', 'fix dirty database state migrate') and pinning runtime actions to idempotent sequences, the agent systematically pairs UP/DOWN scripts, wraps sequential numbers via standard CLI generators, verifies DDL idempotency, and handles state reconciliation using 'migrate force' before applying new structural changes.
Prerequisites & Environment Requirements
Verified Search Intents & Long-Tail Queries Solved
This operational tutorial is engineered to provide deterministic solutions for these developer search queries:
Step-by-Step Execution Playbook
Local Environment Setup & SKILL.md Mounting
Create the local skill manifest directory and inject verified procedural rules.
To enable autonomous agents to discover and parse the migration SOP, place the SKILL.md in the workspace skill directory. Compatible runtimes like Claude Code and Cursor scan these directories on workspace initialization to load skill triggers, security constraints, and execution boundaries into their routing engines.
mkdir -p .agents/skills/golang-migrate-cli-database-tool && curl -fsSL https://askills.dev/api/skills/golang-migrate-cli-database-tool/raw -o .agents/skills/golang-migrate-cli-database-tool/SKILL.md---
name: "golang-migrate-cli-database-tool"
description: "Version-control SQL schema migrations with up/down scripts using golang-migrate."
metadata:
author: "golang-migrate"
version: "1.0.0"
runtime_compatibility:
- "codex"
- "claude-code"
- "cursor"
- "windsurf"
triggers:
- "run golang-migrate up"
- "create migration golang-migrate"
- "fix dirty database state migrate"
permissions:
fs_read: true
fs_write: "workspace"
shell_exec: true
net_access: false
---
# golang-migrate CLI & Programmatic Database Migrations
Applies migrations using golang-migrate: writing versioned SQL scripts (000001_init.up.sql, 000001_init.down.sql), running migrations via CLI, integrating programmatically in Go main(), and resolving dirty state.
## When to Invoke
Invoke this skill whenever you need to:
- run golang-migrate up
- create migration golang-migrate
- fix dirty database state migrate
## Execution SOP & Rules
1. Create migration pair: migrate create -ext sql -dir migrations -seq <name>.
2. Write idempotent DDL in *.up.sql and reverse cleanup in *.down.sql.
3. Apply migrations: migrate -database '${DATABASE_URL}' -path migrations up.
4. Fix dirty state: migrate -database '${DATABASE_URL}' -path migrations force <version>.Keep the SKILL.md concise. Declaring clear trigger phrases eliminates the need to pass entire database schema dumps into system prompts, saving hundreds of prompt tokens per interaction.
Ensure that the agent sandbox configuration grants read and write access to the migrations directory, while restricting direct non-sandboxed raw socket execution if running untrusted SQL.
Environment Secrets & Dependency Validation
Inject required environment variables safely without leaking secrets into conversation history.
Ensure that database connection parameters are passed using standardized environment variables (e.g., DATABASE_URL) inside a local environment file. Prevent LLMs from hardcoding credentials in generated migration scripts or shell invocation logs.
echo 'DATABASE_URL="postgres://postgres:postgres@localhost:5432/app_db?sslmode=disable"' >> .env.local && which migrate || go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latestInstruct the agent runtime to reference ${DATABASE_URL} directly in its shell commands rather than expanding the secret value into the prompt context buffer.
Verify that .env.local is explicitly added to .gitignore. Autonomous agents may inadvertently commit environment files when staging new migration files.
Intent Matching & Trigger Invocation
Verify deterministic activation using tested natural language prompts.
When instructing your coding agent (Codex, Claude Code, Cursor, or Windsurf), phrase requests to match frontmatter trigger declarations. This activates the SOP sequence instead of letting the agent improvise raw SQL alter statements.
Use precise action commands such as 'create migration golang-migrate add_users_table' to ensure the agent selects the -seq format over timestamp-based numbering.
Without the skill trigger, agents frequently generate single .sql files containing both CREATE and DROP instructions together, violating migration parity.
Verification, Linting & Output Quality Gate
Execute automated self-healing checks before accepting agent deliverables.
Force the agent to validate migration syntax, verify version sequence integrity, and perform a dry run against an ephemeral database or local docker container before merging schema modifications.
migrate -database "${DATABASE_URL}" -path migrations version && git status --porcelain migrations/Instruct the agent to inspect the git diff on the migrations folder to confirm that sequence IDs follow a continuous 6-digit order without gaps or collisions.
Never allow the agent to finalize a task if the schema_migrations table returns dirty=true. The agent must resolve dirty state before exiting the quality gate.
Real-World Scenario: Adding a Multi-Tenant Organization Table and Resolving a Dirty Migration Failure
“create migration golang-migrate add_organizations_table, implement idempotent DDL with a corresponding down script, and run golang-migrate up. If the database enters a dirty state due to an existing table, fix dirty database state migrate safely.”
Runtime Troubleshooting & Failure Modes
Root Cause: A migration failed halfway through execution (e.g., syntax error or duplicate index). golang-migrate sets the dirty flag in schema_migrations to prevent further inconsistent executions.
migrate -database "${DATABASE_URL}" -path migrations force <N-1> && # fix sql error in 00000N_*.up.sql then run: migrate -database "${DATABASE_URL}" -path migrations upRoot Cause: The agent created migration files without the required six-digit sequence prefix or missed either the .up.sql or .down.sql extension.
ls -l migrations/ && rm -f migrations/*invalid_name*.sql && migrate create -ext sql -dir migrations -seq <valid_name>Frequently Asked Questions (golang-migrate CLI & Programmatic Database Migrations SOP)
Q:Can I use golang-migrate CLI & Programmatic Database Migrations across Codex, Claude Code, and Cursor simultaneously?
Yes. The skill conforms to the universal SKILL.md specification supported across all modern agent runtimes. You can mount it globally in ~/.agents/skills or within repository roots (.agents/skills).
Q:How does golang-migrate CLI & Programmatic Database Migrations prevent context window overflow during long agent sessions?
By establishing concise procedural boundaries and deterministic validation gates rather than polluting system prompts with redundant rules or raw full-table schemas.
Q:Does this skill require escalated root or container permissions?
No. It is audited with a security score of 98/100 and runs safely within workspace-scoped sandboxes using standard non-root database connection strings.
Ready to equip your autonomous agent with golang-migrate CLI & Programmatic Database Migrations?
Copy the complete audited SKILL.md specification or download the 1-click zip bundle directly from our verified registry.