
Cockroachdb/copyist: Mocking an SQL database in Go tests - kiyanwang
https://github.com/cockroachdb/copyist
======
kodablah
There's only one way to do integration tests with DBs right, use a real
instance of the DB. If you need to speed up the setup/teardown process, so be
it. If you need to fashion your tests to avoid uniqueness constraints, so be
it. But every other approach such as running in transactions and then rolling
back, intercepting calls like this library does, mocking, etc will inevitably
result in missing some quirk with your SQL. FWIW, at my job, I've written
hundreds of tests that use a single local instance of CRDB harmlessly and
there aren't any performance issues of note.

~~~
eikenberry
This looks more like a caching solution to me, not a way to skip integration
tests with your DB. I can't seem to find how you invalidate the saved
interactions, but I assume it must either detect changes or you manually clear
the cache.

This seems like a reasonable solution to speed up integration tests when you
need to run them frequently. Particularly useful when adopting legacy code
that that only supports integration testing due to poor design.

------
Everlag
I am curious how many people mock their SQL queries and how they decide to
mock vs not mock at that level.

From the perspective of the services I'm knee deep in at the moment, SQL feels
like the wrong layer to mock at. Instead, when we do want to mock, we usually
pass around mocked out data access objects. That way we work at the
application layer of `GetN` rather than `SELECT a, b, c FROM N`.

It feels like a lot of the value of the tests we write that do execute sql is
a result of them actually running it. Granted, it is slower than mocking,
requires synchronous rather than parallel tests, and we need to wipe the
impacted tables for every test. However, the value we get is that we know that
a query actually has the intended semantic.

~~~
rmetzler
I may be naive, but do people with slow DB tests put the DB in a tmpfs to
improve performance?

~~~
tarjei
We do this all the time. Our tests run against SQLite (I use Mariadb in prod).

Each parallel testrun gets its own path in /dev/shm/xx so everything is in ram
as well.

~~~
ptx
If you're using SQLite, why the indirection of an in-memory filesystem rather
than SQLite's in-memory database feature?

~~~
yencabulator
Not the person you're asking this, but N connections to sqlite :memory:
backend results in N separate database instances.

This can be worked around in some scenarios, but not always & reliably. Most
importantly, you can't reach the same in-memory database from multiple
processes.

[https://www.sqlite.org/inmemorydb.html](https://www.sqlite.org/inmemorydb.html)

------
bouk
There is a better solution: you run your whole test in a database transaction,
and then roll back when the test is completed. This means you only have to set
up the database once and the overhead per test is minimal.

This is an implementation of this pattern for go: [https://github.com/DATA-
DOG/go-txdb](https://github.com/DATA-DOG/go-txdb)

Ruby on Rails has this built-in, of course.

~~~
ithkuil
But then you need to have an actual database running. I find the ability to
mock the DB entirely very useful to reduce the amount of scaffolding an
average developer needs to have in order to run the test suite.

~~~
thwarted
Maintaining the mock is scaffolding, just of a different type.

~~~
ithkuil
Sure, done by a different persona; and that's crucial:

Let's imagine (I think) a common scenario: a simple Go project, composed of a
number of modules owned by different team members or small groups, and shared
packages etc.

Now, I need to add some stateful component and I reason whether to use, say,
MySQL or something else.

If I choose MySQL and I need a real MySQL to run tests against it, now I need
to prepare some docker-compose or equivalent scaffolding and shove it down
ever other team member's throat.

I want them to be able to run all tests without having to stop and think what
tests their change (possibly in a shared package) might affect.

Sure, CI will catch things. But deferring all tests failure detection to the
CI stage adds latency, and often troubleshooting issues that happen on CI is
hard if it's hard to rerun the same thing locally.

I witnessed a "pressure" towards preferring pure-go solutions so that the team
doesn't have to switch to a more "complex" build/test harness.

Granted, this is only a problem if you managed so far to do all you needed to
do with the pure Go build system (which I have to say, I like very much and I
do need to have a pretty good reason before I abandon/"upgrade" to something
else).

~~~
jrockway
I think the scaffolding is worth it. You ask people on your team to run
"docker run --restart always --name mysql -e MYSQL_ROOT_PASSWORD=foobar -p
3306:3306 -d mysql:5.whatever", and then you never think about it again. (Also
be sure to firewall that off so only localhost can get to your database with a
predictable root password -- lots of bots out there that will "hack" this
thing in 24 hours. Try it and see!)

Compared to maintaining mocks that don't actually detect issues like invalid
queries, or missing defaults, or mapping semantics between language types and
database types, this is a lot simpler. You just need one thing running, and
you only need to set it up once. Every feature that is available to production
code is now available to your tests.

You can also have your tests launch a database container for you. I found this
slow, and that the setup overhead of one instruction in the README was
worthwhile.

Finally, you might not need the database for every test. If you have Service 1
that depends on the database and Service 2 that only depends on Service 1,
writing fakes for Service 1 for the Service 2 tests to use to avoid the
database is productive. Service 2 will want to test the error cases for
Service 1 anyway, so you will have to have the provision for things like "make
the next call to service 1 hang indefinitely", etc. so you will be writing
that anyway. Some people like all integration tests to go all the way to the
bottom of the stack, but I prefer testing only the boundary in depth and
making simpler end-to-end tests. YMMV.

~~~
ithkuil
Then you change from MySQL to postgres and you have to tell everybody to start
another container. Then you have 5 projects, are they all using the same
password?

Here we're talking about record/replay mocks, which are based on a real
database. So, of course somebody will have to run that container with MySQL so
that the tests will be run in record mode (e.g. when developing the tests).
But, then if your teammates don't develop those tests, they don't technically
need a real database. You still want them to run your tests because those
tests might depend on some course your teammates touch (e.g. some common
library code)

------
alexhornbake
This is cool, and I applaud the author’s effort. For certain projects this is
likely the right way to go.

However, another way to solve this which is safe to use when testing packages
in parallel, and let’s you work against your exact database
(driver/protocol/version etc)

Each package under test copies the database schema to a new database with the
name “{package}_{uuid}”.

TestMain in each package is responsible for: -cleanup of old databases with
the same prefix -create a new test database to work against.

Tests within a package are run sequentially, and are responsible for wiping
the package’s test DB at the start of each test.

The pattern of “defer cleanup” is avoided in favor of deleting at the start to
avoid the edge cases of crashing in the middle of a test run.

------
stephen
Interesting, this is essentially a record/reply framework at the db driver
level.

Usually I think record/reply testing is an anti-pattern b/c it's typically
used against external systems (i.e. REST/RPC API calls) where the current
state of the external system is not captured in the recording itself.

(I.e. someone clicked around in the vendor's UI to make it "look like this",
then ran record. However 6 months later when you need to re-record your test,
the state in vendor system no longer "looks like this" so it's a nightmare to
know if the test is failing due to a real regression or just changed data.)

That said, copyist records the test data being inserted/setup into the db as
well...so...huh, it might actually be a good idea.

------
Queue29
There's
[https://github.com/indeedeng/libsql](https://github.com/indeedeng/libsql)
which provides a full interface and mock implementations of the standard sql
package.

~~~
DoctorOW
LibSQL looks like it could be useful for doing something similar to this.
However, Copyist seems to be much easier to drop into a codebase.

------
cygned
What a nice work! I wrote my thesis about this approach couple years back.
Instead of generating test code, though, I saved queries in a serialized graph
which was extended/changed if necessary.

