Unit testing with SQLBoiler

sqlboiler_example_mock.go

package services

import (
	"database/sql"
	"reflect"
	"regexp"
	"testing"
	"github.com/DATA-DOG/go-sqlmock"
	"github.com/volatiletech/sqlboiler/boil"
)

func testSqlBoilerWithMock(t *testing.T) {
	// Mock DB instance by sqlmock
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("an error '%s' was not expected", err)
	}

  	// Inject mock instance into boil.
	oldDB := boil.GetDB()
	defer func() {
		db.Close()
		boil.SetDB(oldDB)
	}()
	boil.SetDB(db)

  	// Create mock data with specific columns
	mockShipperRows := sqlmock.NewRows([]string{"id", "column_name"}).AddRow(1, 123)
  
  	// Use regexp to avoid comparing query failed 
	mockQueryFindShipper := regexp.
		QuoteMeta("SELECT * FROM `objects` WHERE (column_name=?) AND (`objects`.deleted_at is null) LIMIT 1;")

  	// Mock a data
	mock.ExpectQuery(mockQueryFindShipper).WithArgs(123).
		WillReturnRows(mockShipperRows).
		RowsWillBeClosed()
  
  	// Mock an error
  	mock.ExpectQuery(mockQueryFindShipper).WithArgs(124).
		WillReturnError(sql.ErrNoRows).
		RowsWillBeClosed()
}

can you give example for insert ?

i have been trying it since days , but could not find how we can mock the sqlboiler insert.

Hi @smalav-cpi
INSERT use ExpectExec instead of ExpectQuery

//...
query := regexp.QuoteMeta("INSERT INTO `users` (`username`,`password`) VALUES (?,?)")
mock.ExpectExec(query).WithArgs("user1", "somethinghasbeenhashed").WillReturnError(errors.New("internal server error"))
// OR using this
// mock.ExpectExec(query).WithArgs("user1", "somethinghasbeenhashed")..WillReturnResult(sqlmock.NewResult(1, 1))

Ref: https://github.com/DATA-DOG/go-sqlmock/blob/master/sqlmock_test.go

This example imports these packages:

go: downloading github.com/facebookgo/ensure v0.0.0-20160127193407-b4ab57deab51
go: downloading github.com/facebookgo/subset v0.0.0-20150612182917-8dac2c3c4870
go: downloading github.com/facebookgo/stack v0.0.0-20160209184415-751773369052

And they are like 9 years old packages, all were archived by facebook......

Looks like it's because https://github.com/kat-co/vala is too old

And it's used in the generated tests:

pkg/model/mysql_main_test.go
18:	"github.com/kat-co/vala"
61:	err = vala.BeginValidation().Validate(
62:		vala.StringNotEmpty(m.user, "mysql.user"),
63:		vala.StringNotEmpty(m.host, "mysql.host"),
64:		vala.Not(vala.Equals(m.port, 0, "mysql.port")),
65:		vala.StringNotEmpty(m.dbName, "mysql.dbname"),
66:		vala.StringNotEmpty(m.sslmode, "mysql.sslmode"),

I don't really get you @c9s? I saw nothing like that in my side.