How to handle DB errors in Golang correctly
Tags: go, database, tutorial, webdev
Published: 2021-01-01 10:12:01


Hi guys, welcome back!

In the [last lecture](https://dev.to/techschoolguru/add-users-table-with-unique-foreign-key-constraints-in-postgresql-1i29), we’ve added a new users table to the database schema. Today, let’s update our golang code to work with this table.

And while doing so, we’re also gonna learn how to correctly handle some specific errors returned by Postgres.

{% youtube mJ8b5GcvoxQ %}

Here's:

- Link to the [full series playlist on Youtube](https://bit.ly/backendmaster)
- And its [Github repository](https://github.com/techschool/simplebank)

Alright, let’s start!

## Generate code to create and get user

First I’m gonna create a new file `user.sql` inside the `db/query` folder. In this file, we will write 2 SQL queries to create and get users.

They should be similar to the ones we used to create and get accounts, so I’m gonna copy these 2 queries from the `account.sql` file and paste them to the `user.sql` file.

```sql
-- name: CreateAccount :one
INSERT INTO accounts (
  owner,
  balance,
  currency
) VALUES (
  $1, $2, $3
) RETURNING *;

-- name: GetAccount :one
SELECT * FROM accounts
WHERE id = $1 LIMIT 1;
```

Then let’s change the function name to `CreateUser`, the table name to `users`, and the field names are: `username`, `hashed_password`, `full_name`, and `email`.

We don’t have to specified the `password_changed_at` and `created_at` fields because they will be automatically filled with default value by Postgres.

There are 4 input fields, so we have to add 1 more parameter to the value list.

```sql
-- name: CreateUser :one
INSERT INTO users (
  username,
  hashed_password,
  full_name,
  email
) VALUES (
  $1, $2, $3, $4
) RETURNING *;
```

Next, the `GetAccount` function should be changed to `GetUser`, and the query is `SELECT FROM users`.

Note that we don’t have an ID column in the `users` table. Its primary key is `username`, so here we should get user by `username` instead.

```sql
-- name: GetUser :one
SELECT * FROM users
WHERE username = $1 LIMIT 1;
```

Alright, now the queries are completed, let’s open the terminal and run this command to generate golang codes for them.

```bash
❯ make sqlc
sqlc generate
```

Now back to visual studio code. In the `db/sqlc/models.go` file, a new `User` struct has been added:

```sql
type User struct {
    Username          string    `json:"username"`
    HashedPassword    string    `json:"hashed_password"`
    FullName          string    `json:"full_name"`
    Email             string    `json:"email"`
    PasswordChangedAt time.Time `json:"password_changed_at"`
    CreatedAt         time.Time `json:"created_at"`
}
```

And there’s a new file `db/sqlc/user.sql.go` that contains 2 functions to create and get user from the database:

```go
// Code generated by sqlc. DO NOT EDIT.
// source: user.sql

package db

import (
    "context"
)

const createUser = `-- name: CreateUser :one
INSERT INTO users (
  username,
  hashed_password,
  full_name,
  email
) VALUES (
  $1, $2, $3, $4
) RETURNING username, hashed_password, full_name, email, password_changed_at, created_at
`

type CreateUserParams struct {
    Username       string `json:"username"`
    HashedPassword string `json:"hashed_password"`
    FullName       string `json:"full_name"`
    Email          string `json:"email"`
}

func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error) {
    row := q.db.QueryRowContext(ctx, createUser,
        arg.Username,
        arg.HashedPassword,
        arg.FullName,
        arg.Email,
    )
    var i User
    err := row.Scan(
        &i.Username,
        &i.HashedPassword,
        &i.FullName,
        &i.Email,
        &i.PasswordChangedAt,
        &i.CreatedAt,
    )
    return i, err
}

const getUser = `-- name: GetUser :one
SELECT username, hashed_password, full_name, email, password_changed_at, created_at FROM users
WHERE username = $1 LIMIT 1
`

func (q *Queries) GetUser(ctx context.Context, username string) (User, error) {
    row := q.db.QueryRowContext(ctx, getUser, username)
    var i User
    err := row.Scan(
        &i.Username,
        &i.HashedPassword,
        &i.FullName,
        &i.Email,
        &i.PasswordChangedAt,
        &i.CreatedAt,
    )
    return i, err
}
```

Next, we will write tests for these 2 functions to make sure they’re working as expected.

## Write tests for the generated functions

We’ve already learned how to do that in [lecture 5](https://dev.to/techschoolguru/write-go-unit-tests-for-db-crud-with-random-data-53no) of the course.

So I’m gonna create a new file `user_test.go` file in this `db/sqlc` folder. Then I will copy the tests that we wrote for the create and get account function and paste them to this file.

Then let’s change the function name to `createRandomUser`. The argument variable will be of type `CreateUserParams`.

The first field is `username`, which we can leave as a random owner.

The second field is `hashed_password`. Normally we will have to generate a random password and hash it using `bcrypt`, but that would be done in another lecture. For now, I’m just gonna use a simple text value `"secret"` here.

```go
func createRandomUser(t *testing.T) User {
    arg := CreateUserParams{
        Username:       util.RandomOwner(),
        HashedPassword: "secret",
        FullName:       util.RandomOwner(),
        Email:          util.RandomEmail(),
    }

    ...
}
```

The next field is `full_name`. We can use the same `util.RandomOwner()` function for it.

And the last field is `email`. We will need to add a new `RandomEmail()` function to the `util` package. So let’s open the `util/random.go` file and implement it.

```go
// RandomEmail generates a random email
func RandomEmail() string {
    return fmt.Sprintf("%s@email.com", RandomString(6))
}
```

This `RandomEmail` function will return a string, which should be a randomly generated email. I’m gonna keep it simple here by using `fmt.Sprintf` to generate an email of the form: some random string at `email.com`.

Alright, now go back to the test. We have to change this function call to `testQueries.CreateUser()`, and the output result should be a `user` object.

```go
func createRandomUser(t *testing.T) User {
    ...

    user, err := testQueries.CreateUser(context.Background(), arg)
    require.NoError(t, err)
    require.NotEmpty(t, user)

    require.Equal(t, arg.Username, user.Username)
    require.Equal(t, arg.HashedPassword, user.HashedPassword)
    require.Equal(t, arg.FullName, user.FullName)
    require.Equal(t, arg.Email, user.Email)
    require.NotZero(t, user.CreatedAt)
    require.True(t, user.PasswordChangedAt.IsZero())

    return user
}
```

We require this `user` to be not empty. Then we compare each field of the output user with those of the input argument:

- `arg.Username` should be equal to `user.Username`
- `arg.HashedPassword` should be equal to `user.HashedPassword`
- `arg.Fullname` should be equal to `user.Fullname`
- `arg.Email` should be equal to `user.Email`

Then the `user.CreatedAt` field should be not zero, since we expect the database to fill it with the current timestamp.

The last field we have to check is `user.PasswordChangedAt`. When the user is first created, we expect this field to be filled with a default value of a zero timestamp. The `IsZero()` function is used for checking this condition.

Then at the end, we should return the created `user` to the caller. OK, now let’s use this function in the tests!

First, for the `TestCreateUser`, we simply call createRandomUser with the input `testing.T` object.

```go
func TestCreateUser(t *testing.T) {
    createRandomUser(t)
}
```

The next test is `GetUser`. We call the `createRandomUser()` function to create a random `user1`. Then we call `testQueries.GetUser` to fetch the user with `user1.Username` from the database.

```go
func TestGetUser(t *testing.T) {
    user1 := createRandomUser(t)
    user2, err := testQueries.GetUser(context.Background(), user1.Username)
    require.NoError(t, err)
    require.NotEmpty(t, user2)

    require.Equal(t, user1.Username, user2.Username)
    require.Equal(t, user1.HashedPassword, user2.HashedPassword)
    require.Equal(t, user1.FullName, user2.FullName)
    require.Equal(t, user1.Email, user2.Email)
    require.WithinDuration(t, user1.PasswordChangedAt, user2.PasswordChangedAt, time.Second)
    require.WithinDuration(t, user1.CreatedAt, user2.CreatedAt, time.Second)
}
```

The output `user2` of this query should match the input `user1`. So we compare each field of them to make sure they’re equal: `username`, `hashed_password`, `full_name`, and `email`.

For a timestamp field like `created_at` and `password_changed_at`,  I often use `require.WithinDuration` to compare the values because sometimes there might be a very small difference.

Alright, the tests are completed. Let’s run them!

First the `TestCreateUser`.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/x11o9hyjt7nb3ltduc21.jpg)

It passed!

Then the `TestGetUser`.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/dsg1k749e08mriuf1vsz.jpg)

Also passed!

Now if we open the database using [Table Plus](https://tableplus.com/), we can see there are 2 records in the `users` table.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/c0st7wzuhzp0mbtrv3ka.jpg)

OK, so the 2 functions generated by [sqlc]() worked correctly.

Let’s try to run the whole package test!

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/1j2u7w1qosbb9v99xehc.jpg)

This time, there are many tests of the `accounts CRUD` function failed. And the reason is because of the `foreign key constraint violation`.

This is expected because at the time these tests were written, the `foreign key constraint` for the `owner` field didn’t exist yet.

## Fix the failed tests

As you can see in the `db/sqlc/account_test.go` file, we’re just generating a random owner, and it doesn’t link to any existed users:

```go
func createRandomAccount(t *testing.T) Account {
    arg := CreateAccountParams{
        Owner:    util.RandomOwner(),
        Balance:  util.RandomMoney(),
        Currency: util.RandomCurrency(),
    }

    account, err := testQueries.CreateAccount(context.Background(), arg)
    require.NoError(t, err)
    require.NotEmpty(t, account)

    require.Equal(t, arg.Owner, account.Owner)
    require.Equal(t, arg.Balance, account.Balance)
    require.Equal(t, arg.Currency, account.Currency)

    require.NotZero(t, account.ID)
    require.NotZero(t, account.CreatedAt)

    return account
}

func TestCreateAccount(t *testing.T) {
    createRandomAccount(t)
}
```

In order to fix this, we have to create a user in the database first. Then, instead of a random owner, we will use the created user’s username as the account owner:

```go
func createRandomAccount(t *testing.T) Account {
    user := createRandomUser(t)

    arg := CreateAccountParams{
        Owner:    user.Username,
        Balance:  util.RandomMoney(),
        Currency: util.RandomCurrency(),
    }

    ...
}
```

OK, now it should work. Let’s rerun the package tests.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/gp80glx9jpk322h6a8rc.jpg)

All passed this time. Excellent!

But note that this only runs all tests in the `db` package. We also have more tests in the `api` package.

So let’s open the terminal and run `make test` to run all of them.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/fngxfredborjpiykhznz.jpg)

