Low-Level Transactions

Use SQL transactions with prepared db.Query values and low-level execution helpers.

Low-level transactions are useful when you want to work with db.Query values directly.

This is common when:

queries are prepared before execution
queries are loaded from SQL files
queries are selected with db.Dialect
you want explicit control over db.Raw, ExecQuery, GetQuery, ValueQuery, or MapsQuery
transaction logic should stay close to the lower-level db.Conn interface

The transaction behavior is the same as in the high-level transaction API.

If the callback returns an error, the transaction is rolled back.

If the callback returns nil, the transaction is committed.

Basic Idea

Create db.Query values using db.Raw.

q, err := db.Raw(`
	UPDATE users
	SET active = ?
	WHERE id = ?
`, active, id)

if err != nil {
	return err
}

Execute the query inside a transaction with the transactional connection.

err = conn.TransactionCtx(ctx, func(tx db.Conn) error {
	_, err := db.ExecQuery(ctx, tx, q)
	return err
})

Inside the callback, always use tx, not the outer connection.

MySQL Low-Level Transaction

MySQL uses ? placeholders.

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/netlifeguru/db"
)

type User struct {
	ID        int64     `db:"id"`
	Name      string    `db:"name"`
	Email     string    `db:"email"`
	Active    bool      `db:"active"`
	CreatedAt time.Time `db:"created_at"`
}

func RunLowLevelTransaction(ctx context.Context, conn db.Conn) (int64, error) {
	var insertedID int64

	err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
		insertQuery, err := db.Raw(`
			INSERT INTO users (name, email, active)
			VALUES (?, ?, ?)
		`, "Low Level User", "low.level@example.com", true)

		if err != nil {
			return err
		}

		result, err := db.ExecQuery(ctx, tx, insertQuery)
		if err != nil {
			return err
		}

		insertedID = result.LastInsertId()

		updateQuery, err := db.Raw(`
			UPDATE users
			SET active = ?
			WHERE id = ?
		`, false, insertedID)

		if err != nil {
			return err
		}

		if _, err := db.ExecQuery(ctx, tx, updateQuery); err != nil {
			return err
		}

		getQuery, err := db.Raw(`
			SELECT * FROM users
			WHERE id = ?
			LIMIT 1
		`, insertedID)

		if err != nil {
			return err
		}

		user, found, err := db.GetQuery[User](ctx, tx, getQuery)
		if err != nil {
			return err
		}

		if found {
			fmt.Printf("%d | %s | %s | active=%v | created_at=%s\n",
				user.ID,
				user.Name,
				user.Email,
				user.Active,
				user.CreatedAt.Format("2006-01-02 15:04:05"),
			)
		}

		deleteQuery, err := db.Raw(`
			DELETE FROM users
			WHERE id = ?
		`, insertedID)

		if err != nil {
			return err
		}

		if _, err := db.ExecQuery(ctx, tx, deleteQuery); err != nil {
			return err
		}

		return nil
	})

	if err != nil {
		return 0, err
	}

	return insertedID, nil
}

PostgreSQL Low-Level Transaction

PostgreSQL uses numbered placeholders and commonly reads inserted IDs with RETURNING.

package main

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/netlifeguru/db"
)

type User struct {
	ID        int64     `db:"id"`
	Name      string    `db:"name"`
	Email     string    `db:"email"`
	Active    bool      `db:"active"`
	CreatedAt time.Time `db:"created_at"`
}

func RunLowLevelTransaction(ctx context.Context, conn db.Conn) (int64, error) {
	var insertedID int64

	err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
		insertQuery, err := db.Raw(`
			INSERT INTO users (name, email, active)
			VALUES ($1, $2, $3)
			RETURNING id
		`, "Low Level User", "low.level@example.com", true)

		if err != nil {
			return err
		}

		id, found, err := db.ValueQuery[int64](ctx, tx, insertQuery)
		if err != nil {
			return err
		}

		if !found {
			return errors.New("insert did not return id")
		}

		insertedID = id

		updateQuery, err := db.Raw(`
			UPDATE users
			SET active = $1
			WHERE id = $2
		`, false, insertedID)

		if err != nil {
			return err
		}

		if _, err := db.ExecQuery(ctx, tx, updateQuery); err != nil {
			return err
		}

		getQuery, err := db.Raw(`
			SELECT * FROM users
			WHERE id = $1
			LIMIT 1
		`, insertedID)

		if err != nil {
			return err
		}

		user, found, err := db.GetQuery[User](ctx, tx, getQuery)
		if err != nil {
			return err
		}

		if found {
			fmt.Printf("%d | %s | %s | active=%v | created_at=%s\n",
				user.ID,
				user.Name,
				user.Email,
				user.Active,
				user.CreatedAt.Format("2006-01-02 15:04:05"),
			)
		}

		deleteQuery, err := db.Raw(`
			DELETE FROM users
			WHERE id = $1
		`, insertedID)

		if err != nil {
			return err
		}

		if _, err := db.ExecQuery(ctx, tx, deleteQuery); err != nil {
			return err
		}

		return nil
	})

	if err != nil {
		return 0, err
	}

	return insertedID, nil
}

