rss resume / curriculum vitae linkedin linkedin gitlab github twitter mastodon instagram
r/golang: Transactions in context values, is it OK?
Nov 21, 2023

Disclaimer: This post includes Amazon affiliate links. Clicking on them earns me a commission and does not affect the final price.

Disclaimer: This post includes Amazon affiliate links. Clicking on them earns me a commission and does not affect the final price.

Welcome to the inaugural post about r/golang! Here, I discuss exciting, challenging, relevant, or anything interesting on that subreddit by providing a deep-dive discussion. Let’s get started!

r/golang asks

To put it simply, yes it is an anti-pattern but that does not solve the hypothetical problem, what is, if any, an idiomatic way to reuse repositories and still use transactions?

Things to highlight about this question are:

  • It’s time-constrained because it mentions the lifecycle of the request,
  • Repositories are used, so reusability is essential, and
  • Database transactions must work when using repositories.

I covered Context, repositories, and transactions (video) in the past, but here’s a quick refresher:

  • The context package has a method called context.WithValue, used to add an arbitrary key-value pair, it returns a new context.Context you can use in subsequent calls.
  • A repository is an abstraction between your domain and the persistence layer, providing a clear separation between them.
  • Transactions execute a list of statements atomically, known as a “unit of work”. They are the foundation of the ACID properties in relational databases.


Let’s take a concrete example: a system for users and their roles and permissions associated with them.

users, roles, permissions

In practice this will be implemented as the following:

users, roles, permissions

In this example, I’m using PostgreSQL and the pgx package to interact with the database. The code may be similar if you use database/sql in case you use that package instead.

Implementing a transaction helper

The complete example is available on GitHub.

The function implemented follows a well-known Enterprise Application Architecture pattern called Transaction Script:

