The SQLMock package (https://github.com/DATA-DOG/go-sqlmock) describes itself as follows:

I find SQLMock useful, but often more work than directly using the database. Being a pragmatic programmer, I am happy to use either. Typically, the choice of which to use is made based on how I want the tests to work. If I want to be very precise, have no potential for issues related to existing contents of the table, and have no possibility for data races caused by the concurrent usage of the table, then I will spend the extra effort to use SQLMock.

Let's look at using SQLMock to test. Consider the following function:

func SavePerson(db *sql.DB, in *Person) (int, error) {
   // perform DB insert
   query := "INSERT INTO person (fullname, phone, currency, price) VALUES (?, ?, ?, ?)"
   result, err := db.Exec(query, in.FullName, in.Phone, in.Currency, in.Price)
   if err != nil {
      return 0, err
   }

   // retrieve and return the ID of the person created
   id, err := result.LastInsertId()
   if err != nil {
      return 0, err
   }
   return int(id), nil
}

This function takes *Person and *sql.DB as input, saves the person into the database provided, and then returns the ID of the newly created record. This function is using a traditional form of DI to pass the database connection pool into the function. This allows us an easy way to swap out the real database connection with a fake one. Now, let's build the test. First, we create a mock database using SQLMock:

testDb, dbMock, err := sqlmock.New()
require.NoError(t, err)

Then, we define the query we are expecting as a regular expression and use that to configure the mock database. In this case, we are expecting a single db.Exec call that returns 2, the ID of the newly created record, and 1, the affected row:

queryRegex := `QINSERT INTO person (fullname, phone, currency, price) VALUES (?, ?, ?, ?)E`

dbMock.ExpectExec(queryRegex).WillReturnResult(sqlmock.NewResult(2, 1))

Now we call the function:

resultID, err := SavePerson(testDb, person)

And then, we validate the results and the mock's expectations:

require.NoError(t, err)
assert.Equal(t, 2, resultID)
assert.NoError(t, dbMock.ExpectationsWereMet())

Now that we have an idea of how we can leverage SQLMock to test our database interactions, let's apply it to our ACME registration code.