Databases Basics

10 examples to get you started with database/sql - 7 basic and 3 intermediate.

Prerequisites

Install Go 1.26.x or later from go.dev/dl.
Create a module: mkdir dbdemo && cd dbdemo && go mod init example.com/dbdemo.
Add SQLite driver: go get github.com/mattn/go-sqlite3.
Save each snippet as main.go (or separate files in one package) and run with go run ..

Basic Examples

1. sql.Open and PingContext

Open creates a pool; Ping verifies connectivity.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
    "time"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        panic(err)
    }
    defer db.Close()
 
    ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
    defer cancel()
    fmt.Println(db.PingContext(ctx))
}

sql.Open does not guarantee a live connection yet.
Always defer db.Close() to release pool resources on shutdown.
Use PingContext at startup to fail fast on bad DSNs.

Related: Data Access in Go: database/sql as the Foundation - why drivers are separate

2. ExecContext for DDL and inserts

Exec runs statements without row iteration.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, err := db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
    if err != nil {
        panic(err)
    }
    res, err := db.ExecContext(ctx, `INSERT INTO users (name) VALUES (?)`, "Ada")
    if err != nil {
        panic(err)
    }
    id, _ := res.LastInsertId()
    fmt.Println("inserted id:", id)
}

ExecContext returns sql.Result with RowsAffected and LastInsertId when supported.
Placeholders differ by driver (? for SQLite, $1 for Postgres).
DDL in app startup is fine for demos; use migrations in production.

Related: Migrations with golang-migrate & goose - schema versioning

3. QueryRowContext and Scan

Single-row reads use QueryRow plus Scan.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, _ = db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
    _, _ = db.ExecContext(ctx, `INSERT INTO users (name) VALUES ('Grace')`)
 
    var name string
    err := db.QueryRowContext(ctx, `SELECT name FROM users WHERE id = 1`).Scan(&name)
    fmt.Println(name, err)
}

Scan destination count and types must match selected columns.
sql.ErrNoRows means zero rows, not a transport failure.
Map ErrNoRows to domain errors before HTTP handlers.

Related: Transactions, Prepared Statements & sql.Tx - multi-statement flows

4. QueryContext and rows.Next loop

Multi-row queries iterate until Next returns false.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, _ = db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
    for _, n := range []string{"Ada", "Grace", "Linus"} {
        _, _ = db.ExecContext(ctx, `INSERT INTO users (name) VALUES (?)`, n)
    }
 
    rows, err := db.QueryContext(ctx, `SELECT name FROM users ORDER BY id`)
    if err != nil {
        panic(err)
    }
    defer rows.Close()
 
    for rows.Next() {
        var name string
        if err := rows.Scan(&name); err != nil {
            panic(err)
        }
        fmt.Println(name)
    }
    fmt.Println(rows.Err())
}

Always defer rows.Close() to release the connection back to the pool.
Check rows.Err() after the loop for iteration errors.
Preallocate slices when you know approximate row counts.

Related: GORM vs sqlx vs Raw database/sql - scanning helpers

5. sql.NullString for nullable columns

