Database Access: SQL, ORM and Connection Pool

Database Access: SQL, ORM and Connection Pool

The database is the central bottleneck in the vast majority of backend services. Applications can scale horizontally; databases generally cannot — at least not without bound. This means that the efficiency of every individual database interaction directly determines the throughput ceiling of the entire system.

Go's database/sql package was designed in 2011, and it made a choice that stood apart from the ORM-heavy ecosystems of Java and Python: provide a thin, interface-oriented standard layer rather than a complete object-relational mapping framework. That design decision remains controversial today, but the engineering philosophy behind it is worth understanding deeply. This chapter starts from database/sql's internal mechanics and covers the full practice of ORMs, connection pool tuning, transactions, migrations, and testing.

Level 1 · The Database Access Landscape in Go

The Design Philosophy of database/sql

Go's database/sql is a driver interface layer, not a database feature implementation. It defines interfaces — driver.Driver, driver.Conn, driver.Stmt, driver.Rows, and others — which are implemented by concrete database drivers (pq, go-sqlite3, mysql, etc.). Application code interacts with any database through the uniform database/sql API, without caring about the details of the underlying driver.

This is the same idea as Java's JDBC, but Go's implementation is leaner. On top of the interface layer, database/sql provides a connection pool and context awareness — the most important value it adds over using the driver interfaces directly.

Why does Go not include an ORM in the standard library? This is a deliberate trade-off. An ORM's core value is reducing CRUD boilerplate, but its cost is an extra layer of abstraction — and that abstraction frequently becomes an obstacle in performance-critical scenarios: generated SQL is hard to optimize, the N+1 problem hides behind convenient APIs, and support for transactions and bulk operations is rarely as precise as hand-written SQL. A phrase often heard in the Go community: "If you don't know what SQL your ORM is generating, you shouldn't be using it."

The ORM Controversy

Both supporters and opponents of ORMs have valid arguments.

The case for ORMs:

• Dramatically reduces boilerplate: CRUD operations require no hand-written SQL
• Database portability: switching databases does not require rewriting SQL
• Type safety: struct fields map directly to database columns, reducing rows.Scan errors
• Built-in migration tooling: many ORMs ship their own migration facilities

The case against ORMs:

• They hide SQL complexity, leading engineers to not understand what is happening underneath
• On complex queries (JOINs, subqueries, window functions), the quality of generated SQL varies widely
• In performance-sensitive scenarios, ORM-generated SQL almost always needs manual optimization
• In large teams, ORM "magic" makes code review more difficult

The current state of the Go ecosystem: GORM is the most popular ORM (35k+ GitHub stars), suitable for rapid development; sqlc represents a fundamentally different approach — treating SQL as a first-class citizen and generating type-safe Go code from SQL queries; sqlx is a thin wrapper over database/sql that only adds conveniences like scanning and named parameters without hiding SQL.

Level 2 · How database/sql Works Internally

The Driver Interface

database/sql's interaction with specific databases goes entirely through interfaces defined in the database/sql/driver package. The core interfaces:

// The driver itself: creates connections
type Driver interface {
    Open(name string) (Conn, error)
}

// A single database connection
type Conn interface {
    Prepare(query string) (Stmt, error) // create a prepared statement
    Close() error
    Begin() (Tx, error)                 // begin a transaction
}

// A prepared statement
type Stmt interface {
    Close() error
    NumInput() int
    Exec(args []Value) (Result, error)
    Query(args []Value) (Rows, error)
}

