This resume is also available in English.

2. Writing a service3 / 4 · All columns

Columns › 2. Writing a service › Part 7

database/sql, Transactions and Connection Pools in Go

database/sql、交易與連線池

Write a database-backed repository for the order service: understand that sql.DB is a connection pool, close rows correctly, handle NULL and ErrNoRows, use transactions so stock and orders change together, and tune the pool to avoid overselling and exhaustion.

  • Go
  • Database
  • database/sql
  • Transactions
  • Connection Pool
In this article
  1. What this article solves
  2. Step 1: sql.DB is not a connection, it is a connection pool
  3. Step 2: Create the tables and read data correctly
  4. Step 3: Pass a context with every call
  5. Step 4: Tune the connection pool
  6. Step 5: Use a transaction so several writes succeed or fail together
  7. Step 6: Correctness under concurrency: overselling, locks and isolation levels
  8. Step 7: Plug it back into the service
  9. Common pitfalls
  10. Key takeaways
  11. Next

In the previous article, “Layered Architecture and Dependency Injection in Go”, we split the order service into handler, service and repository layers and made the service depend only on the Repository interface it defines itself. This article writes the database implementation of that interface so that orders are actually persisted.

What this article solves

The in-memory repository has two fatal problems: the data disappears when the service restarts, and multiple service instances cannot share data. Putting the data into a relational database is the natural next step, and Go's standard library database/sql provides a database-agnostic interface that you use together with a driver for each database.

The database/sql API is small, yet it has quite a few places that "seem to work, then blow up in production":

  • Assuming sql.Open connects, and only discovering a misconfiguration on the first request.
  • Forgetting to close rows, so connections are held one by one until the whole service hangs waiting for a connection.
  • Not capping the connection pool, so heavy traffic exhausts the database's connection limit.
  • Deducting stock and creating the order as two independent statements, so a failure in between leaves stock deducted with no order.
  • Reading stock and then writing it back, so two simultaneous requests sell goods that don't exist.

This article tackles these problems one step at a time. The example SQL uses PostgreSQL syntax (placeholders are $1, $2); for MySQL change the placeholders to ?, and every other concept is exactly the same. Since the focus is database/sql itself, none of the code is tied to a specific driver.

Step 1: sql.DB is not a connection, it is a connection pool

The name of the *sql.DB returned by sql.Open(driverName, dsn) is easy to misread. It is not a database connection but a connection pool managed by database/sql: it may hold zero or more physical connections, and each query borrows one from the pool and returns it afterwards. This has a few direct consequences:

  1. sql.Open does not actually connect. It only validates its arguments and creates the pool; the first connection is made on the first query. So at startup you should call PingContext to confirm the database is reachable, making configuration errors fail during startup.
  2. The whole program needs only one *sql.DB. It is safe for concurrent use by many goroutines, and should be created once in the composition root and injected into every repository. Calling sql.Open per request means creating a new pool each time, which is not only wasteful but makes the connection count spiral out of control.
  3. Don't call db.Close() casually. It closes the whole pool; call it once, when the program exits.

Drivers register themselves with database/sql through a blank import; for example, github.com/jackc/pgx/v5/stdlib, commonly used for PostgreSQL, registers a driver named pgx. This import should appear only in the composition root (cmd/order/main.go) or in a platform package dedicated to the database; business code depends only on database/sql types.

Here is a helper that opens the database, placed in a shared platform package:

// Snippet: internal/platform/database/database.go
package database

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

