Testing Gorm functions with a SQLite DB

Gorm is an ORM for Go that helps to simplify SQL database management.

It helps by generate a different query according to the DBMS selected. It can also create tables, migrate data, or do multiple transactions.

But, how we can verify that our code works? How to check the queries execution?

In this tutorial, we will see how to test GORM by using an SQLite database to execute our test queries.

Why not go-sqlmock?

Testing with go-sqlmock is very verbose because you have to write every query executed by Gorm. In this way, if the Gorm library will be updated and some queries changed you have to update your tests as well.

Also, why you should test an ORM if executes the right query when it’s made for this very reason?

Why SQLite?

As it is said by the documentation:

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.

SQLite Home Page

The main reason is the velocity, to create and run an SQLite DB is very fast and is done locally without the need for a service or a DBMS. An SQLite database is a file managed with the relative driver.

Furthermore, create this database type required few milliseconds.

So it is perfect? Suddenly, no.

SQLite doesn’t have all the functionality available in more complex DBMS like PostgreSQL or MySQL.

Some of the main missing functionality:

• No checks on foreign keys constraints by default

SQLite Foreign Key Support

• Only 5 types are available, this could be a problem for specific Go data types like time.Time when used with the default Gorm scanner.

Datatypes In SQLite Version 3

A simple example

Look at the end of the article for the full code implementation.

The code to test

First of all, we write some functions for simple database CRUD.
An User model that contains an ID and an username:

A Database struct that contains the Gorm client:

Some functions to be tested:

Simple functions to manage user table

The test cases

For testing, we will create the database, migrate the tables, and add some mock data.

The special TestMain function used to initialize the DB before running the tests

Then we can easily test if a function works properly by checking the data after each execution and, if available, the value returned.

Each test case will call a setup function to get a new Database struct to use.

Setup will be called by each test to get a new Database struct to use.

Here some examples:

Simple test cases

If you look to the setup function, you can see that we start a transaction. In this way, after a data write, it is possible to do a rollback to bring back the initial status.

Look to the defer, at the end the data will be restored

There are other examples, see below if you are interested!

Full example code