Обработка ошибок postgresql golang

For information about how to interpret errors from github.com/lib/pq, see http://godoc.org/github.com/lib/pq#Error.

Here is what I do:

// ShowError sends an appropriate error message.
func ShowError(w http.ResponseWriter, r *http.Request, err error) {
    switch e := err.(type) {
    case *pq.Error:
        switch e.Code {
        case "23502":
            // not-null constraint violation
            http.Error(w, fmt.Sprint("Some required data was left out:nn", e.Message), http.StatusForbidden)
            return

        case "23503":
            // foreign key violation
            switch r.Method {
            case "DELETE":
                http.Error(w, fmt.Sprint("This record can’t be deleted because another record refers to it:nn", e.Detail), http.StatusForbidden)
                return
            }

        case "23505":
            // unique constraint violation
            http.Error(w, fmt.Sprint("This record contains duplicated data that conflicts with what is already in the database:nn", e.Detail), http.StatusForbidden)
            return

        case "23514":
            // check constraint violation
            http.Error(w, fmt.Sprint("This record contains inconsistent or out-of-range data:nn", e.Message), http.StatusForbidden)
            return

        default:
            msg := e.Message
            if d := e.Detail; d != "" {
                msg += "nn" + d
            }
            if h := e.Hint; h != "" {
                msg += "nn" + h
            }
            http.Error(w, msg, http.StatusInternalServerError)
            return
        }

    case *strconv.NumError:
        http.Error(w, fmt.Sprintf(`"%s" is not a valid number.`, e.Num), http.StatusBadRequest)
        return

    default:
        switch err {
        case sql.ErrNoRows:
            http.NotFound(w, r)
            return
        }
    }

    http.Error(w, err.Error(), http.StatusInternalServerError)
}

Error Handling

pgx uses Go 1.13+ style error wrapping. That means that errors.Is() and errors.As() should be used instead of directly testing an error for equality or attempting a type assertion.

If an error is returned from PostgreSQL then underlying error type will be a *pgconn.PgError.

// Be sure the correct package is imported, this will not work if the old, standalone pgconn repo is imported instead.
import "github.com/jackc/pgx/v5/pgconn"

// ...

err = conn.QueryRow(context.Background(), "select 1 +").Scan(&greeting)
if err != nil {
  var pgErr *pgconn.PgError
  if errors.As(err, &pgErr) {
    fmt.Println(pgErr.Message) // => syntax error at end of input
    fmt.Println(pgErr.Code) // => 42601
  }
}

loljapanes,
errorHandler просто для примера написал. Можно сразу после if обрабатывать ошибку, а можно вынести обработку ошибок в отдельную функцию и там формировать вывод для пользователя.

Что касается первого вопроса, действительно, советуют обрабатывать через «As».

Тогда возвращаясь к первоначальному вопросу, основная проблема тут в magic number «23505». Если в библиотеке нет списка человекочитаемых констант с номерами ошибок (я не нашел), можно завести свой. Например:

package pgerrcode

const (
        UniqueConstrintViolation = "23505"
        AnotherError = "another code"
        ...
)

И далее в программе сравнивать по названию констант:

if err != nil {
    var pgErr *pgconn.PgError
    if errors.As(err, &pgErr) {
      if pgErr.Code == pgerrorcode.UniqueConstrintViolation {
        w.Write([]byte("Email is already exists"))
        return
      }
    }
  }

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.

Almost all operations with database/sql types return an error as the last
value. You should always check these errors, never ignore them.

There are a few places where error behavior is special-case, or there’s
something additional you might need to know.

Errors From Iterating Resultsets

Consider the following code:

for rows.Next() {
	// ...
}
if err = rows.Err(); err != nil {
	// handle the error here
}

The error from rows.Err() could be the result of a variety of errors in the
rows.Next() loop. The loop
might exit for some reason other than finishing the loop normally, so you always
need to check whether the loop terminated normally or not. An abnormal
termination automatically calls rows.Close(), although it’s harmless to call it
multiple times.

Errors From Closing Resultsets

You should always explicitly close a sql.Rows if you exit the loop
prematurely, as previously mentioned. It’s auto-closed if the loop exits
normally or through an error, but you might mistakenly do this:

for rows.Next() {
	// ...
	break; // whoops, rows is not closed! memory leak...
}
// do the usual "if err = rows.Err()" [omitted here]...
// it's always safe to [re?]close here:
if err = rows.Close(); err != nil {
	// but what should we do if there's an error?
	log.Println(err)
}

The error returned by rows.Close() is the only exception to the general rule
that it’s best to capture and check for errors in all database operations. If
rows.Close() returns an error, it’s unclear what you should do.
Logging the error message or panicing might be the only sensible thing,
and if that’s not sensible, then perhaps you should just ignore the error.

Errors From QueryRow()

Consider the following code to fetch a single row:

var name string
err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)
if err != nil {
	log.Fatal(err)
}
fmt.Println(name)

What if there was no user with id = 1? Then there would be no row in the
result, and .Scan() would not scan a value into name. What happens then?

Go defines a special error constant, called sql.ErrNoRows, which is returned
from QueryRow() when the result is empty. This needs to be handled as a
special case in most circumstances. An empty result is often not considered an
error by application code, and if you don’t check whether an error is this
special constant, you’ll cause application-code errors you didn’t expect.

Errors from the query are deferred until Scan() is called, and then are
returned from that. The above code is better written like this instead:

var name string
err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)
if err != nil {
	if err == sql.ErrNoRows {
		// there were no rows, but otherwise no error occurred
	} else {
		log.Fatal(err)
	}
}
fmt.Println(name)

One might ask why an empty result set is considered an error. There’s nothing
erroneous about an empty set. The reason is that the QueryRow() method needs
to use this special-case in order to let the caller distinguish whether
QueryRow() in fact found a row; without it, Scan() wouldn’t do anything and
you might not realize that your variable didn’t get any value from the database
after all.

You should only run into this error when you’re using QueryRow(). If you
encounter this error elsewhere, you’re doing something wrong.

Identifying Specific Database Errors

It can be tempting to write code like the following:

rows, err := db.Query("SELECT someval FROM sometable")
// err contains:
// ERROR 1045 (28000): Access denied for user 'foo'@'::1' (using password: NO)
if strings.Contains(err.Error(), "Access denied") {
	// Handle the permission-denied error
}

This is not the best way to do it, though. For example, the string value might
vary depending on what language the server uses to send error messages. It’s
much better to compare error numbers to identify what a specific error is.

The mechanism to do this varies between drivers, however, because this isn’t
part of database/sql itself. In the MySQL driver that this tutorial focuses
on, you could write the following code:

if driverErr, ok := err.(*mysql.MySQLError); ok { // Now the error number is accessible directly
	if driverErr.Number == 1045 {
		// Handle the permission-denied error
	}
}

Again, the MySQLError type here is provided by this specific driver, and the
.Number field may differ between drivers. The value of the number, however,
is taken from MySQL’s error message, and is therefore database specific, not
driver specific.

This code is still ugly. Comparing to 1045, a magic number, is a code smell.
Some drivers (though not the MySQL one, for reasons that are off-topic here)
provide a list of error identifiers. The Postgres pq driver does, for example, in
error.go. And there’s an
external package of MySQL error numbers maintained by
VividCortex. Using such a list, the
above code is better written thus:

if driverErr, ok := err.(*mysql.MySQLError); ok {
	if driverErr.Number == mysqlerr.ER_ACCESS_DENIED_ERROR {
		// Handle the permission-denied error
	}
}

Handling Connection Errors

What if your connection to the database is dropped, killed, or has an error?

You don’t need to implement any logic to retry failed statements when this
happens. As part of the connection pooling in
database/sql, handling failed connections is built-in. If you execute a query
or other statement and the underlying connection has a failure, Go will reopen a
new connection (or just get another from the connection pool) and retry, up to
10 times.

There can be some unintended consequences, however. Some types of errors may be
retried when other error conditions happen. This might also be driver-specific.
One example that has occurred with the MySQL driver is that using KILL to
cancel an undesired statement (such as a long-running query) results in the
statement being retried up to 10 times.

Previous: Using Prepared Statements
Next: Working with NULLs