Database Access Layer Design

A database access layer (DAL) sits between application logic and database storage, translating domain concepts into database operations. It matters when applications scale beyond simple queries, when data consistency, performance, and maintainability become non-trivial. This is the layer that handles connection pooling, transaction management, query composition, result mapping, and error handling—ensuring that database interactions are predictable, efficient, and resilient.

Core Operations

Reading Data

Reading data from a database is the most common operation. The DAL must support fetching a single record, collections, and complex joins with filtering, sorting, and pagination.

-- Example query executed by the DAL
SELECT u.id, u.email, p.title, p.published_at
FROM users u
JOIN posts p ON u.id = p.author_id
WHERE u.active = true
  AND p.published_at > '2024-01-01'
ORDER BY p.published_at DESC
LIMIT 10 OFFSET 20;

The DAL reads this query and applies a standard pattern: prepare the statement, bind parameters, execute, then map the result set to a domain object.

For a user with ID 123, the DAL might expose a method:

user, err := dal.GetUser(context.TODO(), 123)
if err != nil {
    log.Printf("Failed to fetch user %d: %v", 123, err)
}

The layer ensures that database errors—like SQLSTATE[HY000]: General error: 1213 Deadlock found when trying to get lock; try restarting transaction—are translated into application-level errors. It also handles missing records: a UserNotFound error is returned when SELECT returns no rows.

Writing Data

Writing data involves inserts, updates, and deletes. The DAL must coordinate changes across multiple tables and manage transaction boundaries.

func (d *Database) SaveUser(ctx context.Context, user *User) error {
    tx, err := d.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
    if err != nil {
        return err
    }

    query := `
        INSERT INTO users (id, email, name, created_at)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (id) DO UPDATE
        SET email = $2, name = $3, updated_at = $4
        RETURNING id;
    `

    var id int
    err = tx.QueryRowContext(ctx, query, user.ID, user.Email, user.Name, user.CreatedAt).Scan(&id)
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("failed to save user: %w", err)
    }

    // Optional: insert into audit log
    _, err = tx.ExecContext(ctx, "INSERT INTO user_audit (user_id, action, timestamp) VALUES ($1, 'created', NOW())", id)
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("failed to audit user: %w", err)
    }

    return tx.Commit()
}

The layer tracks when the transaction is committed, rolled back, or fails mid-operation. It logs ROLLBACK and COMMIT events and enriches error messages with the current transaction state.

Handling Transactions

Transactions are central to consistency. The DAL ensures that multiple operations are atomic and isolated.

A common pitfall is forgetting to commit a transaction, especially when a function returns early:

func (d *Database) TransferFunds(ctx context.Context, fromID, toID int, amount float64) error {
    tx, err := d.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
    if err != nil {
        return err
    }

    // Read balance
    var fromBalance, toBalance float64
    err = tx.QueryRowContext(ctx, "SELECT balance FROM accounts WHERE id = $1", fromID).Scan(&fromBalance)
    if err != nil {
        return err
    }

    if fromBalance < amount {
        // This error is never committed!
        return fmt.Errorf("insufficient funds: got %.2f, need %.2f", fromBalance, amount)
    }

    // Update balances
    _, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, fromID)
    if err != nil {
        return err
    }

    _, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, toID)
    if err != nil {
        return err
    }

    return tx.Commit() // Must be called explicitly
}

The DAL can enforce this pattern via a WithTransaction helper:

func WithTransaction(ctx context.Context, db *sql.DB, f func(ctx context.Context) error) error {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
    if err != nil {
        return err
    }

    if err := f(ctx); err != nil {
        if rbErr := tx.Rollback(); rbErr != nil {
            return fmt.Errorf("transaction failed: %w; rollback failed: %v", err, rbErr)
        }
        return err
    }

    return tx.Commit()
}

Query Building and Reuse

The DAL must support dynamic query building, especially for filters and search.

