codesnips
go 39 lines · 1 tab

Postgres transaction pattern with pgx: defer rollback, commit explicitly

Leah Thompson Jan 2026
1 tab 
package store

import (
  "context"

  "github.com/jackc/pgx/v5"
)

type Store struct{ db *pgx.Conn }

func (s *Store) CreateOrder(ctx context.Context, userID string, totalCents int64) (string, error) {
  tx, err := s.db.Begin(ctx)
  if err != nil {
    return "", err
  }
  defer func() { _ = tx.Rollback(ctx) }()

  var orderID string
  err = tx.QueryRow(ctx,
    `INSERT INTO orders(user_id, total_cents) VALUES($1, $2) RETURNING id`,
    userID, totalCents,
  ).Scan(&orderID)
  if err != nil {
    return "", err
  }

  _, err = tx.Exec(ctx,
    `INSERT INTO order_audit(order_id, event) VALUES($1, $2)`,
    orderID, "created",
  )
  if err != nil {
    return "", err
  }

  if err := tx.Commit(ctx); err != nil {
    return "", err
  }
  return orderID, nil
}
1 file · go Explain with highlit

The most common transaction bug I see is forgetting to roll back on early returns. With pgx, I like the “defer rollback” pattern: start the transaction, defer tx.Rollback(ctx), then call tx.Commit(ctx) only on success. Rollback after a successful commit is a no-op, so the defer is safe and removes a whole class of mistakes. I also keep DB calls scoped to ctx so cancellations stop long queries. When multiple writes must be atomic (create row, insert audit log, upsert index table), this pattern keeps the failure modes simple. It’s boring code, which is exactly what you want for money, permissions, and idempotency state.

Related snips

go 
package api

import (
  "net/http"
  "runtime/debug"
)

Expose build metadata for debugging deploys

go observability build
by Leah Thompson 1 tab
go 
package dbutil

import (
  "context"

  "github.com/jackc/pgconn"

Retry Postgres serialization failures with bounded attempts

go postgres transactions
by Leah Thompson 1 tab
ruby 
require "csv"

class PeopleCsvStream
  include Enumerable

  HEADERS = %w[id full_name email signed_up_at plan].freeze

Resilient CSV Export as a Streamed Response

rails performance streaming
by codesnips 3 tabs
ruby 
class SignupForm
  include ActiveModel::Model
  include ActiveModel::Attributes

  attribute :account_name, :string
  attribute :email, :string

Shallow Controller, Deep Params: Form Object Pattern

rails activemodel form-object
by codesnips 3 tabs
ruby 
# BAD: N+1 query problem
@users = User.all
@users.each do |user|
  puts user.posts.count  # Fires query for each user!
end

ActiveRecord query optimization and N+1 prevention

ruby rails activerecord
by Sarah Mitchell 3 tabs
ruby 
class AddSettingsToAccounts < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_column :accounts, :settings, :jsonb, null: false, default: {}

Postgres JSONB Partial Index for Feature Flags

rails postgres jsonb
by codesnips 3 tabs

Share this code

Here's the card — post it anywhere.

Postgres transaction pattern with pgx: defer rollback, commit explicitly — share card
Link copied