Hi guys, welcome back!

In the last lecture, 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.

Here's:

• Link to the full series playlist on Youtube
• And its Github repository

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.

-- 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.

-- 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.

-- 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.

❯ make sqlc
sqlc generate

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

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:

// 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 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.

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.

// 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.

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.

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.

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.

It passed!

Then the TestGetUser.

Also passed!

Now if we open the database using Table Plus, we can see there are 2 records in the users table.

OK, so the 2 functions generated by sqlc worked correctly.

Let’s try to run the whole package test!

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:

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:

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.

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.

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.

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

❯ 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:

// 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.

All passed this time. Perfect!

Handle different types of DB error

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

❯ 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.

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:

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.

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.

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

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.

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

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.

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.

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.

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.

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 and follow us on Twitter or Facebook 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. Remote or onsite in Paris/Amsterdam/London/Berlin/Barcelona with visa sponsorship.