We’ve got an error here because our `MockStore` doesn’t implement the `db.Store` interface. It’s missing some functions’ implementation.

That’s because when we run `make sqlc` before to generate codes, 2 new functions: `CreateUser` and `GetUser` has been added to the `Querier` interface. And the `Querier` interface is a part of the `db.Store` interface.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/pafstf256bcjjsjgh8s0.jpg)

To fix this, we have to regenerate the code for the `MockStore`:

```go
❯ make mock
mockgen -package mockdb -destination db/mock/store.go github.com/techschool/simplebank/db/sqlc Store
```

After this, we can see in the `db/mock/store.go` file, the implementation of the `GetUser` and `CreateUser` function has been added:

```go
// CreateUser mocks base method
func (m *MockStore) CreateUser(arg0 context.Context, arg1 db.CreateUserParams) (db.User, error) {
    m.ctrl.T.Helper()
    ret := m.ctrl.Call(m, "CreateUser", arg0, arg1)
    ret0, _ := ret[0].(db.User)
    ret1, _ := ret[1].(error)
    return ret0, ret1
}

// CreateUser indicates an expected call of CreateUser
func (mr *MockStoreMockRecorder) CreateUser(arg0, arg1 interface{}) *gomock.Call {
    mr.mock.ctrl.T.Helper()
    return mr.mock.ctrl.RecordCallWithMethodType(mr.mock, "CreateUser", reflect.TypeOf((*MockStore)(nil).CreateUser), arg0, arg1)
}

// GetUser mocks base method
func (m *MockStore) GetUser(arg0 context.Context, arg1 string) (db.User, error) {
    m.ctrl.T.Helper()
    ret := m.ctrl.Call(m, "GetUser", arg0, arg1)
    ret0, _ := ret[0].(db.User)
    ret1, _ := ret[1].(error)
    return ret0, ret1
}

// GetUser indicates an expected call of GetUser
func (mr *MockStoreMockRecorder) GetUser(arg0, arg1 interface{}) *gomock.Call {
    mr.mock.ctrl.T.Helper()
    return mr.mock.ctrl.RecordCallWithMethodType(mr.mock, "GetUser", reflect.TypeOf((*MockStore)(nil).GetUser), arg0, arg1)
}
```

