From Go to SQL Server and PostgreSQL: database/sql, go-mssqldb, Connection Pools, Transactions and sqlc

Version note: The code in this post was built and run with Go 1.27.4; the module's go line is 1.26.0 and the code also compiles with Go 1.26.11. On the database side it uses SQL Server 2025 with github.com/microsoft/go-mssqldb v1.11.2, and the PostgreSQL example uses PostgreSQL 18 with github.com/jackc/pgx/v5 v5.13.0. The sqlc (v1.31.1) examples were not run.

Guess First

In this post the Go API of Bookstore, the online bookstore used as the shared example across this site's series, writes orders to SQL Server for the first time. The CreatedAt column of the Orders table is datetime2(3); on the Go side o.CreatedAt is a time.Time filled with time.Now(). The first version of the insert looked like this:

_, err = tx.ExecContext(ctx, `
    INSERT INTO dbo.Orders (Id, CustomerId, Status, CreatedAt, UpdatedAt, PaidAt)
    VALUES (@id, @customer, @status, @created, @updated, @paid)`,
    sql.Named("id", o.ID), sql.Named("customer", o.CustomerID), sql.Named("status", int(o.Status)),
    sql.Named("created", o.CreatedAt), sql.Named("updated", o.UpdatedAt), sql.Named("paid", o.PaidAt))

The server runs in the Europe/Istanbul (UTC+3) time zone. If you place an order and read it back right away, is createdAt the same in both responses? (Below is only the createdAt field of the two responses.)

POST /api/orders         "createdAt":"2026-11-15T02:08:48.821561Z"
GET  /api/orders/ORD-1001 "createdAt":"2026-11-15T05:08:48.822Z"