11func transaction(ctx context.Context, tx pgx.Tx, f func() error) error {
12	if err := f(); err != nil {
13		_ = tx.Rollback(ctx)
15		return fmt.Errorf("f %w", err)
16	}
18	if err := tx.Commit(ctx); err != nil {
19		return fmt.Errorf("Commit %w", err)
20	}
22	return nil

Implementing Repositories

There are three types in the postgresql package that represent repositories:

  • User: interacts with the users table. We only implement the Insert method to insert new user records for simplicity.
  • UserRole: interacts with all the tables and allows inserting values to the users_role and selecting a complete User that includes all permissions and roles.
  • Role: interacts with the roles and permissions tables, allows inserting roles and permissions simultaneously, and allows inserting permissions for concrete roles.

All repositories follow the initialization convention where a New-like function receives the database connection to initialize the type assigning the unexported field. For example:

13type User struct {
14	conn *pgx.Conn
17func NewUser(conn *pgx.Conn) *User {
18	return &User{
19		conn: conn,
20	}

Let’s look at the implementation of the methods interacting with the databases for each repository.

User Repository

The complete example is available on GitHub.

13func (u *User) Insert(ctx context.Context, name string) (internal.User, error) {
14	const sql = `INSERT INTO users(name) VALUES ($1) RETURNING id`
16	row := u.conn.QueryRow(ctx, sql, &name)
18	var id uuid.UUID
20	if err := row.Scan(&id); err != nil {
21		return internal.User{}, fmt.Errorf("Insert %w", err)
22	}
24	return internal.User{
25		ID:   id,
26		Name: name,
27	}, nil
  • L15: Uses QueryRow to insert the new record and return the created ID.

Role Repository

The complete example is available on GitHub.

 87func (r *Role) insertPermissionTx(ctx context.Context, tx pgx.Tx, roleID uuid.UUID, ptype internal.PermissionType) (internal.Permission, error) {
 88	const sql = `INSERT INTO permissions(role_id, type) VALUES ($1, $2) RETURNING id`
 90	row := tx.QueryRow(ctx, sql, roleID, &ptype)
 92	var id uuid.UUID
 94	if err := row.Scan(&id); err != nil {
 95		return internal.Permission{}, fmt.Errorf("Insert %w", err)
 96	}
 98	return internal.Permission{
 99		ID:     id,
100		RoleID: roleID,
101		Type:   ptype,
102	}, nil

Insert and InsertPermission use an unexported method called insertPermissionTx. Writing a method like that is a common way to share the implementation of the SQL statements interacting with the database. I will show you how to do it differently to explicitly indicate the purposes of those methods directly interacting with the database.

UserRole Repository

The complete example is available on GitHub.

23func (u *UserRole) Insert(ctx context.Context, id uuid.UUID, roleIDs ...uuid.UUID) error {
24	tx, err := u.conn.Begin(ctx)
25	if err != nil {
26		return fmt.Errorf("Begin %w", err)
27	}
29	const sql = `INSERT INTO users_roles(user_id, role_id) VALUES ($1, $2)`
31	err = transaction(ctx, tx, func() error {
32		for _, roleID := range roleIDs {
33			_, err := tx.Exec(ctx, sql, &id, &roleID)
34			if err != nil {
35				return fmt.Errorf("Exec %w", err)
36			}
37		}
39		return nil
40	})
41	if err != nil {
42		return fmt.Errorf("transaction %w", err)
43	}
45	return nil

The Insert method uses the transaction function we implemented above to insert each role-user pair into the table.

48func (u *UserRole) Select(ctx context.Context, id uuid.UUID) (internal.User, error) {
49	const sql = `
51 AS user_id,
52 AS user_name,
53 AS role_id,
54 AS role_name,
55 AS permission_name,
56	P.type AS permission_type
58	users U
59	LEFT JOIN users_roles UR ON = UR.user_id
60	LEFT JOIN roles R ON = UR.role_id
61	LEFT JOIN permissions P ON = P.role_id
63 = $1
66	// ...

The Select method is more elaborated, and it uses a LEFT JOIN to select users and their corresponding roles and permissions. We implemented it this way to avoid the N+1 queries problem and make a single query that simultaneously returns all the necessary records.

Using the Repositories

The complete example is available on GitHub.

There are four programs in the cmd folder that you can use to interact with those repositories:

Feel free to interact with them on your own. Those programs won’t need to be modified after we start refactoring the code.

Reusing repositories for complex tasks

What if we receive a new requirement indicating we should implement new functionality that consists of creating a new user that copies all other roles and permissions from a different user? In other words, a way to clone an existing user.

Implementing this feature requires refactoring existing code to introduce a new DBTX interface type and the Queries pattern that separates the repositories from the dedicated types executing the SQL statements. Let’s start.

Introducing the DBTX type

The complete example is available on GitHub.

A fundamental change we have to add is the introduction of a new interface type that defines four methods typically used to execute SQL statements:

12type DBTX interface {
13	Exec(context.Context, string, ...any) (pgconn.CommandTag, error)
14	Query(context.Context, string, ...any) (pgx.Rows, error)
15	QueryRow(context.Context, string, ...any) pgx.Row
16	Prepare(context.Context, string, string) (*pgconn.StatementDescription, error)

The Queries types will use this type to support regular connections and transactions.

User repository uses userQueries type

The complete example is available on GitHub.

23func (u *User) Insert(ctx context.Context, name string) (internal.User, error) {
24	uq := userQueries{conn: u.conn}
26	return uq.Insert(ctx, name)
33func (u *userQueries) Insert(ctx context.Context, name string) (internal.User, error) {
34	const sql = `INSERT INTO users(name) VALUES ($1) RETURNING id`
36	row := u.conn.QueryRow(ctx, sql, &name)
38	var id uuid.UUID
40	if err := row.Scan(&id); err != nil {
41		return internal.User{}, fmt.Errorf("Insert %w", err)
42	}
44	return internal.User{
45		ID:   id,
46		Name: name,
47	}, nil

This change is straightforward. It requires moving the code in User.Insert to a new method: userQueries.Insert and have User call that instead.

Role repository uses roleQueries type

The complete example is available on GitHub.

 84func (r *roleQueries) Insert(ctx context.Context, name string) (internal.Role, error) {
 85	const sql = `INSERT INTO roles(name) VALUES ($1) RETURNING id`
 87	row := r.conn.QueryRow(ctx, sql, &name)
 89	var id uuid.UUID
 91	if err := row.Scan(&id); err != nil {
 92		return internal.Role{}, fmt.Errorf("Scan %w", err)
 93	}
 95	return internal.Role{
 96		ID:   id,
 97		Name: name,
 98	}, nil
101func (r *roleQueries) InsertPermission(ctx context.Context, roleID uuid.UUID, ptype internal.PermissionType) (internal.Permission, error) {
102	const sql = `INSERT INTO permissions(role_id, type) VALUES ($1, $2) RETURNING id`
104	row := r.conn.QueryRow(ctx, sql, roleID, &ptype)
106	var id uuid.UUID
108	if err := row.Scan(&id); err != nil {
109		return internal.Permission{}, fmt.Errorf("Insert %w", err)
110	}
112	return internal.Permission{
113		ID:     id,
114		RoleID: roleID,
115		Type:   ptype,
116	}, nil

This change is more elaborated. It involves moving the Role.insertPermissionTx method to roleQueries.InsertPermission and creating a new roleQueries.Insert extracting out the logic in Role.Insert to still insert permissions but separating the creation of the role itself.

UserRole repository uses userRoleQueries type

The complete example is available on GitHub.

52func (u *userRoleQueries) Insert(ctx context.Context, id uuid.UUID, roleID uuid.UUID) error {
53	// ...
63func (u *userRoleQueries) Select(ctx context.Context, id uuid.UUID) (internal.User, error) {
64	// ...

Similar to the changes to Role, we add two new methods to the new type userRoleQueries. Insert for inserting the joined records, and Select for selecting a complete User.

Implementing the UserCloner type

After making all the changes above, we can implement the new functionality, using transactions and reusing existing queries to make everything work together.

22func (u *UserCloner) Clone(ctx context.Context, id uuid.UUID, name string) (internal.User, error) {
23	var user internal.User
25	transaction(ctx, u.conn, func(tx pgx.Tx) error {
26		urq := userRoleQueries{conn: tx}
28		userFound, err := urq.Select(ctx, id)
29		if err != nil {
30			return fmt.Errorf("urq.Select(1) %w", err)
31		}
33		uq := userQueries{conn: tx}
35		userNew, err := uq.Insert(ctx, name)
36		if err != nil {
37			return fmt.Errorf("uq.Insert %w", err)
38		}
40		for _, role := range userFound.Roles {
41			if err := urq.Insert(ctx, userNew.ID, role.ID); err != nil {
42				return fmt.Errorf("urq.Insert %w", err)
43			}
44		}
46		userFound, err = urq.Select(ctx, userNew.ID)
47		if err != nil {
48			return fmt.Errorf("urq.Select(2) %w", err)
49		}
51		user = userFound
53		return nil
54	})
56	return user, nil
  • L26-L31: userRoleQueries to find the User and all their roles and permissions,
  • L33-L38: userQueries to create the new User,
  • L40-L44: userRoleQueries to insert the roles for the new User and finally
  • L46-L49: userRoleQueries to find the new User to include all the fileds and new the corresponding ids.


Reusing repositories requires planning to support transactions. Using the “Queries” pattern to separate types executing SQL statements allows us to reuse that logic in different repositories and, more importantly, enable transactions to work across multiple types. Recommended reading

Looking to sink your teeth into more Go-related topics? Make sure to read the following:

Back to posts