Using Dialect Queries

Low-level transactions work well with dialect SQL.

type Queries struct {
	InsertUser db.DialectSQL `json:"InsertUser"`
	UpdateUser db.DialectSQL `json:"UpdateUser"`
	GetUser    db.DialectSQL `json:"GetUser"`
	DeleteUser db.DialectSQL `json:"DeleteUser"`
}

Select the correct query for the active driver inside the transaction.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	insertQuery, err := db.Dialect(tx, queries.InsertUser, name, email, active)
	if err != nil {
		return err
	}

	result, err := db.ExecQuery(ctx, tx, insertQuery)
	if err != nil {
		return err
	}

	id := result.LastInsertId()

	getQuery, err := db.Dialect(tx, queries.GetUser, id)
	if err != nil {
		return err
	}

	user, found, err := db.GetQuery[User](ctx, tx, getQuery)
	if err != nil {
		return err
	}

	if found {
		fmt.Println(user.Name)
	}

	return nil
})

For PostgreSQL inserts with RETURNING, use ValueQuery instead of ExecQuery.

insertQuery, err := db.Dialect(tx, queries.InsertUser, name, email, active)
if err != nil {
	return err
}

id, found, err := db.ValueQuery[int64](ctx, tx, insertQuery)
if err != nil {
	return err
}

if !found {
	return errors.New("insert did not return id")
}

When to Use Low-Level Transactions

Use low-level transaction helpers when:

you already have db.Query values
you use db.Raw explicitly
you use db.Dialect to select driver-specific SQL
queries are loaded from SQL files
you want to call ExecQuery, GetQuery, ValueQuery, or MapsQuery
you want explicit control over query preparation before execution

When to Use High-Level Helpers Instead

Use high-level helpers when the SQL is local and simple.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	_, err := db.Update(ctx, tx, `
		UPDATE users
		SET active = ?
		WHERE id = ?
	`, active, id)

	return err
})

This is easier to read for small transaction blocks.

Use low-level helpers when you need reusable or dialect-selected query objects.

Commit and Rollback

Commit and rollback behavior is controlled by the callback return value.

Return nil to commit.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	q, err := db.Raw(`
		UPDATE users
		SET active = ?
		WHERE id = ?
	`, active, id)

	if err != nil {
		return err
	}

	_, err = db.ExecQuery(ctx, tx, q)
	return err
})

Return an error to roll back.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	q, err := db.Raw(`
		UPDATE users
		SET active = ?
		WHERE id = ?
	`, active, id)

	if err != nil {
		return err
	}

	if _, err := db.ExecQuery(ctx, tx, q); err != nil {
		return err
	}

	return errors.New("rollback this transaction")
})

Use the Transaction Connection

Inside the transaction callback, always use the tx connection.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	q, err := db.Raw(`
		UPDATE users
		SET active = ?
		WHERE id = ?
	`, active, id)

	if err != nil {
		return err
	}

	_, err = db.ExecQuery(ctx, tx, q)
	return err
})

Do not use the outer connection inside the transaction callback.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	q, err := db.Raw(`
		UPDATE users
		SET active = ?
		WHERE id = ?
	`, active, id)

	if err != nil {
		return err
	}

	// Wrong: this uses the outer connection, not the transaction.
	_, err = db.ExecQuery(ctx, conn, q)
	return err
})

Driver Support

Low-level SQL transactions are supported by:

MySQL
PostgreSQL

Scylla does not use SQL transactions in the same way.

For Scylla conditional writes, see the Scylla Lightweight Transactions guide.

For grouped Scylla writes, see the Scylla Batches guide.

Standalone examples are available in the examples repository:

Low-level transactions are useful when you want to work with db.Query values directly.

This is common when:

queries are prepared before execution
queries are loaded from SQL files
queries are selected with db.Dialect
you want explicit control over db.Raw, ExecQuery, GetQuery, ValueQuery, or MapsQuery
transaction logic should stay close to the lower-level db.Conn interface