They are three hours apart. go-mssqldb sends a time.Time as datetimeoffset (the driver's README says so too). When SQL Server stores that value in a datetime2 column it drops the offset and keeps the clock time: 05:08 is stored without the +03:00. On the way back the driver treats the offset-less datetime2 as UTC and returns 05:08Z. No error message, just wrong data. The fix is to convert the time with UTC() before it goes to the database; Save in the snapshot does that. The lost digits after the millisecond come from the precision of datetime2(3). In this post we look at database/sql, the connection pool, transactions, and running sqlstore, which has never run since post 04, against a real SQL Server.

database/sql, Drivers and the Connection Pool

database/sql is an abstraction in the standard library; the code that talks to SQL Server, PostgreSQL or SQLite lives in drivers. It resembles the DbConnection/DbCommand split in ADO.NET, with one difference: in Go you do not open and close connections. sql.Open returns a *sql.DB, and that is not a connection but a connection pool. To bring the driver into the program you import its package only for its side effect; its init function registers it under the name "sqlserver":

import (
    "context"
    "database/sql"
    "log"
    "net/http"
    "os"
    "time"

    _ "github.com/microsoft/go-mssqldb" // registers the "sqlserver" driver

    "bookstore/internal/catalog"
    "bookstore/internal/httpapi"
    "bookstore/internal/orders"
    "bookstore/internal/orders/memstore"
    "bookstore/internal/orders/sqlstore"
)

main uses sqlstore if the BOOKSTORE_DB environment variable is set, and the memstore from post 08 otherwise. This is the function that sets up the pool:

// openDB creates the pool once for the whole process, checks that the
// server answers and creates the tables.
func openDB(dsn string, books []catalog.Book) (*sql.DB, error) {
    db, err := sql.Open("sqlserver", dsn) // no connection yet, only the pool
    if err != nil {
        return nil, err
    }
    db.SetMaxOpenConns(20)                  // default 0: no limit
    db.SetMaxIdleConns(10)                  // default 2
    db.SetConnMaxLifetime(30 * time.Minute) // default 0: connections live forever
    db.SetConnMaxIdleTime(5 * time.Minute)  // default 0: idle connections are kept

    ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil { // the first real connection
        db.Close()
        return nil, err
    }
    if err := sqlstore.EnsureSchema(ctx, db, books); err != nil {
        db.Close()
        return nil, err
    }
    return db, nil
}

I read the defaults in the comments from database/sql/sql.go in Go 1.27.4: defaultMaxIdleConns = 2, no limit on open connections, and connections live forever. Two idle connections are too few for an API that takes hundreds of requests per second: under load the pool keeps opening new connections and closing the extra ones. Having no open-connection limit does not protect the database either; when traffic grows every goroutine can open its own connection. SetConnMaxLifetime makes old connections get replaced after a while behind a load balancer or on a SQL Server that fails over. The right numbers come from measurement; db.Stats() gives the current state of the pool (InUse, Idle, WaitCount, WaitDuration), and we will use it in the connection leak example below.

sql.Open does not connect to the database; it only validates the DSN. The first real connection is made in PingContext, so a wrong password or a server that is down shows up there and not on the first HTTP request. The connection details are not in the code; they come from an environment variable:

export BOOKSTORE_DB='sqlserver://sa:<password>@localhost:1433?database=BookstoreDb&encrypt=true&TrustServerCertificate=true&app+name=bookstore-api'
go run ./cmd/bookstore-api

2026/11/15 05:11:28 orders are stored in SQL Server
2026/11/15 05:11:28 bookstore-api listening on http://127.0.0.1:52710

On Windows the same line in PowerShell is $env:BOOKSTORE_DB = '...' (I did not run that line). TrustServerCertificate=true is only for the container on your own machine; in production the server certificate must be validated. On the .NET side of Bookstore the same role was played by the single SqlConnection factory from the Dapper series' "Async ve Connection Yönetimi" post and ADO.NET's own pool. In Go the pool is the *sql.DB itself; you open one per process and pass it everywhere.

Schema, Scanner and Valuer: From decimal to Money

For the Go API we use three tables and a sequence in BookstoreDb. The schema the .NET side creates with EF Core migrations is wider; here there is only what the order flow needs. The schema lives in an SQL file and is embedded in the binary with //go:embed (post 18 covers embed in detail):

IF OBJECT_ID(N'dbo.Books', N'U') IS NULL
CREATE TABLE dbo.Books (
    Isbn   char(13)       NOT NULL CONSTRAINT PK_Books PRIMARY KEY,
    Title  nvarchar(200)  NOT NULL,
    Author nvarchar(200)  NOT NULL,
    Price  decimal(10, 2) NOT NULL,
    Stock  int            NOT NULL CONSTRAINT CK_Books_Stock CHECK (Stock >= 0)
);

IF OBJECT_ID(N'dbo.Orders', N'U') IS NULL
CREATE TABLE dbo.Orders (
    Id         varchar(20)  NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
    CustomerId nvarchar(64) NOT NULL,
    Status     tinyint      NOT NULL,
    CreatedAt  datetime2(3) NOT NULL,
    UpdatedAt  datetime2(3) NOT NULL,
    PaidAt     datetime2(3) NULL
);

IF OBJECT_ID(N'dbo.OrderItems', N'U') IS NULL
CREATE TABLE dbo.OrderItems (
    OrderId    varchar(20)    NOT NULL CONSTRAINT FK_OrderItems_Orders REFERENCES dbo.Orders (Id),
    LineNumber int            NOT NULL,
    Isbn       char(13)       NOT NULL CONSTRAINT FK_OrderItems_Books REFERENCES dbo.Books (Isbn),
    Title      nvarchar(200)  NOT NULL,
    UnitPrice  decimal(10, 2) NOT NULL,
    Quantity   int            NOT NULL CONSTRAINT CK_OrderItems_Quantity CHECK (Quantity > 0),
    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderId, LineNumber)
);

IF OBJECT_ID(N'dbo.OrderNumbers', N'SO') IS NULL
CREATE SEQUENCE dbo.OrderNumbers AS int START WITH 1001;

