Queries and transactions

Call a query

Create the query set with a pgx pool, then call methods on it:

queries := db.New(pool)
posts, err := queries.ListUserPosts(ctx, userID)

Your own functions can accept db.Read or db.ReadWrite instead of the concrete *db.Queries type. Which methods go in which interface depends on the configured query modes. ReadWrite includes every Read method.

Use a store

store := db.NewStore(pool)
posts, err := store.ListUserPosts(ctx, userID)

A store has the same query methods and implements the same interfaces. Direct calls on it use the pool. It also has methods that run a callback inside a transaction. You still own the pool and close it yourself.

Create a user and their first post together

A transaction runs several queries as one unit. Either all of them are saved, or none are. Here, the user and the post are created together. If the post insert fails, the new user is rolled back too:

post, err := store.ReadWriteTxValue(
    ctx,
    func(ctx context.Context, tx db.ReadWrite) (db.Post, error) {
        user, err := tx.CreateUser(ctx, db.CreateUserParams{
            Email: "[email protected]",
        })
        if err != nil {
            return db.Post{}, err
        }
        return tx.CreatePost(ctx, db.CreatePostParams{
            UserID: user.ID,
            Title:  "My first day at Lumon",
        })
    },
)
if err != nil {
    return err
}

ReadWriteTxValue commits when the callback returns nil, and only then returns the post. If the callback returns an error, it rolls back the transaction. On any error, including a commit error, the returned post is the zero Post value.

Inside the callback, always use the ctx and tx that the callback receives. If you call another function that should be part of the transaction, pass both of those to it.

Choose a callback

Method	Callback queries	Callback result	Database transaction
ReadTx	db.Read	error	Read-only
ReadTxValue	db.Read	(T, error)	Read-only
ReadWriteTx	db.ReadWrite	error	Read-write
ReadWriteTxValue	db.ReadWrite	(T, error)	Read-write

The Tx variants return only an error. The TxValue variants also return a value. For example, a read transaction can return a slice of posts:

posts, err := store.ReadTxValue(
    ctx,
    func(ctx context.Context, tx db.Read) ([]db.Post, error) {
        return tx.ListUserPosts(ctx, userID)
    },
)

The callback receives db.Read, so it cannot call write methods such as CreatePost or DeletePost.

When there is nothing to return, use ReadWriteTx or ReadTx:

err := store.ReadWriteTx(ctx, func(ctx context.Context, tx db.ReadWrite) error {
    _, err := tx.DeletePost(ctx, postID)
    return err
})

All four helpers commit when the callback returns nil and roll back when it returns an error. They use the database’s default isolation level and do not retry. At the default isolation level, two queries in the same read-only transaction can still see different data if another connection commits between them.

Use the callback’s context and queries

This example is wrong. The callback calls store instead of tx. store uses the pool, so the query would run outside the transaction. PlainSQL detects this and returns an error before the query reaches the database:

err := store.ReadWriteTx(ctx, func(ctx context.Context, tx db.ReadWrite) error {
    // The outer store uses the pool, outside this transaction.
    _, err := store.ListUserPosts(ctx, userID)
    return err // Matches db.ErrTransactionScope.
})

The correct call is tx.ListUserPosts(ctx, userID). A separate db.New(pool) triggers the same check. Starting another store transaction with the callback’s context returns db.ErrNestedTransaction. PlainSQL does not create savepoints for nested calls.

These checks work through the callback’s context, and only within one generated package. They do not catch queries made with the outer context, direct pgx calls, or calls into a different generated package. Other code can still use the pool while the transaction runs. Do not use tx after the callback returns.

Manage a transaction yourself

To set the isolation level, use savepoints, or retry on conflict, start the transaction with pgx and bind the queries to it:

err := pgx.BeginTxFunc(ctx, pool, pgx.TxOptions{
    IsoLevel: pgx.RepeatableRead,
}, func(tx pgx.Tx) error {
    q := db.New(tx)
    _, err := q.ListUserPosts(ctx, userID)
    return err
})

db.New(tx) runs its queries in the pgx transaction. It never commits or rolls back. In this example, pgx.BeginTxFunc does that.