henriqueatila/golang-gin-best-practices / skills/golang-gin-database
golang-gin-database
VerifiedPostgreSQL integration for Go Gin with GORM/sqlx. Use when adding a database, writing queries, creating repositories, running migrations, or wiring DB layers in a Gin project.
references/migrations-setup.md
View on GitHub# Migrations — Setup, CLI, and Library Usage
golang-migrate overview, file naming convention, CLI commands, library usage (run on startup), and startup vs CI/CD strategy.
> **Architectural recommendation:** golang-migrate is not part of the Gin framework. These are mainstream Go community patterns.
## golang-migrate Overview
`golang-migrate/migrate` runs versioned SQL migration files against a database. Each migration has an **up** file (apply) and a **down** file (rollback). Versions are tracked in a `schema_migrations` table.
```bash
# CLI
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest
# Library (add to go.mod)
go get github.com/golang-migrate/migrate/v4
go get github.com/golang-migrate/migrate/v4/database/postgres
go get github.com/golang-migrate/migrate/v4/source/file
```
## File Naming Convention
```
db/migrations/
├── 000001_create_users_table.up.sql
├── 000001_create_users_table.down.sql
├── 000002_create_roles_table.up.sql
├── 000002_create_roles_table.down.sql
├── 000003_add_user_roles_junction.up.sql
└── 000003_add_user_roles_junction.down.sql
```
**Rules:**
- Prefix with a **zero-padded sequential integer** (`000001`, `000002`, …). golang-migrate sorts lexicographically — gaps are fine, but never reorder.
- Suffix: `.up.sql` for apply, `.down.sql` for rollback.
- Descriptive name in snake_case between version and suffix.
- **Never edit a migration once it has been applied to any environment.** Create a new migration to fix mistakes.
## CLI Usage
```bash
# Apply all pending migrations
migrate -path db/migrations -database "$DATABASE_URL" up
# Apply exactly N migrations
migrate -path db/migrations -database "$DATABASE_URL" up 2
# Roll back the most recent migration
migrate -path db/migrations -database "$DATABASE_URL" down 1
# Roll back all migrations (destructive — dev only)
migrate -path db/migrations -database "$DATABASE_URL" down
# Show current version and dirty state
migrate -path db/migrations -database "$DATABASE_URL" version
# Force a specific version (use when dirty=true after a failed migration)
migrate -path db/migrations -database "$DATABASE_URL" force 3
# Create a new migration file pair
migrate create -ext sql -dir db/migrations -seq add_profile_table
# Creates: 000004_add_profile_table.up.sql + 000004_add_profile_table.down.sql
```
**Critical:** If a migration fails halfway, the database is left in a "dirty" state (version N, dirty=true). Fix the schema manually, then run `force N` to clear the dirty flag before retrying.
## Library Usage (Run on Startup)
Run migrations automatically when the server starts. Suitable for simple deployments and development.
```go
// internal/repository/migrate.go
package repository
import (
"errors"
"fmt"
"log/slog"
"github.com/golang-migrate/migrate/v4"
_ "github.com/golang-migrate/migrate/v4/database/postgres"
_ "github.com/golang-migrate/migrate/v4/source/file"
)
// RunMigrations applies all pending up migrations.
// Call before starting the HTTP server.
func RunMigrations(dsn, migrationsPath string, logger *slog.Logger) error {
m, err := migrate.New(
fmt.Sprintf("file://%s", migrationsPath), // e.g. "file://db/migrations"
dsn,
)
if err != nil {
return fmt.Errorf("migrate.New: %w", err)
}
defer m.Close()
if err := m.Up(); err != nil {
if errors.Is(err, migrate.ErrNoChange) {
logger.Info("migrations: no changes")
return nil
}
return fmt.Errorf("migrate.Up: %w", err)
}
version, dirty, _ := m.Version()
logger.Info("migrations applied", "version", version, "dirty", dirty)
return nil
}
```
Wire into `main.go` before starting the router:
```go
func main() {
logger := slog.New(slog.NewJSONHandler(os.Stdout, nil))
dsn := os.Getenv("DATABASE_URL")
migrationsPath := os.Getenv("MIGRATIONS_PATH")
if migrationsPath == "" {
migrationsPath = "db/migrations"
}
if err := repository.RunMigrations(dsn, migrationsPath, logger); err != nil {
logger.Error("migration failed", "error", err)
os.Exit(1)
}
// ... router setup
}
```
## Startup vs CI/CD Strategy
| Strategy | Best for | Pros | Cons |
|----------|----------|------|------|
| **Run on startup** | Dev, small teams | Zero-config, always up to date | Risk in multi-replica rollout (race) |
| **CI/CD step** | Production, teams | Explicit, auditable, no race | Requires separate migration job |
| **Init container** (K8s) | Kubernetes | Runs once before app pods | K8s-specific, adds complexity |
For Kubernetes Job YAML and GitHub Actions step: see [migrations-advanced.md](migrations-advanced.md).
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |