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 touser.Username
-
arg.HashedPassword
should be equal touser.HashedPassword
-
arg.Fullname
should be equal touser.Fullname
-
arg.Email
should be equal touser.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.
Top comments (2)
Grate course, absolutely enjoyed it from the beginning to the ending, thanks for your work man
You were exposing the technical details to the client, not to mention that the handler is tightly coupled with the database implementation by checking its internal error types