I kept the price as decimal(10,2) in the database; reports and the .NET side should see prices that way. But on the Go side prices have been Money int64 in kuruş since post 03. The first attempt at Scan(&it.UnitPrice) gave this error:

sql: Scan error on column index 0, name "": converting driver.Value type []uint8 ("349.90") to a int64: invalid syntax

go-mssqldb returns decimal columns as text ([]byte("349.90")), because Go has no lossless decimal type. database/sql tries to turn that text into an integer and stops at the dot. The fix is the Go counterpart of the type handler from the Dapper series' "Type Handler'ları ve Custom Mapping" post: give the type the sql.Scanner and driver.Valuer interfaces. Both are one-method interfaces; as we saw in post 04, Money satisfies them without naming them:

// ParseMoney reads a decimal amount such as "349.90", "349.9" or "349"
// without going through float64.
func ParseMoney(s string) (Money, error) {
    whole, frac, hasDot := strings.Cut(s, ".")
    neg := strings.HasPrefix(whole, "-")
    whole = strings.TrimPrefix(whole, "-")
    if whole == "" || len(frac) > 2 || (hasDot && frac == "") || !digits(whole) || !digits(frac) {
        return 0, fmt.Errorf("%q: %w", s, ErrInvalidMoney)
    }
    n, err := strconv.ParseInt(whole+(frac + "00")[:2], 10, 64)
    if err != nil {
        return 0, fmt.Errorf("%q: %w", s, ErrInvalidMoney)
    }
    if neg {
        n = -n
    }
    return Money(n), nil
}

// Scan implements sql.Scanner. SQL Server drivers return decimal columns as
// text ("349.90"), which database/sql cannot convert to an integer type.
func (m *Money) Scan(src any) error {
    switch v := src.(type) {
    case []byte:
        return m.parseInto(string(v))
    case string:
        return m.parseInto(v)
    case int64:
        *m = Money(v * 100)
        return nil
    default:
        return fmt.Errorf("scan %T into Money: %w", src, ErrInvalidMoney)
    }
}

// Value implements driver.Valuer: the amount goes to the database as
// decimal text, so a decimal(10,2) column receives it without rounding.
func (m Money) Value() (driver.Value, error) {
    return m.Decimal(), nil
}

ParseMoney turns the text into kuruş without ever going through float64, so the 1248.6999999999998 problem from post 10 does not happen here. Value sends the parameter as the text "349.90", and SQL Server converts it to decimal(10,2) without loss. Thanks to these two methods there is no price conversion anywhere in sqlstore: we write sql.Named("price", it.UnitPrice) and move on.

Transactions: Order, Items and Stock in One Go

An order changes three things together: one row in Orders, one row per item in OrderItems, and the quantities taken from Books.Stock. If one is written and another is not, you end up either with stock taken for an order that has no record or with an order whose stock was never taken. So they all go into one transaction:

// Save inserts the order and its items and takes the quantities out of
// stock, all in one transaction: either everything is stored or nothing.
func (s *Store) Save(ctx context.Context, o *orders.Order) error {
    tx, err := s.db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin: %w", err)
    }
    defer tx.Rollback() // after Commit this returns sql.ErrTxDone and does nothing

    _, err = tx.ExecContext(ctx, `
        INSERT INTO dbo.Orders (Id, CustomerId, Status, CreatedAt, UpdatedAt, PaidAt)
        VALUES (@id, @customer, @status, @created, @updated, @paid)`,
        sql.Named("id", o.ID), sql.Named("customer", o.CustomerID), sql.Named("status", int(o.Status)),
        sql.Named("created", o.CreatedAt.UTC()), sql.Named("updated", o.UpdatedAt.UTC()), sql.Named("paid", utc(o.PaidAt)))
    if err != nil {
        return fmt.Errorf("insert order: %w", err)
    }

    // Update stock in ISBN order, so two orders for the same books always
    // lock the rows in the same order and cannot deadlock each other.
    byISBN := slices.SortedFunc(slices.Values(o.Items), func(a, b orders.Item) int {
        return cmp.Compare(a.ISBN, b.ISBN)
    })
    for _, it := range byISBN {
        res, err := tx.ExecContext(ctx, `
            UPDATE dbo.Books SET Stock = Stock - @qty
            WHERE Isbn = @isbn AND Stock >= @qty`,
            sql.Named("qty", it.Quantity), sql.Named("isbn", it.ISBN))
        if err != nil {
            return fmt.Errorf("take %s from stock: %w", it.ISBN, err)
        }
        if n, err := res.RowsAffected(); err != nil {
            return fmt.Errorf("take %s from stock: %w", it.ISBN, err)
        } else if n == 0 {
            return fmt.Errorf("isbn %s: %d requested: %w", it.ISBN, it.Quantity, orders.ErrOutOfStock)
        }
    }

    for i, it := range o.Items {
        _, err := tx.ExecContext(ctx, `
            INSERT INTO dbo.OrderItems (OrderId, LineNumber, Isbn, Title, UnitPrice, Quantity)
            VALUES (@order, @line, @isbn, @title, @price, @qty)`,
            sql.Named("order", o.ID), sql.Named("line", i+1), sql.Named("isbn", it.ISBN),
            sql.Named("title", it.Title), sql.Named("price", it.UnitPrice), sql.Named("qty", it.Quantity))
        if err != nil {
            return fmt.Errorf("insert item %d: %w", i+1, err)
        }
    }
    return tx.Commit()
}

The shape is always the same in Go: BeginTx, right after it defer tx.Rollback(), and tx.Commit() at the end. Whichever return the function leaves through, the transaction is closed; after a commit Rollback just returns sql.ErrTxDone and does nothing. If the context given to BeginTx is cancelled, for example because the client closed the connection, database/sql rolls the transaction back itself. That is why the handler passes r.Context() to the service and the service passes it to this function: the context chain from post 08 now reaches the database. The defer is the counterpart of the using var tx = connection.BeginTransaction() shape from the Dapper series' "Transaction" post.

Parameters go by name with sql.Named, as @id, @qty and so on; go-mssqldb also accepts the positional @p1, @p2 form. The UPDATE that takes stock has Stock >= @qty in its condition, and if no row is affected the function returns an error wrapping orders.ErrOutOfStock. We will discuss that separately in the next section; first let us see that the rollback works. In the database Persuasion's stock is 0 and Dune's is 10. An order with two items:

curl -i -X POST -H 'Content-Type: application/json' -H 'X-Request-ID: rollback-demo' \
  --data '{"customerId":"alan","items":[{"isbn":"9780441172719","quantity":1},{"isbn":"9780141439686","quantity":1}]}' \
  http://127.0.0.1:52710/api/orders

HTTP/1.1 409 Conflict
Content-Type: application/problem+json
X-Request-Id: rollback-demo
Content-Length: 224

{"type":"https://tools.ietf.org/html/rfc9110#section-15.5.10","title":"Conflict","status":409,"detail":"save order ORD-1043: isbn 9780141439686: 1 requested: out of stock","instance":"/api/orders","traceId":"rollback-demo"}

Isbn Stock
---- -----
9780141439686 0
9780441172719 10

Dune's stock stayed at 10, and no row was added to Orders. The next order got ORD-1044; 1043 was left unused, because in SQL Server a number taken from a sequence is not given back on rollback. Gaps in order numbers are not a problem, but if accounting expects "every number is an invoice", talk about it up front.

Here is why the sequence came in: since post 08 the order number came from a counter in Service, and the counter started at 1000 with every process. With orders now persisted in the database, the first order after a server restart would again be ORD-1001 and would fail with a primary key violation and a 500. Generating numbers is now the store's job, so I added a method to the Repository interface:

