golang-database-patterns

Go Database Patterns

Overview

Go's database ecosystem provides multiple layers of abstraction for SQL database integration. From the standard library's database/sql to enhanced libraries like sqlx and PostgreSQL-optimized pgx, developers can choose the right tool for their performance and ergonomics needs.

Key Features:

• 🔌 database/sql: Standard interface for any SQL database
• 🚀 sqlx: Convenience methods with struct scanning and named queries
• 🐘 pgx: PostgreSQL-native driver with maximum performance
• 📦 Repository Pattern: Interface-based data access for testability
• 🔄 Migrations: Schema versioning with golang-migrate
• ⚡ Connection Pooling: Production-ready connection management
• 🔒 Transaction Safety: Context-aware transaction handling

When to Use This Skill

Activate this skill when:

• Building CRUD operations with type safety
• Implementing data access layers for web services
• Managing database schema evolution across environments
• Optimizing database connection pooling for production
• Testing database code with mock repositories
• Handling concurrent database access patterns
• Migrating from ORMs to SQL-first approaches
• Integrating PostgreSQL-specific features (COPY, LISTEN/NOTIFY)

Core Database Libraries

Decision Tree: Choosing Your Database Library

┌─────────────────────────────────────┐
│ What database are you using?       │
└──────────────┬──────────────────────┘
               │
    ┌──────────┴──────────┐
    │                     │
PostgreSQL            Other SQL DB
    │                     │
    ▼                     ▼
┌─────────────────┐   Use database/sql
│ Need max perf?  │   + sqlx for convenience
└─────┬───────────┘
      │
   ┌──┴──┐
  Yes    No
   │      │
  pgx   sqlx + pq driver

Use database/sql when:

• Working with any SQL database (MySQL, SQLite, PostgreSQL, etc.)
• Need database portability
• Want standard library stability with no dependencies

Use sqlx when:

• Want convenience methods (Get, Select, StructScan)
• Need named parameter queries
• Using IN clause expansion
• Prefer less boilerplate than database/sql