Use sql.Null* types for nullable SQL fields.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, _ = db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, nickname TEXT)`)
    _, _ = db.ExecContext(ctx, `INSERT INTO users (id) VALUES (1)`)
 
    var nick sql.NullString
    _ = db.QueryRowContext(ctx, `SELECT nickname FROM users WHERE id = 1`).Scan(&nick)
    if nick.Valid {
        fmt.Println(nick.String)
    } else {
        fmt.Println("nickname is NULL")
    }
}

Valid is false when the column is SQL NULL.
Prefer pointers (*string) in domain structs when NULL is common.
ORMs may hide NULL handling; raw SQL makes it explicit.

Related: Databases & Data Access Best Practices - mapping conventions

6. Context timeout on a slow query

Deadlines cancel work when clients leave.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
    "time"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
 
    ctx, cancel := context.WithTimeout(context.Background(), 50*time.Millisecond)
    defer cancel()
 
    _, err := db.ExecContext(ctx, `SELECT 1`)
    fmt.Println(err)
}

Prefer *Context methods on every request-scoped path.
Combine context with SQL statement_timeout when drivers lag on cancel.
Return ctx.Err() when cancellation caused the failure.

Related: database/sql Connection Pooling & Timeouts - pool plus deadlines

7. Basic transaction with BeginTx

BeginTx groups statements atomically.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, _ = db.ExecContext(ctx, `CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER)`)
    _, _ = db.ExecContext(ctx, `INSERT INTO accounts (id, balance) VALUES (1, 100), (2, 0)`)
 
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        panic(err)
    }
    _, err = tx.ExecContext(ctx, `UPDATE accounts SET balance = balance - 10 WHERE id = 1`)
    if err != nil {
        _ = tx.Rollback()
        panic(err)
    }
    _, err = tx.ExecContext(ctx, `UPDATE accounts SET balance = balance + 10 WHERE id = 2`)
    if err != nil {
        _ = tx.Rollback()
        panic(err)
    }
    fmt.Println(tx.Commit())
}

Roll back on any error before commit.
Keep transactions short to reduce lock time.
Pass the same ctx into BeginTx and statements inside the tx.

Related: Transactions, Prepared Statements & sql.Tx - rollback patterns

Intermediate Examples

8. Prepared statement reuse

PrepareContext amortizes parse cost for repeated queries.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, _ = db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
 
    stmt, err := db.PrepareContext(ctx, `INSERT INTO users (name) VALUES (?)`)
    if err != nil {
        panic(err)
    }
    defer stmt.Close()
 
    for _, name := range []string{"a", "b", "c"} {
        if _, err := stmt.ExecContext(ctx, name); err != nil {
            panic(err)
        }
    }
 
    var count int
    _ = db.QueryRowContext(ctx, `SELECT COUNT(*) FROM users`).Scan(&count)
    fmt.Println(count)
}

Close statements when done; leaked stmts hold connections.
Some drivers cache prepared statements automatically; measure before manual Prepare.
Use Tx.Stmt to rebind a prepared stmt to a transaction.

Related: Transactions, Prepared Statements & sql.Tx - Stmt on Tx

9. IN clause with dynamic placeholders

Build placeholder lists safely for variable-length filters.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
    "strings"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func namesByIDs(ctx context.Context, db *sql.DB, ids []int64) ([]string, error) {
    if len(ids) == 0 {
        return nil, nil
    }
    placeholders := strings.TrimSuffix(strings.Repeat("?,", len(ids)), ",")
    query := fmt.Sprintf(`SELECT name FROM users WHERE id IN (%s)`, placeholders)
    args := make([]any, len(ids))
    for i, id := range ids {
        args[i] = id
    }
    rows, err := db.QueryContext(ctx, query, args...)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var names []string
    for rows.Next() {
        var n string
        if err := rows.Scan(&n); err != nil {
            return nil, err
        }
        names = append(names, n)
    }
    return names, rows.Err()
}
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
    _, _ = db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
    _, _ = db.ExecContext(ctx, `INSERT INTO users (id, name) VALUES (1,'Ada'), (2,'Grace')`)
    fmt.Println(namesByIDs(ctx, db, []int64{1, 2}))
}

Never interpolate user input into SQL strings; only placeholders.
For very large IN lists, consider a temp table or unnest pattern on Postgres.
sqlc and query builders generate this boilerplate for you.

Related: GORM vs sqlx vs Raw database/sql - when helpers help

10. Pool stats and SetMaxOpenConns

Tune the pool so goroutines do not exhaust database connections.

package main
 
import (
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
 
    db.SetMaxOpenConns(10)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(0)
 
    stats := db.Stats()
    fmt.Printf("open=%d inUse=%d idle=%d\n",
        stats.OpenConnections, stats.InUse, stats.Idle)
}

MaxOpenConns should align with database max_connections divided by app replicas.
Export db.Stats() to metrics for saturation alerts.
Idle settings trade memory for reconnect latency.

Related: database/sql Connection Pooling & Timeouts - production sizing

Stack versions: This page was written for Go 1.26.x (Green Tea GC default, go fix modernizers - verify patch at build), chi (latest - verify at build), gin (latest - verify at build), echo (latest - verify at build), google.golang.org/grpc (latest - verify at build), sigs.k8s.io/controller-runtime (latest - verify at build), kubebuilder (latest - verify at build), tinygo (latest - verify board targets at build), wazero (latest - verify at build), and golangci-lint (latest - verify linter set at build).

Prerequisites

Install Go 1.26.x or later from go.dev/dl.
Create a module: mkdir dbdemo && cd dbdemo && go mod init example.com/dbdemo.
Add SQLite driver: go get github.com/mattn/go-sqlite3.
Save each snippet as main.go (or separate files in one package) and run with go run ..

Basic Examples

1. sql.Open and PingContext

Open creates a pool; Ping verifies connectivity.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
    "time"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        panic(err)
    }
    defer db.Close()
 
    ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
    defer cancel()
    fmt.Println(db.PingContext(ctx))
}

sql.Open does not guarantee a live connection yet.
Always defer db.Close() to release pool resources on shutdown.
Use PingContext at startup to fail fast on bad DSNs.

Related: Data Access in Go: database/sql as the Foundation - why drivers are separate

2. ExecContext for DDL and inserts

Exec runs statements without row iteration.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, err := db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
    if err != nil {
        panic(err)
    }
    res, err := db.ExecContext(ctx, `INSERT INTO users (name) VALUES (?)`, "Ada")
    if err != nil {
        panic(err)
    }
    id, _ := res.LastInsertId()
    fmt.Println("inserted id:", id)
}

ExecContext returns sql.Result with RowsAffected and LastInsertId when supported.
Placeholders differ by driver (? for SQLite, $1 for Postgres).
DDL in app startup is fine for demos; use migrations in production.

Related: Migrations with golang-migrate & goose - schema versioning

3. QueryRowContext and Scan

Single-row reads use QueryRow plus Scan.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, _ = db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
    _, _ = db.ExecContext(ctx, `INSERT INTO users (name) VALUES ('Grace')`)
 
    var name string
    err := db.QueryRowContext(ctx, `SELECT name FROM users WHERE id = 1`).Scan(&name)
    fmt.Println(name, err)
}

Scan destination count and types must match selected columns.
sql.ErrNoRows means zero rows, not a transport failure.
Map ErrNoRows to domain errors before HTTP handlers.

Related: Transactions, Prepared Statements & sql.Tx - multi-statement flows

4. QueryContext and rows.Next loop

Multi-row queries iterate until Next returns false.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, _ = db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
    for _, n := range []string{"Ada", "Grace", "Linus"} {
        _, _ = db.ExecContext(ctx, `INSERT INTO users (name) VALUES (?)`, n)
    }
 
    rows, err := db.QueryContext(ctx, `SELECT name FROM users ORDER BY id`)
    if err != nil {
        panic(err)
    }
    defer rows.Close()
 
    for rows.Next() {
        var name string
        if err := rows.Scan(&name); err != nil {
            panic(err)
        }
        fmt.Println(name)
    }
    fmt.Println(rows.Err())
}

Always defer rows.Close() to release the connection back to the pool.
Check rows.Err() after the loop for iteration errors.
Preallocate slices when you know approximate row counts.

Related: GORM vs sqlx vs Raw database/sql - scanning helpers

5. sql.NullString for nullable columns

Use sql.Null* types for nullable SQL fields.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, _ = db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, nickname TEXT)`)
    _, _ = db.ExecContext(ctx, `INSERT INTO users (id) VALUES (1)`)
 
    var nick sql.NullString
    _ = db.QueryRowContext(ctx, `SELECT nickname FROM users WHERE id = 1`).Scan(&nick)
    if nick.Valid {
        fmt.Println(nick.String)
    } else {
        fmt.Println("nickname is NULL")
    }
}

