How to mock SQL DB in Go unit tests?

Question

I have a unit test testing service layer of my REST APIs. What I want to test is RegisterAccount in the service layer, which has dependencies on Repository (Database access layer). How do I mock in testing if the dependencies are Database-related?

Here's my repository struct:

type Repository struct {
    Users interface {
        Insert(ctx context.Context, tx *sql.Tx, usr UserModel) (int, error)
        GetById(ctx context.Context, id int) (UserModel, error)
        GetByEmail(ctx context.Context, email string) (UserModel, error)
        Update(ctx context.Context, tx *sql.Tx, usr UserModel) error
        Delete(ctx context.Context, tx *sql.Tx, id int) error
    }

    Invitation interface {
        Insert(ctx context.Context, tx *sql.Tx, invt InvitationModel) error
        Get(ctx context.Context, tx *sql.Tx, token string) (int, error)
        DeleteByUserId(ctx context.Context, tx *sql.Tx, usrid int) error
    }

    Roles interface {
        Insert(ctx context.Context, nw RolesModel) error
        GetAll(ctx context.Context) ([]RolesModel, error)
        GetById(ctx context.Context, id int) (RolesModel, error)
        Update(ctx context.Context, nw RolesModel) error
        Delete(ctx context.Context, id int) error
        DestroyMany(ctx context.Context) error
    }

    Beans interface {
        Insert(ctx context.Context, nw BeansModel) error
        GetAll(ctx context.Context) ([]BeansModel, error)
        GetById(ctx context.Context, id int) (BeansModel, error)
        Update(ctx context.Context, nw BeansModel) error
        Delete(ctx context.Context, id int) error
        DestroyMany(ctx context.Context) error
    }

    Forms interface {
        Insert(ctx context.Context, nw FormsModel) error
        GetAll(ctx context.Context) ([]FormsModel, error)
        GetById(ctx context.Context, id int) (FormsModel, error)
        Update(ctx context.Context, nw FormsModel) error
        Delete(ctx context.Context, id int) error
        DestroyMany(ctx context.Context) error
    }
}

UserService implementation:

type UsersServices struct {
    Repository repository.Repository
    Db         *sql.DB
    TransFnc   db.TransFnc
}

This is what i'm trying to test:

func (us *UsersServices) RegisterAccount(ctx context.Context, req RegisterRequest) (*RegisterResponse, error) {

    var response = new(RegisterResponse)

    err := utils.IsPasswordValid(req.Password)
    if err != nil {
        return nil, errorService.New(err, err)
    }

    err = us.TransFnc(us.Db, ctx, func(tx *sql.Tx) error {

        var newAccount repository.UserModel
        newAccount.Email = req.Email
        newAccount.Username = req.Username

        if err = newAccount.Password.ParseFromPassword(req.Password); err != nil {
            return errorService.New(err, err)
        }

        usrId, err := us.Repository.Users.Insert(ctx, tx, newAccount)
        if err != nil {
            duplicateKey := CONFLICT_CODE
            switch {
            case strings.Contains(err.Error(), duplicateKey):
                return errorService.New(ErrUserAlreadyExist, err)
            default:
                //Todo: handle error client
                return errorService.New(err, err)
            }

        }

        tokenIvt := utils.GenerateTokenUuid()

        invt := repository.InvitationModel{
            UserId:   usrId,
            Token:    tokenIvt,
            ExpireAt: time.Hour * 24,
        }

        err = us.Repository.Invitation.Insert(ctx, tx, invt)
        if err != nil {
            return errorService.New(err, err)
        }

        // register and invite success, send to response
        response.Token = tokenIvt

        return nil
    })

    if err != nil {
        return nil, err
    }

    return response, nil
}

This is the unit test for RegisterAccount:

func TestRegisterAccount(t *testing.T) {
    userMock := &UserRepositoryMock{}

    userServ := UsersServices{
        Repository: repository.Repository{
            Users: userMock,
        },
        Db:       nil,
        TransFnc: nil,
    }

    request := RegisterRequest{
        Username: "test69",
        Email:    "[email protected]",
        Password: "HelloWorld$123",
    }

    want := &RegisterResponse{Token: ""}
    got, err := userServ.RegisterAccount(context.Background(), request)
    if err != nil {
        t.Errorf("got error %q but want none", err)
    }

    if got != want {
        t.Errorf("want to equal %v, but got: %v", want, got)
    }
}

type UserRepositoryMock struct{}

func (u *UserRepositoryMock) Insert(ctx context.Context, tx *sql.Tx, usr repository.UserModel) (int, error) {

    return 0, nil
}

func (u *UserRepositoryMock) GetById(ctx context.Context, id int) (repository.UserModel, error) {

    return repository.UserModel{}, nil
}

func (u *UserRepositoryMock) GetByEmail(ctx context.Context, email string) (repository.UserModel, error) {
    return repository.UserModel{}, nil
}

func (u *UserRepositoryMock) Update(ctx context.Context, tx *sql.Tx, usr repository.UserModel) error {

    return nil
}

func (u *UserRepositoryMock) Delete(ctx context.Context, tx *sql.Tx, id int) error {

    return nil
}

This is the DB transaction custom type:

type TransFnc func(db *sql.DB, ctx context.Context, operation func(*sql.Tx) error) error

How can I be able to test/mock if the dependency of UserServices is a sql.DB and function that depends on sql.DB?

zahra keshtkar · Accepted Answer · 2025-09-12 19:11:44Z

When testing a service layer that depends on sql.DB and a custom transaction function, you don’t want to hit a real database in your unit tests. There are a few common approaches in Go:

1. Use sqlmock for repository tests

go-sqlmock lets you stub out sql.DB and verify the exact queries.
This is perfect for repository layer tests, where you want to check SQL correctness.

sqlDB, mock, _ := sqlmock.New()
mock.ExpectBegin()
mock.ExpectExec("INSERT INTO `users`").
    WithArgs("[email protected]", "hashed-password").
    WillReturnResult(sqlmock.NewResult(1, 1))
mock.ExpectCommit()

repo := NewUserRepository(sqlDB)
err := repo.Insert(ctx, user)

require.NoError(t, err)
require.NoError(t, mock.ExpectationsWereMet())

2. Use gomonkey to patch functions

If you need to override something like your custom TransFnc, you can patch it at runtime:

patches := gomonkey.ApplyFunc(TransFnc, func(db *sql.DB, ctx context.Context, fn func(*sql.Tx) error) error {
    return fn(nil) // simulate transaction without DB
})
defer patches.Reset()

resp, err := userService.RegisterAccount(ctx, req)
require.NoError(t, err)

3. Use interfaces and dependency injection

The idiomatic Go way is to design your service so dependencies can be replaced by mocks/fakes in tests.
For example, abstract your transaction logic:

type Transactioner interface {
    Do(ctx context.Context, fn func(*sql.Tx) error) error
}

type FakeTx struct{}

func (f *FakeTx) Do(ctx context.Context, fn func(*sql.Tx) error) error {
    return fn(nil) // no real DB transaction
}

Then in your service:

type UsersServices struct {
    Repository repository.Repository
    Tx         Transactioner
}

And in the test:

userMock := &UserRepositoryMock{}
svc := UsersServices{
    Repository: repository.Repository{Users: userMock},
    Tx:         &FakeTx{},
}

resp, err := svc.RegisterAccount(ctx, req)
require.NoError(t, err)
require.NotEmpty(t, resp.Token)

Collectives™ on Stack Overflow

How to mock SQL DB in Go unit tests?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related