func OpenDB(ctx context.Context, driver, dsn string) (*sql.DB, error) {
	db, err := sql.Open(driver, dsn) // only validates arguments; no real connection yet
	if err != nil {
		return nil, fmt.Errorf("open db: %w", err)
	}

	db.SetMaxOpenConns(20)                  // at most 20 connections at once (in use plus idle)
	db.SetMaxIdleConns(10)                  // keep at most 10 idle connections in reserve
	db.SetConnMaxLifetime(30 * time.Minute) // maximum connection lifetime, matching load balancer and database settings
	db.SetConnMaxIdleTime(5 * time.Minute)  // close connections idle too long, giving resources back off-peak

	pingCtx, cancel := context.WithTimeout(ctx, 3*time.Second)
	defer cancel()
	if err := db.PingContext(pingCtx); err != nil {
		db.Close()
		return nil, fmt.Errorf("ping db: %w", err)
	}
	return db, nil
}

The four pool settings are explained in detail in Step 4. For now, remember one thing: the defaults are not fit for production. MaxOpenConns defaults to unlimited, MaxIdleConns defaults to just 2, and connections have no maximum lifetime. The numbers in the code are only illustrative; the real values depend on your database's specs and traffic.

Step 2: Create the tables and read data correctly

The order service needs three tables. The products table stores unit price and stock, the orders table stores the user, total and status, and the order items table records what each order bought and at what unit price:

CREATE TABLE products (
    id    BIGSERIAL PRIMARY KEY,
    name  TEXT   NOT NULL,
    price BIGINT NOT NULL,                      -- unit: cents
    stock INT    NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    total      BIGINT NOT NULL,
    status     TEXT   NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending', 'paid', 'cancelled')),
    paid_at    TIMESTAMPTZ,                     -- NULL until paid
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX orders_user_id_idx ON orders (user_id, id DESC);

CREATE TABLE order_items (
    order_id   BIGINT NOT NULL REFERENCES orders (id),
    product_id BIGINT NOT NULL REFERENCES products (id),
    qty        INT    NOT NULL CHECK (qty > 0),
    price      BIGINT NOT NULL,                 -- unit price at the time of ordering
    PRIMARY KEY (order_id, product_id)
);

A few design choices are worth noting: amounts are always integer "cents"; the CHECK constraint stock >= 0 is the last line of defence, so even if the code has a bug the database will never let stock go negative; the order status is likewise restricted by CHECK to pending, paid and cancelled; and the items table records the unit price separately, because a product's price may change later but the amount on an order must not change with it.

Next come the repository's read methods:

// Snippet: internal/order/repository.go (reads)
package order

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

// To focus on database/sql, this Order keeps only the columns of the orders table;
// in reality it is the Order from order.go (items are queried from order_items separately), where ErrNotFound is also defined.
type Order struct {
	ID        int64
	UserID    int64
	Total     int64               // unit: cents
	Status    string              // pending / paid / cancelled
	PaidAt    sql.Null[time.Time] // NULL until paid
	CreatedAt time.Time
}

type OrderRepo struct{ db *sql.DB }

func NewOrderRepo(db *sql.DB) *OrderRepo { return &OrderRepo{db: db} }

func (r *OrderRepo) Get(ctx context.Context, id int64) (Order, error) {
	var o Order
	err := r.db.QueryRowContext(ctx,
		`SELECT id, user_id, total, status, paid_at, created_at FROM orders WHERE id = $1`, id,
	).Scan(&o.ID, &o.UserID, &o.Total, &o.Status, &o.PaidAt, &o.CreatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return Order{}, ErrNotFound
	}
	if err != nil {
		return Order{}, fmt.Errorf("get order %d: %w", id, err)
	}
	return o, nil
}

func (r *OrderRepo) ListByUser(ctx context.Context, userID int64, limit int) ([]Order, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT id, user_id, total, status, paid_at, created_at
		   FROM orders WHERE user_id = $1
		  ORDER BY id DESC LIMIT $2`, userID, limit)
	if err != nil {
		return nil, fmt.Errorf("list orders: %w", err)
	}
	defer rows.Close() // must be closed, or this connection never returns to the pool

	var out []Order
	for rows.Next() {
		var o Order
		if err := rows.Scan(&o.ID, &o.UserID, &o.Total, &o.Status, &o.PaidAt, &o.CreatedAt); err != nil {
			return nil, fmt.Errorf("scan order: %w", err)
		}
		out = append(out, o)
	}
	if err := rows.Err(); err != nil { // errors during iteration (e.g. a dropped connection) only surface here
		return nil, fmt.Errorf("iterate orders: %w", err)
	}
	return out, nil
}

Single-row queries: QueryRowContext

The *sql.Row returned by QueryRowContext defers any error until Scan. When no row is found, Scan returns sql.ErrNoRows. That is not "something went wrong" but "there is no such record", so the repository translates it into the domain's ErrNotFound, which lets the upper layers respond with 404. This is exactly the "repository translates low-level errors" rule from the previous article: sql.ErrNoRows must never leak out of this layer. Other errors are wrapped with context (which record was being queried) using %w and returned upwards.

*sql.Row releases its connection automatically after Scan; no extra close is needed.

Multi-row queries: three rules for rows

QueryContext returns *sql.Rows, which holds a connection for the entire iteration. There are three rules for using it, and you need all of them:

  1. defer rows.Close() as soon as you get rows. Without it, the connection never returns to the pool. This is one of the most common reasons a Go service "hangs after running for a while". rows.Next() closes automatically when it reaches the end, but any early return (for example a Scan error) leaks the connection without the defer.
  2. Check rows.Err() after the loop. rows.Next() returning false has two possible meanings: all data has been read, or an error occurred along the way (such as a dropped connection). Only rows.Err() can tell them apart; without checking it you may return "half-read data" as a complete result.
  3. Don't do slow work during iteration. Calling an external API or doing heavy computation inside the for rows.Next() loop keeps the connection busy for a long time. Read the data into a slice first, and process it after the rows are closed.

Handling NULL

A database NULL cannot be Scanned directly into a Go time.Time or string; you get a conversion error. The standard library provides types like sql.NullString and sql.NullTime, and since Go 1.22 there is also the generic sql.Null[T]; the example's PaidAt sql.Null[time.Time] uses it to express "NULL until paid". After reading, check the Valid field to see whether there is a value. Another approach is scanning into a pointer (*time.Time), where nil means NULL. If a column should never be NULL in business terms, add NOT NULL to the table instead of handling empty values all over the code.

Always use placeholders

Pass every parameter through $1, $2 placeholders and let the driver handle it; never use fmt.Sprintf to splice user input into an SQL string, which is a direct source of SQL injection. The only exception is parts that placeholders cannot express, such as column names or sort direction; for those, check against an allowlist that only permits a few values listed in advance.

Step 3: Pass a context with every call

Every database/sql operation has a version that takes a context: QueryContext, QueryRowContext, ExecContext, BeginTx, PingContext. The older versions without a context (Query, Exec) use context.Background() internally and cannot be cancelled; in a service they should be avoided entirely.

Passing a context gives you exactly the cancellation chain described in “Go context: Cancellation, Timeouts and Request-Scoped Values”: when the user closes the page or the HTTP connection drops, the handler's r.Context() is cancelled, the signal travels through the service to the repository, the query waiting on the database is aborted, and the connection is released (the driver does its best to tell the database to cancel the query). Without this chain, a slow query nobody is waiting for any more keeps holding a connection and database resources.

Besides the cancellation inherited from the request, you can also set stricter timeouts for database operations. For example, use context.WithTimeout in a repository method to limit a single query's duration, so one query missing an index cannot drag down the whole service. The timeout should be shorter than the overall deadline from upstream; how the timeouts of different layers fit together and when to retry is covered fully in “Timeouts, Retries and Circuit Breakers”.

Tip: When the context is cancelled, Scan or rows.Err() returns context.Canceled or context.DeadlineExceeded (possibly wrapped by the driver). Check with errors.Is(err, context.Canceled); such errors usually mean the client has already left and need not trigger alerts as server errors.

Step 4: Tune the connection pool

Every connection in the pool moves between a few states:

New connection In use InUse Requests queue MaxOpenConns reached Idle Idle Closed opened if under limit taken once one returns Returned by rows.Close, Commit, Rollback Next query reuses it directly Watch with db.Stats(): InUse, Idle, WaitCount, WaitDuration A connection is closed when idle longer than ConnMaxIdleTime, when the idle count exceeds MaxIdleConns, or when it has been in use for ConnMaxLifetime.

Figure: Connections move back and forth between in use and idle; when the limit is reached, new requests can only queue, and connections idle too long or past their lifetime are closed.

SettingEffectDefaultConsiderations
SetMaxOpenConnsMaximum number of connections at once (in use + idle)UnlimitedThe total across all service instances must not exceed what the database allows
SetMaxIdleConnsHow many idle connections to keep in reserve2Too small means constant opening and closing; usually equal to or slightly below MaxOpen
SetConnMaxLifetimeHow long a single connection may be usedUnlimitedRotates connections regularly, matching database or load balancer timeouts and failover
SetConnMaxIdleTimeHow long a connection may sit idle before being closedUnlimitedGives connections back to the database off-peak, reducing resource usage

Choosing MaxOpenConns

Bigger is not better here. The number of queries a database can process effectively at once is limited by its CPU cores and disks; opening far more connections than that just makes queries fight over resources inside the database, and everything gets slower. A practical way to think about it:

  • First find the database's connection limit; say it allows 200 connections, and keep some in reserve for admin tools and migrations.
  • Then work out how many instances the service will have, say 4; the limit per instance is then roughly (200 − reserve) ÷ 4.
  • With autoscaling the instance count grows, so use the maximum number of instances in the calculation.

These numbers are only illustrative. The real answer comes from measurement: watch the sql.DBStats returned by db.Stats().

MetricMeaningWhat an anomaly indicates
OpenConnectionsCurrent total number of connectionsConstantly at the limit means the pool is too small or connections are not being returned
InUse / IdleNumber in use and idleInUse stays high while traffic is low: suspect unclosed rows
WaitCount / WaitDurationNumber of waits and total wait time due to a full poolSteady growth means requests are queuing for connections
MaxIdleClosed / MaxLifetimeClosedConnections closed due to the idle limit or lifetimeA high MaxIdleClosed means MaxIdleConns is too small and connections are constantly rebuilt

Exporting these numbers regularly as metrics is something “Observability in Go: Logs, Metrics and Tracing” will do. For now, know this: when the service slows down, "waiting for the database to execute" and "waiting for the pool to hand out a connection" are two completely different problems, and WaitDuration helps you tell them apart.

Step 5: Use a transaction so several writes succeed or fail together

Placing an order involves three writes: deducting product stock, inserting the order, and inserting the order items. If the program fails after deducting stock but before writing the order, the data is left in a state of "stock reduced, but no order exists". A transaction makes this group of operations atomic: either all of them take effect, or none of them count.

The whole transaction runs on one connection (*sql.Tx), returned only at Commit or Rollback BeginTx takes a connection Deduct stock UPDATE products Insert order INSERT orders Insert items INSERT items Commit all take effect any step fails Rollback: every change is undone together No stock deducted, no half-written order When the context passed to BeginTx is cancelled, database/sql also rolls this transaction back automatically.

Figure: A transaction holds one connection from BeginTx onwards, all statements run on that connection, Commit makes them take effect at the end, and an error at any step means Rollback undoes everything.

db.BeginTx(ctx, opts) returns a *sql.Tx. Its most important property is that it is bound to one connection: every statement in the transaction must run through tx.ExecContext, tx.QueryRowContext and so on to land on the same connection, in the same transaction. If you accidentally use db.ExecContext partway through a transaction, that statement borrows another connection from the pool, is not inside the transaction, and is not undone when the transaction rolls back.

The standard routine for using a transaction correctly is:

  1. Arrange the Rollback immediately after BeginTx, so every early-return path ends the transaction and returns the connection.
  2. Run every statement with tx.
  3. Commit only when everything succeeded, and check Commit's error. Commit can fail too (for example a deferred constraint violation or a dropped connection), and when it does the database has already discarded the transaction.

Calling Rollback after a successful Commit is safe: it just returns sql.ErrTxDone and nothing happens. That is why a lot of code simply writes defer tx.Rollback(). Below, this routine is wrapped in a WithTx helper, which is then used to implement placing an order:

// Snippet: internal/order/repository.go (transactions)
package order

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

var ErrOutOfStock = errors.New("out of stock")

// WithTx runs fn in a transaction; if fn returns an error it rolls back, otherwise it commits.
func WithTx(ctx context.Context, db *sql.DB, opts *sql.TxOptions, fn func(*sql.Tx) error) (err error) {
	tx, err := db.BeginTx(ctx, opts)
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			panic(p)
		}
		if err != nil {
			if rbErr := tx.Rollback(); rbErr != nil && !errors.Is(rbErr, sql.ErrTxDone) {
				err = errors.Join(err, fmt.Errorf("rollback: %w", rbErr))
			}
		}
	}()

	if err = fn(tx); err != nil {
		return err
	}
	if err = tx.Commit(); err != nil {
		return fmt.Errorf("commit: %w", err)
	}
	return nil
}

func (r *OrderRepo) PlaceOrder(ctx context.Context, userID int64, items []Item) (int64, error) {
	var orderID int64
	err := WithTx(ctx, r.db, nil, func(tx *sql.Tx) error {
		var total int64
		for i, it := range items {
			// Conditional stock deduction: updates no row when stock is insufficient, and takes the unit price from the database
			err := tx.QueryRowContext(ctx,
				`UPDATE products SET stock = stock - $1
				  WHERE id = $2 AND stock >= $1
				RETURNING price`,
				it.Qty, it.ProductID).Scan(&items[i].Price)
			if errors.Is(err, sql.ErrNoRows) {
				return fmt.Errorf("product %d: %w", it.ProductID, ErrOutOfStock)
			}
			if err != nil {
				return fmt.Errorf("reserve product %d: %w", it.ProductID, err)
			}
			total += items[i].Price * int64(it.Qty)
		}

		if err := tx.QueryRowContext(ctx,
			`INSERT INTO orders (user_id, total, status) VALUES ($1, $2, 'pending') RETURNING id`,
			userID, total).Scan(&orderID); err != nil {
			return fmt.Errorf("insert order: %w", err)
		}

		for _, it := range items {
			if _, err := tx.ExecContext(ctx,
				`INSERT INTO order_items (order_id, product_id, qty, price) VALUES ($1, $2, $3, $4)`,
				orderID, it.ProductID, it.Qty, it.Price); err != nil {
				return fmt.Errorf("insert item: %w", err)
			}
		}
		return nil
	})
	if err != nil {
		return 0, err
	}
	return orderID, nil
}

Key points of this code

  • WithTx keeps the transaction's lifecycle in one place. Callers only write "what to do inside the transaction" instead of rewriting the Begin, Rollback and Commit boilerplate every time. If fn returns an error, it rolls back; if fn panics, it rolls back first and then re-panics; if Rollback itself fails, errors.Join returns both errors together, so neither is swallowed.
  • The transaction rolls back automatically when the context is cancelled. database/sql watches the context passed to BeginTx; once it is cancelled, it rolls back and releases the connection. This is an extra safety net, not a reason to skip Rollback.
  • The unit price comes from the database. The stock deduction's UPDATE ... RETURNING price fetches the unit price at the same time and writes it back into items; the total is calculated by the server from the database prices, never trusting an amount sent by the client.
  • Errors are clearly classified. Insufficient stock returns the domain error ErrOutOfStock, which the service and handler can check with errors.Is and answer with a status such as 409; other errors are wrapped with context and passed up.

Keep transactions short

From Begin to Commit, a transaction holds a connection, and the rows it has updated are locked, so other transactions wanting to update the same rows must wait. So inside a transaction, do only database work: don't call external HTTP APIs, send messages or wait for user input. For example, "call the payment service after creating the order" should not be in the same transaction: if the payment service is slow by a few seconds, every order request for the same product is stuck on the row lock in the meantime. Consistency across services needs other techniques, which “Distributed Transactions: Sagas and the Outbox Pattern” discusses in depth.

Step 6: Correctness under concurrency: overselling, locks and isolation levels

A transaction guarantees atomicity, but it does not automatically guarantee correct results "when several transactions run at the same time". Overselling is the classic example.

Read then write: both succeed Conditional update: database guards Transaction A Transaction B Transaction A Transaction B Reads stock 1 Reads stock 1 Writes back stock 0 Also writes back 0 Conditional deduct OK Waits for A's row lock Commit Affects 0 rows Only 1 in stock, yet two orders succeed (later write wins: a lost update) stock >= qty is checked in the database: one succeeds, the other gets ErrOutOfStock

Figure: On the left, both transactions read a stock of 1 and each write back 0, so both orders go through; on the right, the check is left to the database, and the second update affects 0 rows because its condition no longer holds.

The intuitive approach is: SELECT stock first, check in Go whether there is enough, then UPDATE products SET stock = <new value>. At the default isolation level, two transactions may read the same stock at the same time, each calculate a new value and write it back, with the later write overwriting the earlier one. This is called a lost update.

There are three common fixes:

  1. Conditional update (used in this article): UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1. The check and the change happen in a single statement, completed atomically by the database; the second transaction waits for the first to release the row lock, then re-checks the condition and affects 0 rows if it no longer holds. This is the simplest approach and also performs best. With ExecContext, check whether RowsAffected() is 0; with RETURNING and QueryRowContext, you get sql.ErrNoRows.
  2. Pessimistic locking: SELECT stock FROM products WHERE id = $1 FOR UPDATE locks the row inside the transaction first, and other transactions that want to read and lock the same row must wait. Suitable when the check is too complex to express in a single UPDATE condition.
  3. A higher isolation level: pass &sql.TxOptions{Isolation: sql.LevelSerializable} to BeginTx, letting the database detect conflicts and fail one of the transactions. The program must be ready to retry rejected transactions.
Isolation levelPreventsCost
Read Committed (PostgreSQL default)Reading data other transactions have not committedTwo reads in one transaction may differ; updates can be lost
Repeatable Read (MySQL InnoDB default)The above, plus repeated reads in one transaction stay the sameImplementation details differ between databases; conflicts may need retries
SerializableResults equivalent to running one after anotherConflicting transactions fail and must be retried; lower throughput

Deadlocks

When two transactions lock the same rows in different orders, they may each wait for the other to release, forming a deadlock. The database detects it and forces one of them to fail. In the ordering example, if one order deducts product 1 then product 2 while another does the opposite, a deadlock can occur. A simple prevention is a fixed locking order: sort the items by ProductID before the transaction starts. Even with prevention, when you hit a deadlock or a serialisation failure, retrying the whole transaction once usually succeeds; but the retry must re-run the entire transaction, not just the failed statement.

Overselling is one of the most common concurrency problems in e-commerce systems. Once stock moves into a separate inventory service, or is deducted in a cache instead, more variants appear; “Concurrency Pitfalls: From Data Races to Overselling” collects the full set of scenarios and remedies.

Step 7: Plug it back into the service

Once the database repository satisfies the Repository interface defined by the service, plugging it in only requires changing the composition root: in cmd/order/main.go, open the connection pool with database.OpenDB, defer db.Close(), and replace order.NewMemoryRepository() with the database version built from db. Not a single line of the service or handler changes; that is the payoff for the effort of layering in the previous article.

A few more tasks come along with the database:

  • Schema migrations: changes to table structure should be written as versioned migration files, kept in version control with the code, and run in order by the deployment pipeline or a dedicated tool, not by typing commands into the database by hand.
  • Which layer owns the transaction boundary: this article puts the transaction inside the repository's PlaceOrder method, because "deduct stock and create the order" is an indivisible storage operation for this service. If the service needs to put operations from several repositories into one transaction, the repository methods can accept a small interface satisfied by both *sql.DB and *sql.Tx (both have ExecContext, QueryContext, QueryRowContext), with the service deciding the scope via WithTx; just be careful not to let database/sql types spread into the business rules.
  • Startup order: when the database is unreachable, the failed PingContext makes the program exit, leaving the deployment platform to restart it; the health check endpoint can also report database status, which is covered in more detail when “Deploying One Go Service with Docker and Kubernetes” discusses probes.

Common pitfalls

  • Forgetting defer rows.Close(). Connections cannot be returned, and after a while every request is stuck waiting for a connection, with db.Stats().InUse constantly at the limit.
  • Not checking rows.Err(). An error during iteration is mistaken for "all data read", and an incomplete result is returned.
  • Treating sql.ErrNoRows as a system error. No data should be translated into the domain's ErrNotFound and answered with 404, not 500 plus an alert.
  • Using db instead of tx inside a transaction. That statement runs on another connection, outside the transaction; with a very small pool limit it can even hang forever, because the transaction holds the only connection while db waits for one.
  • Forgetting to roll back. An error path returns early, the transaction never ends, and the connection and row locks stay held. Handle it with defer right after BeginTx.
  • Using Query and Exec without a context. The query keeps running after the client has left, and no timeout can be set.
  • Building SQL with fmt.Sprintf. A direct source of SQL injection; always pass parameters through placeholders.
  • Going live with the default pool settings. MaxOpenConns is unlimited, so a traffic spike exhausts the database's connections and affects every service sharing that database.
  • Calling sql.Open per request. A program needs only one *sql.DB, created in the composition root and injected.
  • Deducting stock by reading then writing. Under concurrency, updates are lost and you oversell; use a conditional update or SELECT ... FOR UPDATE instead.
  • Calling external services inside a transaction. The transaction gets longer, row locks are held for a long time, and the whole order path slows down with it.

Key takeaways

  • *sql.DB is a connection pool, not a connection; sql.Open does not connect, so confirm with PingContext at startup; create just one for the whole program and inject it into each repository.
  • Use QueryRowContext for single rows and translate sql.ErrNoRows into a domain error; use QueryContext for multiple rows, defer rows.Close() immediately, and check rows.Err() after the loop.
  • Handle NULL with sql.Null[T] or pointers; always pass parameters through placeholders, never splice strings.
  • Use the context-aware version of every database call, so cancellation and timeouts take effect along the call chain.
  • In production always set MaxOpenConns, MaxIdleConns, ConnMaxLifetime and ConnMaxIdleTime, and watch wait counts and durations with db.Stats().
  • Start transactions with BeginTx; they are bound to one connection, and every statement must run through tx; handle Rollback with defer, and Commit only on success while checking its error.
  • Keep transactions short: only database work, no calls to external services.
  • Prevent overselling with conditional updates, SELECT ... FOR UPDATE or a higher isolation level; use a fixed locking order to reduce deadlocks, and retry the whole transaction on conflict.

Next

The order service now has an HTTP entry point, a layered structure and a database, but we still have no way to confirm it is actually correct, nor to refactor with confidence. “Testing in Go: Table-Driven Tests, httptest and Integration Tests” writes tests for each layer, including httptest for testing handlers without starting a server, and integration tests against a real database.