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

Related tags



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://[email protected]")
	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.

  • 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
  • Fix error extraction during replay in proxyConn.ExecContext

    Fix error extraction during replay in proxyConn.ExecContext

    We were using the wrong argument index for the error, causing an error to never be replayed correctly in ExecContext.

    opened by petermattis 3
  • README syntax highlighting

    README syntax highlighting


    opened by Jolg42 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
  • 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
  • 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
  • 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
  • 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
  • recordingFile.Parse sliently fail when line is too long

    recordingFile.Parse sliently fail when line is too long

    The Problem is Scanner.Scan() is limited to MaxScanTokenSize. You will get bufio.ErrTooLong error. but there is no handling code.

    I think it should tell the user or use reader.ReadString instead

    opened by panotza 0
  • avoid nil-pointer dereference in IsOpen

    avoid nil-pointer dereference in IsOpen

    Avoid a nil-pointer dereference in IsOpen() when that function is called without a copyist driver having been registered.

    opened by petermattis 0
  • 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
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 543 Oct 13, 2021
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 2.9k Oct 15, 2021
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 126 Oct 11, 2021
golang orm and sql builder

gosql gosql is a easy ORM library for Golang. Style: var userList []UserModel err := db.FetchAll(&userList, gosql.Columns("id","name"), gosql.

RushTeam 147 Sep 24, 2021
SQL builder and query library for golang

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

Doug Martin 1.3k Oct 22, 2021
igor is an abstraction layer for PostgreSQL with a gorm like syntax.

igor igor is an abstraction layer for PostgreSQL, written in Go. Igor syntax is (almost) compatible with GORM. When to use igor You should use igor wh

Paolo Galeone 84 Aug 2, 2021
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 750 Oct 12, 2021
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 27 Oct 13, 2021
Query git repositories with SQL. Generate reports, perform status checks, analyze codebases. 🔍 📊

askgit askgit is a command-line tool for running SQL queries on git repositories. It's meant for ad-hoc querying of git repositories on disk through a

Augmentable 2.6k Oct 20, 2021
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 231 Sep 26, 2021
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 148 Sep 22, 2021
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 4.3k Oct 24, 2021
gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

Sang-gon Lee 40 Oct 9, 2021
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 84 Sep 7, 2021
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 19 Sep 30, 2021
Go fearless SQL. Sqlvet performs static analysis on raw SQL queries in your Go code base.

Sqlvet Sqlvet performs static analysis on raw SQL queries in your Go code base to surface potential runtime errors at build time. Feature highlights:

QP Hou 428 Sep 17, 2021
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 601 Oct 15, 2021
Golang Sequel ORM that support Enum, JSON, Spatial and many more

sqlike A golang SQL ORM which anti toxic query and focus on latest features. Installation go get Fully compatible with nat

SianLoong 15 Oct 18, 2021
A Go library for collecting sql.DBStats in Prometheus format

sqlstats A Go library for collecting sql.DBStats and exporting them in Prometheus format. A sql.DB object represents a pool of zero or more underlying

Daniel Middlecote 128 Aug 8, 2021