GitHub - sembraniteam/sqlmock: A lightweight SQL mock driver for Go that enables expectation-based testing for database/sql without a real database.

A lightweight SQL mock driver for Go that helps you test code using database/sql without a real database connection.

This package provides:

Expectation-based query and exec matching.
Simple argument matching, including wildcard support via AnyArg().
In-memory rows builder for deterministic query results.
Final expectation verification with ExpectationsWereMet().

Install

go get github.com/sembraniteam/sqlmock

Simple Usage

Application code

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func GetUserNameByID(ctx context.Context, db *sql.DB, id int64) (string, error) {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return "", err
	}
	defer tx.Rollback()

	var name string
	err = tx.QueryRowContext(ctx, "SELECT name FROM users WHERE id = ?", id).Scan(&name)
	if err != nil {
		return "", err
	}

	if err := tx.Commit(); err != nil {
		return "", err
	}

	return name, nil
}

func main() {
	db, err := sql.Open("mysql", "user:pass@tcp(localhost:3306)/app_db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}

	name, err := GetUserNameByID(context.Background(), db, 1)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("user name:", name)
}

Test code for the function above

package main

import (
	"context"
	"testing"

	"github.com/sembraniteam/sqlmock"
	"github.com/stretchr/testify/require"
)

func TestGetUserNameByID(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectBegin()

	rows := sqlmock.NewRows([]string{"name"}).AddRows("Alice")
	mock.ExpectQuery("SELECT name FROM users WHERE id = ?").
		WithArgs(int64(1)).
		WillReturnRows(rows)

	mock.ExpectCommit()

	name, err := GetUserNameByID(context.Background(), db, 1)
	require.NoError(t, err)
	require.Equal(t, "Alice", name)
	require.NoError(t, mock.ExpectationsWereMet())
}

Application and test code without transaction

By default, sqlmock.New() uses non-strict transaction mode, so query/exec can run without Begin/Commit.

package main

import (
	"database/sql"
)

func GetUserNameByIDNoTx(db *sql.DB, id int64) (string, error) {
	var name string
	err := db.QueryRow("SELECT name FROM users WHERE id = ?", id).Scan(&name)
	if err != nil {
		return "", err
	}
	return name, nil
}

package main

import (
	"testing"

	"github.com/sembraniteam/sqlmock"
	"github.com/stretchr/testify/require"
)

func TestGetUserNameByIDNoTx(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	rows := sqlmock.NewRows([]string{"name"}).AddRows("Alice")
	mock.ExpectQuery("SELECT name FROM users WHERE id = ?").
		WithArgs(int64(1)).
		WillReturnRows(rows)

	name, err := GetUserNameByIDNoTx(db, 1)
	require.NoError(t, err)
	require.Equal(t, "Alice", name)
	require.NoError(t, mock.ExpectationsWereMet())
}

Strict transaction mode with `WithStrictTx()`

Use WithStrictTx() when you want tests to fail if query/exec is executed outside a transaction.

package main

import (
	"context"
	"testing"

	"github.com/sembraniteam/sqlmock"
	"github.com/stretchr/testify/require"
)

func TestGetUserNameByID_StrictTx(t *testing.T) {
	db, mock, err := sqlmock.NewWithOptions(sqlmock.WithStrictTx())
	require.NoError(t, err)
	defer db.Close()

	// Query without Begin should fail in strict transaction mode.
	_, err = db.Query("SELECT name FROM users WHERE id = ?", int64(1))
	require.Error(t, err)
	require.ErrorContains(t, err, "query without active transaction")

	// Proper transactional flow.
	mock.ExpectBegin()
	mock.ExpectQuery("SELECT name FROM users WHERE id = ?").
		WithArgs(int64(1)).
		WillReturnRows(sqlmock.NewRows([]string{"name"}).AddRows("Alice"))
	mock.ExpectCommit()

	tx, err := db.BeginTx(context.Background(), nil)
	require.NoError(t, err)

	var name string
	err = tx.QueryRow("SELECT name FROM users WHERE id = ?", int64(1)).Scan(&name)
	require.NoError(t, err)
	require.Equal(t, "Alice", name)
	require.NoError(t, tx.Commit())
	require.NoError(t, mock.ExpectationsWereMet())
}

Wildcard Argument Matching

Use AnyArg() or AnyArgOf[T]() when the exact argument value is not important:

mock.ExpectExec("INSERT INTO sessions (id, created_at, updated_at) VALUES (?, ?, ?)").
	WithArgs(
		int64(1),
		sqlmock.AnyArg(),
		sqlmock.AnyArgOf[int64](),
	).
	WillReturnResult(1)