Go Database & GORM: The Persistence Mirror

Go connects to databases via the database/sql standard library package combined with a database-specific driver. sql.DB manages a connection pool automatically. For productivity, the GORM ORM wraps database/sql with a clean, chainable API for CRUD, associations, and migrations. Use raw database/sql for maximum performance and control; use GORM for faster development with less boilerplate.

In this module, we will explore both raw SQL interactions and the most popular Object-Relational Mapper (ORM) in the Go world: GORM.

1. The Persistence Mirror: The Driver Architecture

Go's database logic is based on the Separation of Interface and Hardware.

The Driver Physics

• The Generic Interface Mirror: The database/sql package provides the "Interface" (the SQL verbs like Query and Exec).
• The Driver Adapter Mirror: Specific drivers (e.g., pgx, go-sql-driver/mysql) translate these Go verbs into the specific binary protocol required by the database server's silicon.
• The Result: You can swap your entire database engine (e.g., SQLite to PostgreSQL) by changing a single import line and the connection string, keeping your business logic's state mirror intact.

2. Database Connectivity: SQL & GORM

One of the best things about Go's sql.DB object is that it is NOT a single connection. It is a connection pool. The standard library automatically handles opening, closing, and reusing connections for you, preventing your database from being overwhelmed under high load.

3. Connection Pool Physics: Socket Management

Managing database connections is expensive. Go optimizes this via the sql.DB pool manager.

The Pool Physics

• The Idle Mirror: When a query finishes, Go doesn't close the TCP socket. It returns the connection to an "Idle Pool." This avoids the "Handshake Latency" of constant TCP/TLS negotiation.
• The Active Mirror: Under heavy load, Go opens new connections up to a limit (SetMaxOpenConns). If all slots are full, new goroutines wait for an existing connection to return to the pool.
• The Result: Your application maintains a steady "Mirror" of connectivity, ensuring that spikes in user traffic don't crash the database server with a "Too Many Connections" error.

4. The Standard Way: database/sql

To use the standard library, you need a "Driver" for your specific database. For this example, we'll use PostgreSQL.

import (
    "database/sql"
    _ "github.com/lib/pq" // Anonymous import to register the driver
)

func main() {
    // Open the connection pool
    db, err := sql.Open("postgres", "user=pqgotest dbname=pqgotest sslmode=verify-full")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Querying for data
    var username string
    err = db.QueryRow("SELECT name FROM users WHERE id = $1", 1).Scan(&username)
    if err != nil {
        log.Fatal(err)
    }
}

The Developer's Favorite: GORM

While raw SQL gives you the most control, many developers prefer the productivity of an ORM. GORM is the industry standard for Go, offering a fantastic balance of features and performance.

Defining Your Model

GORM uses structs as the blueprint for your database tables.

type User struct {
    gorm.Model // Adds ID, CreatedAt, UpdatedAt, DeletedAt automatically
    Name  string
    Email string `gorm:"uniqueIndex"`
}

CRUD Operations with GORM

func main() {
    db, _ := gorm.Open(postgres.Open(dsn), &gorm.Config{})

    // Create
    db.Create(&User{Name: "Alice", Email: "alice@example.com"})

    // Read
    var user User
    db.First(&user, 1) // find user with integer primary key

    // Update
    db.Model(&user).Update("Email", "new_alice@example.com")

    // Delete
    db.Delete(&user, 1)
}

Using Transactions

Transactions are critical when multiple database operations must succeed or fail together. A payment that deducts from one account and credits another must be atomic — if the credit fails, the deduction must be rolled back.

Transactions with database/sql

func transferFunds(db *sql.DB, fromID, toID int, amount float64) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    // Ensure we always either commit or rollback
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        }
    }()

    _, err = tx.Exec("UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, fromID)
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("debit failed: %w", err)
    }

    _, err = tx.Exec("UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, toID)
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("credit failed: %w", err)
    }

    return tx.Commit()
}

Transactions with GORM

func transferFundsGORM(db *gorm.DB, fromID, toID uint, amount float64) error {
    return db.Transaction(func(tx *gorm.DB) error {
        if err := tx.Model(&Account{}).Where("id = ?", fromID).
            Update("balance", gorm.Expr("balance - ?", amount)).Error; err != nil {
            return err // GORM automatically rolls back on error return
        }
        if err := tx.Model(&Account{}).Where("id = ?", toID).
            Update("balance", gorm.Expr("balance + ?", amount)).Error; err != nil {
            return err
        }
        return nil // Committed on nil return
    })
}

GORM's Transaction helper automatically commits on nil return and rolls back on error — a cleaner API than managing tx.Rollback() manually.

Connection Pool Configuration

The sql.DB connection pool has sensible defaults but should be tuned for production:

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