// Repository is what Service needs from storage. It is declared here, next
// to its consumer, and implemented elsewhere without naming it.
type Repository interface {
    // NextID returns a new order ID. The store owns the counter, so IDs
    // stay unique across restarts when the store is a database.
    NextID(ctx context.Context) (string, error)
    // Save stores a new order. A store that tracks stock returns an error
    // wrapping ErrOutOfStock and saves nothing if a line cannot be filled.
    Save(ctx context.Context, o *Order) error
    ByID(ctx context.Context, id string) (*Order, error)
}

// NextID takes the next number from the OrderNumbers sequence.
func (s *Store) NextID(ctx context.Context) (string, error) {
    var n int
    if err := s.db.QueryRowContext(ctx, `SELECT NEXT VALUE FOR dbo.OrderNumbers`).Scan(&n); err != nil {
        return "", fmt.Errorf("next order id: %w", err)
    }
    return fmt.Sprintf("ORD-%d", n), nil
}

memstore satisfies the same method with the old counter, and the mutex in Service is gone because it is no longer needed. After restarting the server the next order came back with Location: /api/orders/ORD-1002, and GET /api/orders/ORD-1001 returned the order placed before the restart. The "orders are lost on restart" problem from the end of post 10 is closed.

Twenty Orders at Once: Lost Updates and the Conditional UPDATE

There are 3 copies of Persuasion left, and 20 customers want one each at the same moment. labs/11-oversell runs 20 goroutines at once, and opens 20 connections first so that the requests really overlap. The first version is a common approach that reads the stock, checks it in Go and writes the new value back:

// placeNaive reads the stock, checks it in Go and writes the new value back.
// Two transactions can read the same value, and the later write wins.
func placeNaive(db *sql.DB) func(context.Context, string) error {
    return func(ctx context.Context, _ string) error {
        tx, err := db.BeginTx(ctx, nil)
        if err != nil {
            return err
        }
        defer tx.Rollback()
        var stock int
        if err := tx.QueryRowContext(ctx, `SELECT Stock FROM dbo.Books WHERE Isbn = @p1`, persuasion).Scan(&stock); err != nil {
            return err
        }
        if stock < 1 {
            return orders.ErrOutOfStock
        }
        time.Sleep(20 * time.Millisecond) // e.g. a call to the payment service
        if _, err := tx.ExecContext(ctx, `UPDATE dbo.Books SET Stock = @p1 WHERE Isbn = @p2`, stock-1, persuasion); err != nil {
            return err
        }
        return tx.Commit()
    }
}

$ go run ./labs/11-oversell naive
placed 20, out of stock 0, failed 0, stock left 2
$ go run ./labs/11-oversell
placed 3, out of stock 17, failed 0, stock left 0

The naive version took 20 orders for 3 copies, and the stock still shows 2. This is the classic lost update: under SQL Server's default isolation level, READ COMMITTED, a SELECT does not keep the row it read locked, two transactions read the same 3, and both write 2. The 20 ms time.Sleep in between widens the window; a real call to a payment service opens the same window. The sqlstore path does the check and the write in a single UPDATE ... WHERE Stock >= @qty statement. SQL Server locks the row while it updates it, the second transaction waits, and when its turn comes and the condition no longer holds, it affects 0 rows. Result: 3 orders and 17 409s.

Two notes. First, the stock check in Service.Place still looks at the in-memory catalog; in this post that check is only a quick first filter, and the real decision is made by the conditional UPDATE in the database. Once the read side of the catalog also moves to the database, the duplication goes away (the ★★★ exercise below). Second, Save takes stock in ISBN order. If one order wants Dune+Emma and another Emma+Dune and they lock the rows in different orders, they can wait for each other and deadlock; SQL Server returns error 1205 to one of them. A fixed order removes that possibility. The details of isolation levels and locks are in the SQL Server Derinlemesine series' "Isolation Level'lar ve Row Versioning" and "Deadlock'lar" posts; the theory is in the upcoming Concurrency Teorisi series.