A driver registers itself via sql.Register("postgres", &pq.Driver{}) (typically in the driver package's init() function). Application code calls sql.Open("postgres", dsn) to obtain a *sql.DB.

*sql.DB does not represent a single connection. It represents a connection pool. This is the single most important thing to understand about database/sql.

The Connection Pool's Internal Implementation

database/sql ships a built-in connection pool whose state is described by these fields (simplified):

type DB struct {
    driver driver.Driver
    dsn    string

    mu           sync.Mutex
    freeConn     []*driverConn              // idle connections
    connRequests map[uint64]chan connRequest  // requests waiting for a connection
    numOpen      int                         // total open connections (including in-use)
    maxOpen      int                         // maximum connections (0 = unlimited)
    maxIdle      int                         // maximum idle connections
    maxLifetime  time.Duration               // maximum connection lifetime
    maxIdleTime  time.Duration               // maximum connection idle time
}

When the application calls db.QueryContext(ctx, sql, args...), the internal sequence is:

1. Acquire the lock; try to take an idle connection from freeConn
2. If no idle connection is available:
   • If numOpen < maxOpen (or maxOpen == 0), create a new connection (call driver.Open)
   • Otherwise, add the current request to connRequests, release the lock, and block waiting
3. Check whether the connection has exceeded maxLifetime or maxIdleTime; if so, close it and retry
4. Execute the query on the connection and return the result
5. When the query completes, return the connection to freeConn (or close it, if idle connections have reached maxIdle)

The key optimization in this design is avoiding a new TCP connection for every query — establishing a database connection is expensive (TCP handshake + TLS handshake + database authentication, typically several milliseconds to tens of milliseconds).

The Four Critical Connection Pool Parameters

db.SetMaxOpenConns(25)                      // maximum open connections
db.SetMaxIdleConns(5)                       // maximum idle connections
db.SetConnMaxLifetime(5 * time.Minute)      // maximum connection lifetime
db.SetConnMaxIdleTime(1 * time.Minute)      // maximum connection idle time

SetMaxOpenConns: the most important parameter. Too high exhausts the database server's connection resources (PostgreSQL's default max_connections is 100); too low causes the application to wait for connections, increasing latency. A rule of thumb: for CPU-bound workloads, maxOpenConns should roughly equal the number of CPU cores; for I/O-bound workloads, it can be somewhat higher.

SetMaxIdleConns: idle connections keep the TCP connection open, reducing the latency of subsequent queries, but also consuming resources on the database server. maxIdleConns should be less than or equal to maxOpenConns; if it exceeds maxOpenConns, the excess is silently ignored.

SetConnMaxLifetime: the maximum age of a connection before it is closed and replaced. This is critical for handling database restarts (e.g., PostgreSQL primary failover) — connections older than maxLifetime are closed when returned to the pool, not reused.

SetConnMaxIdleTime (Go 1.15+): the maximum time a connection may sit idle before being cleaned up. This reduces resource consumption during low-traffic periods and prevents database servers from complaining about excessive idle connections.

Prepared Statements and SQL Injection

Prepared statements in database/sql serve two purposes. First, they prevent SQL injection — parameterized queries transmit the SQL statement and its arguments separately to the database; the driver guarantees that arguments cannot be parsed as SQL code. Second, they can improve performance — the database can cache the query's execution plan; for repeated executions of the same query, only the arguments need to be transmitted, not the SQL text.

// Never do this — string concatenation is vulnerable to SQL injection:
query := "SELECT * FROM users WHERE name = '" + name + "'"

// Always use parameterized queries:
rows, err := db.QueryContext(ctx, "SELECT * FROM users WHERE name = $1", name)

database/sql internally creates and reuses prepared statements when you call db.Query / db.QueryContext (the exact behavior depends on the driver), but you can also explicitly use db.PrepareContext to create persistent prepared statements and reuse them across multiple calls.

Transactions and Context Cancellation

A database/sql transaction is started with db.BeginTx(ctx, opts), returning a *sql.Tx that is bound to one specific database connection (bypassing the pool). This means the connection is exclusively held for the duration of the transaction and cannot be used by other queries.

context.Context cancellation propagates to in-progress database operations. When an HTTP request's context is cancelled (client disconnects), database queries should be cancelled too, avoiding wasted database resources:

func getUserByID(ctx context.Context, db *sql.DB, id int64) (*User, error) {
    var u User
    err := db.QueryRowContext(ctx,
        "SELECT id, name, email FROM users WHERE id = $1", id,
    ).Scan(&u.ID, &u.Name, &u.Email)
    if err == sql.ErrNoRows {
        return nil, ErrNotFound
    }
    return &u, err
}

If ctx is cancelled during query execution, QueryRowContext returns a context.Canceled or context.DeadlineExceeded error.

Level 3 · Code in Practice

The Repository Pattern

package repository

import (
    "context"
    "database/sql"
    "errors"
    "time"
)

var ErrNotFound = errors.New("record not found")

type User struct {
    ID        int64
    Name      string
    Email     string
    CreatedAt time.Time
    UpdatedAt time.Time
}

type UserRepository struct {
    db *sql.DB
}

func NewUserRepository(db *sql.DB) *UserRepository {
    return &UserRepository{db: db}
}

func (r *UserRepository) FindByID(ctx context.Context, id int64) (*User, error) {
    var u User
    err := r.db.QueryRowContext(ctx,
        `SELECT id, name, email, created_at, updated_at
         FROM users WHERE id = $1`,
        id,
    ).Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt, &u.UpdatedAt)
    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    return &u, err
}

func (r *UserRepository) Create(ctx context.Context, u *User) error {
    return r.db.QueryRowContext(ctx,
        `INSERT INTO users (name, email, created_at, updated_at)
         VALUES ($1, $2, NOW(), NOW())
         RETURNING id, created_at, updated_at`,
        u.Name, u.Email,
    ).Scan(&u.ID, &u.CreatedAt, &u.UpdatedAt)
}

func (r *UserRepository) Update(ctx context.Context, u *User) error {
    result, err := r.db.ExecContext(ctx,
        `UPDATE users SET name = $1, email = $2, updated_at = NOW()
         WHERE id = $3`,
        u.Name, u.Email, u.ID,
    )
    if err != nil {
        return err
    }
    rows, err := result.RowsAffected()
    if err != nil {
        return err
    }
    if rows == 0 {
        return ErrNotFound
    }
    return nil
}

Handling Nullable Columns

Database NULL values are a common pain point. Go's primitive types (string, int64) cannot represent NULL; you must use the sql.Null* family:

type Order struct {
    ID          int64
    UserID      int64
    Note        sql.NullString  // may be NULL
    CompletedAt sql.NullTime    // may be NULL
    Discount    sql.NullFloat64 // may be NULL
}

func (r *OrderRepository) FindByID(ctx context.Context, id int64) (*Order, error) {
    var o Order
    err := r.db.QueryRowContext(ctx,
        `SELECT id, user_id, note, completed_at, discount
         FROM orders WHERE id = $1`, id,
    ).Scan(&o.ID, &o.UserID, &o.Note, &o.CompletedAt, &o.Discount)

    // Check Valid before using the value
    if o.Note.Valid {
        fmt.Println("note:", o.Note.String)
    }
    return &o, err
}

For more complex cases, implement the driver.Valuer and sql.Scanner interfaces on a custom type:

// Custom type: a JSON column stored as text
type JSONMap map[string]interface{}

func (m JSONMap) Value() (driver.Value, error) {
    if m == nil {
        return nil, nil
    }
    b, err := json.Marshal(m)
    return string(b), err
}

func (m *JSONMap) Scan(src interface{}) error {
    if src == nil {
        *m = nil
        return nil
    }
    var b []byte
    switch v := src.(type) {
    case string:
        b = []byte(v)
    case []byte:
        b = v
    default:
        return fmt.Errorf("cannot scan type %T into JSONMap", src)
    }
    return json.Unmarshal(b, m)
}

Bulk Insert Optimization

When inserting large numbers of rows one at a time, each INSERT is a separate network round-trip — extremely inefficient. Bulk insert packs multiple rows into a single SQL statement:

func (r *OrderRepository) BulkCreate(ctx context.Context, orders []*Order) error {
    if len(orders) == 0 {
        return nil
    }

    valueStrings := make([]string, 0, len(orders))
    valueArgs := make([]interface{}, 0, len(orders)*2)

    for i, o := range orders {
        valueStrings = append(valueStrings,
            fmt.Sprintf("($%d, $%d)", i*2+1, i*2+2))
        valueArgs = append(valueArgs, o.UserID, o.Amount)
    }

    query := fmt.Sprintf(
        "INSERT INTO orders (user_id, amount) VALUES %s",
        strings.Join(valueStrings, ","),
    )

    _, err := r.db.ExecContext(ctx, query, valueArgs...)
    return err
}

For PostgreSQL specifically, the COPY protocol (via pgx's CopyFrom) is 5-10x faster than bulk INSERT and is appropriate for large-scale data imports:

import "github.com/jackc/pgx/v5"

func bulkInsertWithCopy(ctx context.Context, conn *pgx.Conn, orders []*Order) error {
    rows := make([][]interface{}, len(orders))
    for i, o := range orders {
        rows[i] = []interface{}{o.UserID, o.Amount}
    }

    _, err := conn.CopyFrom(ctx,
        pgx.Identifier{"orders"},
        []string{"user_id", "amount"},
        pgx.CopyFromRows(rows),
    )
    return err
}

Pagination: Keyset vs Offset

Offset pagination (LIMIT x OFFSET y) is simple and works well for small datasets, but performs terribly at large offsets:

-- The database must scan and discard 100,000 rows before returning 20
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

Keyset pagination (also called cursor pagination) avoids large offset scans entirely by remembering the last element of the previous page:

type PostPage struct {
    Posts      []*Post
    NextCursor string // base64-encoded cursor
}

func (r *PostRepository) FindPage(ctx context.Context, cursor string, limit int) (*PostPage, error) {
    var query string
    var args []interface{}

    if cursor == "" {
        // First page — no cursor
        query = `SELECT id, title, created_at FROM posts
                 ORDER BY created_at DESC, id DESC
                 LIMIT $1`
        args = []interface{}{limit + 1} // fetch one extra to detect whether a next page exists
    } else {
        cursorTime, cursorID, err := decodeCursor(cursor)
        if err != nil {
            return nil, err
        }
        query = `SELECT id, title, created_at FROM posts
                 WHERE (created_at, id) < ($1, $2)
                 ORDER BY created_at DESC, id DESC
                 LIMIT $3`
        args = []interface{}{cursorTime, cursorID, limit + 1}
    }

    rows, err := r.db.QueryContext(ctx, query, args...)
    // ... scan results, build next cursor

    page := &PostPage{Posts: posts}
    if len(posts) > limit {
        page.Posts = posts[:limit]
        last := posts[limit-1]
        page.NextCursor = encodeCursor(last.CreatedAt, last.ID)
    }
    return page, nil
}

Keyset pagination's constraints: arbitrary page jumps are not supported (only sequential forward/backward navigation); the sort key combination must be unique (here (created_at, id) ensures uniqueness).

Database Migrations with golang-migrate

Production database schema changes must be managed through a migration tool to make them trackable and reversible. golang-migrate is the most mature migration library in the Go ecosystem:

migrations/
├── 000001_create_users.up.sql
├── 000001_create_users.down.sql
├── 000002_add_user_roles.up.sql
└── 000002_add_user_roles.down.sql

-- 000001_create_users.up.sql
CREATE TABLE users (
    id         BIGSERIAL PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 000001_create_users.down.sql
DROP TABLE IF EXISTS users;

Integrating migrations in Go code using embedded files:

import (
    "embed"
    "github.com/golang-migrate/migrate/v4"
    _ "github.com/golang-migrate/migrate/v4/database/postgres"
    "github.com/golang-migrate/migrate/v4/source/iofs"
)

//go:embed migrations/*.sql
var migrationsFS embed.FS

func runMigrations(dsn string) error {
    source, err := iofs.New(migrationsFS, "migrations")
    if err != nil {
        return err
    }
    m, err := migrate.NewWithSourceInstance("iofs", source, dsn)
    if err != nil {
        return err
    }
    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return err
    }
    return nil
}

Using embed.FS embeds the SQL files into the binary, so production deployments do not need to carry the migration files separately.

Integration Testing with testcontainers-go

Mocking the database connection can test business logic, but it cannot test the correctness of your SQL (especially complex JOINs, subqueries, and database-specific behavior). testcontainers-go starts a real database Docker container for tests and automatically cleans up afterward:

package repository_test

import (
    "context"
    "testing"

    "github.com/testcontainers/testcontainers-go"
    "github.com/testcontainers/testcontainers-go/modules/postgres"
    "github.com/testcontainers/testcontainers-go/wait"
)

func TestUserRepository(t *testing.T) {
    ctx := context.Background()

    pgContainer, err := postgres.RunContainer(ctx,
        testcontainers.WithImage("postgres:16-alpine"),
        postgres.WithDatabase("testdb"),
        postgres.WithUsername("test"),
        postgres.WithPassword("test"),
        testcontainers.WithWaitStrategy(
            wait.ForLog("database system is ready to accept connections").
                WithOccurrence(2),
        ),
    )
    if err != nil {
        t.Fatal(err)
    }
    defer pgContainer.Terminate(ctx)

    dsn, err := pgContainer.ConnectionString(ctx, "sslmode=disable")
    if err != nil {
        t.Fatal(err)
    }

    db, err := sql.Open("postgres", dsn)
    if err != nil {
        t.Fatal(err)
    }
    defer db.Close()

    if err := runMigrations(dsn); err != nil {
        t.Fatal(err)
    }

    repo := NewUserRepository(db)

    t.Run("Create and FindByID", func(t *testing.T) {
        user := &User{Name: "Alice", Email: "[email protected]"}
        if err := repo.Create(ctx, user); err != nil {
            t.Fatal(err)
        }
        if user.ID == 0 {
            t.Error("expected non-zero ID after create")
        }

        found, err := repo.FindByID(ctx, user.ID)
        if err != nil {
            t.Fatal(err)
        }
        if found.Email != user.Email {
            t.Errorf("got email %q, want %q", found.Email, user.Email)
        }
    })
}

testcontainers-go tests are slower than regular unit tests (container startup typically takes 2-5 seconds). Place them under an integration build tag to run separately from unit tests:

go test -tags integration ./...

Level 4 · Advanced Topics and Edge Cases

The N+1 Query Problem and the DataLoader Pattern

The N+1 problem is the most common ORM performance trap, but it occurs with hand-written SQL too:

// Wrong: N+1 queries
posts, _ := repo.FindAllPosts(ctx)            // 1 query
for _, post := range posts {
    author, _ := repo.FindUserByID(ctx, post.AuthorID)  // N queries
    post.Author = author
}

Solution 1: a JOIN query fetching all data in one shot:

SELECT p.id, p.title, u.id, u.name
FROM posts p
JOIN users u ON u.id = p.author_id
WHERE p.id = ANY($1)

Solution 2: the DataLoader pattern — batch multiple individual queries for the same entity type into a single bulk query:

type UserLoader struct {
    db    *sql.DB
    mu    sync.Mutex
    batch []*loadRequest
    timer *time.Timer
}

type loadRequest struct {
    id     int64
    result chan *userResult
}

type userResult struct {
    user *User
    err  error
}

// Load is the caller-facing API. It returns the user asynchronously.
func (l *UserLoader) Load(ctx context.Context, id int64) (*User, error) {
    req := &loadRequest{
        id:     id,
        result: make(chan *userResult, 1),
    }
    l.mu.Lock()
    l.batch = append(l.batch, req)
    // Wait a short window (e.g., 1ms) to collect more requests before executing
    if l.timer == nil {
        l.timer = time.AfterFunc(1*time.Millisecond, l.executeBatch)
    }
    l.mu.Unlock()

    res := <-req.result
    return res.user, res.err
}

func (l *UserLoader) executeBatch() {
    l.mu.Lock()
    requests := l.batch
    l.batch = nil
    l.timer = nil
    l.mu.Unlock()

    ids := make([]int64, len(requests))
    for i, r := range requests {
        ids[i] = r.id
    }

    users, err := l.findByIDs(context.Background(), ids)
    userMap := make(map[int64]*User)
    for _, u := range users {
        userMap[u.ID] = u
    }

    for _, req := range requests {
        if err != nil {
            req.result <- &userResult{err: err}
        } else {
            req.result <- &userResult{user: userMap[req.id]}
        }
    }
}

DataLoader is a classic pattern from the GraphQL ecosystem (originally implemented by Facebook in Node.js) and is widely used in Go GraphQL services built with tools like gqlgen.

Read Replicas with Connection Routing

Under high traffic, a typical setup has one primary database (handling writes) and multiple replicas (handling reads). Implementing read/write splitting in Go:

type DBCluster struct {
    primary  *sql.DB
    replicas []*sql.DB
    mu       sync.Mutex
    counter  int
}

// Writes always go to the primary
func (c *DBCluster) Primary() *sql.DB {
    return c.primary
}

// Reads are distributed across replicas using round-robin
func (c *DBCluster) Replica() *sql.DB {
    if len(c.replicas) == 0 {
        return c.primary // fall back to primary if no replicas exist
    }
    c.mu.Lock()
    idx := c.counter % len(c.replicas)
    c.counter++
    c.mu.Unlock()
    return c.replicas[idx]
}

In the repository layer, use c.Replica() for SELECT queries and c.Primary() for INSERT/UPDATE/DELETE. Note that replicas have replication lag (typically milliseconds to seconds). If your application requires "read your own writes" consistency, data that was just written should be read from the primary.

Optimistic Locking with a Version Column

Optimistic locking handles concurrent update conflicts without using database locks (which risk deadlocks and carry performance costs). The implementation adds a version column to the table:

CREATE TABLE products (
    id      BIGSERIAL PRIMARY KEY,
    name    VARCHAR(255) NOT NULL,
    price   NUMERIC(10,2) NOT NULL,
    version INT NOT NULL DEFAULT 0
);

On update, include the current version in the WHERE clause. If another transaction already modified the record, the version will not match, and RowsAffected will be zero:

func (r *ProductRepository) Update(ctx context.Context, p *Product) error {
    result, err := r.db.ExecContext(ctx,
        `UPDATE products
         SET name = $1, price = $2, version = version + 1
         WHERE id = $3 AND version = $4`,
        p.Name, p.Price, p.ID, p.Version,
    )
    if err != nil {
        return err
    }
    rows, err := result.RowsAffected()
    if err != nil {
        return err
    }
    if rows == 0 {
        return ErrConflict // concurrent modification; caller should retry
    }
    p.Version++
    return nil
}

Optimistic locking is appropriate when conflicts are rare (most of the time, only one client is modifying a given record). If conflicts are frequent, the overhead of constant retries produces worse performance than pessimistic locking (SELECT ... FOR UPDATE).

pgx: Accessing PostgreSQL-Specific Features

pgx is the native Go driver for PostgreSQL, offering capabilities beyond what lib/pq provides:

• CopyFrom: COPY protocol for bulk imports, far faster than INSERT
• pgxpool: more fine-grained connection pool control than database/sql
• Listen/Notify: PostgreSQL's pub/sub mechanism for real-time notifications
• Array types: native support for int[], text[], and other PostgreSQL array types
• pgtype: richer type mappings including hstore, jsonb, UUID, and more

import (
    "github.com/jackc/pgx/v5/pgxpool"
)

pool, err := pgxpool.New(ctx, dsn)

// Listen for PostgreSQL notifications
conn, _ := pool.Acquire(ctx)
defer conn.Release()

_, err = conn.Exec(ctx, "LISTEN user_events")
for {
    notification, err := conn.Conn().WaitForNotification(ctx)
    if err != nil {
        break
    }
    fmt.Printf("Channel: %s, Payload: %s\n",
        notification.Channel, notification.Payload)
}

Connection Pool Tuning Under Load

Theoretical connection pool parameters and actual production environments often diverge. Here are several practical guidelines for tuning the connection pool under load.

Monitor wait time: use db.Stats() to obtain connection pool statistics, especially WaitCount and WaitDuration. If WaitDuration / WaitCount exceeds 10 ms, the pool is too small — increase MaxOpenConns.

stats := db.Stats()
slog.Info("db pool stats",
    "open", stats.OpenConnections,
    "in_use", stats.InUse,
    "idle", stats.Idle,
    "wait_count", stats.WaitCount,
    "wait_duration", stats.WaitDuration,
    "max_idle_closed", stats.MaxIdleClosed,
    "max_lifetime_closed", stats.MaxLifetimeClosed,
)

Never set MaxOpenConns to 0 (unlimited): under a traffic spike, this causes the application to attempt to open hundreds or thousands of database connections, likely exceeding PostgreSQL's max_connections limit and triggering cascading connection errors.

Pool size does not equal maximum concurrent queries: if every query is fast (< 1 ms), a small pool can support very high QPS; if queries are slow (> 100 ms), you need more connections to support the same concurrency. The formula: needed connections ≈ queries per second × average query duration (in seconds).

The Query Builder vs Raw SQL Debate

Between raw SQL strings, query builders (like squirrel), and full ORMs like GORM, each occupies a different point on the flexibility-vs-safety spectrum.

Raw SQL is the most expressive and transparent — every query is exactly what the database sees. The downside is that building dynamic queries (with optional filters, variable sort orders) via string concatenation is tedious and error-prone.

Query builders solve the dynamic query problem while keeping SQL visible. squirrel is a popular Go query builder:

import sq "github.com/Masterminds/squirrel"

// Build a query with optional filters
qb := sq.Select("id", "name", "email").From("users")
if name != "" {
    qb = qb.Where(sq.Eq{"name": name})
}
if email != "" {
    qb = qb.Where(sq.Eq{"email": email})
}

sql, args, err := qb.PlaceholderFormat(sq.Dollar).ToSql()
rows, err := db.QueryContext(ctx, sql, args...)

sqlc occupies a unique position: you write raw SQL queries in .sql files, and sqlc generates type-safe Go code from them. The SQL is the source of truth; the Go code is derived. This is arguably the best of both worlds — full SQL expressiveness with compile-time type safety:

-- query.sql
-- name: GetUserByID :one
SELECT id, name, email, created_at FROM users WHERE id = $1;

-- name: ListUsers :many
SELECT id, name, email, created_at FROM users ORDER BY created_at DESC LIMIT $1;

sqlc generate produces:

func (q *Queries) GetUserByID(ctx context.Context, id int64) (User, error) { ... }
func (q *Queries) ListUsers(ctx context.Context, limit int32) ([]User, error) { ... }

The generated functions are strongly typed — no interface{}, no rows.Scan with raw column indexes, no possibility of a runtime type mismatch.

Database access is one of the areas in backend engineering that most rewards careful thought. database/sql provides the right level of abstraction — low enough to give you full SQL control, high enough to manage connection lifetimes. Understanding its internals, combined with the right tool for each scenario (sqlx for light convenience, sqlc for type-safe SQL-first development, GORM for rapid prototyping), and solid knowledge of performance tuning, equips you to make correct database access decisions at any scale.