Valid is false when the column is SQL NULL.
Prefer pointers (*string) in domain structs when NULL is common.
ORMs may hide NULL handling; raw SQL makes it explicit.

Related: Databases & Data Access Best Practices - mapping conventions

6. Context timeout on a slow query

Deadlines cancel work when clients leave.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
    "time"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
 
    ctx, cancel := context.WithTimeout(context.Background(), 50*time.Millisecond)
    defer cancel()
 
    _, err := db.ExecContext(ctx, `SELECT 1`)
    fmt.Println(err)
}

Prefer *Context methods on every request-scoped path.
Combine context with SQL statement_timeout when drivers lag on cancel.
Return ctx.Err() when cancellation caused the failure.

Related: database/sql Connection Pooling & Timeouts - pool plus deadlines

7. Basic transaction with BeginTx

BeginTx groups statements atomically.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, _ = db.ExecContext(ctx, `CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER)`)
    _, _ = db.ExecContext(ctx, `INSERT INTO accounts (id, balance) VALUES (1, 100), (2, 0)`)
 
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        panic(err)
    }
    _, err = tx.ExecContext(ctx, `UPDATE accounts SET balance = balance - 10 WHERE id = 1`)
    if err != nil {
        _ = tx.Rollback()
        panic(err)
    }
    _, err = tx.ExecContext(ctx, `UPDATE accounts SET balance = balance + 10 WHERE id = 2`)
    if err != nil {
        _ = tx.Rollback()
        panic(err)
    }
    fmt.Println(tx.Commit())
}