So now the api unit tests should work. Let’s rerun `make test` in the terminal.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/84gn4j25c1kuw4kyz74l.jpg)

All passed this time. Perfect!

## Handle different types of DB error

Alright, now let’s try to run the HTTP server.

```bash
❯ make server
go run main.go
[GIN-debug] [WARNING] Creating an Engine instance with the Logger and Recovery middleware already attached.

[GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production.
 - using env:   export GIN_MODE=release
 - using code:  gin.SetMode(gin.ReleaseMode)

[GIN-debug] POST   /users                    --> github.com/techschool/simplebank/api.(*Server).createUser-fm (3 handlers)
[GIN-debug] POST   /accounts                 --> github.com/techschool/simplebank/api.(*Server).createAccount-fm (3 handlers)
[GIN-debug] GET    /accounts/:id             --> github.com/techschool/simplebank/api.(*Server).getAccount-fm (3 handlers)
[GIN-debug] GET    /accounts                 --> github.com/techschool/simplebank/api.(*Server).listAccounts-fm (3 handlers)
[GIN-debug] POST   /transfers                --> github.com/techschool/simplebank/api.(*Server).createTransfer-fm (3 handlers)
[GIN-debug] Listening and serving HTTP on 0.0.0.0:8080
```

Then open Postman to test the existing API to create a new account.

