Lesson 18. How to handle DB errors in Golang correctly

By /

This tutorial demonstrates how to properly handle PostgreSQL database errors in Golang applications, focusing on foreign key violations and unique constraint violations. We’ll update our banking system to work with the new users table and implement proper error handling for better API responses.

When your application interacts with a database, various errors can occur. Returning a generic 500 Internal Server Error for all database issues is not ideal. Some errors, like foreign key violations or unique constraint violations, are client-side issues and should be communicated with a more appropriate status code like 403 Forbidden.

Goal: Update the Go code to work with the new users table and handle specific PostgreSQL errors gracefully, returning meaningful HTTP status codes.

Prerequisites

  • Completed users table implementation from previous tutorial
  • Understanding of PostgreSQL constraints
  • Golang error handling concepts
  • SQLC for code generation

Understanding the Problem

After adding a users table and a foreign key constraint from accounts.owner to users.username, your existing API might break.

  • Scenario: A client tries to create an account for a user that doesn’t exist.
  • PostgreSQL Response: Returns a foreign_key_violation error.
  • Old Behavior: Your Go code would catch this error and return a 500 Internal Server Error.
  • Desired Behavior: Return a 403 Forbidden status code, indicating the client’s request was invalid.

Step 1: Create User SQL Queries

Step 1: Create db/query/user.sql with basic CRUD operations, in this file we add 2SQL queries to create and get user, this is similar to account.sql

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

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

Note:

  • password_changed_at and created_at are filled automatically by Postgres defaults.
  • Primary key = username.

Steps 2: Generate Go Code:

make sqlc

This generates db/sqlc/user.sql.go: with

  • New User struct in db/sqlc/models.go models.go → new User struct
  • CreateUser and GetUser functions in new file called db/sqlc/user.sql.go. user.sql.go → CreateUser & GetUser functions

We can see that in models.go, a new User struct has been added.

#other model struct ..
type User struct {
	Username         string    `json:"username"`
	HashedPassword   string    `json:"hashed_password"`
	FullName         string    `json:"full_name"`
	Email            string    `json:"email"`
	PasswordChangeAt time.Time `json:"password_change_at"`
	CreatedAt        time.Time `json:"created_at"`
}

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

Step 3: Write Unit Tests for User Queries

Create db/sqlc/user_test.go and we also need to add a util.RandomEmail() function to util/random.go.

package db

import (
	"context"
	"testing"
	"time"

	"github.com/ngodup/simplebank/util"
	"github.com/stretchr/testify/require"
)

func createRandomUser(t *testing.T) User {
	arg := CreateUserParams{
		Username:       util.RandomOwner(),
		HashedPassword: "secret", // later we'll hash properly
		FullName:       util.RandomOwner(),
		Email:          util.RandomEmail(),
	}
	user, err := testQueries.CreateUser(context.Background(), args)
	require.NoError(t, err)
	require.NotEmpty(t, user)

	require.Equal(t, args.Username, user.Username)
	require.Equal(t, args.HashedPassword, user.HashedPassword)
	require.Equal(t, args.FullName, user.FullName)
	require.Equal(t, args.Email, user.Email)

	require.True(t, user.PasswordChangeAt.IsZero())
	require.NotZero(t, user.CreatedAt)

	return user
}

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

// TestGetUser test retrieve user match the orignal username
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.PasswordChangeAt, user2.PasswordChangeAt, time.Second)
	require.WithinDuration(t, user1.CreatedAt, user2.CreatedAt, time.Second)

	require.Equal(t, user1.Username, user2.Username)

}

Write TestCreateUser and TestGetUser.

  1. TestCreateUser: asserts that the returned user object has the correct data and that CreatedAt is not zero, while PasswordChangedAt is zero.
  2. TestGetUser: creates a user, fetches it, and asserts that the data matches. Use require.WithinDuration for timestamp comparisons.

In user_test.go, create a helper function createRandomUser to generate a new user for testing.

Step 4: Add Random Email Utility

Update util/random.go:

import (
    "fmt"
    "math/rand"
    "strings"
    "time"
)

// Add this function
func RandomEmail() string {
    return fmt.Sprintf("%[email protected]", RandomString(6))
}

Step 5: Fix Account Tests

