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.

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.

Example

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 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)
14
15		return fmt.Errorf("f %w", err)
16	}
17
18	if err := tx.Commit(ctx); err != nil {
19		return fmt.Errorf("Commit %w", err)
20	}
21
22	return nil
23}

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
15}
16
17func NewUser(conn *pgx.Conn) *User {
18	return &User{
19		conn: conn,
20	}
21}

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

User Repository

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

L15: Uses QueryRow to insert the new record and return the created ID.

Role Repository

 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`
 89
 90	row := tx.QueryRow(ctx, sql, roleID, &ptype)
 91
 92	var id uuid.UUID
 93
 94	if err := row.Scan(&id); err != nil {
 95		return internal.Permission{}, fmt.Errorf("Insert %w", err)
 96	}
 97
 98	return internal.Permission{
 99		ID:     id,
100		RoleID: roleID,
101		Type:   ptype,
102	}, nil
103}

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

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	}
28
29	const sql = `INSERT INTO users_roles(user_id, role_id) VALUES ($1, $2)`
30
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		}
38
39		return nil
40	})
41	if err != nil {
42		return fmt.Errorf("transaction %w", err)
43	}
44
45	return nil
46}

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 = `
50SELECT
51	U.id AS user_id,
52	U.name AS user_name,
53	R.id AS role_id,
54	R.name AS role_name,
55	P.id AS permission_name,
56	P.type AS permission_type
57FROM
58	users U
59	LEFT JOIN users_roles UR ON U.id = UR.user_id
60	LEFT JOIN roles R ON R.id = UR.role_id
61	LEFT JOIN permissions P ON R.id = P.role_id
62WHERE
63	U.id = $1
64`
65
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

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

cmd/user: to insert new users,
cmd/role: to insert new roles and their corresponding permissions,
cmd/user_role_insert: to insert users using concrete roles and
cmd/user_role: to select a user with all their roles and permissions.

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

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

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

`User` repository uses `userQueries` type

23func (u *User) Insert(ctx context.Context, name string) (internal.User, error) {
24	uq := userQueries{conn: u.conn}
25
26	return uq.Insert(ctx, name)
27}

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

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

 84func (r *roleQueries) Insert(ctx context.Context, name string) (internal.Role, error) {
 85	const sql = `INSERT INTO roles(name) VALUES ($1) RETURNING id`
 86
 87	row := r.conn.QueryRow(ctx, sql, &name)
 88
 89	var id uuid.UUID
 90
 91	if err := row.Scan(&id); err != nil {
 92		return internal.Role{}, fmt.Errorf("Scan %w", err)
 93	}
 94
 95	return internal.Role{
 96		ID:   id,
 97		Name: name,
 98	}, nil
 99}
100
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`
103
104	row := r.conn.QueryRow(ctx, sql, roleID, &ptype)
105
106	var id uuid.UUID
107
108	if err := row.Scan(&id); err != nil {
109		return internal.Permission{}, fmt.Errorf("Insert %w", err)
110	}
111
112	return internal.Permission{
113		ID:     id,
114		RoleID: roleID,
115		Type:   ptype,
116	}, nil
117}

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

52func (u *userRoleQueries) Insert(ctx context.Context, id uuid.UUID, roleID uuid.UUID) error {
53	// ...
54}

63func (u *userRoleQueries) Select(ctx context.Context, id uuid.UUID) (internal.User, error) {
64	// ...
65}

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
24
25	transaction(ctx, u.conn, func(tx pgx.Tx) error {
26		urq := userRoleQueries{conn: tx}
27
28		userFound, err := urq.Select(ctx, id)
29		if err != nil {
30			return fmt.Errorf("urq.Select(1) %w", err)
31		}
32
33		uq := userQueries{conn: tx}
34
35		userNew, err := uq.Insert(ctx, name)
36		if err != nil {
37			return fmt.Errorf("uq.Insert %w", err)
38		}
39
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		}
45
46		userFound, err = urq.Select(ctx, userNew.ID)
47		if err != nil {
48			return fmt.Errorf("urq.Select(2) %w", err)
49		}
50
51		user = userFound
52
53		return nil
54	})
55
56	return user, nil
57}

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.

Conclusion

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