Irbisa · Cheatsheet
Databases & SQL in Go
database/sql pooling, transactions, NULLs, pgx, migrations
What is the difference between
WHEREandHAVING, and betweenINNER JOINandLEFT JOIN?-- customers with more than 5 paid orders this yearSELECT c.id, c.email, count(*) AS paid_ordersFROM customers cJOIN orders o ON o.customer_id = c.id -- INNER: customers with no orders drop outWHERE o.status = 'paid' -- row filter, runs before GROUP BY AND o.created_at >= '2026-01-01'…What does
sql.Openactually do, and why does it return no error when the database is unreachable?func openDB(ctx context.Context, dsn string) (*sql.DB, error) { // No network traffic here. An error means an unknown driver or a // malformed DSN, never "the database is down". db, err := sql.Open("pgx", dsn) if err != nil { return nil, fmt.Errorf("open: %w", err)…When do you use
Exec,QueryandQueryRow, and what breaks if you run aDELETEthroughQuery?// wrong: Query on a write. The rows are never closed, so the connection never// returns to the pool. The DELETE itself succeeds, which hides the leak.rows, err := db.QueryContext(ctx, "DELETE FROM sessions WHERE expires_at < now()")if err != nil { return err}…Why does a missing row arrive as
sql.ErrNoRowsfromScanrather than fromQueryRowitself, and where should that error be handled?var ErrUserNotFound = errors.New("user not found") func (r *Repo) User(ctx context.Context, id int64) (User, error) { var u User err := r.db.QueryRowContext(ctx, "SELECT id, email, created_at FROM users WHERE id = $1", id,…A column can be NULL. What can you scan it into, how do you write NULL back, and what does
COALESCEchange?func profile(ctx context.Context, db *sql.DB, id int64) error { // wrong: var nickname string // sql: Scan error on column index 0, name "nickname": // converting NULL to string is unsupported var nickname *string // nil means NULL; marshals to JSON null…This export sometimes writes fewer rows than the table holds, and no error is ever logged. What is missing?
func exportUsers(ctx context.Context, db *sql.DB, w io.Writer) error { rows, err := db.QueryContext(ctx, "SELECT id, email FROM users ORDER BY id") if err != nil { return err } defer rows.Close() for rows.Next() { var id int64 var email string if err := rows.Scan(&id, &email); err != nil { return err } fmt.Fprintf(w, "%d,%s\n", id, email) } return nil}What does ACID mean, and which anomalies do Read Committed, Repeatable Read and Serializable each allow?
What index types does PostgreSQL offer, when does a composite index help a query, and how do you read
EXPLAIN ANALYZE?CREATE INDEX CONCURRENTLY orders_user_created_idx ON orders (user_id, created_at DESC); -- uses it: equality on the leading column, range and order on the secondEXPLAIN (ANALYZE, BUFFERS)SELECT id, total FROM orders…This function places an order inside a transaction and passes its tests. Which two bugs will show up in production?
func (s *Store) PlaceOrder(ctx context.Context, o Order) error { tx, err := s.db.BeginTx(ctx, nil) if err != nil { return err } if _, err := tx.ExecContext(ctx, insertOrderSQL, o.ID, o.UserID); err != nil { return err } if _, err := tx.ExecContext(ctx, reserveStockSQL, o.SKU, o.Qty); err != nil { tx.Rollback() return err } tx.Commit() return nil}This search function is open to SQL injection. A placeholder fixes one of the two interpolations. What do you do with the other?
func (r *Repo) Search(ctx context.Context, q, sortBy string) (*sql.Rows, error) { query := fmt.Sprintf( "SELECT id, name FROM docs WHERE name ILIKE '%%%s%%' ORDER BY %s LIMIT 50", q, sortBy) return r.db.QueryContext(ctx, query)}How do
SetMaxOpenConns,SetMaxIdleConnsandSetConnMaxLifetimework, and how do you size the pool for a busy service?func configurePool(db *sql.DB) { // Per pod. Postgres max_connections is shared by every pod, every cron // job and whatever a human has open in psql. db.SetMaxOpenConns(20) db.SetMaxIdleConns(20) // equal, or the pool re-dials under load db.SetConnMaxLifetime(30 * time.Minute) // recycle by age: DNS/failover changes, proxy max age…What happens to a running query when the request's context is cancelled, and why do you still need
statement_timeout?func (r *Repo) TopSellers(ctx context.Context, since time.Time) ([]Row, error) { // A slice of the request's budget: one slow report cannot hold a pool // connection for a minute, and a client disconnect cancels it too. ctx, cancel := context.WithTimeout(ctx, 500*time.Millisecond) defer cancel()…How do you run one transaction across several repository methods without leaking
*sql.Txinto the service layer?// Implemented by *sql.DB, *sql.Tx and *sql.Conn.type DBTX interface { ExecContext(context.Context, string, ...any) (sql.Result, error) QueryContext(context.Context, string, ...any) (*sql.Rows, error) QueryRowContext(context.Context, string, ...any) *sql.Row}…What is the difference between
database/sql,pgx,sqlx,sqlcand GORM, and how do you choose one for a new service?type Order struct { ID int64 `db:"id"` UserID int64 `db:"user_id"` Total int64 `db:"total"`}…What is the N+1 query problem, and how do you fix it in Go without an ORM?
// Two queries total, however many orders came back.func (r *Repo) OrdersWithItems(ctx context.Context, userID int64) ([]Order, error) { orders, err := r.orders(ctx, userID) if err != nil || len(orders) == 0 { return orders, err }…How do you bulk-insert 200,000 rows from Go efficiently, and what limits a multi-row
INSERT?// pgx native: one statement, one stream, no per-row protocol overheadfunc loadEvents(ctx context.Context, pool *pgxpool.Pool, events []Event) (int64, error) { return pool.CopyFrom(ctx, pgx.Identifier{"events"}, []string{"user_id", "kind", "at"}, pgx.CopyFromSlice(len(events), func(i int) ([]any, error) {…What is the difference between optimistic and pessimistic locking, and how do you build a job queue on Postgres with
SKIP LOCKED?// Optimistic: no lock held; the version check catches a concurrent writer.func (r *Repo) Rename(ctx context.Context, id, version int64, title string) error { res, err := r.db.ExecContext(ctx, ` UPDATE docs SET title = $1, version = version + 1 WHERE id = $2 AND version = $3`, title, id, version) if err != nil {…Two transfers between the same accounts fail with
40P01 deadlock detected. Why does it happen, and how do you prevent it instead of just retrying?// wrong: lock order follows the transfer direction, so A->B and B->A deadlock// SELECT ... WHERE id = $from FOR UPDATE; then SELECT ... WHERE id = $to FOR UPDATE // right: one deterministic order for every transaction in the systemfunc transfer(ctx context.Context, tx *sql.Tx, from, to, amount int64) error { ids := []int64{from, to}…Find the bug: under load
CreateOrderstalls until the request times out, while the database itself is nearly idle.func (s *Store) CreateOrder(ctx context.Context, o Order) error { tx, err := s.db.BeginTx(ctx, nil) if err != nil { return err } defer tx.Rollback() if _, err := tx.ExecContext(ctx, insertOrderSQL, o.ID, o.UserID, o.Total); err != nil { return err } if _, err := s.db.ExecContext(ctx, insertAuditSQL, o.ID, "order_created"); err != nil { return err } return tx.Commit()}What does
db.Preparemean when a connection pool sits underneath, and why can a shared*sql.Stmtbreak behind PgBouncer?// wrong: a process-wide Stmt over a pool: re-prepared on every connection it// touches, and broken behind a transaction-mode pooler.// var getUser *sql.Stmt // prepared in main(), used from every handler // right: just pass the args; pgx caches the statement per connection itselffunc (r *Repo) User(ctx context.Context, id int64) (User, error) {…How does MVCC work in PostgreSQL, and why can one long-running transaction bloat tables for everyone?
How do you retry a transaction that fails with SQLSTATE
40001underSerializable, and what must never be inside the retry loop?func (r *Repo) InSerializableTx(ctx context.Context, fn func(DBTX) error) error { const maxAttempts = 4 var err error for attempt := 1; attempt <= maxAttempts; attempt++ { err = r.runOnce(ctx, fn) // the whole transaction, re-read from scratch if err == nil || !isRetryable(err) {…How do you change a production schema with no downtime (say, renaming a column), and where do goose or golang-migrate fit in?
// Release 1 code: still reads name, writes both, so old pods keep working.func (r *Repo) SetName(ctx context.Context, id int64, name string) error { _, err := r.db.ExecContext(ctx, "UPDATE users SET name = $1, full_name = $1 WHERE id = $2", name, id) return err}…What is the difference between replication and sharding, and when do you choose synchronous over asynchronous replication?