Reading: QueryContext, rows.Close, rows.Err and NULL

The method that reads an order runs two queries: QueryRowContext for the header and QueryContext for the items:

// ByID loads an order and its items. Every call builds a new *Order, so
// two requests never share one value.
func (s *Store) ByID(ctx context.Context, id string) (*orders.Order, error) {
    o := &orders.Order{}
    err := s.db.QueryRowContext(ctx, `
        SELECT Id, CustomerId, Status, CreatedAt, UpdatedAt, PaidAt
        FROM dbo.Orders WHERE Id = @id`, sql.Named("id", id)).
        Scan(&o.ID, &o.CustomerID, &o.Status, &o.CreatedAt, &o.UpdatedAt, &o.PaidAt)
    if errors.Is(err, sql.ErrNoRows) {
        return nil, fmt.Errorf("id %s: %w", id, orders.ErrNotFound)
    }
    if err != nil {
        return nil, fmt.Errorf("load order %s: %w", id, err)
    }

    rows, err := s.db.QueryContext(ctx, `
        SELECT Isbn, Title, UnitPrice, Quantity
        FROM dbo.OrderItems WHERE OrderId = @id ORDER BY LineNumber`, sql.Named("id", id))
    if err != nil {
        return nil, fmt.Errorf("load items of %s: %w", id, err)
    }
    defer rows.Close() // gives the connection back to the pool
    for rows.Next() {
        var it orders.Item
        if err := rows.Scan(&it.ISBN, &it.Title, &it.UnitPrice, &it.Quantity); err != nil {
            return nil, fmt.Errorf("scan item of %s: %w", id, err)
        }
        o.Items = append(o.Items, it)
    }
    if err := rows.Err(); err != nil { // an error that ended the loop early
        return nil, fmt.Errorf("read items of %s: %w", id, err)
    }
    return o, nil
}

If there is no row, QueryRowContext reports it in Scan as sql.ErrNoRows; we turn that into the domain's orders.ErrNotFound, and the handler returns 404 as it has since post 05. The PaidAt column can be NULL; I made Order.PaidAt a *time.Time, and Scan(&o.PaidAt) writes nil for NULL and a new pointer for a value. Where you do not want a pointer there are sql.NullTime, sql.NullString and, since Go 1.22, the generic sql.Null[T]; the Valid field tells you whether a value came. Scanning a column that can be NULL into a plain string fails at run time.

There are two rules when working with rows. defer rows.Close() gives the connection back to the pool, and rows.Err() reports the error that ended the loop early: when rows.Next() returns false, only rows.Err() tells you whether the result ended or the connection dropped. I measured the cost of forgetting Close in a lab. The pool is limited to two connections, and the query reads only the first row and returns:

// firstTitle reads only the first row. Without rows.Close the rest of the
// result stays on the connection and the connection never goes back to the pool.
func firstTitle(ctx context.Context, db *sql.DB, closeRows bool) error {
    rows, err := db.QueryContext(ctx, `SELECT Title FROM dbo.Books ORDER BY Title`)
    if err != nil {
        return err
    }
    if closeRows {
        defer rows.Close()
    }
    if rows.Next() {
        var title string
        return rows.Scan(&title)
    }
    return rows.Err()
}

$ go run ./labs/11-rowsleak
query 1: err=<nil> after 100ms | open=1 inUse=1 idle=0 waitCount=0
query 2: err=<nil> after 100ms | open=2 inUse=2 idle=0 waitCount=0
query 3: err=context deadline exceeded after 2s | open=2 inUse=2 idle=0 waitCount=1
$ go run ./labs/11-rowsleak fixed
query 1: err=<nil> after 100ms | open=1 inUse=0 idle=1 waitCount=0
query 2: err=<nil> after 0s | open=1 inUse=0 idle=1 waitCount=0
query 3: err=<nil> after 0s | open=1 inUse=0 idle=1 waitCount=0