The transaction behavior is the same as in the high-level transaction API.

If the callback returns an error, the transaction is rolled back.

If the callback returns nil, the transaction is committed.

Basic Idea

Create db.Query values using db.Raw.

q, err := db.Raw(`
	UPDATE users
	SET active = ?
	WHERE id = ?
`, active, id)

if err != nil {
	return err
}

Execute the query inside a transaction with the transactional connection.

err = conn.TransactionCtx(ctx, func(tx db.Conn) error {
	_, err := db.ExecQuery(ctx, tx, q)
	return err
})

Inside the callback, always use tx, not the outer connection.

MySQL Low-Level Transaction

MySQL uses ? placeholders.

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/netlifeguru/db"
)

type User struct {
	ID        int64     `db:"id"`
	Name      string    `db:"name"`
	Email     string    `db:"email"`
	Active    bool      `db:"active"`
	CreatedAt time.Time `db:"created_at"`
}

func RunLowLevelTransaction(ctx context.Context, conn db.Conn) (int64, error) {
	var insertedID int64

	err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
		insertQuery, err := db.Raw(`
			INSERT INTO users (name, email, active)
			VALUES (?, ?, ?)
		`, "Low Level User", "low.level@example.com", true)

		if err != nil {
			return err
		}

		result, err := db.ExecQuery(ctx, tx, insertQuery)
		if err != nil {
			return err
		}

		insertedID = result.LastInsertId()

		updateQuery, err := db.Raw(`
			UPDATE users
			SET active = ?
			WHERE id = ?
		`, false, insertedID)

		if err != nil {
			return err
		}

		if _, err := db.ExecQuery(ctx, tx, updateQuery); err != nil {
			return err
		}

		getQuery, err := db.Raw(`
			SELECT * FROM users
			WHERE id = ?
			LIMIT 1
		`, insertedID)

		if err != nil {
			return err
		}

		user, found, err := db.GetQuery[User](ctx, tx, getQuery)
		if err != nil {
			return err
		}

		if found {
			fmt.Printf("%d | %s | %s | active=%v | created_at=%s\n",
				user.ID,
				user.Name,
				user.Email,
				user.Active,
				user.CreatedAt.Format("2006-01-02 15:04:05"),
			)
		}

		deleteQuery, err := db.Raw(`
			DELETE FROM users
			WHERE id = ?
		`, insertedID)

		if err != nil {
			return err
		}

		if _, err := db.ExecQuery(ctx, tx, deleteQuery); err != nil {
			return err
		}

		return nil
	})

	if err != nil {
		return 0, err
	}

	return insertedID, nil
}

PostgreSQL Low-Level Transaction

PostgreSQL uses numbered placeholders and commonly reads inserted IDs with RETURNING.

package main

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/netlifeguru/db"
)

type User struct {
	ID        int64     `db:"id"`
	Name      string    `db:"name"`
	Email     string    `db:"email"`
	Active    bool      `db:"active"`
	CreatedAt time.Time `db:"created_at"`
}

func RunLowLevelTransaction(ctx context.Context, conn db.Conn) (int64, error) {
	var insertedID int64

	err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
		insertQuery, err := db.Raw(`
			INSERT INTO users (name, email, active)
			VALUES ($1, $2, $3)
			RETURNING id
		`, "Low Level User", "low.level@example.com", true)

		if err != nil {
			return err
		}

		id, found, err := db.ValueQuery[int64](ctx, tx, insertQuery)
		if err != nil {
			return err
		}

		if !found {
			return errors.New("insert did not return id")
		}

		insertedID = id

		updateQuery, err := db.Raw(`
			UPDATE users
			SET active = $1
			WHERE id = $2
		`, false, insertedID)

		if err != nil {
			return err
		}

		if _, err := db.ExecQuery(ctx, tx, updateQuery); err != nil {
			return err
		}

		getQuery, err := db.Raw(`
			SELECT * FROM users
			WHERE id = $1
			LIMIT 1
		`, insertedID)

		if err != nil {
			return err
		}

		user, found, err := db.GetQuery[User](ctx, tx, getQuery)
		if err != nil {
			return err
		}

		if found {
			fmt.Printf("%d | %s | %s | active=%v | created_at=%s\n",
				user.ID,
				user.Name,
				user.Email,
				user.Active,
				user.CreatedAt.Format("2006-01-02 15:04:05"),
			)
		}

		deleteQuery, err := db.Raw(`
			DELETE FROM users
			WHERE id = $1
		`, insertedID)

		if err != nil {
			return err
		}

		if _, err := db.ExecQuery(ctx, tx, deleteQuery); err != nil {
			return err
		}

		return nil
	})

	if err != nil {
		return 0, err
	}

	return insertedID, nil
}

