Mocking your SQL database in Go tests has never been easier.



Mocking your SQL database in Go tests has never been easier. The copyist library automatically records low-level SQL calls made during your tests. It then generates recording files that can be used to play back those calls without connecting to the real SQL database. Run your tests again. This time, they'll run much faster, because now they do not require a database connection.

Best of all, your tests will run as if your test database was reset to a clean, well-known state between every test case. Gone are the frustrating problems where a test runs fine in isolation, but fails when run in concert with other tests that modify the database. In fact, during playback you can run different test packages in parallel, since they will not conflict with one another at the database level.

copyist imposes no overhead on production code, and it requires almost no changes to your application or testing code, as long as that code directly or indirectly uses Go's sql package (e.g. Go ORM's and the widely used sqlx package). This is because copyist runs at the driver level of Go's sql package.

What problems does copyist solve?

Imagine you have some application code that opens a connection to a Postgres database and queries some customer data:

func QueryName(db *sql.DB) string {
	rows, _ := db.Query("SELECT name FROM customers WHERE id=$1", 100)
	defer rows.Close()

	for rows.Next() {
		var name string
		return name
	return ""

The customary way to test this code would be to create a test database and populate it with test customer data. However, what if application code modifies rows in the database, like removing customers? If the above code runs on a modified database, it may not return the expected customer. Therefore, it's important to reset the state of the database between test cases so that tests behave predictably. But connecting to a database is slow. Running queries is slow. And resetting the state of an entire database between every test is really slow.

Various mocking libraries are another alternative to using a test database. These libraries intercept calls at some layer of the application or data access stack, and return canned responses without needing to touch the database. The problem with many of these libraries is that they require the developer to manually construct the canned responses, which is time-consuming and fragile when application changes occur.

How does copyist solve these problems?

copyist includes a Go sql package driver that records the low-level SQL calls made by application and test code. When a Go test using copyist is invoked with the "-record" command-line flag, then the copyist driver will record all SQL calls. When the test completes, copyist will generate a custom text file that contains the recorded SQL calls. The Go test can then be run again without the "-record" flag. This time the copyist driver will play back the recorded calls, without needing to access the database. The Go test is none the wiser, and runs as if it was using the database.

How do I use copyist?

Below is the recommended test pattern for using copyist. The example shows how to unit test the QueryName function shown above.

func init() {

func TestQueryName(t *testing.T) {
	defer copyist.Open(t).Close()

	db, _ := sql.Open("copyist_postgres", "postgresql://root@localhost")
	defer db.Close()

	name := QueryName(db)
	if name != "Andy" {
		t.Error("failed test")

In your init or TestMain function (or any other place that gets called before any of the tests), call the copyist.Register function. This function registers a new driver with Go's sql package with the name copyist_<driverName>. In any tests you'd like to record, add a defer copyist.Open(t).Close() statement. This statement begins a new recording session, and then generates a playback file when Close is called at the end of the test.

copyist does need to know whether to run in "recording" mode or "playback" mode. To make copyist run in "recording" mode, invoke the test with the record flag:

go test -run TestQueryName -record

This will generate a new recording file in a testdata subdirectory, with the same name as the test file, but with a .copyist extension. For example, if the test file is called app_test.go, then copyist will generate a testdata/app_test.copyist file containing the recording for the TestQueryName test. Now try running the test again without the record flag:

go test -run TestQueryName

It should now run significantly faster. You can also define the COPYIST_RECORD environment variable (to any value) to make copyist run in recording mode:

COPYIST_RECORD=1 go test ./...

This is useful when running many test packages, some of which may not link to the copyist library, and therefore do not define the record flag.

How do I reset the database between tests?

You can call SetSessionInit to register a function that will clean your database:

func init() {

The resetDB function will be called by copyist each time you call copyist.Open in your tests, as long as copyist is running in "recording" mode. The session initialization function can do anything it likes, but usually it will run a SQL script against the database in order to reset it to a clean state, by dropping/creating tables, deleting data from tables, and/or inserting "fixture" data into tables that makes testing more convenient.


I'm seeing "unexpected call" panics telling me to "regenerate recording"

This just means that you need to re-run your tests with the "-record" command line flag, in order to generate new recordings. Most likely, you changed either your application or your test code so that they call the database differently, using a different sequence or content of calls.

However, there are rarer cases where you've regenerated recordings, have made no test or application changes, and yet are still seeing this error when you run your tests in different orders. This is caused by non-determinism in either your application or in the ORM you're using.

As an example of non-determinism, some ORMs send a setup query to the database when the first connection is opened in order to determine the database version. So whichever test happens to run first records an extra Query call. If you run a different test first, you'll see the "unexpected call" error, since other tests aren't expecting the extra call.

The solution to these problems is to eliminate the non-determinism. For example, in the case of an ORM sending a setup query, you might initialize it from your TestMain method:

func TestMain(m *testing.M) {
	closer := copyist.OpenNamed("test.copyist", "OpenCopyist")

This triggers the first query in TestMain, which is always run before tests.

The generated copyist recording files are too big

The size of the recording files is directly related to the number of accesses your tests make to the database, as well as the amount of data that they request. While copyist takes pains to generate efficient recording files that eliminate as much redundancy as possible, there's only so much it can do. Try to write tests that operate over smaller amounts of interesting data. For tests that require large numbers of database calls, or large amounts of data, use a different form of verification. One nice thing about copyist is that you can pick and choose which tests will use it. The right tool for the right job, and all that.


  • Because of the way copyist works, it cannot be used with test and application code that accesses the database concurrently on multiple threads. This includes tests running with the "-parallel" testing flag, which enables tests in the same package to run in parallel. Multiple threads are problematic because the copyist driver code has no way to know which threads are associated with which tests. However, this limitation does not apply to running different test packages in parallel; in playback mode, this is both possible and highly encouraged! However, in recording mode, there may be problems if your tests conflict with one another at the database layer (i.e. by reading/modifying the same rows). The recommended pattern is to run test packages serially in recording mode, and then in parallel in playback mode.

  • copyist currently supports only the Postgres pq and pgx stdlib drivers. If you'd like to extend copyist to support other drivers, like MySql or SQLite, you're invited to submit a pull request.

  • copyist does not implement every sql package driver interface and method. This may mean that copyist may not fully work with some drivers with more advanced features. Contributions in this area are welcome.

  • pgxtest: fix RunTestDataTypes for pgx

    pgxtest: fix RunTestDataTypes for pgx

    Timezone comparisons aren't a fair test because it truly depends on the person's machine running the program, so let's fix it up slightly by forcing comparisons against time.UTC.

    opened by otan 8
  • call testing.T.Fatalf on recording mismatch instead of panicing

    call testing.T.Fatalf on recording mismatch instead of panicing

    Call testing.T.Fatalf instead of panicing on various session errors such as a recording mismatch. This provides a better user experience when multiple tests have recording mismatches. Now each test will be reported as failed, while previously only the first test would fail and subsequent tests would not be run.

    opened by petermattis 6
  • return errors in favor of panicking, when possible

    return errors in favor of panicking, when possible

    This commit changes copyist to instead return *sessionError via the sql driver in favor of panicking. Note in some cases, namely proxyRows.Columns, copyist will still panic as the proper behavior is otherwise unclear. Most usecases will encounter an error either before or after calling .Columns, so it is unlikely for users to encounter this panic.

    Returning errors instead of panicking fixes deadlocks that can occur when a driver.Conn is returning a driver.Rows inside of a transaction that recovers panics. Namely, cockroach-go [1]. A panic within a SQL Driver will cause the connections read mutex to NOT be unlocked as control should have been passed down to the sql.Rows (see queryDC in sql/sql.go). When the transaction helper method attempts to execute tx.Rollback, it will deadlock upon attempting to acquire the mutex.

    	// Cancel the Tx to release any active R-closemu locks.
    	// This is safe to do because tx.done has already transitioned
    	// from 0 to 1. Hold the W-closemu lock prior to rollback
    	// to ensure no other connection has an active query.

    Note that similar behavior would be encountered if instead attempting to call .Close on the Conn object.

    Users should be able to make use of transaction helpers that recover panics as behavior may become unpredictable within system that recover the panic elsewhere such as an HTTP server. Repositories under the cockroachdb organization should also work well together.


    opened by chrisseto 4
  • fix panic message in VerifyRecordWithStringArg

    fix panic message in VerifyRecordWithStringArg

    It appears that the "mismatched arugment" panic message prints the "expected" and "got" queries mixed around, this change fixes that.

    opened by 1lann 2
  • Add support for non-file recordings

    Add support for non-file recordings

     Add support for non-file recordings
        This commit exposes copyist.OpenSource as an alternative to
        copyist.OpenNamed. OpenSource allows users to pass an
        io.ReadWriteCloser which allows them to control how a recording is
        loaded and persisted. For example, if users wish to handle a common
        query executed open opening a connection, like a version check, it
        may be inconvenient to locate a common recording file. OpenSource
        would allow users to go:embed the common recording in a shared
        package rather than struggling with locating a file from many

    This PR also contains a fix for TestNonSessionPanicsAreNotCaught

    opened by chrisseto 2
  • pass through non-session panics, rather than swallowing them

    pass through non-session panics, rather than swallowing them

    The previous commit to translate session panics into testing.T.Fatal errors unintentionally caused non-session panics in the test to be swallowed.

    Convert the recordingFile panics into sessionError so they can be translated to testing.T.Fatal.

    opened by petermattis 2
  • Include the recording name in the verification panic message

    Include the recording name in the verification panic message

    Include the recording name in the session.VerifyRecord* panic in order to make it easier to identify a failing test. This is useful when running go tests without the -v (verbose) flag.

    opened by petermattis 2
  • What is the inspiration for the design of copyist?

    What is the inspiration for the design of copyist?

    Just curious to hear more about what inspired the design of copyist, whether another library or CS concept. Never come across a mocking library explained in these terms so I'm interested to delve deeper.

    opened by zachvalenta 2
  • bump dependencies and roundtrip pq.Error values

    bump dependencies and roundtrip pq.Error values

    roundtrip pq.Error values

    Previously, copyist was not preserving pq.Error values returned by DB calls. This commit adds support for that type so that callers can check error codes and other information only available on pq.Error.

    Fixes #12

    bump versions of all dependencies and fix sqlx issue

    Bump versions of dependencies in go.mod files to the latest offered. Switch to use Go 1.16. Fix long-standing parameter binding issue with sqlx. The new sqlx.BindDriver function added to a recent sqlx release enables the fix.

    opened by andy-kimball 1
  • implement proxyConn.{Exec,Query}Context

    implement proxyConn.{Exec,Query}Context

    Implement proxyConn.{Exec,Query}Context. These two methods implement driver.{Execer,Queryer}Context which are needed in order to support running multiple SQL statements in a single operation. If these methods don't exist, the database/sql package falls back to using PrepareContext which only supports a single SQL statement.

    Note that the pgx driver does not support multiple SQL statements in an Exec/Query operation, so this enhancement only benefits lib/pq.

    opened by petermattis 1
  • improve findTestFile heuristics

    improve findTestFile heuristics

    findTestFile was previously returning the first file in the call stack that ended in _test.go, but that heuristic doesn't work nicely when there is a _test.go file that contains utility functions, such as a testServer struct. The new heuristics is to walk up the call stack looking for the last file ending in _test.go.

    opened by petermattis 1
  • Examples, please

    Examples, please

    The solution seems interesting and useful; however, as a golang newbie, I honestly have no idea how to use it. Any chance anyone has some examples? Readme unfortunately doesn't help.

    And, to truly show off my noobiness, can I use this with mocked context.Context?

    opened by laszukdawid 0
  • -record flag example

    -record flag example

    Hi I was trying to record the output for a test and I got the following

    $ go test -run TestDictWordList -record
    Foo {} no recording exists with this name: TestDictWordList/Foo

    I also tried with a double dash between

    $ go test -run TestDictWordList -- -record
    Foo {} no recording exists with this name: TestDictWordList/Foo

    The only way I could get it to work was

    COPYIST_RECORD=1 go test -run TestDictWordList


    $ go version
    go version go1.17.5 darwin/arm64

    go.mod has v1.4.1
    opened by shaneHowearth 3
  • Query arguments are not stored or verified

    Query arguments are not stored or verified

    The arguments to a query are not stored when creating a recording, and not verified when playing back recording (obviously because there is nothing to verify with). This means that it is possible to change the arguments to a query without failing existing tests, as long as the sequence of queries does not change. As an example, consider this line in the simple query test: If we change the id argument to 2 or 50 the result should change to a different name or no rows in the result set respectively. However running the test with playback (I used pqtest but I think it should be the same for all) results in a pass with no warning that the tests need to recorded again. If you update the test to look for the correct name or error then the test fails and gives a message suggesting you might need to redo the recording. I know this example is a bit silly because you are changing the test without changing the expected result or redoing the recording, but the same thing could happen if you make changes in your application logic which ends up affecting the arguments to a query.

    Is there a reason to not include the query arguments in the recording? I don't think serialization should be an issue because the library already handles that for the results.

    opened by avgeorge 3
the scalable, survivable, SQL database
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

XO 3.3k Jan 8, 2023
Write your SQL queries in raw files with all benefits of modern IDEs, use them in an easy way inside your application with all the profit of compile time constants

About qry is a general purpose library for storing your raw database queries in .sql files with all benefits of modern IDEs, instead of strings and co

Sergey Treinis 26 Dec 25, 2022
Database Abstraction Layer (dbal) for Go. Support SQL builder and get result easily (now only support mysql)

godbal Database Abstraction Layer (dbal) for go (now only support mysql) Motivation I wanted a DBAL that No ORM、No Reflect、Concurrency Save, support S

徐佳军 56 Nov 17, 2022
A Go (golang) package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.

ozzo-dbx Summary Description Requirements Installation Supported Databases Getting Started Connecting to Database Executing Queries Binding Parameters

Ozzo Framework 589 Dec 31, 2022
LBADD: An experimental, distributed SQL database

LBADD Let's build a distributed database. LBADD is an experimental distributed SQL database, written in Go. The goal of this project is to build a dat

Tom Arrell 379 Nov 29, 2022
Additions to Go's database/sql for super fast performance and convenience. (fork of gocraft/dbr)

dbr (fork of gocraft/dbr) provides additions to Go's database/sql for super fast performance and convenience. Getting Started // create a connection (

Free and open source software developed at Mail.Ru 162 Dec 31, 2022
Go library for accessing multi-host SQL database installations

hasql hasql provides simple and reliable way to access high-availability database setups with multiple hosts. Status hasql is production-ready and is

Yandex 106 Dec 28, 2022
Document-oriented, embedded SQL database

Genji Document-oriented, embedded, SQL database Table of contents Table of contents Introduction Features Installation Usage Using Genji's API Using d

Genji 1.1k Jan 1, 2023
Generate a Go ORM tailored to your database schema.

SQLBoiler is a tool to generate a Go ORM tailored to your database schema. It is a "database-first" ORM as opposed to "code-first" (like gorm/gorp). T

Volatile Technologies Inc. 5.5k Jan 9, 2023
A Golang library for using SQL.

dotsql A Golang library for using SQL. It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and

Gustavo Chaín 653 Dec 27, 2022
a golang library for sql builder

Gendry gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepar

DiDi 1.4k Dec 26, 2022
SQL builder and query library for golang

__ _ ___ __ _ _ _ / _` |/ _ \ / _` | | | | | (_| | (_) | (_| | |_| | \__, |\___/ \__, |\__,_| |___/ |_| goqu is an expressive SQL bu

Doug Martin 1.8k Dec 30, 2022
SQL query builder for Go

GoSQL Query builder with some handy utility functions. Documentation For full documentation see the or GitBook. Examples // Open database a

Travis Harmon 27 Dec 12, 2022
Type safe SQL builder with code generation and automatic query result data mapping

Jet Jet is a complete solution for efficient and high performance database access, consisting of type-safe SQL builder with code generation and automa

null 940 Jan 6, 2023
Type safe SQL query builder and struct mapper for Go

sq (Structured Query) ?? ?? sq is a code-generated, type safe query builder and struct mapper for Go. ?? ?? Documentation • Reference • Examples This

null 185 Dec 19, 2022
Fast SQL query builder for Go

sqlf A fast SQL query builder for Go. sqlf statement builder provides a way to: Combine SQL statements from fragments of raw SQL and arguments that ma

Vlad Glushchuk 92 Dec 23, 2022
💥 A lightweight DSL & ORM which helps you to write SQL in Go.

sqlingo is a SQL DSL (a.k.a. SQL Builder or ORM) library in Go. It generates code from the database and lets you write SQL queries in an elegant way.

Qishuai Liu 274 Jan 2, 2023
Fluent SQL generation for golang

sqrl - fat-free version of squirrel - fluent SQL generator for Go Non thread safe fork of squirrel. The same handy fluffy helper, but with extra lette

Ivan Kirichenko 245 Dec 16, 2022
Fluent SQL generation for golang

Squirrel is "complete". Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily respond to them. If another fork

null 5.4k Jan 6, 2023