Go Database Access: SQL and GORM Guide

Database Access in Go

Go provides database access through the database/sql package in its standard library, which defines interfaces for SQL database operations without tying to any specific database. This design, inspired by the database/sql proposal by Russ Cox, keeps the standard library lean while supporting any SQL database through a consistent API — PostgreSQL, MySQL, SQLite, SQL Server, and Oracle all have compatible driver implementations. The package handles connection pooling, transaction management, prepared statement caching, and context-based cancellation. Drivers like pgx for PostgreSQL, go-sql-driver/mysql for MySQL, and mattn/go-sqlite3 for SQLite implement the underlying wire protocol and register themselves with database/sql through blank imports. Go’s philosophy of explicit error handling means database code is more verbose than dynamically typed languages but more predictable — every network error, connection timeout, and constraint violation is surfaced immediately rather than hidden behind magic behavior. Go 1.22 introduced database/sql improvements including sql.Null[T] generic types and SetConnMaxIdleTime for finer-grained pool tuning. For high-throughput applications, connection pool tuning is one of the most impactful performance optimizations available.

Setting Up database/sql

Initializing a connection requires importing the driver and calling sql.Open. The function validates the driver name and data source name but does not establish a connection — actual connections are created lazily:

import (
    "context"
    "database/sql"
    "fmt"
    "time"
    _ "github.com/jackc/pgx/v5/stdlib"
)

type Database struct { *sql.DB }

