sqlc/docs/howto/transactions.md

Using transactions

In the code generated by sqlc, the WithTx method allows a Queries instance to be associated with a transaction.

For example, with the following SQL structure:

schema.sql:

CREATE TABLE records (
  id SERIAL PRIMARY KEY,
  counter INT NOT NULL
);

query.sql

-- name: GetRecord :one
SELECT * FROM records
WHERE id = $1;

-- name: UpdateRecord :exec
UPDATE records SET counter = $2
WHERE id = $1;

And the generated code from sqlc in db.go:

package tutorial

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
	PrepareContext(context.Context, string) (*sql.Stmt, error)
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

func New(db DBTX) *Queries {
	return &Queries{db: db}
}

type Queries struct {
	db DBTX
}

func (q *Queries) WithTx(tx *sql.Tx) *Queries {
	return &Queries{
		db: tx,
	}
}

You'd use it like this:

func bumpCounter(ctx context.Context, db *sql.DB, queries *tutorial.Queries, id int32) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	qtx := queries.WithTx(tx)
	r, err := qtx.GetRecord(ctx, id)
	if err != nil {
		return err
	}
	if err := qtx.UpdateRecord(ctx, tutorial.UpdateRecordParams{
		ID:      r.ID,
		Counter: r.Counter + 1,
	}); err != nil {
		return err
	}
	return tx.Commit()
}