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()
}