By clicking on run test on TestCreateUser and TestGetUser: Run the new user tests to confirm they both pass. Now if we open table plus we can use two new records in users table. The two function generated by sqlc worked correctly and running whole package test in user_test.go we got Error. There are many tests of the accounts CRUD funciton failed reason is the because of the foreign key constraint violation.

  1. foreign_key_violation: Triggered by a missing user.
  2. unique_violation: Triggered by trying to create a second account with the same (owner, currency).

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

Fix Existing Account Tests:

Once we know the error, we can fixed it. As in db/sqlc/acount_test.go function createRandomAccount we are using util.RandomOwner() function to generate random Owner and it doesn’t link to any existed users. Now let create user from createRandomUser function and replace random owner with user.Username.

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

	args := CreateAccountParams{
		Owner:    util.RandomOwner, 
   //Need to replace it with user
		Balance:  util.RandomMoney(),
		Currency: util.RandomCurrency(),
	}
func createRandomAccount(t *testing.T) Account {
	user := createRandomUser(t)

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

	... other code 
}

Now rerun our user_test.go run package test, it will successfully pass the test but note that this only runs all tests in the db packages. But we also have more tests in the api packages and let open the terminal and run make test to run all of them.

make test

We can see an error from this command because our MockStore doesn’t implement the db.Store interface. It’s missing some function implementations generated via make sqlc for the two new functions, CreateUser and GetUser. These functions were added to the Querier interface, which is part of the db.Store interface and we didn’t yet implmented it.

Fixing the error

To fixed this, we have to run make mock to regenrate the code for the MockStore in terminal.

make mock

After running above code we can see in db/mock/store.go the implementation of the missing two functions GetUser and CreateUser function has been added. So the updte of the MockStore implmentation should now fixed the api unit test and we can comfir this by running make test in termnal.

make test

Run make test to execute all tests in the project and ensure everything passes.

Now we can run the http server by running 

make server

Open the Postman to test the existing API to create a new account and first try to create a new user owner that doesn’t exist.

Running this we will get an error, violates foreign key constraint, because of the foreign key constraint for account owner is violated. This is expected, since there’s no account with this username in the database yet.

Implement DB Error return by Postgres and Handling in the API:
Case 1 No user exist: The current createAccount API returns a 500 Internal Server Error for foreseeable issues like foreign key violations (non-existent user) or unique constraint violations (duplicate account for the same owner/currency). This should be a client error (e.g., 403 Forbidden).

In the createAccount handler in api/account.go, inspect the error returned from the database call.Cast the error to *pq.Error to access Postgres-specific error details log printing the pqErrCode.Name, we can see that the error in log in terminal foreign_key_violation by run Postman.

Case 2: User exist but different currency, let copy an existing usename from user table via tablePlus and sent the request, we succesfully created a new account. If we sent the same request, then we got another error,duplicate key value violates unique constraints owner_currency_key. Thus because we’re trying to create more than 1 account with same currency for same owner. In this case also want to return an error 403 Forbidden instead of 500 Internal server error. Rerun the Postman create account and in log we can see that error log code name unique_violation error. We this information, we can update the code with switch case to check the error code name.

Use a switch on the pqErr.Code.Name() to handle specific errors.

func (server *Server) createAccount(ctx *gin.Context) {
....
   if err != nil {
	if pqErr, ok := err.(*pq.Error); ok {
	   // log.Println(pqErr.Code.Name()) get error code name
	    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)

}

Verify the API Error Handling:

  • Start the server with make server.
  • Use Postman to test the POST /accounts endpoint:
    1. Foreign Key Error: Try to create an account with an owner that does not exist. Verify it returns 403 Forbidden.
    2. Unique Constraint Error: Create a valid account. Send the exact same request again. Verify it returns 403 Forbidden.
    3. Success Case: Create a second account for the same owner but with a different currency. Verify it succeeds.

    This process ensures that our database logic is correct and that our API provides meaningful error codes to the client instead of generic server errors.

    Conclusion

    We have successfully implemented proper database error handling by:

    1. Creating user management functionality with proper SQL queries and tests
    2. Handling foreign key constraints by creating users before accounts
    3. Implementing specific error handling for PostgreSQL constraint violations
    4. Returning appropriate HTTP status codes based on error types
    5. Adding comprehensive logging for debugging and monitoring

    This error handling approach ensures that:

    • Clients receive meaningful error responses
    • Server logs contain detailed debugging information
    • Database constraint violations are handled gracefully
    • The API follows REST conventions for error codes
    Scroll to Top