// Maximum number of open connections to the database
db.SetMaxOpenConns(25)

// Maximum number of idle connections in the pool
db.SetMaxIdleConns(10)

// Maximum time a connection may be reused
db.SetConnMaxLifetime(5 * time.Minute)

Under-provisioning MaxOpenConns causes request queuing under load. Over-provisioning can exhaust database connection limits. For PostgreSQL, the max_connections setting (default 100) is the hard ceiling — your pool size across all application instances must stay below it.

Preventing SQL Injection

Always use parameterised queries. Never concatenate user input into SQL strings:

// DANGEROUS: SQL injection vulnerability
query := "SELECT * FROM users WHERE name = '" + userInput + "'"

// SAFE: Parameterised query
row := db.QueryRow("SELECT * FROM users WHERE name = $1", userInput)

GORM also uses parameterised queries by default when you use its API methods. Only be careful with the Raw() and Exec() methods if you construct query strings manually.

For more on SQL patterns and query structure, see our SQL query examples guide. For securing your entire backend, see Go security best practices.

GORM Associations

GORM's relationship handling is one of its biggest productivity advantages. Defining associations between models automatically handles JOIN queries and eager loading:

type User struct {
    gorm.Model
    Name    string
    Email   string
    Posts   []Post // HasMany
}

type Post struct {
    gorm.Model
    Title  string
    Body   string
    UserID uint // Foreign key
    User   User // BelongsTo
}

// Fetch a user with all their posts loaded
var user User
db.Preload("Posts").First(&user, 1)

The Preload clause generates an efficient WHERE user_id IN (...) query rather than N+1 individual queries.

External Resources

• database/sql — Go standard library documentation
• GORM official documentation — gorm.io
• Opening a Database Handle — go.dev

Next Steps

Persistence is the final piece of the structural puzzle. Now we enter the final phases of our course. In our next tutorial, we will explore Middleware Patterns, learning how to layer security, logging, and metrics onto our web servers in a clean and reusable way.

Common Database Mistakes in Go

1. Not closing rows after a query rows, err := db.QueryContext(ctx, query) — if you forget defer rows.Close(), the connection is held open until garbage collection, exhausting the connection pool under load.

2. Using db.Query instead of db.QueryContext The non-context variants (Query, Exec, QueryRow) cannot be cancelled. In a web server, always use the Context variants and pass r.Context() so database calls are cancelled when the client disconnects.

3. Not using db.Prepare for repeated queries Executing the same query thousands of times without a prepared statement re-parses it on every call. Use db.PrepareContext for queries in hot paths to save the round-trip to the database server. See the database/sql documentation.

4. GORM AutoMigrate in production db.AutoMigrate(&User{}) is convenient for development but dangerous in production — it can add columns and indexes but never drops them, and may lock tables on large datasets. Use proper migration tools (golang-migrate, goose) for production schema changes.

5. Ignoring sql.ErrNoRows db.QueryRowContext(...).Scan(...) returns sql.ErrNoRows when no record is found. This is not a fatal error — handle it explicitly: if errors.Is(err, sql.ErrNoRows) { return nil, ErrNotFound }.

Frequently Asked Questions

Should I use GORM or raw database/sql? GORM reduces boilerplate for CRUD operations and is excellent for rapid development. Raw database/sql with sqlx or pgx gives more control and is faster for complex queries. Many production services use GORM for simple operations and drop to raw SQL for performance-critical paths.

How do I manage database connection pool settings? Set db.SetMaxOpenConns(n), db.SetMaxIdleConns(n), and db.SetConnMaxLifetime(d) immediately after sql.Open. A typical starting point for a web server: max open = number of CPU cores × 2, max idle = same, lifetime = 5 minutes.

golang-migrate is the most widely used migration tool for Go — it supports PostgreSQL, MySQL, SQLite, and more, and integrates with both CLI and application code. Write migrations as plain SQL files for maximum portability.

Phase 20: Persistence Architecture Mastery Checklist

• Verify Connection Pool Tuning: Audit your SetMaxOpenConns and SetMaxIdleConns settings to ensure they correspond to your database's silicon limits.
• Audit Context Propagation: Ensure that every database call uses the Context variant (e.g., QueryContext) to prevent orphan queries from leaking in the background mirror.
• Implement Sovereign Migrations: Replace GORM AutoMigrate with a dedicated tool like golang-migrate for predictable, versioned schema evolution.
• Test Transactional Atomicity: Verify that multi-step operations (e.g., Order + Payment) are wrapped in a single transaction that rolls back correctly on network failure.
• Use Nullable Mirror Types: Utilize sql.NullString or pointers for optional database columns to accurately represent the difference between "Zero" and "NULL" in silicon state.

Read next: Go Middleware Patterns: The Interceptor Mirror →