func NewDatabase(ctx context.Context, dsn string) (*Database, error) {
    db, err := sql.Open("pgx", dsn)
    if err != nil { return nil, fmt.Errorf("sql.Open: %w", err) }
    if err := db.PingContext(ctx); err != nil {
        db.Close()
        return nil, fmt.Errorf("db.Ping: %w", err)
    }
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(30 * time.Minute)
    db.SetConnMaxIdleTime(5 * time.Minute)
    return &Database{db}, nil
---

Pool configuration varies by workload. OLTP applications with many short-lived queries benefit from larger idle pools. Batch processing workloads benefit from higher MaxOpenConns. Monitoring pool metrics through db.Stats() provides visibility into OpenConnections, InUse, and Idle counts. Setting SetConnMaxLifetime prevents issues with expired database connections and network middleboxes that close long-lived connections.

Querying with Type Safety

database/sql returns rows through a cursor that must be iterated and closed:

type Product struct {
    ID        int       `db:"id"`
    Name      string    `db:"name"`
    Price     float64   `db:"price"`
    CreatedAt time.Time `db:"created_at"`
---

func (d *Database) GetProduct(ctx context.Context, id int) (*Product, error) {
    query := `SELECT id, name, price, created_at FROM products WHERE id = $1`
    row := d.QueryRowContext(ctx, query, id)
    var p Product
    if err := row.Scan(&p.ID, &p.Name, &p.Price, &p.CreatedAt); err != nil {
        if errors.Is(err, sql.ErrNoRows) { return nil, nil }
        return nil, fmt.Errorf("scan product %d: %w", id, err)
    }
    return &p, nil
---

Always use QueryRowContext instead of QueryRow when a context is available, and use defer rows.Close() to ensure the row cursor is released. The rows.Err() check after the iteration loop catches any errors that occurred during row processing. For NULL handling, use sql.NullString, sql.NullInt64, or the generic sql.Null[T] from Go 1.22.

GORM ORM

GORM is Go’s most popular ORM, providing automatic migrations, association management, hooks, and a fluent query builder:

import "gorm.io/gorm"
import "gorm.io/driver/postgres"

type Product struct {
    gorm.Model
    Name        string  `gorm:"size:255;not null;index"`
    Description string  `gorm:"type:text"`
    Price       float64 `gorm:"type:decimal(10,2);not null"`
    SKU         string  `gorm:"uniqueIndex;size:50"`
    CategoryID  uint
    Category    Category `gorm:"foreignKey:CategoryID"`
    Tags        []Tag    `gorm:"many2many:product_tags;"`
---

GORM supports preloading through Preload("Category") and Preload("Tags"), generating efficient SQL joins. For complex reporting, GORM’s Scopes system allows reusable query fragments. Transaction support is built in through db.Transaction(func(tx *gorm.DB) error { ... }), which automatically commits on success and rolls back on error. GORM hooks (BeforeCreate, AfterUpdate, etc.) allow injecting logic at lifecycle events.

Migration Tools

Use golang-migrate/migrate or pressly/goose for version-controlled SQL migrations. These tools manage migration files in a migrations/ directory with up/down pairs, ensuring reproducible database schema changes across environments. GORM’s AutoMigrate is convenient for development but lacks version control and rollback capabilities needed in production.

Advanced Query Patterns

Beyond simple CRUD operations, production database workloads require sophisticated query patterns. Batch inserts use pgx.CopyFrom for PostgreSQL or MySQL’s multi-value INSERT syntax, achieving 10-100x throughput improvements over individual row inserts. Prepared statements with db.PrepareContext provide query plan caching and SQL injection prevention across repeated executions. For pagination, keyset pagination (WHERE id > $1 ORDER BY id LIMIT $2) outperforms OFFSET-based pagination on large datasets because it uses index scans rather than sequential scans. The sqlx crate (not to be confused with standard database/sql) provides compile-time checked queries using Query! macros that validate SQL against the database schema during build. For read-heavy workloads, database read replicas can be configured using multiple sql.DB instances with a custom router that directs SELECT queries to replicas and writes to the primary. Connection pool sizing follows Little’s Law: pool size = target_throughput × latency_per_query. For typical web services with 5ms query latency and 1000 QPS target, approximately 5-10 connections suffice — far fewer than the default 25 often configured.

Transaction Management and Error Handling

Database transactions in Go require careful error handling to avoid resource leaks. The standard pattern uses db.BeginTx to start a transaction with a context, defers tx.Rollback for cleanup on errors, and explicitly calls tx.Commit on success. The sql.Tx object supports all query methods available on sql.DB. Nested transactions are not supported by database/sql — use savepoints through raw SQL (SAVEPOINT sp1, ROLLBACK TO SAVEPOINT sp1) for partial rollback within a transaction. For distributed transactions, the dtm package provides Saga and TCC patterns across multiple databases and services. Connection pooling behavior differs inside transactions: a transaction holds a single connection from the pool until commit or rollback, reducing concurrency under high transaction loads. The database/sql package’s Stmt type provides prepared statement caching, but prepared statements within transactions should be created using tx.PrepareContext rather than db.PrepareContext to ensure they use the transaction’s connection. Timeouts should be set per-query using context deadlines rather than global database driver timeouts, allowing different timeouts for different query types.

Connection Pool Tuning and Monitoring

The database/sql package’s connection pool behavior is configurable and observable. db.SetMaxOpenConns limits concurrent connections to the database; set it to the database server’s configured connection limit minus connections for admin tools and connection pools from other services. db.SetMaxIdleConns controls the connection pool size for idle connections — too few causes frequent connection creation overhead, too many wastes resources. db.SetConnMaxLifetime prevents stale connections from accumulating; 30 minutes is a common value for PostgreSQL. db.SetConnMaxIdleTime closes idle connections after the specified duration, useful for reducing resource usage during low traffic.

Monitor pool statistics with db.Stats() which returns sql.DBStats including OpenConnections, InUse, Idle, WaitCount, WaitDuration, and MaxIdleClosed/MaxLifetimeClosed. A high WaitCount indicates insufficient MaxOpenConns. A high MaxIdleClosed means connections are recycled too aggressively. Database driver-specific metrics can supplement these: pgxpool.Stat for pgx, go-sql-driver/mysql’s registered metrics for Prometheus. Use context-based query cancellation (context.WithTimeout) to prevent queries from hanging indefinitely, and monitor slow queries with database/sql’s DB.SetConnMaxLifetime combined with database-side pg_stat_activity or SHOW FULL PROCESSLIST introspection.

ORM Alternatives and Raw SQL

While database/sql provides direct database access, ORMs like gorm, sqlx, and ent offer higher-level abstractions with different tradeoffs. sqlx extends database/sql with struct scanning, named parameters, and IN clause building while remaining close to SQL. gorm provides full object-relational mapping with automatic migrations, associations, and hooks, but its magic can obscure slow queries. ent generates type-safe query builders from schema definitions, catching SQL errors at compile time.

For complex queries, raw SQL with sqlx.Named or gorm.Raw provides better readability and DBA familiarity than ORM query builders. The sqlx.In function expands slices for WHERE x IN (?) clauses. The any forumlating (typo in the original?) The squirrel package builds SQL queries programmatically without full ORM overhead. For read-heavy workloads, dbr provides a lightweight query builder. The goqu package constructs SQL dialect-aware queries supporting PostgreSQL-specific features like RETURNING, ON CONFLICT, and WINDOW functions. When choosing an approach, benchmark actual query performance and inspect generated SQL with database logging enabled.

Prepared Statement Caching

Prepared statement caching in database/sql improves performance by reusing query plans. The db.Prepare method creates a prepared statement on the database server, and subsequent stmt.Exec or stmt.Query calls reuse the plan. The connection pool caches up to db.SetMaxIdleConns prepared statements per connection. For high-throughput applications, prepare statements at startup and reuse them throughout the application lifecycle rather than preparing per-request.

The sql.Stmt object is safe for concurrent use, making prepared statements shareable across goroutines. The PrepareContext variant accepts a context for cancellation. For dynamic queries with varying WHERE clauses, consider the pgx driver’s prepared statement caching which automatically manages the cache across connections. Monitoring db.Stats() shows the number of prepared statements via StmtCount. Performance gains are most significant for complex queries with expensive query plan generation. Simple queries may not benefit enough to justify the code complexity - benchmark to confirm.

Frequently Asked Questions

What is the best PostgreSQL driver for Go? pgx (jackc/pgx) is recommended with better performance and native protocol support. lib/pq is stable but no longer actively maintained.

Should I use database/sql or GORM? Use database/sql for performance-critical or complex SQL workloads. Use GORM for standard CRUD applications where developer productivity is a priority.

How do I handle NULL values? Use sql.NullString, sql.NullInt64, sql.NullFloat64, sql.NullTime, or the generic sql.Null[T] from Go 1.22.

How do I run database migrations? Use golang-migrate/migrate or pressly/goose for version-controlled SQL migrations.

How do I prevent SQL injection? Always use parameterized queries with $1, $2 placeholders. Never concatenate user input into SQL strings.

Related: Go JSON Serialization | Go Testing Frameworks | Go Modules and Dependencies