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:
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
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.
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.
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.
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
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 ('','','');
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