type UserQuery struct {
    Active     *bool
    MinAge     *int
    MaxAge     *int
    EmailLike  *string
    SortBy     string
    Limit      int
    Offset     int
}

func (q UserQuery) BuildSQL() (string, []any) {
    var parts []string
    var args []any

    if q.Active != nil {
        parts = append(parts, fmt.Sprintf("active = $%d", len(args)+1))
        args = append(args, *q.Active)
    }

    if q.MinAge != nil {
        parts = append(parts, fmt.Sprintf("age >= $%d", len(args)+1))
        args = append(args, *q.MinAge)
    }

    if q.MaxAge != nil {
        parts = append(parts, fmt.Sprintf("age <= $%d", len(args)+1))
        args = append(args, *q.MaxAge)
    }

    if q.EmailLike != nil {
        parts = append(parts, fmt.Sprintf("email ILIKE $%d", len(args)+1))
        args = append(args, "%"+*q.EmailLike+"%")
    }

    whereClause := strings.Join(parts, " AND ")
    if whereClause != "" {
        whereClause = "WHERE " + whereClause
    }

    sortClause := "ORDER BY id ASC"
    if q.SortBy != "" {
        sortClause = fmt.Sprintf("ORDER BY %s", q.SortBy)
    }

    limitClause := fmt.Sprintf("LIMIT %d", q.Limit)
    offsetClause := fmt.Sprintf("OFFSET %d", q.Offset)

    return fmt.Sprintf(`
        SELECT id, email, name, age, active
        FROM users
        %s
        %s
        %s
        %s
    `, whereClause, sortClause, limitClause, offsetClause), args
}

The DAL executes this query and returns a []User, possibly with a PaginationMetadata struct.

Advanced Patterns

Connection Pooling and Lifecycle

The DAL manages database connections, reusing them to reduce overhead. It starts with a connection pool configured via:

database:
  dsn: "host=prod-db.example.com port=5432 dbname=app user=app password=secret"
  max_connections: 100
  min_idle: 10
  idle_timeout: "30s"
  max_lifetime: "1h"
  ping_interval: "10s"

On startup, the DAL opens connections and runs a periodic ping to validate them. If a connection fails, the pool marks it as invalid and replaces it on the next use.

When a query runs, it pulls a connection from the pool. If all connections are busy, the next query waits until one becomes available. The pool tracks metrics: total connections, active, idle, waiting, and pool size.

Failure modes include:

• connection pool exhausted — no connection available after timeout
• connection closed unexpectedly — network loss, server restart
• connection timeout — query takes longer than max_lifetime

The DAL logs these as pool: connection acquired, pool: connection released, and pool: connection expired.

Error Handling and Retry

The DAL captures database errors and applies retry logic for transient failures.

func (d *Database) ExecuteWithRetry(ctx context.Context, query string, args ...any) (sql.Result, error) {
    var lastErr error
    for attempt := 0; attempt < 3; attempt++ {
        result, err := d.db.ExecContext(ctx, query, args...)
        if err == nil {
            return result, nil
        }

        lastErr = err

        // Check for transient errors
        if isTransientError(err) {
            // Exponential backoff
            time.Sleep(time.Duration(1<<attempt) * time.Second)
            continue
        }

        break
    }

    return nil, fmt.Errorf("database operation failed after %d attempts: %w", 3, lastErr)
}

func isTransientError(err error) bool {
    return strings.Contains(err.Error(), "deadlock") ||
           strings.Contains(err.Error(), "connection timeout") ||
           strings.Contains(err.Error(), "connection lost") ||
           strings.Contains(err.Error(), "network error")
}

The layer logs retry: database query failed, retrying (attempt 1) and retry: final failure after 3 attempts.

Schema Migrations and Versioning

The DAL integrates with schema migrations to manage database evolution.

Migration files are stored in migrations/ directory:

-- migrations/001-create-users-table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

