SQL Transactions

Use SQL transactions with MySQL and PostgreSQL through the shared DB transaction API.

SQL transactions are supported by the MySQL and PostgreSQL drivers.

Use transactions when multiple database operations must either all succeed or all fail together.

Typical use cases include:

inserting related records
updating multiple rows consistently
reading data after a write inside the same transaction
deleting related data
grouping business operations into one atomic unit

Scylla does not use SQL transactions in the same way as MySQL or PostgreSQL. For Scylla conditional writes, see the Scylla Lightweight Transactions guide.

Basic Idea

Use TransactionCtx on a db.Conn.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	// use tx inside the transaction
	return nil
})

The callback receives a transactional db.Conn.

Use that tx connection with the same shared helpers:

db.Insert(ctx, tx, query, args...)
db.Update(ctx, tx, query, args...)
db.Get[T](ctx, tx, query, args...)
db.Delete(ctx, tx, query, args...)

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

If the callback returns nil, the transaction is committed.

MySQL Transaction Example

MySQL uses ? placeholders and can read inserted IDs from LastInsertId.

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"`
}

const insertUserQuery = `
	INSERT INTO users (name, email, active)
	VALUES (?, ?, ?)
`

const updateUserQuery = `
	UPDATE users
	SET name = ?, email = ?, active = ?
	WHERE id = ?
`

const selectUserQuery = `
	SELECT * FROM users
	WHERE id = ?
	LIMIT 1
`

const deleteUserQuery = `
	DELETE FROM users
	WHERE id = ?
`

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

	err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
		result, err := db.Insert(
			ctx,
			tx,
			insertUserQuery,
			"Transaction User",
			"transaction.user@example.com",
			true,
		)
		if err != nil {
			return err
		}

		insertedID = result.LastInsertId()

		if _, err := db.Update(
			ctx,
			tx,
			updateUserQuery,
			"Updated Transaction User",
			"updated.transaction.user@example.com",
			false,
			insertedID,
		); err != nil {
			return err
		}

		user, found, err := db.Get[User](ctx, tx, selectUserQuery, insertedID)
		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"),
			)
		}

		if _, err := db.Delete(ctx, tx, deleteUserQuery, insertedID); err != nil {
			return err
		}

		return nil
	})

	if err != nil {
		return 0, err
	}

	return insertedID, nil
}

PostgreSQL Transaction Example

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"`
}

const insertUserQuery = `
	INSERT INTO users (name, email, active)
	VALUES ($1, $2, $3)
	RETURNING id
`

const updateUserQuery = `
	UPDATE users
	SET name = $1, email = $2, active = $3
	WHERE id = $4
`

const selectUserQuery = `
	SELECT * FROM users
	WHERE id = $1
	LIMIT 1
`

const deleteUserQuery = `
	DELETE FROM users
	WHERE id = $1
`

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

	err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
		id, found, err := db.Value[int64](
			ctx,
			tx,
			insertUserQuery,
			"Transaction User",
			"transaction.user@example.com",
			true,
		)
		if err != nil {
			return err
		}

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

		insertedID = id

		if _, err := db.Update(
			ctx,
			tx,
			updateUserQuery,
			"Updated Transaction User",
			"updated.transaction.user@example.com",
			false,
			insertedID,
		); err != nil {
			return err
		}

		user, found, err := db.Get[User](ctx, tx, selectUserQuery, insertedID)
		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"),
			)
		}

		if _, err := db.Delete(ctx, tx, deleteUserQuery, insertedID); err != nil {
			return err
		}

		return nil
	})

	if err != nil {
		return 0, err
	}

	return insertedID, nil
}

Usage Example

The application code is the same for MySQL and PostgreSQL once connectDB returns a db.Conn.

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/joho/godotenv"
)

func main() {
	ctx := context.Background()

	err := godotenv.Load()
	if err != nil {
		log.Println(".env file not found, I'm using system env variables")
	}

	conn, err := connectDB()
	if err != nil {
		log.Fatal(err)
	}

	id, err := RunUserTransaction(ctx, conn)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("transaction completed for user id=%d\n", id)
}

Commit and Rollback

The transaction callback controls commit and rollback behavior.

Return nil to commit:

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	_, err := db.Update(ctx, tx, updateUserQuery, active, id)
	if err != nil {
		return err
	}

	return nil
})

