playbooks

home / skills / hoangnguyen0403 / agent-skills-standard / database

database skill

/skills/golang/database

This skill enforces Golang database best practices, promoting repository patterns, safe transactions, and proper connection pooling for reliable,

npx playbooks add skill hoangnguyen0403/agent-skills-standard --skill database

Review the files below or copy the command above to add this skill to your agents.

Files (2)
SKILL.md
1.4 KB
---
name: Database
description: Standards for database interaction, connection pooling, and repository patterns in Golang.
metadata:
  labels: [golang, database, sql, postgres, repository]
  triggers:
    files: ['internal/adapter/repository/**']
    keywords: [database, sql, postgres, gorm, sqlc, pgx]
---

# Golang Database Standards

## **Priority: P0 (CRITICAL)**

## Principles

- **Prefer Raw SQL/Builders over ORMs**: Go structs map well to SQL. ORMs (GORM) can obscure performance. Recommended: `sqlc` (type-safe SQL generation).
- **Repository Pattern**: Abstract DB access behind interfaces in `internal/port/` (e.g., `UserRepository`).
- **Connection Pooling**: Always configure `SetMaxOpenConns`, `SetMaxIdleConns`, `SetConnMaxLifetime`.
- **Transactions**: Logic requiring ACID MUST use transactions. Pass `context.Context` everywhere.

## Drivers

- **PostgreSQL**: `jackc/pgx` (Prefer `pgx/v5` for performance and features).
- **MySQL**: `go-sql-driver/mysql`.
- **SQLite**: `mattn/go-sqlite3` or `modernc.org/sqlite` (pure Go).

## Anti-Patterns

- **Global DB Connection**: Do not use global `var db *sql.DB`. Inject it.
- **Ignoring Context**: Always use `db.QueryContext` or `db.ExecContext` to handle timeouts.
- **Leaking Rows**: ALWAYS `defer rows.Close()` and check `rows.Err()`.

## References

- [Repository Pattern Implementation](references/repository-pattern.md)
- [Connection Tuning](references/connection-tuning.md)

Overview

This skill documents standards for database interaction, connection pooling, and repository patterns in Golang. It focuses on practical rules that improve performance, reliability, and testability for services that use SQL databases. The guidance prioritizes raw SQL or builders, clear abstractions, and safe connection management.

How this skill works

The skill inspects and enforces patterns for database access: using type-safe SQL generation or builders instead of heavy ORMs, abstracting data access behind repository interfaces, and configuring database connection pools. It emphasizes passing context.Context through DB calls, using transaction boundaries for ACID operations, and avoiding global mutable DB state. Drivers and anti-patterns are highlighted for PostgreSQL, MySQL, and SQLite.

When to use it

  • Building or refactoring backend services in Go that access SQL databases.
  • Implementing repositories to decouple business logic from persistence.
  • Tuning database connections for performance and resource limits.
  • Designing transactional flows that require ACID guarantees.
  • Writing tests that inject database dependencies or mocks.

Best practices

  • Prefer raw SQL or SQL builders (e.g., sqlc) over heavy ORMs to keep queries explicit and performant.
  • Abstract DB access with repository interfaces in internal/port/ and inject implementations for testability.
  • Configure connection pools: SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime to match workload and DB limits.
  • Always use context-aware methods (QueryContext/ExecContext) and pass context.Context through call chains.
  • Wrap multi-statement or multi-row operations in transactions and propagate the transaction via interfaces.
  • Avoid global *sql.DB variables; inject DB handles via constructors to enable mocking and lifecycle control.

Example use cases

  • Service that needs predictable query performance: use sqlc or a builder and tune pool settings.
  • API endpoint that updates multiple tables atomically: implement a repository method that starts and commits a transaction.
  • Large-scale microservice: inject repository interfaces to swap real DBs for fakes in tests or local dev.
  • CLI or batch job using SQLite for local processing: pick a pure-Go driver for cross-compilation.
  • Connection-limited environments: adjust max open/idle conns and connection lifetime to avoid saturation.

FAQ

Why prefer raw SQL over an ORM in Go?

Go structs map cleanly to SQL and raw SQL or generators like sqlc keep queries explicit, predictable, and easier to profile than opaque ORM behavior.

How should I handle transactions across multiple repository calls?

Start the transaction in a higher-level service, pass a transactional context or tx-scoped repository implementation, and ensure commit/rollback on completion or error.