The two unclosed rows held both connections, and the third query waited until its timeout. The first two queries of the lab use context.Background(), like a background job. I did that on purpose: in my first attempt I gave every query a 2-second context and called cancel() right away, and the leak never showed. database/sql closes open rows itself when the context is cancelled. In HTTP handlers r.Context() is cancelled when the request ends, so the leak is limited to the length of the request; lasting leaks happen with contexts that are never cancelled. Do not rely on that anyway; write defer rows.Close() right under every QueryContext.

Parameterized Queries and SQL Injection

Using parameters is not only about readability. labs/11-injection runs the same search in two ways:

func main() {
    db, err := sql.Open("sqlserver", os.Getenv("BOOKSTORE_DB"))
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    ctx := context.Background()

    input := "x' OR 1=1 --" // what an attacker types into the search box

    unsafe := "SELECT Title FROM dbo.Books WHERE Title LIKE '%" + input + "%'"
    fmt.Println("concatenated:", titles(ctx, db, unsafe))

    safe := "SELECT Title FROM dbo.Books WHERE Title LIKE '%' + @q + '%'"
    fmt.Println("parameter:   ", titles(ctx, db, safe, sql.Named("q", input)))
}

concatenated: [Middlemarch Emma Sense and Sensibility Persuasion Dune The Hobbit]
parameter:    []

In the concatenated query the quote the user typed closes the string, OR 1=1 selects every row, and -- turns the rest into a comment; the whole catalog comes back. In the parameterized query the same text is just a value: no book has x' OR 1=1 -- in its title. go-mssqldb sends parameters separately through sp_executesql, and the SQL text never changes. The rule is the same as in the Dapper series' "Parameter'lar ve SQL Injection" post. The Go-specific trap is fmt.Sprintf: writing "... WHERE Title = '%s'" is easy, and go vet does not catch it. If parts that cannot be parameters, such as table or column names, are dynamic, pick them from a fixed allow list.

PostgreSQL, pgx and sqlc

For PostgreSQL the de facto standard in the Go world is jackc/pgx. It can be used in two ways: as a database/sql driver through pgx/v5/stdlib, or through its own interface. pgx's documentation recommends the pgx interface directly when the application only targets PostgreSQL; it has better support for COPY, LISTEN/NOTIFY and PostgreSQL types. The same stock update with pgx looks like this (the lab ran against my own PostgreSQL 18 container):

for i := 1; i <= 4; i++ {
    var left int
    // $1, $2 instead of @name; RETURNING gives the new value in the same round trip.
    err := pool.QueryRow(ctx, `
        UPDATE books SET stock = stock - $1
        WHERE isbn = $2 AND stock >= $1
        RETURNING stock`, 1, "9780141439686").Scan(&left)
    if errors.Is(err, pgx.ErrNoRows) {
        fmt.Printf("order %d: out of stock\n", i)
        continue
    }
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("order %d: placed, %d left\n", i, left)
}

rows, _ := pool.Query(ctx, `SELECT isbn, title, stock FROM books`)
books, err := pgx.CollectRows(rows, pgx.RowToStructByPos[book]) // closes rows for us
if err != nil {
    log.Fatal(err)
}
fmt.Printf("%+v\n", books)

order 1: placed, 2 left
order 2: placed, 1 left
order 3: placed, 0 left
order 4: out of stock
[{ISBN:9780141439686 Title:Persuasion Stock:0}]

The differences are small but they matter: parameters are $1, $2 instead of @name; RETURNING brings the updated value back in the same round trip; *pgxpool.Pool takes the place of *sql.DB. pgx.CollectRows and RowToStructByPos are generic functions; they turn rows into structs and close rows themselves. PostgreSQL itself is covered in the site's PostgreSQL series.

