Setting up a Database Driver, Repository and Implementation of a transaction function for your Go App
Tags: go, database, tutorial
Published: 2024-04-22 09:21:32

## Overview
Databases are the foundation for building any app as it stores the data for implementing the architectural design. It is important to set it up in the best possible way that will allow for optimal performance of the app and also to make it seamless when writing tests. In this article, I will be taking you on a step-by-step journey on how to set up your database connection and repository. 

## Prerequisite
- Basic understanding of the Go programming language

## Getting Started
Sometimes, backend developers tend to opt for an ORM library because it provides an abstraction between your app and the database and thus there is little or no need to write raw queries and migrations which is nice. However, if you want to get better at writing queries (SQL for example), you need to learn how to build your repositories without an ORM. To open a database handle, you can either do it directly from the database driver or do it from `database/sql` with the driver passed into it. I will be opening the connection with `database/sql` together with [pgx](https://github.com/jackc/pgx/wiki/Getting-started-with-pgx) which is a driver and toolkit for PostgreSQL. Walk with me.

First things first, we start by installing pgx with the command below.
```go
go get github.com/jackc/pgx/v5
```

In a `driver.go` file, put the following code
```go
package driver

import (
	"database/sql"

	_ "github.com/jackc/pgx/v5/stdlib"
)

type DB struct {
	SQL *sql.DB
}

var dbConn = &DB{}

func ConnectSQL(dsn string)(*DB, error){
	db, err := sql.Open("pgx", dsn)

	if err := db.Ping(); err != nil {
		panic(err)
	}

	dbConn.SQL = db

	return dbConn, err
}
```
In the above snippet, we import `database/sql` and do a blank import of `github.com/jackc/pgx/v5/stdlib`. The essence of the blank import is to make the package available to the driver from `database/sql`. `sql.Open` returns a pointer to `sql.DB` so we, first of all, create a struct `DB` with a field `SQL` that can hold it and declare it into `dbConn`. The essence of having a struct is so it can hold a variety of different connections, for example, if you were to open another connection to a MongoDB database, you could store it in a field called `Mongo` in the `DB` struct. Afterward, we open a connection in the ConnectSQL function, test for an error by pinging it, and assign it to dbConn.

Then in our main.go file we call the ConnectSQL function as thus.
```go
package main

import (
	"fmt"
	"log"
	"net/http"

	"github.com/orololuwa/crispy-octo-guacamole/driver"
)
const portNumber = ":8080"

func main(){
	db, err := run()
	if (err != nil){
		log.Fatal(err)
	}
	defer db.SQL.Close()

	fmt.Println(fmt.Sprintf("Staring application on port %s", portNumber))


	srv := &http.Server{
		Addr: portNumber,
		Handler: nil,
	}

	err = srv.ListenAndServe()
	if err != nil {
		log.Fatal(err)
	}
}

func run()(*driver.DB, error){
	dbHost := "localhost"
	dbPort := "5432"
	dbName := "your_db_name"
	dbUser := "your_user"
	dbPassword := ""
	dbSSL := "disable"

	// Connecto to DB
	log.Println("Connecting to dabase")
	connectionString := fmt.Sprintf("host=%s port=%s dbname=%s user=%s password=%s sslmode=%s", dbHost, dbPort, dbName, dbUser, dbPassword, dbSSL)

	db, err := driver.ConnectSQL(connectionString)
	if err != nil {
		log.Fatal("Cannot conect to database: Dying!", err)
	}
	log.Println("Connected to database")

	return db, nil
}
```
In the `main.go` file above, we have a run function where we call the ConnectSQL function to open a connection to the database, and is passed to the main function after which we call `defer db.SQL.Close()` to close the connection when the main function is done executing.

Next, we create two files `repository.go` and `users.go` in a folder or package `repository`, but before then we have to define the user model. So in a `models.go` file, we define the model with fields as thus:
```go
package models

import "time"

type User struct {
	ID int
	FirstName string
	LastName  string
	Email     string
	Password string
	CreatedAt time.Time
	UpdatedAt time.Time
}
```
I'm guessing you know how to create migrations to populate your database, if not you can check out an amazing package I use called [fizz](https://pkg.go.dev/github.com/gobuffalo/fizz).

Next, in the `repository.go` file, we put the following code:
```go
package repository

import (
	"github.com/orololuwa/crispy-octo-guacamole/models"
)

type UserRepo interface {
	CreateAUser(user models.User) (int, error)
	GetAUser(id int) (models.User, error)
	GetAllUser() ([]models.User, error)
	UpdateAUsersName(id int, firstName, lastName string)(error)
	DeleteUserByID(id int) error
}
```
In the snippet above, the UserRepo is an interface that defines all the functions that a user repo struct should implement.

Next, in the `users.go` file, we would go ahead and create a user repo struct, a New-like function to initialize it, and receiver functions to the user repo struct that satisfies the UserRepo interface as thus:
```go
package repository

import (
	"context"
	"database/sql"
	"time"

	"github.com/orololuwa/crispy-octo-guacamole/models"
)

type user struct {
	DB *sql.DB
}

func NewUserRepo(conn *sql.DB) UserRepo {
	return &user{
		DB: conn,
	}
}

func (m *user) CreateAUser(user models.User) (int, error){
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var newId int

	query := `
			INSERT into users 
				(first_name, last_name, email, password, created_at, updated_at)
			values 
				($1, $2, $3, $4, $5, $6)
			returning id`
	
	err := m.DB.QueryRowContext(ctx, query, 
		user.FirstName, 
		user.LastName, 
		user.Email, 
		user.Password,
		time.Now(),
		time.Now(),
	).Scan(&newId)

	if err != nil {
		return 0, err
	}

	return newId, nil
}

func (m *user) GetAUser(id int) (models.User, error){
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var user models.User

	query := `
			SELECT id, first_name, last_name, email, password, created_at, updated_at
			from users
			WHERE
			id=$1
	`

	err := m.DB.QueryRowContext(ctx, query, id).Scan(
		&user.ID,
		&user.FirstName,
		&user.LastName,
		&user.Email,
		&user.Password,
		&user.CreatedAt,
		&user.UpdatedAt,
	)

	if err != nil {
		return user, err
	}

	return user, nil
}

func (m *user) GetAllUser() ([]models.User, error){
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var users = make([]models.User, 0)

	query := `
		SELECT id, first_name, last_name, email, password, created_at, updated_at
		from users
	`

	rows, err := m.DB.QueryContext(ctx, query)
	if err != nil {
		return users, err
	}

	for rows.Next(){
		var user models.User
		err := rows.Scan(
			&user.ID,
			&user.FirstName,
			&user.LastName,
			&user.Email,
			&user.Password,
			&user.CreatedAt,
			&user.UpdatedAt,
		)
		if err != nil {
			return users, err
		}
		users = append(users, user)
	}

	if err = rows.Err(); err != nil {
		return users, err
	}

	return users, nil
}

func (m *user) UpdateAUsersName(id int, firstName, lastName string)(error){
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	query := `
		UPDATE 
			users set (first_name, last_name) = ($1, $2)
		WHERE
			id = $3
	`

	_, err := m.DB.ExecContext(ctx, query, firstName, lastName, id)
	if err != nil{
		return  err
	}

	return nil
}

func (m *user) DeleteUserByID(id int) error {
    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()

    query := "DELETE FROM users WHERE id = $1"

    _, err := m.DB.ExecContext(ctx, query, id)
    if err != nil {
        return err
    }

    return nil
}
```
The standard convention across all repositories involves an initialization pattern, where a New-like function takes a database connection to initialize the type, thereby assigning it to an unexported field. Some refer to this convention as the Repository Pattern. The function **NewUserRepo** takes in a connection and initializes a new repo from the user struct, it also returns the UserRepo to ensure that the struct properly implements the functions in the UserRepo interface.
Without going too deep, the functions create, read from, update, and delete a user from the database. You'd notice that the values were being passed into the query in the format of `$1, $2`; it is a good practice to pass it as such to prevent SQL injection.

Next, in the `main.go` file, I would create a handler that will call one of the DatabaseRepo functions to test as thus:
```diff
package main

import (
+	"encoding/json"
	"fmt"
	"log"
	"net/http"

+	"github.com/go-chi/chi/v5"
	"github.com/orololuwa/crispy-octo-guacamole/driver"
+	"github.com/orololuwa/crispy-octo-guacamole/models"
+	"github.com/orololuwa/crispy-octo-guacamole/repository"
)
const portNumber = ":8080"

func main(){
-	db, err := run()
+	db, route, err := run()
	if (err != nil){
		log.Fatal(err)
	}
	defer db.SQL.Close()

	fmt.Println(fmt.Sprintf("Staring application on port %s", portNumber))


	srv := &http.Server{
		Addr: portNumber,
-		Handler: nil,
+		Handler: route,
	}

	err = srv.ListenAndServe()
	if err != nil {
		log.Fatal(err)
	}
}

-func run()(*driver.DB, error){
+func run()(*driver.DB, *chi.Mux, error){
	dbHost := "localhost"
	dbPort := "5432"
	dbName := "bookings"
	dbUser := "orololuwa"
	dbPassword := ""
	dbSSL := "disable"

	// Connecto to DB
	log.Println("Connecting to dabase")
	connectionString := fmt.Sprintf("host=%s port=%s dbname=%s user=%s password=%s sslmode=%s", dbHost, dbPort, dbName, dbUser, dbPassword, dbSSL)

	db, err := driver.ConnectSQL(connectionString)
	if err != nil {
		log.Fatal("Cannot conect to database: Dying!", err)
	}
	log.Println("Connected to database")
+	userRepo := repository.NewUserRepo(db.SQL)
+	router := chi.NewRouter()
+
+	router.Post("/user", func(w http.ResponseWriter, r *http.Request) {
+		type userBody struct {
+			FirstName string `json:"firstName"`
+			LastName string `json:"lastName"`
+			Email string `json:"email"`
+			Password string `json:"password"`
+		}
+
+		var body userBody
+		
+		err := json.NewDecoder(r.Body).Decode(&body)
+		if err != nil {
+			w.Header().Set("Content-Type", "application/json")
+			+w.WriteHeader(http.StatusInternalServerError)
+			return
+		}
+
+		user := models.User{
+			FirstName: body.FirstName,
+			LastName: body.LastName,
+			Email: body.Email,
+			Password: body.Password,
+		}
+		
+		id, err := userRepo.CreateAUser(user)
+		if err != nil {
+			w.Header().Set("Content-Type", "application/json")
+			+w.WriteHeader(http.StatusInternalServerError)
+			return
+		}
+
+		response := map[string]interface{}{"message": "user created successfully", "data": id}
+		jsonResponse, err := json.Marshal(response)
+		if err != nil {
+			http.Error(w, "Failed to marshal response", http.StatusInternalServerError)
+			return
+		}
+		w.Header().Set("Content-Type", "application/json")
+		w.WriteHeader(http.StatusOK)
+		w.Write(jsonResponse)
+	})
+
-	return db, nil
+	return db, router, nil
}
```
in the code above, we register a new router ([chi router](https://pkg.go.dev/github.com/go-chi/chi/v5@v5.0.12) is being used here), create a post handler and assign it to a `/user` route. In the handler,  we decode the data from the request's body and pass it into the `CreateAUser` function which is available to us in the userRepo. You can run your application and go ahead to test it on Postman.

![a Postman image showing the result of successfully the user route on a post method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kva99tl8qqjec0stkokb.png)


## Part 2: Implementing a transaction function
There are some queries you want to either completely succeed or fail, e.g. If you have an e-commerce system that has a wallet feature; When a user buys a product, you want to debit the wallet and reduce the product count in the inventory. These two actions in the database have to either completely succeed or completely fail.
Transaction functions are very crucial to maintaining data consistency. They have a state and all queries executed in the transaction function are tagged to the state after which you can decide to commit the results of executing those queries or rollback if there is an error with either one of the queries.
To start, we'd create a new file under the repository package and name it. you can name it whatever you want, i'd go ahead and name mine `db-repo.go`. In the file, we'd use the repository pattern to initialize a new instance, but before then we need to create an interface that declares the transaction function that should be implemented. So, we would go ahead and update the `repository.go` file with this.
```diff
package repository

import (
+	"context"
+	"database/sql"
+
	"github.com/orololuwa/crispy-octo-guacamole/models"
)

type UserRepo interface {
	CreateAUser(user models.User) (int, error)
	GetAUser(id int) (models.User, error)
	GetAllUser() ([]models.User, error)
	UpdateAUsersName(id int, firstName, lastName string)(error)
	DeleteUserByID(id int) error
}

+type DBRepo interface {
+	Transaction(ctx context.Context, operation func(context.Context, *sql.Tx) error) error 
+}
```
Then, in the `db-repo.go` file, we go ahead and put the following code.
```go
package repository

import (
	"context"
	"database/sql"
)

type dbRepo struct {
	DB *sql.DB
}

func NewDBRepo(conn *sql.DB) DBRepo {
	return &dbRepo{
		DB: conn,
	}
}

func (m *dbRepo) Transaction(ctx context.Context, operation func(context.Context, *sql.Tx) error) error {
    tx, err := m.DB.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
	
    defer func() error{
        if err != nil {
            tx.Rollback()
            return err
        }

        if err := tx.Commit(); err != nil {
            return err
        }

        return nil
    }()

    if err := operation(ctx, tx); err != nil {
        return err
    }

    return nil
}
```
In the code above, we declare a dbRepo struct and a function to initialize it and implement the DBRepo interface. The transaction function takes in a context and a function that uses the context and the transaction that was started inside the parent function to execute queries. 
The BeginTx function from *sql.DB takes in two variables, the context and an object where you can specify the isolation level of the transaction. A function is run after the transaction has been called where there is a rollback if an error occurs or the changes are committed if no error occurs.
Now we would have to refactor the UserRepo functions to accept the context and the transaction reference.
```diff
package repository

import (
	"context"
	"database/sql"

	"github.com/orololuwa/crispy-octo-guacamole/models"
)

type UserRepo interface {
-	CreateAUser(user models.User) (int, error)
-	GetAUser(id int) (models.User, error)
-	GetAllUser() ([]models.User, error)
-	UpdateAUsersName(id int, firstName, lastName string)(error)
-	DeleteUserByID(id int) error
+	CreateAUser(ctx context.Context, tx *sql.Tx, user models.User) (int, error)
+	GetAUser(ctx context.Context, tx *sql.Tx, id int) (models.User, error)
+	GetAllUser(ctx context.Context, tx *sql.Tx) ([]models.User, error)
+	UpdateAUsersName(ctx context.Context, tx *sql.Tx, id int, firstName, lastName string)(error)
+	DeleteUserByID(ctx context.Context, tx *sql.Tx, id int) error
}

type DBRepo interface {
	Transaction(ctx context.Context, operation func(context.Context, *sql.Tx) error) error 
}
```

Using the CreateAUser function to highlight the difference in the `user.go` file, we have;

```diff
-func (m *user) CreateAUser(user models.User) (int, error){
-	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
+func (m *user) CreateAUser(ctx context.Context, tx *sql.Tx, user models.User) (int, error){
+	ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
	defer cancel()

	var newId int

	query := `
			INSERT into users 
				(first_name, last_name, email, password, created_at, updated_at)
			values 
				($1, $2, $3, $4, $5, $6)
			returning id`

-	err := m.DB.QueryRowContext(ctx, query, 
+	var err error;
+	if tx != nil {
+		err = tx.QueryRowContext(ctx, query, 
+			user.FirstName, 
+			user.LastName, 
+			user.Email, 
+			user.Password,
+			time.Now(),
+			time.Now(),
+		).Scan(&newId)
+	}else{
		err = m.DB.QueryRowContext(ctx, query, 
			user.FirstName, 
			user.LastName, 
			user.Email, 
			user.Password,
			time.Now(),
			time.Now(),
		).Scan(&newId)
+	}
```
we now use the context obtained from the function variable and dependent on whether a transaction is passed, we use the tx variable to execute our queries. The full updated code for all the UserRepo functions:
```go
package repository

import (
	"context"
	"database/sql"
	"time"

	"github.com/orololuwa/crispy-octo-guacamole/models"
)

type user struct {
	DB *sql.DB
}

func NewUserRepo(conn *sql.DB) UserRepo {
	return &user{
		DB: conn,
	}
}

func (m *user) CreateAUser(ctx context.Context, tx *sql.Tx, user models.User) (int, error){
	ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
	defer cancel()

	var newId int

	query := `
			INSERT into users 
				(first_name, last_name, email, password, created_at, updated_at)
			values 
				($1, $2, $3, $4, $5, $6)
			returning id`

	var err error;
	if tx != nil {
		err = tx.QueryRowContext(ctx, query, 
			user.FirstName, 
			user.LastName, 
			user.Email, 
			user.Password,
			time.Now(),
			time.Now(),
		).Scan(&newId)
	}else{
		err = m.DB.QueryRowContext(ctx, query, 
			user.FirstName, 
			user.LastName, 
			user.Email, 
			user.Password,
			time.Now(),
			time.Now(),
		).Scan(&newId)
	}

	if err != nil {
		return 0, err
	}

	return newId, nil
}

func (m *user) GetAUser(ctx context.Context, tx *sql.Tx, id int) (models.User, error){
	ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
	defer cancel()

	var user models.User

	query := `
			SELECT (id, first_name, last_name, email, password, created_at, updated_at)
			from users
			WHERE
			id=$1
	`

	var err error
	if tx != nil {
		err = tx.QueryRowContext(ctx, query, id).Scan(
			&user.ID,
			&user.FirstName,
			&user.LastName,
			&user.Email,
			&user.Password,
			&user.CreatedAt,
			&user.UpdatedAt,
		)
	}else{
		err = m.DB.QueryRowContext(ctx, query, id).Scan(
			&user.ID,
			&user.FirstName,
			&user.LastName,
			&user.Email,
			&user.Password,
			&user.CreatedAt,
			&user.UpdatedAt,
		)
	}

	if err != nil {
		return user, err
	}

	return user, nil
}

func (m *user) GetAllUser(ctx context.Context, tx *sql.Tx) ([]models.User, error){
	ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
	defer cancel()

	var users = make([]models.User, 0)

	query := `
		SELECT (id, first_name, last_name, email, password, created_at, updated_at)
		from users
	`

	var rows *sql.Rows
	var err error

	if tx != nil {
		rows, err = tx.QueryContext(ctx, query)
	}else{
		rows, err = m.DB.QueryContext(ctx, query)
	}
	if err != nil {
		return users, err
	}

	for rows.Next(){
		var user models.User
		err := rows.Scan(
			&user.ID,
			&user.FirstName,
			&user.LastName,
			&user.Email,
			&user.Password,
			&user.CreatedAt,
			&user.UpdatedAt,
		)
		if err != nil {
			return users, err
		}
		users = append(users, user)
	}

	if err = rows.Err(); err != nil {
		return users, err
	}

	return users, nil
}

func (m *user) UpdateAUsersName(ctx context.Context, tx *sql.Tx, id int, firstName, lastName string)(error){
	ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
	defer cancel()

	query := `
		UPDATE 
			users set (first_name, last_name) = ($1, $2)
		WHERE
			id = $3
	`

	var err error
	if tx != nil{
		_, err = tx.ExecContext(ctx, query, firstName, lastName, id)
	}else{
		_, err = m.DB.ExecContext(ctx, query, firstName, lastName, id)
	}

	if err != nil{
		return  err
	}

	return nil
}

func (m *user) DeleteUserByID(ctx context.Context, tx *sql.Tx, id int) error {
    ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
    defer cancel()

    query := "DELETE FROM users WHERE id = $1"

	var err error 

	if tx != nil {
		_, err = tx.ExecContext(ctx, query, id)
	}else{
		_, err = m.DB.ExecContext(ctx, query, id)
	}

    if err != nil {
        return err
    }

    return nil
}
```
Then in your handler, you can go ahead and call the transaction function as thus:
```diff
package main

import (
+	"context"
+	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"net/http"

	"github.com/go-chi/chi/v5"
	"github.com/orololuwa/crispy-octo-guacamole/driver"
	"github.com/orololuwa/crispy-octo-guacamole/models"
	"github.com/orololuwa/crispy-octo-guacamole/repository"
)
const portNumber = ":8080"

func main(){
	db, route, err := run()
	if (err != nil){
		log.Fatal(err)
	}
	defer db.SQL.Close()

	fmt.Println(fmt.Sprintf("Staring application on port %s", portNumber))


	srv := &http.Server{
		Addr: portNumber,
		Handler: route,
	}

	err = srv.ListenAndServe()
	if err != nil {
		log.Fatal(err)
	}
}

func run()(*driver.DB, *chi.Mux, error){
	dbHost := "localhost"
	dbPort := "5432"
	dbName := "bookings"
	dbUser := "orololuwa"
	dbPassword := ""
	dbSSL := "disable"

	// Connecto to DB
	log.Println("Connecting to dabase")
	connectionString := fmt.Sprintf("host=%s port=%s dbname=%s user=%s password=%s sslmode=%s", dbHost, dbPort, dbName, dbUser, dbPassword, dbSSL)

	db, err := driver.ConnectSQL(connectionString)
	if err != nil {
		log.Fatal("Cannot conect to database: Dying!", err)
	}
	log.Println("Connected to database")

	userRepo := repository.NewUserRepo(db.SQL)
	dbRepo := repository.NewDBRepo(db.SQL)
	router := chi.NewRouter()

	router.Post("/user", func(w http.ResponseWriter, r *http.Request) {
		type userBody struct {
			FirstName string `json:"firstName"`
			LastName string `json:"lastName"`
			Email string `json:"email"`
			Password string `json:"password"`
		}

		var body userBody
		
		err := json.NewDecoder(r.Body).Decode(&body)
		if err != nil {
			w.Header().Set("Content-Type", "application/json")
			w.WriteHeader(http.StatusInternalServerError)
			return
		}

		user := models.User{
			FirstName: body.FirstName,
			LastName: body.LastName,
			Email: body.Email,
			Password: body.Password,
		}

-		id, err := userRepo.CreateAUser(user)
+		ctx := context.Background()
+		var id int

+		err = dbRepo.Transaction(ctx, func(ctx context.Context, tx *sql.Tx) error {
+			id, err = userRepo.CreateAUser(ctx, tx, user)
+			if err != nil {
+				return err
+			}
+
+			userRepo.UpdateAUsersName(ctx, tx, id, body.FirstName, "test")
+			if err != nil {
+				return err
+			}
+
+			return nil
+		})
+		
		if err != nil {
			w.Header().Set("Content-Type", "application/json")
			w.WriteHeader(http.StatusInternalServerError)
			return
		}

		response := map[string]interface{}{"message": "user created successfully", "data": id}
		jsonResponse, err := json.Marshal(response)
		if err != nil {
			http.Error(w, "Failed to marshal response", http.StatusInternalServerError)
			return
		}
		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(http.StatusOK)
		w.Write(jsonResponse)
	})

	return db, router, nil
}
```

The full code is available here on [Github](https://github.com/Orololuwa/crispy-octo-guacamole)

### What next?
You'd probably want to learn how to properly set up and test handlers. Stay tuned 🧏🏽