Testing GORM with SQLMock

GORM is one of the most popular ORM (Object-Relational Mapper) libraries in Go, providing a simplified and consistent way to interact with databases. As with all application code, it's imperative to ensure that your database operations are thoroughly tested. However, hitting a real database during unit tests isn't ideal. This is where SQLMock comes in. SQLMock provides a way to mock out your SQL database so you can test your GORM code without needing a live database.

In this blog post, we will walk you through setting up SQLMock, integrating it with GORM, and writing tests for common database operations.

Basic Setup for SQLMock with GORM:

package main

import (
	"database/sql"
	"log"
	"testing"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"github.com/DATA-DOG/go-sqlmock"
)

func NewMockDB() (*gorm.DB, sqlmock.Sqlmock) {
    db, mock, err := sqlmock.New()
    if err != nil {
        log.Fatalf("An error '%s' was not expected when opening a stub database connection", err)
    }

    gormDB, err := gorm.Open(mysql.New(mysql.Config{
        Conn:                      db,
        SkipInitializeWithVersion: true,
    }), &gorm.Config{})

    if err != nil {
        log.Fatalf("An error '%s' was not expected when opening gorm database", err)
    }

    return gormDB, mock
}

Writing Tests

Assuming we have a simple User model as shown:

type User struct {
	ID   uint
	Name string
}

SELECT Operation:

Let's start with a simple SELECT operation:

func TestSelect(t *testing.T) {
    db, mock := NewMockDB()

    rows := sqlmock.NewRows([]string{"id", "name"}).
        AddRow(1, "John Doe")

    mock.ExpectQuery("^SELECT (.+) FROM users$").WillReturnRows(rows)

    var users []User
    if err := db.Find(&users).Error; err != nil {
        t.Fatalf("Error in finding users: %v", err)
    }

    if len(users) != 1 || users[0].Name != "John Doe" {
        t.Fatalf("Unexpected user data retrieved: %v", users)
    }
}

INSERT Operation:

For the INSERT operation:

func TestInsert(t *testing.T) {
    db, mock := NewMockDB()

    mock.ExpectExec("^INSERT INTO users (.+)$").WillReturnResult(sqlmock.NewResult(1, 1))

    user := User{Name: "Jane Doe"}
    if err := db.Create(&user).Error; err != nil {
        t.Fatalf("Failed to insert user: %v", err)
    }
}

UPDATE Operation:

For the UPDATE operation:

func TestUpdate(t *testing.T) {
    db, mock := NewMockDB()

    mock.ExpectExec("^UPDATE users SET name=? WHERE (.+)$").WillReturnResult(sqlmock.NewResult(1, 1))

    user := User{ID: 1, Name: "Jane Smith"}
    if err := db.Save(&user).Error; err != nil {
        t.Fatalf("Failed to update user: %v", err)
    }
}

DELETE Operation:

For the DELETE operation:

func TestDelete(t *testing.T) {
    db, mock := NewMockDB()

    mock.ExpectExec("^DELETE FROM users WHERE (.+)$").WillReturnResult(sqlmock.NewResult(1, 1))

    user := User{ID: 1}
    if err := db.Delete(&user).Error; err != nil {
        t.Fatalf("Failed to delete user: %v", err)
    }
}

One-To-Many Operation:

For a one-to-many association operation:

func TestOneToMany(t *testing.T) {
	db, mock := NewMockDB()

	userRows := sqlmock.
		NewRows([]string{"id", "name"}).
		AddRow(1, "John Doe")
	postRows := sqlmock.
		NewRows([]string{"id", "title", "user_id"}).
		AddRow(1, "Post 1", 1)

	mock.ExpectQuery("^SELECT (.+) FROM users$").WillReturnRows(userRows)
	mock.ExpectQuery("^SELECT (.+) FROM posts WHERE (.+)$").WillReturnRows(postRows)

	var users []User
	if err := db.Preload("Posts").Find(&users).Error; err != nil {
		t.Fatalf

Conclusion

By using SQLMock, you can effectively test GORM operations without relying on a real database, ensuring that your application logic is correct. While the above examples cover basic operations, remember that for more complex scenarios or relationships, you might need to set up more detailed mock expectations. Happy testing!