For someone used to Dapper, the closest Go experience is sqlc: you write the SQL, mark it with a comment such as -- name: GetOrder :one, and sqlc generate reads the schema and the query and generates type-safe Go functions and structs. A wrong column name shows up before compilation, not at run time as in Dapper. But there is a limit: the engines of the latest release, v1.31.1, are PostgreSQL, MySQL and SQLite; there is no SQL Server in the internal/engine folder of its source. The development branch has an mssql engine and the current documentation page lists SQL Server as beta, but it has not shipped in a release yet (November 2026). Since Bookstore uses SQL Server, I did not run sqlc in this post; on SQL Server today the options are hand-written database/sql or sqlx, which maps columns to struct fields by name.

There are also ORMs like GORM, and in Go they are the closest thing to EF Core. The Go community keeps its distance from them for a reason tied to the language: there is no query language the compiler understands, like LINQ, and no expression trees, so ORM queries rely on strings and reflection. Errors show up at run time instead of compile time. The reasons for moving to Dapper from the EF Core series' "EF Core ve Mimari" post become the default choice in Go.

Production Notes

EnsureSchema is enough for teaching, but changing the schema at application start-up is risky in production: if two instances start at the same time, both run the same DDL. In Go, migration tools such as golang-migrate and goose are common; if the .NET side uses EF Core migrations or DbUp, the Go service should not touch the schema, and the schema should have a single owner.

Think of pool sizes in total, not per instance. Scale a service with SetMaxOpenConns(20) to 10 replicas in Kubernetes and the database sees 200 connections. SQL Server's limit is generous, but services like Azure SQL have a limit that depends on the tier. Exporting WaitCount and WaitDuration from db.Stats() as metrics (in post 13 and in the Observability series) is the easiest way to tell whether a request is waiting for the database or for the pool.

The context sets the timeout. The handler's context is cancelled when the request ends, but a query has no limit of its own; a slow query can hold a connection until the WriteTimeout from post 09. Give critical queries their own limit with context.WithTimeout. The timings in this post come from a container running under emulation; they are for comparison, not absolute speed.

For transient errors such as a deadlock (1205) or a dropped connection the transaction has to be run again from the start. Since Save is a single function, putting the retry around it is easy; you can read the Number field of go-mssqldb's mssql.Error type with errors.As. So that the same order is not stored twice, retry only when you know the error happened before the commit.

Common Pitfalls

sql.Open on every request. The pool is built from scratch each time, the TLS handshake and login are repeated on every request, and if Close is forgotten the connections pile up. Open the *sql.DB once in main and pass it on.

Forgetting rows.Close or rows.Err. The first leaks connections; the second makes you take a partial result for a full one.

Reading, checking in Go, then writing. That is the lost update we saw in the lab; combine the check and the write in one conditional UPDATE, or lock the row.

Leaving the time zone to the driver. Convert a time.Time to UTC() before writing it to datetime2, or make the column datetimeoffset.

Using db instead of tx inside a transaction. s.db.ExecContext takes another connection from the pool and runs outside the transaction; a rollback does not undo it.

A Small Experiment

★☆☆ Run the API with BOOKSTORE_DB pointing at your own SQL Server, place an order and restart the server. Does GET /api/orders/{id} still return the order? Try the same without BOOKSTORE_DB.

★★☆ In sqlstore.Save, run the stock UPDATE with s.db.ExecContext instead of tx.ExecContext and run the rollback example again. What happened to Dune's stock? Then undo the change.

★★★ Add a context to the orders.Catalog interface (ByISBN(ctx, isbn)) and write a sqlstore.Catalog that reads books from the Books table. Use it in main when there is a database. Does GET /api/books now show the right stock?

We ran everything in this post by hand and looked at its output; we do not want to do that again after the next change. The next post brings Go's testing package: a table-driven test for the stock rule, a fake Repository and a fixed clock through Service.now, handler tests with httptest, the generic-code benchmarks promised in post 06, fuzzing, more of the synctest from post 08, and go test -race ./... in CI.