Roll back on any error before commit.
Keep transactions short to reduce lock time.
Pass the same ctx into BeginTx and statements inside the tx.

Related: Transactions, Prepared Statements & sql.Tx - rollback patterns

Intermediate Examples

8. Prepared statement reuse

PrepareContext amortizes parse cost for repeated queries.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, _ = db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
 
    stmt, err := db.PrepareContext(ctx, `INSERT INTO users (name) VALUES (?)`)
    if err != nil {
        panic(err)
    }
    defer stmt.Close()
 
    for _, name := range []string{"a", "b", "c"} {
        if _, err := stmt.ExecContext(ctx, name); err != nil {
            panic(err)
        }
    }
 
    var count int
    _ = db.QueryRowContext(ctx, `SELECT COUNT(*) FROM users`).Scan(&count)
    fmt.Println(count)
}

Close statements when done; leaked stmts hold connections.
Some drivers cache prepared statements automatically; measure before manual Prepare.
Use Tx.Stmt to rebind a prepared stmt to a transaction.

Related: Transactions, Prepared Statements & sql.Tx - Stmt on Tx

9. IN clause with dynamic placeholders

Build placeholder lists safely for variable-length filters.

package main
 
import (
    "context"
    "database/sql"
    "fmt"
    "strings"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func namesByIDs(ctx context.Context, db *sql.DB, ids []int64) ([]string, error) {
    if len(ids) == 0 {
        return nil, nil
    }
    placeholders := strings.TrimSuffix(strings.Repeat("?,", len(ids)), ",")
    query := fmt.Sprintf(`SELECT name FROM users WHERE id IN (%s)`, placeholders)
    args := make([]any, len(ids))
    for i, id := range ids {
        args[i] = id
    }
    rows, err := db.QueryContext(ctx, query, args...)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var names []string
    for rows.Next() {
        var n string
        if err := rows.Scan(&n); err != nil {
            return nil, err
        }
        names = append(names, n)
    }
    return names, rows.Err()
}
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
    _, _ = db.ExecContext(ctx, `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
    _, _ = db.ExecContext(ctx, `INSERT INTO users (id, name) VALUES (1,'Ada'), (2,'Grace')`)
    fmt.Println(namesByIDs(ctx, db, []int64{1, 2}))
}

Never interpolate user input into SQL strings; only placeholders.
For very large IN lists, consider a temp table or unnest pattern on Postgres.
sqlc and query builders generate this boilerplate for you.

Related: GORM vs sqlx vs Raw database/sql - when helpers help

10. Pool stats and SetMaxOpenConns

Tune the pool so goroutines do not exhaust database connections.

package main
 
import (
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
 
    db.SetMaxOpenConns(10)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(0)
 
    stats := db.Stats()
    fmt.Printf("open=%d inUse=%d idle=%d\n",
        stats.OpenConnections, stats.InUse, stats.Idle)
}

MaxOpenConns should align with database max_connections divided by app replicas.
Export db.Stats() to metrics for saturation alerts.
Idle settings trade memory for reconnect latency.

Related: database/sql Connection Pooling & Timeouts - production sizing

Stack versions: This page was written for Go 1.26.x (Green Tea GC default, go fix modernizers - verify patch at build), chi (latest - verify at build), gin (latest - verify at build), echo (latest - verify at build), google.golang.org/grpc (latest - verify at build), sigs.k8s.io/controller-runtime (latest - verify at build), kubebuilder (latest - verify at build), tinygo (latest - verify board targets at build), wazero (latest - verify at build), and golangci-lint (latest - verify linter set at build).

Databases Basics

Search Documentation

Databases Basics