First, I’m gonna try creating an account for an owner that doesn’t exist in the database.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ud19z258t1bsg0msjwv5.jpg)

As you can see, we’ve got an error because the `foreign key constraint` for the account owner is violated. This is expected, since there’s no account with this username in the database yet.

However, the HTTP response status code is `500 Internal Server Error`. This status is not very suitable in this case since the fault is on the client’s side because it’s trying to create a new account for an inexisted user.

It’s better to return something like `403 Forbiden` status instead. In order to do that, we have to handle the error returned by Postgres.

Here in the create account handler of `api/account.go` file, after calling `store.CreateAccount`, if an error is returned, we will try to convert it to `pq.Error` type, and assign the result to `pqErr` variable:

```go
func (server *Server) createAccount(ctx *gin.Context) {
    ...

    account, err := server.store.CreateAccount(ctx, arg)
    if err != nil {
        if pqErr, ok := err.(*pq.Error); ok {
            log.Println(pqErr.Code.Name())
        }
        ctx.JSON(http.StatusInternalServerError, errorResponse(err))
        return
    }

    ctx.JSON(http.StatusOK, account)
}
```

If the conversion is OK, let’s print out a log here to see this error’s code name.

Now I will restart the server. Then go back to Postman and resend the same request.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/78hwincf1apo4btsdnm5.jpg)

Now in the log, we can see the error’s code name is `foreign_key_violation`. We can use it to classify the error later.

But before that, I’m gonna try to create a new account for an existed user. Let’s copy this username from the `users` table, and paste it to this owner field’s value, then send the request.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/3vw34cgfrm0jbf2qwzsz.jpg)

This time the request is successful, and a new account is created. But what if we send this same request a second time?

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/iulrbpdg6ptss6wnh42u.jpg)

Now we’ve got another error: duplicate key value violates unique constraints `owner_currency_key`. That’s because we’re trying to create more than 1 account with the same currency for the same owner.

In this case, we also want to return status `403 Forbidden` instead of `500 Internal Server Error`. So let’s look at the log to see its error code name.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/7k5redac229k0k0gkc6c.jpg)

It’s `unique_violation`. OK, now with this information, let’s go back to the code and update it.

```go
func (server *Server) createAccount(ctx *gin.Context) {
    ...

    account, err := server.store.CreateAccount(ctx, arg)
    if err != nil {
        if pqErr, ok := err.(*pq.Error); ok {
            switch pqErr.Code.Name() {
            case "foreign_key_violation", "unique_violation":
                ctx.JSON(http.StatusForbidden, errorResponse(err))
                return
            }
        }
        ctx.JSON(http.StatusInternalServerError, errorResponse(err))
        return
    }

    ctx.JSON(http.StatusOK, account)
}
```

Here I will use a simple `switch case` statement to check the error code name. In case it is `foreign_key_violation` or `unique_violation`, we will send this error response with `http.StatusForbidden` status code.

Alright, let’s restart the server! Then resend the request.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/z4yline1s616wvndix7d.jpg)

As you can see, the returned status code is now `403 Forbidden` as we expected.

Let’s try changing the owner field’s value to an inexisted username as before, and resend the request one more time.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/zilu2dxz7hzuety1rvvs.jpg)

We also get a `403 Forbidden` status in this case. So it worked!

Last but not least, I’m gonna try to create a second account for this user, but with a different currency, such as `EUR`.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/tw8ifn4yrj1fxxn2n92y.jpg)

The request is successful. And in the database, we can see there are 2 new accounts linking to the same username, 1 account is `EUR` and the other is `USD`.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/mutpynklb3fahus5qq7a.jpg)

So that brings us to the end of this lecture. I hope you have learned something useful.

Thank you for reading and see you in the next one!

---

If you like the article, please subscribe to our [Youtube channel](http://bit.ly/techschoolguru) and follow us on [Twitter](https://twitter.com/techschoolguru) or [Facebook](https://www.facebook.com/techschool.guru) for more tutorials in the future.

---
If you want to join me on my current amazing team at Voodoo, check out [our job openings here](https://bit.ly/voodoojobs). Remote or onsite in Paris/Amsterdam/London/Berlin/Barcelona with visa sponsorship.