Return an error to rollback:

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	_, err := db.Update(ctx, tx, updateUserQuery, active, id)
	if err != nil {
		return err
	}

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

If any operation fails, return the error from the callback.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	if _, err := db.Insert(ctx, tx, insertUserQuery, name, email, active); err != nil {
		return err
	}

	if _, err := db.Update(ctx, tx, updateUserQuery, active, id); err != nil {
		return err
	}

	return nil
})

Use the Transaction Connection

Inside the callback, always use the tx connection.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	return db.Update(ctx, tx, updateUserQuery, active, id)
})

Do not accidentally use the outer connection inside the transaction callback.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	// Wrong: this uses the outer connection, not the transaction.
	_, err := db.Update(ctx, conn, updateUserQuery, active, id)
	return err
})

Using the outer connection means the operation may run outside the transaction.

Driver Differences

The transaction API is shared, but SQL syntax still depends on the driver.

Topic	MySQL	Postgres
Placeholder style	`?`	`$1`, `$2`, `$3`
Insert ID	`result.LastInsertId()`	`RETURNING id` + `db.Value`
Transaction API	`TransactionCtx`	`TransactionCtx`
Shared helpers	supported	supported

When to Use SQL Transactions

Use SQL transactions when:

multiple statements must succeed or fail together
you need atomic write behavior
you read after writing and need transaction consistency
you update related records
rollback should happen automatically on error

When Not to Use SQL Transactions

Do not use SQL transactions for Scylla workloads.

Scylla uses different patterns such as:

query-driven data modeling
idempotent writes
batches
lightweight transactions with conditional CQL

Do not keep transactions open longer than necessary.

Avoid:

slow network calls inside a transaction
long-running background work inside a transaction
waiting for user input inside a transaction
large unbounded loops inside a transaction

Standalone examples are available in the examples repository:

SQL transactions are supported by the MySQL and PostgreSQL drivers.

Use transactions when multiple database operations must either all succeed or all fail together.

Typical use cases include:

inserting related records
updating multiple rows consistently
reading data after a write inside the same transaction
deleting related data
grouping business operations into one atomic unit

Scylla does not use SQL transactions in the same way as MySQL or PostgreSQL. For Scylla conditional writes, see the Scylla Lightweight Transactions guide.

Basic Idea

Use TransactionCtx on a db.Conn.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	// use tx inside the transaction
	return nil
})

The callback receives a transactional db.Conn.

Use that tx connection with the same shared helpers:

db.Insert(ctx, tx, query, args...)
db.Update(ctx, tx, query, args...)
db.Get[T](ctx, tx, query, args...)
db.Delete(ctx, tx, query, args...)

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

If the callback returns nil, the transaction is committed.

MySQL Transaction Example

MySQL uses ? placeholders and can read inserted IDs from LastInsertId.

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"`
}

const insertUserQuery = `
	INSERT INTO users (name, email, active)
	VALUES (?, ?, ?)
`

const updateUserQuery = `
	UPDATE users
	SET name = ?, email = ?, active = ?
	WHERE id = ?
`

const selectUserQuery = `
	SELECT * FROM users
	WHERE id = ?
	LIMIT 1
`

const deleteUserQuery = `
	DELETE FROM users
	WHERE id = ?
`

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

	err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
		result, err := db.Insert(
			ctx,
			tx,
			insertUserQuery,
			"Transaction User",
			"transaction.user@example.com",
			true,
		)
		if err != nil {
			return err
		}

		insertedID = result.LastInsertId()

		if _, err := db.Update(
			ctx,
			tx,
			updateUserQuery,
			"Updated Transaction User",
			"updated.transaction.user@example.com",
			false,
			insertedID,
		); err != nil {
			return err
		}

		user, found, err := db.Get[User](ctx, tx, selectUserQuery, insertedID)
		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"),
			)
		}

		if _, err := db.Delete(ctx, tx, deleteUserQuery, insertedID); err != nil {
			return err
		}

		return nil
	})

	if err != nil {
		return 0, err
	}

	return insertedID, nil
}

PostgreSQL Transaction Example

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"`
}

const insertUserQuery = `
	INSERT INTO users (name, email, active)
	VALUES ($1, $2, $3)
	RETURNING id
`

