5 ms·
Cockroachdb/copyist: Mocking an SQL database in Go tests
- Everlag 6y agoI 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.
- xyzzy_plugh 6y agoYep, this is the pattern I usually use. It's useful to put tests that hit a real DB behind a build flag, for situations where a DB might not be present. Also since I prefer to bake migrations into my application, those get thoroughly tested as well.
- sverhagen 6y agoWe require that CI builds are self-contained. So if you need a database, run it. Others mentioned Docker. Specifically there is Testcontainers. If you need cross-service integration, those don't belong in your CI builds. (So the tech/setup can also be a bit different.)
- yashap 6y agoIn unit tests, I like to only test a single method in isolation, with all of it’s dependencies mocked. So in a domain layer method, that depends on some infrastructure layer code that itself does the DB access, I mock the DB access layer. And the DB access layer already hides the SQL, so you’re mocking “normal” objects/methods, not SQL. However, when unit testing the DB access code itself, I like to connect to a real-but-embedded database (for example with JVM projects, these guys have some great embedded DB libs: https://github.com/flapdoodle-oss https://github.com/flapdoodle-oss), with a different DB per test class (different test classes run concurrently), but no cleanup between test cases (which run sequentially). This lets me test the SQL queries themselves. Integration tests are a different story, they run against actual deployed versions of services, with real everything (including databases), nothing is mocked.
- controversy 6y agoI agree with you. SQL needs to be integration tests. In this era of Docker spinning up a DB should be easy.
- deleted 6y ago[deleted]
- jeltz 6y agoTypically there is no issue with running parallel tests against a real database. I just wrap every test case in a transaction and turn the transactions into subtransactions. This way you can just rollback at the end of each test.
- ramraj07 6y agoWe typically have multiple options in our tests - some SQL logic is testable in sqlite itself, while others (crap like arrays and datediffs) we try to run on an actual postgres instance. Snowflake has been the most annoying, since it doesn't support transactions and is also relatively slow anyways.
- rmetzler 6y agoI may be naive, but do people with slow DB tests put the DB in a tmpfs to improve performance?
- sverhagen 6y agoFor integration testing I'm sure people do all sorts of "interesting" things. For unit tests in an automated CI build, it is often more desirable to have a smaller subset of data, which presumably is less slow, since tests there also focus on more isolated workflows.
- mixedCase 6y agoI have used libeatmydata to help with this, but you just made me realize that in my current project I could just dump the test db to RAM. Thanks for the tip!
- tarjei 6y agoWe 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 6y agoIf you're using SQLite, why the indirection of an in-memory filesystem rather than SQLite's in-memory database feature?
- yencabulator 6y agoNot 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
- Queue29 6y agoThere'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 6y agoLibSQL looks like it could be useful for doing something similar to this. However, Copyist seems to be much easier to drop into a codebase.
- alexhornbake 6y agoThis 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 6y agoInteresting, 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.
- cygned 6y agoWhat 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.
- bouk 6y agoThere 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.
- thunderbong 6y agoUntil I started working in other frameworks, I thought this was the way it is done everywhere. I still don't understand why in the software world we keep reinventing the same patterns again and again ad nauseum.
- darksaints 6y agoAs long as those tests can be done within a single block of code, that works fine. A lot of ORMs and connection pools do not play nicely with complex transactions. It is surprisingly difficult to get a single transaction to work correctly if the transaction happens across multiple API calls chained together, as would happen in an integration test. It would be nice if SQL databases and clients all had support for named transactions that can be created, accessed, updated, and finished all on different connections or sessions.
- ithkuil 6y agoBut 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.
- kodablah 6y agoThere'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.
- DoctorOW 6y agoThat's essentially what this is. It looks like they just spin up cockroach in a docker container and intercept queries as they're sent for some of the analytics features.
- eikenberry 6y agoThis 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.