Using Dialect Queries

Low-level transactions work well with dialect SQL.

type Queries struct {
	InsertUser db.DialectSQL `json:"InsertUser"`
	UpdateUser db.DialectSQL `json:"UpdateUser"`
	GetUser    db.DialectSQL `json:"GetUser"`
	DeleteUser db.DialectSQL `json:"DeleteUser"`
}

Select the correct query for the active driver inside the transaction.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	insertQuery, err := db.Dialect(tx, queries.InsertUser, name, email, active)
	if err != nil {
		return err
	}

	result, err := db.ExecQuery(ctx, tx, insertQuery)
	if err != nil {
		return err
	}

	id := result.LastInsertId()

	getQuery, err := db.Dialect(tx, queries.GetUser, id)
	if err != nil {
		return err
	}

	user, found, err := db.GetQuery[User](ctx, tx, getQuery)
	if err != nil {
		return err
	}

	if found {
		fmt.Println(user.Name)
	}

	return nil
})

For PostgreSQL inserts with RETURNING, use ValueQuery instead of ExecQuery.

insertQuery, err := db.Dialect(tx, queries.InsertUser, name, email, active)
if err != nil {
	return err
}

id, found, err := db.ValueQuery[int64](ctx, tx, insertQuery)
if err != nil {
	return err
}

if !found {
	return errors.New("insert did not return id")
}

When to Use Low-Level Transactions

Use low-level transaction helpers when:

you already have db.Query values
you use db.Raw explicitly
you use db.Dialect to select driver-specific SQL
queries are loaded from SQL files
you want to call ExecQuery, GetQuery, ValueQuery, or MapsQuery
you want explicit control over query preparation before execution

When to Use High-Level Helpers Instead

Use high-level helpers when the SQL is local and simple.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	_, err := db.Update(ctx, tx, `
		UPDATE users
		SET active = ?
		WHERE id = ?
	`, active, id)

	return err
})

This is easier to read for small transaction blocks.

Use low-level helpers when you need reusable or dialect-selected query objects.

Commit and Rollback

Commit and rollback behavior is controlled by the callback return value.

Return nil to commit.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	q, err := db.Raw(`
		UPDATE users
		SET active = ?
		WHERE id = ?
	`, active, id)

	if err != nil {
		return err
	}

	_, err = db.ExecQuery(ctx, tx, q)
	return err
})

Return an error to roll back.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	q, err := db.Raw(`
		UPDATE users
		SET active = ?
		WHERE id = ?
	`, active, id)

	if err != nil {
		return err
	}

	if _, err := db.ExecQuery(ctx, tx, q); err != nil {
		return err
	}

	return errors.New("rollback this transaction")
})

Use the Transaction Connection

Inside the transaction callback, always use the tx connection.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	q, err := db.Raw(`
		UPDATE users
		SET active = ?
		WHERE id = ?
	`, active, id)

	if err != nil {
		return err
	}

	_, err = db.ExecQuery(ctx, tx, q)
	return err
})

Do not use the outer connection inside the transaction callback.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	q, err := db.Raw(`
		UPDATE users
		SET active = ?
		WHERE id = ?
	`, active, id)

	if err != nil {
		return err
	}

	// Wrong: this uses the outer connection, not the transaction.
	_, err = db.ExecQuery(ctx, conn, q)
	return err
})

Driver Support

Low-level SQL transactions are supported by:

MySQL
PostgreSQL

Scylla does not use SQL transactions in the same way.

For Scylla conditional writes, see the Scylla Lightweight Transactions guide.

For grouped Scylla writes, see the Scylla Batches guide.

Standalone examples are available in the examples repository:

Low-Level Transactions

Basic Idea

MySQL Low-Level Transaction

PostgreSQL Low-Level Transaction

Using Dialect Queries

When to Use Low-Level Transactions

When to Use High-Level Helpers Instead

Commit and Rollback

Use the Transaction Connection

Driver Support

On this page

Low-Level Transactions

Basic Idea

MySQL Low-Level Transaction

PostgreSQL Low-Level Transaction

Using Dialect Queries

When to Use Low-Level Transactions

When to Use High-Level Helpers Instead

Commit and Rollback

Use the Transaction Connection

Driver Support

On this page