2023 Golang Gorm and Postgresql Unit Testing using Sqlmock

4 min readJun 2, 2023

Unit test is really needed for maintain good code quality, and I also use that for testing the query in my project. For testing the database, I choose to use sqlmock package, to help me test that, but I’m having some problems when creating that, because postgres needs different treatment to test, unlike another database. After doing some experiments, I finally found the correct way. So let’s start.

How Sqlmock work?

Sqlmock works by checking if the generated query is already the same or not. It’s different from the h2 memory database in Java, because in the h2 database, the data is saved temporarily in memory, so we can run another query to check whether the data is the same as what we expected or not.

For checking the query, there are two methods, ExpectedExec and ExpectedQuery. If we see from the documentation, there’s two main differences, between ExpectedExec and ExpectedQuery.

ExpectedExec is used for query that’ll change the data, e.g. Insert, Update, Delete

While ExpectedQuery is used for Select query.

For you who are new to sqlmock, please remember that the query we expect doesn’t have to be the complete query. We can also use regex for that purpose. For example:

Real query:

SELECT id, created_at, updated_at, deleted_at, full_name, user_name, password FROM users WHERE id = 1

We can expect query just like this:

SELECT .+ FROM users WHERE id = .+

Or, the most extreme case

.+

so, what’s unit testing use for? ;)

Start the test:

First, add this dependency

github.com/stretchr/testify v1.8.3 // used for assert function
gorm.io/driver/postgres v1.5.2
gorm.io/gorm v1.25.1
github.com/DATA-DOG/go-sqlmock v1.5.0

We start by creating the model and database config.

Init model and database connection:

We will have 3 struct, User, Company and CompanyScale. For Company and CompanyScale, we use gorm.Model , and there’s One to many relationship between CompanyScale and Company.

Btw, maybe you asked, what is inside the gorm.Model? Actually, it’s just a simple struct, with ID, and timestamps.

CRUD process

For User Model, we will have simple Create, Read, Update and Delete.

For Company model, we will have Create, Find with join, and also delete function.

First, we need to mock the database.

Init db mock.

A bit tips, you can add Logger in gorm config, so you can show SQL query that run by Gorm.

Test Find

Let’s start from the easy one. We can just use mock.ExpectQuery for that. When we met specific query, we can return spesific row.

Test Insert

We need to use mock.ExpectBegin() and mock.ExpectCommit() for testing insert with sqlmock. For the query, we can use mock.ExpectQuery, because postgre insert query could return something, using keyword RETURNING. We can compare the documentation between Postgre and Mysql

Mysql

Postgre

For example, if we want to get id of the data that we just insert, we can use 1 query only in postgresql

INSERT INTO "users" ("full_name","user_name","password") VALUES ('','','') RETURNING "id"

While in Mysql, we need to run two separate query:

INSERT INTO "users" ("full_name","user_name","password") VALUES ('','','');
SELECT LAST_INSERT_ID();

Here’s the test case:

Test Update and Delete

For update and delete, we use ExpectedExec for testing, and, don’t forget to add ExpectBegin() and ExpectCommit() or ExpectRollback() . Let’s see the example:

If we using gorm.Model when creating the struct / table, the treatment for delete is a bit different, instead of running delete query, what will be do is:

UPDATE "companies" SET "deleted_at"='2023–06–02 13:34:08.286' WHERE id = 1 AND "companies"."deleted_at" IS NULL

You can also check the full code in my repo