Don't mock the database: Data fixtures are parallel safe, and plenty fast

The API powering our Crunchy Bridge product is written in Go, a language that provides a good compromise between productivity and speed. We're able to keep good forward momentum on getting new features out the door, while maintaining an expected latency of low double digits of milliseconds for most API endpoints.

A common pitfall for new projects in fast languages like Go is that their creators, experiencing a temporary DX sugar high of faster compile and runtime speeds than they've previously encountered in their career, become myopically focused on performance above anything else, and start making performance optimizations with bad cost/benefit tradeoffs.

The textbook example of this is the database mock. Here's a rough articulation of the bull case for this idea: CPUs are fast. Memory is fast. Disks are slow. Why should tests have to store data to a full relational database with all its associated bookkeeping when that could be swapped out for an ultra-fast, in-memory key/value store? Think of all the time that could be saved by skipping that pesky fsync, not having to update that plethora of indexes, and foregoing all that expensive WAL accounting. Database operations measured in hundreds of microseconds or even *gasp*, milliseconds, could plausibly be knocked down to 10s of microseconds instead.

Mock everything, test nothing

Anyone who's substantially journeyed down the path of database mocks will generally tell you that it leads nowhere good. They are fast (although disk speed has improved by orders of magnitude over the last decade), but every other one of their aspects leaves something to be desired.

A fatal flaw is that an in-memory mock bears no resemblance to a real database and the exhaustive constraints that real databases put on input data. Consider for example, whether a mock would fail like a database in any of these scenarios:

• A value is inserted for a column that doesn't exist.
• A value of the wrong data type for a column is inserted.
• Duplicate values are inserted such that a UNIQUE constraint would not be satisfied.
• A value is inserted in a foreign key column that doesn't exist in the reference table.
• The conditions of a CHECK constraint aren't met.

The likelihood is that it wouldn't. The database mock would dumbly accept mocked test data that was completely invalid, and the code under test would melt down spectacularly once it hit production with errors like this one:

ERROR: insert or update on table "cluster" violates foreign key constraint "cluster_team_id_fkey"
    (SQLSTATE 23503)

And the trouble with mocks doesn't stop there:

• There isn't a query engine to determine what mocked data should be returned so that has to be mocked. Sometimes that might work, but it could also just be hopelessly wrong, and there's no way to catch those errors except production.

  expect_any_instance_of(Cluster).to receive(:where)
    .with("id IN (?, ?, ?)", 1, 2, 3)
    .and_return([cluster1, cluster2, cluster4])
  

• From a human perspective, writing mock code (imagine having to write expect(...).to receive(...) chained across multiple objects) is laboriously, error prone, and slow! By comparison, inserting rows into and querying a database are faster and easier.

• In languages like Go without dynamic typing, it's difficult to write a general purpose mocking framework, which often leaves it to the app developer to write and maintain their own internal mocking platforms making up interfaces and mock structs. The interfaces add a suboptimal layer of indirectness to code, making it harder to make good use of IDE features like jump-to-definition.

With the widespread use of mocks, you may have to consider that because so much of the stack under exercise is synthetic, you're really just testing that you got your mocks right rather than testing that code actually works.

Fixtures are fast enough

Hopefully this has done something to convince you that database mocks aren't an appropriate way for testing code intended for production, but even more relevant is that the entire premise behind their use is flawed!

The principle support database mocks starts from the notion that database access is unacceptably slow, and if that were ever true, it certainly isn't today. Oh my commodity laptop, inserting a reasonably complex object with over a dozen columns and multiple foreign keys and constraints takes about ~100µs. That's ten objects that'll fit in a millisecond, and using techniques like test transactions and ubiquitous use of t.Parallel() it's entirely parallelizable.

To hold up our large, mature app as an example, we have a little under 4,900 tests that run in ~23s uncached:

$ PLATFORM_RUN_ID=$(uuidgen) gotestsum ./... -- -count=1
✓  apiendpoint (235ms)
✓  apierror (370ms)
✓  apiexample (483ms)
...
✓  util/urlutil (1.058s)
✓  util/uuidutil (1.084s)
✓  validate (1.077s)

DONE 4876 tests, 4 skipped in 23.156s

We have strong conventions around the use of database fixtures in tests, which are exactly like inserting a normal record except they come with defaults which makes their use fast, easier, and more concise:

package dbfactory

type MultiFactorOpts struct {
    ID          *uuid.UUID              `validate:"-"`
    AccountID   uuid.UUID               `validate:"required"`
    ActivatedAt *time.Time              `validate:"-"`
    ExpiresAt   *time.Time              `validate:"-"`
    Kind        *dbsqlc.MultiFactorKind `validate:"-"`
}

func MultiFactor(ctx context.Context, t *testing.T, e db.Executor, opts *MultiFactorOpts) *dbsqlc.MultiFactor {
    t.Helper()

    validateOpts(t, opts)

    var (
        num          = nextNumSeq()
        numFormatted = formatNumSeq(num)
    )

    multiFactor, err := dbsqlc.New().MultiFactorInsert(ctx, e, dbsqlc.MultiFactorInsertParams{
        ID:          ptrutil.ValOrDefaultFunc(opts.ID, func() uuid.UUID { return ptesting.ULID(ctx).New() }),
        AccountID:   opts.AccountID,
        ActivatedAt: ptrutil.TimeSQLNull(opts.ActivatedAt),
        ExpiresAt:   ptrutil.TimeSQLNull(opts.ExpiresAt),
        Kind:        string(ptrutil.ValOrDefault(opts.Kind, dbsqlc.MultiFactorKindTOTP)),
        Name:        fmt.Sprintf("%s no. %s", ptrutil.ValOrDefault(opts.Kind, dbsqlc.MultiFactorKindTOTP), numFormatted),
    })
    require.NoError(t, err)

    return multiFactor
}

With constructs like Go's var ( ... ) block, they even look pretty when assembling long series of them in test cases:

func TestClusterServiceActionRestart(t *testing.T) {
    t.Parallel()

    setup := func(t *testing.T) (*testBundle, context.Context) {
        t.Helper()

        var (
            account = dbfactory.Account(ctx, t, tx, &dbfactory.AccountOpts{})
            team    = dbfactory.Team(ctx, t, tx, &dbfactory.TeamOpts{})
            _       = dbfactory.AccessGroupAccount_Admin(ctx, t, tx, team.ID, account.ID)
            cluster = dbfactory.Cluster(ctx, t, tx, &dbfactory.ClusterOpts{TeamID: team.ID})
        )

I wrote a plugin to measure how many test fixtures are generated during the course of a complete run of the test suite, and found the number to be a little north of 18,000:

=# select * from test_stat;
                  id                  |          created_at           | num_fixtures
--------------------------------------+-------------------------------+--------------
 9E06C8B9-EA6E-490F-A0D3-1A18310376CF | 2025-05-28 07:42:49.500298-07 |        18132

An imperfect calculation would suggest we're generating 18k fixtures / 23 seconds = 780 fixtures/s. This doesn't account at all for tests that don't need database access or non-fixture database operations, so we're really averaging more like a few thousand database operations per second of testing.

Summary: Fast fixtures, total parallelization, good constraints

To sum it up, here's how to design a test suite that's fast and thorough:

• Don't mock databases. A little extra speed isn't worth the dramatic reduction in test fidelity.

• Make database use in tests easy with a fixture framework that does most of the work for you. It can even be homegrown (ours is) as long as it's easy to use and establishes strong convention.

• Make up for any lost speed by using techniques like test transactions to maximize parallel throughput. Databases are built to accommodate this.

• With database mocks in the rear view mirror, take advantage of all the nice constraints RDBMSes offer like strongly defined schema, data types, check constraints, and foreign keys. Each of these features that catches a mistake during tests is one less bug to fix in production.