const updateUserQuery = `
	UPDATE users
	SET name = $1, email = $2, active = $3
	WHERE id = $4
`

const selectUserQuery = `
	SELECT * FROM users
	WHERE id = $1
	LIMIT 1
`

const deleteUserQuery = `
	DELETE FROM users
	WHERE id = $1
`

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

	err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
		id, found, err := db.Value[int64](
			ctx,
			tx,
			insertUserQuery,
			"Transaction User",
			"transaction.user@example.com",
			true,
		)
		if err != nil {
			return err
		}

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

		insertedID = id

		if _, err := db.Update(
			ctx,
			tx,
			updateUserQuery,
			"Updated Transaction User",
			"updated.transaction.user@example.com",
			false,
			insertedID,
		); err != nil {
			return err
		}

		user, found, err := db.Get[User](ctx, tx, selectUserQuery, insertedID)
		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"),
			)
		}

		if _, err := db.Delete(ctx, tx, deleteUserQuery, insertedID); err != nil {
			return err
		}

		return nil
	})

	if err != nil {
		return 0, err
	}

	return insertedID, nil
}

Usage Example

The application code is the same for MySQL and PostgreSQL once connectDB returns a db.Conn.

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/joho/godotenv"
)

func main() {
	ctx := context.Background()

	err := godotenv.Load()
	if err != nil {
		log.Println(".env file not found, I'm using system env variables")
	}

	conn, err := connectDB()
	if err != nil {
		log.Fatal(err)
	}

	id, err := RunUserTransaction(ctx, conn)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("transaction completed for user id=%d\n", id)
}

Commit and Rollback

The transaction callback controls commit and rollback behavior.

Return nil to commit:

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	_, err := db.Update(ctx, tx, updateUserQuery, active, id)
	if err != nil {
		return err
	}

	return nil
})

Return an error to rollback:

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	_, err := db.Update(ctx, tx, updateUserQuery, active, id)
	if err != nil {
		return err
	}

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

If any operation fails, return the error from the callback.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	if _, err := db.Insert(ctx, tx, insertUserQuery, name, email, active); err != nil {
		return err
	}

	if _, err := db.Update(ctx, tx, updateUserQuery, active, id); err != nil {
		return err
	}

	return nil
})

Use the Transaction Connection

Inside the callback, always use the tx connection.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	return db.Update(ctx, tx, updateUserQuery, active, id)
})

Do not accidentally use the outer connection inside the transaction callback.

err := conn.TransactionCtx(ctx, func(tx db.Conn) error {
	// Wrong: this uses the outer connection, not the transaction.
	_, err := db.Update(ctx, conn, updateUserQuery, active, id)
	return err
})

Using the outer connection means the operation may run outside the transaction.

Driver Differences

The transaction API is shared, but SQL syntax still depends on the driver.

Topic	MySQL	Postgres
Placeholder style	`?`	`$1`, `$2`, `$3`
Insert ID	`result.LastInsertId()`	`RETURNING id` + `db.Value`
Transaction API	`TransactionCtx`	`TransactionCtx`
Shared helpers	supported	supported

When to Use SQL Transactions

Use SQL transactions when:

multiple statements must succeed or fail together
you need atomic write behavior
you read after writing and need transaction consistency
you update related records
rollback should happen automatically on error

When Not to Use SQL Transactions

Do not use SQL transactions for Scylla workloads.

Scylla uses different patterns such as:

query-driven data modeling
idempotent writes
batches
lightweight transactions with conditional CQL

Do not keep transactions open longer than necessary.

Avoid:

slow network calls inside a transaction
long-running background work inside a transaction
waiting for user input inside a transaction
large unbounded loops inside a transaction

Standalone examples are available in the examples repository:

SQL Transactions

Basic Idea

MySQL Transaction Example

PostgreSQL Transaction Example

Usage Example

Commit and Rollback

Use the Transaction Connection

Driver Differences

When to Use SQL Transactions

When Not to Use SQL Transactions

On this page

SQL Transactions

Basic Idea

MySQL Transaction Example

PostgreSQL Transaction Example

Usage Example

Commit and Rollback

Use the Transaction Connection

Driver Differences

When to Use SQL Transactions

When Not to Use SQL Transactions

On this page