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.
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.
at my company they just told us to stop reporting edge cases. much easier, much cheaper.
Most places aren't like that, at least the ones I've seen. :)
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 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
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.
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"
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.
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).
Note that the tests are run in parallel however.
> 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.
Edit: Ok, example helps. https://dotnet.testcontainers.org/examples/aspnet/
I can declare docker infrastructure from my code, right?
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.
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).
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.
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.
Snowflake, on the other hand, is just special.
Of course most people who have complex queries are probably not using sqlite and so may not care about testing the database.
That is by installed instances. Whether that translates to the amount of developers is not so clear.
SET SESSION synchronous_commit TO OFF;
(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.
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.
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.
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.
jokes aside, redshift is based on pg^1, you can try an older version to get some semblance of it running locally.
1. https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-an...
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.