We spin up a docker container running the DB technology we use, run our DB migration scripts on it, and then run integration tests against it. You get coverage of your migration scripts this way too.
This is what we did at my last job. You can catch DB specific issues that a false implementation wouldn’t show and make sure all your code paths work as expected.
Every time new issues cropped up we would put new data in the test data designed to reproduce it. Every edge case we would run into.
It provided so much confidence because it would catch and trigger so many edge cases that testing with mocks or by hand would miss.
Edit: also, it’s great for test/dev environments. You don’t have to worry about losing important data or filling new environments with data. Just start with the full test data and you’re good to go. It’s got stuff for all the corner cases already. Something got screwed up? Blow it away and reload, it’s not precious.
same here. every backend service with a sql datastore runs an real ephemeral db instance during CI, runs the normal migrations against it, and uses it for tests of the direct data access/persistence code. everything else in the service with a dependency on the data-access stuff gets a mocked version, but testing the actual data access code against a real datasource is non-negotiable IMO.
We did something similar, being a small company we used batch files (checked into source control) to run database migrations on the different platforms we supported. Most database platforms have command line tools, although you need to be careful as there can be subtle differences in behaviour between the command line tools & those running with a UI.
im not saying its right im just saying thats how it is, apparently i shouldnt joke about that stuff because i was downvoted to minus three.
our website crashes when the user types a double space. their solution is to have us tell users to stop typing in double spaces. since it only happens a few times a month its not considered a high priority. (details have been edited to protect the innocent)
We do this too for PostgreSQL: to ensure the tests are really fast:
- we create a template database using the migrations
- for *every* integration test we do `CREATE DATABASE test123 TEMPLATE test_template;`
- we tune the PostgreSQL instance inside Docker to speed up things, for exampling disabling synchronous_commit
On a successful test, we drop the test123 database. On a failed test, we keep the database around, so we can inspect it a bit.
The really great thing about this approach (IMHO), is that you can validate certain constraint violations.
For example, exclusion constraints are great for modelling certain use cases where overlapping ranges should be avoided. In our (go) code, the test cases can use the sqlstate code, or the constraint name to figure out if we hit the error we expect to hit.
This approach is pretty much as fast as our unit tests (your mileage may vary), but it prevents way more bugs from being merged into our codebase.
Just did a sequential run (to get some better measurements), and this is an excerpt of the things happening in the PostgreSQL instance inside the Docker container, for creating and dropping the databases:
08:25:37.114 UTC [1456] LOG: statement: CREATE DATABASE "test_1675239937111796557" WITH template = test_template
[noise]
08:25:48.002 UTC [1486] LOG: statement: DROP DATABASE "test_1675239947937354435"
Start time of first test:
2023-02-01 08:25:03.633 UTC
Finish time of last test:
2023-02-01 08:26:13.861 UTC
82 tests, or 0.856 seconds per test (sequentially).
In parallel, we take 6.941 seconds for 82 tests, or 0.085 seconds per test.
We apply the overall same strategy (individual DB for each test created from a template).
Our whole test suite (integration + unit tests) takes ~80 seconds to run for ~800 integration tests (each with their own DB) and 300 unit tests. And that's on my dev laptop (T14s, cpu: i7-1185G7) without much optimization (mainly fsync = off in postgresql.conf).
In fact, I just ran a quick test, and just putting the DB on a tmpfs cuts that time to ~40 seconds.
So overall 0.1 to 0.05 second per test on average, same ballpark as parent (and it's kind of an over estimation actually since we have a dozen or so of slow tests taking 5 to 10 seconds).
I rolled my own with docker for a few years and recently made the switch to testcontainers. So far so good - but if you’re in an environment or language where test containers are difficult, rolling your own really it ant to hard. It also keeps you honest with maintaining good migration scripts.
What is testcontainer? Reading the first page I don't understand what benefits it buys me:
> Testcontainers for .NET is a library to support tests with throwaway instances of Docker containers for all compatible .NET Standard versions. The library is built on top of the .NET Docker remote API and provides a lightweight implementation to support your test environment in all circumstances.
Yup, same. Last time i set this up i used Sqitch¹ for migrations, which encourages you to write tests for each migration; caught a lot of bugs early that way, all in a local-first dev environment. Worked especially well for Postgres since plpgsql makes it easy to write tests more imperatively.
At my job, we're breaking down a monolith into services with a hand-me-down database schema. DB changes are manual, every dev runs against a shared test DB, and everybody dreads doing schema changes. I've been looking for a way to transition into version controlled migrations and it looks like sqitch might be a solid option, as the language-specific frameworks are too opinionated. Thanks for recommending!
FWIW there are other general migration frameworks worth considering; the two most popular seem to be Flyway and Liquibase. I've heard good things about both, and don't have a particularly strong defense for my sqitch preference. I like that it's simple, has great docs, and has verification as a natural step in the workflow.
We do that too, in fact it's not only the DB that run in docker, but the whole build + CI process.
Our overall strategy is to create a master "test" DB with a test dataset, and for each test, copy this master DB to a test specific DB (CREATE DATABASE <testdb> TEMPLATE <master>) so that tests can run in parallel without interfering with each other and without the significant overhead of a "from scratch" DB initialization.
For schema migrations, we build the "branch/PR" version and the "main" version, then we check that 'init DB with "main" + migration with the "branch/PR" version' results in the same schema as 'init the DB directly with the "branch/PR" version' using apgdiff.
This strategy could probably be extended to migrating from every older version by building each tag, but we don't have that need.
We could also probably improve checks on the data itself however as for now, we only check the schemas.
Few things to note:
* it's still possible to run the tests outside of docker and use a local DB instead with some light setup (it's faster than running everything in docker when developing)
* docker argument --tmpfs <dir/of/db> is quite good, assuming you have enough ram for your dataset
* few configuration tweaks on the DB, like max connection might be necessary.
Overall, we are quite happy with this setup as it permits to implement end to end integration tests quite easily without spending too much time mocking dependencies.
As a general pattern, I find instantiating dependencies internal to your service (like a DB or Queue) to be the way to go, with mocking only for external dependencies (like external APIs) or exceptionally to reach a specific code branch (specially error handling sections).
Running sqlite in memory as a test db speeds up your test runner as crazy. You can do this if you use an sql query builder library, because it can translate your queries to the specific database.
This can be a good fast/local test or maybe a sanity test ... but there are definitely differences between databases that need to be accounted for. You wanna take that green test pass with a bit of skepticism. So you always want to test on the same DB engine that is running your prod workloads. If your surface area is small, you can get by with the approach you mentioned, but it would need to be marked tech debt that should be paid down as soon as possible, or as soon as that bug that manifests itself in PSQL but not sqlite appears :)
(Update: I just looked in our test code, you can also replace the CREATE TABLE commands with "CREATE UNLOGGED TABLE" to disable write-ahead logging.)
There are possibly other tricks?
I slightly disagree with the tech debt comment, though. If you get a huge speed up, it may be worth paying for the occasional bug, depending on the circumstances. Or you could do both, and only run the test on Postgres occasionally.
Sqlite is the most popular database in the world by a large margin. While you are correct for those who use other databases, the majority case you are wrong.
Of course most people who have complex queries are probably not using sqlite and so may not care about testing the database.
You can get the 'in-memory' speed advantage by putting the datastore on a ramdisk (or even just disabling fsync, which is pretty easy to do in postgresql).
Can split test regime so that as much as possible is covered with SQLite, and then have a second test phase with a heavyweight db only if the first phase passes. So code errors, malformed SQL, etc. cause it to fail fast and early, and you only test with the real DB once you know everything else is working.
Or along similar lines you could divide it such that developers can test things locally on their machines with SQLite, but once it gets pushed into CI (and passes code review etc.) it's tested against the heavy db.
That still doesn't fix the compatibility issues. Postgres has features/syntax that sqlite does not have, so you can't test postgres syntax with sqlite sometimes
I meant in situations like parent comment where you're using an ORM such as hibernate that supports multiple databases, you can test as much of the non-DB specific stuff with SQLite in-memory and then do a separate batch of tests with DB specific behaviour.
A real nice thing about Postgres and Mysql is that in the JVM world the H2 and HSQLDB engines have large compatibility, you can use them in-JVM for unit test speed in many cases. Doesn't help developing the SQL, does help with testing.
This may work for something simple, but a typical database cluster setup will be impossible / impractical to try to emulate in containers because you'd need to configure a lot of things not normally available inside containers (s.a. how storage is attached, how memory is allocated).
Since OP mentioned DBT (kind of weird, hopefully, it's at least DBT2, since DBT is very old), they mean to test the productivity of the system rather than correctness of some queries (typical tests that deal with workloads similar to DBT2 are, eg. pgbench). Running pgbench over a database setup in a container will tell you nothing / might just confuse you, if you don't understand the difference between how database is meant to be setup and what happens in container.
Yeah. That’s it. I have seen method with sample data (low fidelity) and/or performed against production copies of RDS DB (high fidelity). It’s still hard to catch some migrations or other operations under workload, but you can emulate that as well to a certain point.
Same (though I don't use Docker). Did TDD for a while like this, and it wasn't perfect, but it worked better than anything else. I didn't even know how to run the frontend; that was a separate team.
Absolutely this. I stood up a negative test suite for continuous DB queries late in 2020 and it's caught many potential show stopper integration issues since; about 45% more YoY than pre-suite.
Took about a week of duplicating happy path tests, investigating gaps, and switching inputs/assertion types to get everything passing, but less than a week later we had our first verifiable test failure.
This is a great way to test for backwards-incompatible changes if your fleet is running canaries or instances on different versions backed by a singleton database. You apply the migrations, checkout the app from the old version, and then re-run your test suite. Any failures are a reasonably high signal that some backwards-incompatible migration was introduced.
Sorry for the digression first. (If anyone has different definitions for the ideas here, I would love to learn.)
I think the answers would depend on the types of tests that the term "this" encompasses. From how I understand it, calling something a unit test or an integration test depends on the context of what is is being tested. For example, if a developer is writing a unit test for a HTTP handler, and the handler implementation includes calls to an external database, then one would have to use a mock for the database, in order for the test to be deemed a true unit test [1]. If the developer is writing an integration test for the same HTTP handler, then the database would have to be a real database implementation [2].
On other hand, if the developer were testing SQL queries for syntactical or logical correctness against a database, these tests would want to use a real database implementation. Note that though the test uses a real database, it is still a true unit test [3]. Additionally, note that using a mocked database here would not serve the purpose of the tests, which is to catch syntactical and logical errors against a database engine. This can, of course, only be achieved by using a real database—or, if you insisted on using a "mock", then, by implementing an entire SQL engine, with the exact same quirks as the real database's engine, inside the mock!
On the original question:
> Do you do this in place of unit tests (where you have to mock/stub the DB interactions) or do you do both?
I guess the answer would be: It would depend on the objectives of and types of tests. Do both of them, because some tests, such as unit tests on the HTTP handler, would use use mocks, while other tests, such as the SQL query correctness tests, would use the real database.
[1] A true unit test is one that has no external interactions, neither directly nor transitively, besides interactions with the system under test (SUT). The SUT here is the HTTP handler.
[2] An integration test should include external system interactions, if any. That's what integration means.
[3] The SUT is each SQL query and the its interaction with the database. There are no interactions in the test with systems outside the SUT, so it is still a true unit test.
I was wondering the other day how to classify tests that use a test double/fake like pg-mem, which isn't returning stubbed results but isn't the Dockerized test DB either :
Redshift speaks the postgres protocol so you might be able to use postgres. There are a few purpose-built docker images (googleable) that may replicate Redshift slightly better than just `docker run -p 127.0.0.1:5439:5432 postgres:latest`, but if you're at the point of having a test suite for your data warehouse code, you're likely using Redshift-specific features in your code and postgres won't suffice.
I have seen teams give each developer a personal schema on a dev cluster, to ensure their Redshift SQL actually works. The downside is that now your tests are non-local, so it's a real tradeoff. In CI you probably connect to a real test cluster.
Same. That's one of the benefits of being able to replicate your environment with something like docker compose and then use a tool like alembic to manage migrations both locally and in production.