A migration runner applies these in order:

$ migrate --config=migrations.yaml --up

Configuration:

migrations:
  dir: "migrations"
  table: "schema_migrations"
  up: "up"
  down: "down"
  version_column: "version"
  timestamp_format: "20060102150405"

The layer tracks applied migrations in a schema_migrations table:

version	applied_at
1	2024-01-15 10:30:00
2	2024-01-16 08:15:22

On startup, the DAL runs migrate --up automatically. If a migration fails, it logs migration: failed to apply version 3: SQLSTATE[42P01]: Undefined table: 1001: relation "users" does not exist.

Caching and Denormalization

The DAL supports read-through caching to reduce database load.

type CachedUserRepository struct {
    dal     *Database
    cache   cache.Cache  // e.g., redis, memcached
    ttl     time.Duration
}

func (r *CachedUserRepository) GetUser(ctx context.Context, id int) (*User, error) {
    key := fmt.Sprintf("user:%d", id)
    cached, ok := r.cache.Get(key)
    if ok {
        return cached.(*User), nil
    }

    user, err := r.dal.GetUser(ctx, id)
    if err != nil {
        return nil, err
    }

    r.cache.Set(key, user, r.ttl)
    return user, nil
}

The layer handles cache misses, cache hits, and cache invalidation. On SaveUser, it invalidates the user:{id} key.

Query Optimization and Instrumentation

The DAL instruments queries with timing and metadata.

type QueryLogger interface {
    Log(ctx context.Context, query string, args []any, duration time.Duration, rowCount int)
}

func (d *Database) QueryContextWithLogging(ctx context.Context, query string, args ...any) (*sql.Rows, error) {
    start := time.Now()
    rows, err := d.db.QueryContext(ctx, query, args...)
    duration := time.Since(start)

    if logger := d.QueryLogger; logger != nil {
        logger.Log(ctx, query, args, duration, 0) // rowCount to be updated
    }

    if err != nil {
        return rows, err
    }

    // Wrap rows to count rows
    return &instrumentedRows{Rows: rows, logger: d.QueryLogger, query: query}, nil
}

type instrumentedRows struct {
    sql.Rows
    logger   QueryLogger
    query    string
    rowCount int
}

func (i *instrumentedRows) Close() error {
    if err := i.Rows.Close(); err != nil {
        return err
    }

    if i.logger != nil {
        i.logger.Log(context.Background(), i.query, nil, time.Since(time.Now()), i.rowCount)
    }
    return nil
}

func (i *instrumentedRows) Next() bool {
    if i.Rows.Next() {
        i.rowCount++
        return true
    }
    return false
}

The layer produces logs like:

db: query executed: SELECT * FROM users WHERE active = $1; args=[true]; duration=123ms; rows=42

It supports profiling: SELECT * FROM pg_stat_statements WHERE query LIKE '%users%'.

Common Pitfalls and Sharp Edges

• Transaction not committed — a function returns nil but the transaction is never committed. The DAL catches this via defer tx.Commit() and logs transaction not committed.
• Query not prepared — a query with SELECT * FROM users WHERE id = $1 is executed repeatedly without PREPARE. The layer can detect this and auto-prepare common queries.
• Result mapping errors — a User struct has CreatedAt but the column is created_at. The DAL maps created_at to CreatedAt via reflection or a mapping table. A mismatch causes column not found: created_at or type mismatch: expected time.Time, got timestamp.
• Connection leak — a transaction is started but never committed or rolled back. The layer detects this via defer tx.Close() and logs transaction open for more than 30s.
• Transaction isolation not set — the default READ COMMITTED is used, but SERIALIZABLE is needed for financial data. The layer allows per-operation isolation levels and logs transaction isolation set to SERIALIZABLE.

The DAL is not a black box. It is the heart of data reliability — where application logic meets persistence, and where the subtle failures of databases become visible and actionable.

This page was rewritten on 15 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.