9 ms·

The argument against clearing the database between tests (2020)

Groxx 5y ago
At a high level: yep, completely agreed. In my experience, not-cleaning the database has consistently lead to more realistic tests, near-trivial parallelism, and fewer errors. The extreme few exceptions (e.g. testing db-wide behavior, like reports) are easy to put in a special folder... and people will quickly grow annoyed with how slow they are to run in comparison, and will avoid adding them. It's a nice self-preserving feedback loop. My only recommendation / near-requirement for doing this: randomize your test order (prevents cross-test dependencies from leaking in), and consider running them twice in CI. Once with a clean DB (ensures you can bootstrap your test environments - it's easy to lose this unless you have something checking it), and once without cleaning (helps ensure you don't have imprecise tests, and just rerunning is a trivial way to get a decently-strong assertion, without having to resort to truly persistent shared databases). Or just do a weekly test with a clean db or something - bootstrapping problems are usually easy to identify and fix, it's fine if they leak temporarily. --- If you don't want to do this, or want a magic performance hack for db-bound tests while improving things: throw your test database onto a ramdisk. It's particularly easy with a dockerized DB: just use the ramdisk as the DB's volume.
- chii 5y ago
  > consider running them twice in CI that's an interesting suggestion that i have not thought about before! And it makes sense - running a test twice doesn't take _that_ much extra time, but it can catch problems with the test itself!
charcircuit 5y ago
>assert db_session.query(User).count() == 1 >assert db_session.query(User).get(user.user_id) is not None These aren't testing the same thing. Wind database clearing you are observing count going from 0 to 1, but without clearing the database you way still pass if it goes from 1 to 1 instead of 1 to 2. If you don't clear your database your database state will be considered an input to the test and it may be needed to reproduce a test.
mbell 5y ago
Truncating the DB between every test is indeed horrifically slow. However it's much faster to wrap the test in a transaction and roll it back at the end. Transaction based cleaning also allows parallel testing. That mostly leaves the argument of not writing tests that rely on the state of the database being clean. I have mixed feelings on this one. Just last week I opened a PR to fix some tests that should not have been passing but were due to an issue along these lines. The tests were making assertions about id columns from different tables and despite the code being incorrect the tests were passing because the sequence generators were clean and thus in sync. The order in which the test records were created happened to line up in the right way that an id in one table matched the id in another table. So, I get the pain. But I'm not yet convinced it's worth a change. Another option that I think isn't a bad approach is the default testing setup that Rails uses. Every test runs in a transaction but the test database is also initially seeded with a bunch of realistic data (fixtures in Rails lingo). This makes it impossible to write a test that assumes a clean database while also starting every test with a known state.
- commandlinefan 5y ago
  > Truncating the DB between every test is indeed horrifically slow Using a database at all in unit tests is horrifically slow - one of the (many) reasons you shouldn’t.
  - srer 5y ago
    One supposes horrifically slow might be a bit subjective. I notice in a VM on my laptop establishing the initial connection to postgres seems to take 2-3ms, and running a trivial query takes 300-1000us. I routinely involve the database in unit tests, it is certainly slower but my primary concern is the correct behavior of production code which uses real databases.
    - edgyquant 5y ago
      If testing using the db is slowing you down that means the test has discovered slow code, and worked, not that you should get rid of the test.
barrkel 5y ago
Test data only has a "realistic shape" in some applications; applications with short small transactions and low cardinality relationships. I agree keeping the data isn't a bad idea in those cases. It's probably normal in B2C apps. Some B2B apps have large background transactions with 100k to 10s of millions of rows inserted or updated (with application controlled queuing rather than only relying on database locking), and you won't get realistically shaped data long term on unit tests designed to run in reasonable time. It's just as easy to end up with a performance problem in test code that you never see in production. Except when you have a customer who tests your service remotely on an automated basis. That can be annoying.
blondin 5y ago
people keep re-discovering these things over and over again. i wonder if this is due to microservices and the desire to avoid "big" frameworks? as for loading test data, you could go hybrid by loading all of it once, upfront, and then using database transactions.
hk1337 5y ago
Unit tests should be small, specific enough tests that they should not ever require using the database. End-to-end tests, behavioral tests, integration tests however can involve the database as you can actually test how the application responds to requests. I don’t know if it’s laziness, IDGAF, ignorance, stubbornness, or what but the above tests seemed to have merged recently where we now have “unit tests” that are actually behavior tests.
- dragonwriter 5y ago
  > Unit tests should be small, specific enough tests that they should not ever require using the database. That's somewhat architecture dependent; in a microservice architecture, it can be quite reasonable to see the service boundary as the unit boundary, in which case a DB-involved “unit test” makes some sense. You probably still also want fairly complete DB-independent tests that you can run automatically as you code, but the idea that the latter fully encompass unit tests is, I think, an artifact of predominantly monolithic architecture of the time the language developed, where you’d have multiple logical units in monolithic app that would then connect to a database (which even the monolith might not fully own).
  - goblin89 5y ago
    The “unit” in “unit testing” is unambiguous across the industry, referring to a function or a class that you want to verify the implementation of. Trying to impose another meaning onto the word is a sure way to complicate knowledge exchange with unnecessary misunderstanding (I certainly would think twice about joining a team that invents its own bespoke language to reference common concepts). Testing a unit of any complexity is already a can of worms; with multitudes of factors like hardware conditions, memory state, or phases of the moon causing Heisenbugs what we need is more predictability, not less. So if some runtime quirk at the database server, unavailable connection, etc. has the power to fail your tests, call those tests “integration”, “component”, “service”, “behavior”, “e2e”, anything else—the available vocabulary is rich enough that it doesn’t really call for overloading existing designations.
- cwbrandsma 5y ago
sa46 5y ago
If we could wave a medium-sized magic wand to make the database fast for tests (as least as fast as the proposed alternative), would we still use a single shared database for all tests? I would use a separate database per test since I don't find the arguments about realistic data shape convincing. Back the real world: since the database isn't fast, we can either make the test infrastructure faster or hoist the problem into the application domain by using tricks like the OP. I'm a platform engineer at heart so I lean towards making the infra faster in order to accelerate app development. My hot take is that developers are too comfortable with slow test databases and we're due for an "esbuild moment". With appropriate elbow grease, some databases, like Postgres, can be quite speedy (~300ms to start a cluster for a suite and ~40ms per test). The main tricks: - Avoid Docker. Postgres is cross platform and Docker on macOS is sloooow. - Create a single database cluster (Postgres lingo). Initialize a fresh database in the cluster with the seed data. Use that database as a template for all future tests. Creating from a template was 12x faster than creating from scratch in a benchmark I ran. - Use a ram-backed file system. Using /tmp is probably good enough since its backed by tmpfs which should use RAM for small files. - Disable fsync for the database. I wrote up a more detailed take for Postgres here: https://news.ycombinator.com/item?id=28472062 https://news.ycombinator.com/item?id=28472062.
- dataflow 5y ago
  I'm not sure what distro you use but /tmp isn't generally tmpfs (like on Ubuntu for example), so you might want to double check before relying on that.
  - sa46 5y ago
    Nice catch, that was a neat rabbit hole [1]! I run an Ubuntu derivative so you're correct that /tmp is not mounted using tmpfs. Looks like Arch and Fedora use tmpfs and the Debian flavors do not. [1]: https://lwn.net/Articles/499410/ https://lwn.net/Articles/499410/
deleted 5y ago
[deleted]
vlovich123 5y ago
In my experience such tests quickly become unmaintainable and suddenly the correctness of the test becomes dependent on the order tests are run in preventing you from running subsets, running them in parallel, being extremely flaky (since the shape of the data is random on each machine it’s run on). This is discussed but dismissed, but tests need to be as maintainable as the code itself. I tried this on a codebase I came into that had maybe 10 end to end integration tests. This was a bit worse because each test relied on the one declared before it running. Refactored it so that we spun up a unique s3rver and tore it down for each test. Sure, this means only the test cases we write are tested. That’s a feature not a bug. If you want coverage of weird states then use proper testing tools (eg fuzzing and property testing) to generate those complex test cases. Doing so formally is a much better recipe for success then hoping upon success by incidental randomness. I went from spending a day and a half and failing to add one new unit tests of some functionality to a day of pretty straightforward test case writing once I refactored each test to be standalone. Make it easy to write and debug tests not harder and let your testing frameworks handle the heavy lifting of finding corner cases. Also, converting serial tests to parallel isn’t as complicated as the author makes it sound. You would start piecemeal and let that grow by enforcing policies in code review about what tests look like going forward while you go through the backlog of converting brokenness (running the parallel pieces in parallel and the serial pieces serially)
- cabalamat 5y ago
  > suddenly the correctness of the test becomes dependent on the order tests are run in preventing you from running subsets I once write a test library where you could annotate tests with TestA requires TestB which in turn requires TestC, and it automatically runs all the requirements for each test. This seemed to work quite nicely. > tests need to be as maintainable as the code itself. Yes, if not more so. Tests that are a hassle to use won't get used, and so are useless.
  - NickNameNick 5y ago
    In the Java ecosystem, you can do that with TestNG
von_lohengramm 5y ago
I'd like to follow this advice, but it falls apart when you have a service that interacts with or worse relies on state in external services. Best case, you can create new test data on each run in those services as part of your tests if they expose an API. However, far more often you need to prearrange test data in those external services to cover the various possible states.
- paulryanrogers 5y ago
  Why not stub or double the external service? For truly end to end that may be too fake but could help with less rigorous integration tests. Or could be an option for frequent ongoing testing, then be turned off to use real 3P services for final pre-deployment tests.
  - petepete 5y ago
    I wouldn't want my main test suite to be dependent on anything external. Ensuring a third party API is still doing what you think it's doing is a legitimate concern, but that test should be standalone and scheduled.
twhitmore 5y ago
Good article. Data & state are hugely important for testing, and it's good to see others engaging with the reality of these. I had key responsibility for a very large software system (10M LOC) which was highly configurable; everything was data-dependent, both for product configuration and the actual accounts being tested. Others had tried building test datasets using in-memory DBs, but the cost of maintaining these test datasets had already exceeded their value without even achieving 10% test coverage. Rather than avoiding the database we took the architectural choice to acknowledge it, leverage the already-available standard configuration datasets, manage & minimize the degree of changes committed (test in transactions & rollback wherever possible), and manage & minimize dependence on pre-existing account state. Within six months we were able to achieve a level of coverage & reliability where >60% of bugs were detected promptly on being introduced. Prior to this they had taken 3-5 days before end-to-end testing had confirmed detection. Use of a database in testing is not completely easy, but sometimes it can be the most efficient solution to the problem at hand.
deleted 5y ago
[deleted]
herpderperator 5y ago
Yes, this is slow because of how many database roundtrips the author is doing: @before_test def clean_session: for table in all_tables(): db.session.execute("truncate %s;" % table.name) db.session.commit() return db.session But if you instead modify your before_suite (not before_test) flow to dynamically query the table names and subsequently create/update a stored procedure in the database that has your truncate queries in it: delimiter // create procedure truncate_tables() begin truncate table1; truncate table2; truncate table3; truncate table4; ...etc... end delimiter ; and then calling `db.session.execute("call truncate_tables()")` ONCE in your before_test instead, it will be orders of magnitudes faster. I improved the test suite speed 15x at my previous job with this approach. We had a lot of tests and a lot of tables. O(n^2) to O(n)... :)
mping 5y ago
Nobody mentioned the biggest disadvantage, which is losing determinism. Good luck debugging CI tests that run locally because some other PR added some quirky row to the db. Rollbacks are not perfect because sometimes you need data to be visible outside of the tx. Tests must be simple and as close to the real thing as possible. The main reason we have unit tests instead of integration is because they are simpler to exercise and maintain, thus giving a quicker feedback in the long run.
AtNightWeCode 5y ago
Most tests should test only one aspect of the system and that aspect is most often correctness. Dirty databases make the tests non-deterministic. You can use a pre-seeded database for testing. SQLite can be used sometimes for instance. Create load tests to test the performance. Data can and sometimes should be reused for a specific set of tests. This is needed for browser tests for instance. As a person who worked on systems with thousands of tests. I cannot stress enough that there should never be any randomness added to the automatic tests. Most times it is better to add more tests then to make a test do many things.
- ryanbrunner 5y ago
  > Dirty databases make the tests non-deterministic. If this is the case, is the test really testing something useful? Tests should verify that the code works as it's intended to work. If pre-existing (but presumably valid) data can break the test, then surely there's states where whatever behaviour is being tested won't work in production? Provided the test is creating and verifying its own data, it's not really non-deterministic except in a very technical sense.
  - bsaul 5y ago
    I personnally use tests for two things : - in tdd, to make sure my code works in expected scenarios - in CI to make sure new code don't break old features. In both case, i need to control my testing environment. Randomizing inputs could be used in a third scenario for extensive testing, a bit like fuzzing. But it's yet another different usage.
    - ryanbrunner 5y ago
      I guess the disconnect is that I don't think of the state of the database as inputs, per se, but more the environment the test is running in. I wouldn't for instance necessarily need to set my clock to a fixed time (provided I'm not specifically testing code where that is relevant), or wipe out the hard drive that the code is running on for a test.
deleted 5y ago
[deleted]
helge9210 5y ago
I wonder if "xUnit Test Patterns" (http://xunitpatterns.com/ http://xunitpatterns.com/) is still relevant.
majkinetor 5y ago
I never clear database, use fuzzy tests, and combined its VERY hard in practice. The reason you do it is mostly DEVELOPER HAPPINESS and QUUICK TEST WRITTING/DEBUGGING which I can't state enough how important it is. So, integration tests. I have REST API which i test via PowerShell and pester so no transactions are possible. There are 2 options: 1. The database is dropped before each run and recreated via migrations and seed 2. The database is never dropped 3. This one possible to: run thousands of times then drop the database. It helps with randomization exhaustion of certain limited set of objects. First step lasts from 20s to several minutes depending on the size of the db so its not that bad during CI/CD run, but its BAD during development because you can't just run single test 10 times quickly but you need to wait for DB cleaning in between. That clearly sux. Oracle db is the worst here. Another thing that sux is once you have it running longer then 5 minutes, you again block development AND deployment. That sux big time. Now you want to parallelize which is mega problem since its ONE THING to run single test on database, and ANOTHER THING to run multiple tests on the single database in parallel. It can be done, but its better to forget this IMO and just make it in parallel on different databases (I did this one tests run on 10 servers each using its own db and taking set of suites of tests). Now, WRITING TESTS LIKE THIS REQUIRES LOTS OF THOUGHT, REFACTORING OF BOTH TESTS AND BACKENDS, particularly combined with FUZZY tests (where in essence you randomize test input on each run) Fuzzy tests with the non-dropping database are absolute astonishing both in its testing ability and its potential to produce flaky tests. Out of 10 failed tests, in my experience 1 is due to bug, 9 are due to flakiness of randomization and database state. And all could be fixed. But it requires infrastrucutre, tools, and methodology. 1. I log each test run on InfluxDb and dashboard them on Grafana so I can compare months or runs and see what tests are flaky and if there are any other patterns. 2. I require from developers to NOT drop the database and use randomization for any input of almost ALL attributes it has. 3. I ask for them to run serially any test thousands times without ANY error (to prevent flaky tests). Then I ask them to run them in combination with all other tests (so they don't get dependent). 4. I have tools like complete db export when tests fail, verbose communication details with any touched REST endpoints, option to quickly run single or set of test in a loop, option to change tests before run without any particular toolset (i.e. notepad/vim). This is VERY hard to do, honestly. But it totally rocks if you happen to do it right. We almost never had a bug found by a user.
deleted 5y ago
[deleted]
rvr_ 5y ago
I follow the same testing philosophy. No mocking, except for external dependencies (mainly 3rd party APIs). No cleanups, except for problematic cases. RDBMS cleanups, when needed, are done by enclosing tests with transactions and explicit rollbacks.
softwarebeware 5y ago
I can't even get past the first paragraph. It's not a "unit test" if it touches the database, by definition. It should say integration tests, or just stick with "tests."
stickfigure 5y ago
There is a much better way to reset the database: Postgres has template databases. You can clone a new database for each test like this: CREATE DATABASE test_run_1234 TEMPLATE "vanilla_database"; It's fast. Also, truncating tables doesn't reset the database; there is a lot of "static" data initialized in my migration scripts. Rerunning the migrations would be very slow. In CI, my template database is initialized with migration scripts at the beginning of each suite run. Locally, I reset the template database (and purge the generated test databases) by running a script manually.
- grogers 5y ago
  Wow, that's a fantastic feature for development! I remember once we explored various approaches to resetting the DB, because resetting the DB between tests was about half the time of the test suite. In our case the static data wasn't huge so just truncating every table then re-adding the static data was fast enough. I think originally it was doing something like a mysqldump restore.
kazinator 5y ago
def test_adding_a_user_2(session): user = make_user() db_session.add(user) # this is safe, doesn't assume no other users exist assert db_session.query(User).get(user.user_id) is not None If the database is dirty, and that user ID happens to exist in it, this test will continue to pass even if db_session.add(user) has suffered a regression such that it does nothing.

At a high level: yep, completely agreed. In my experience, not-cleaning the database has consistently lead to more realistic tests, near-trivial parallelism, and fewer errors. The extreme few exceptions (e.g. testing db-wide behavior, like reports) are easy to put in a special folder... and people will quickly grow annoyed with how slow they are to run in comparison, and will avoid adding them. It's a nice self-preserving feedback loop. My only recommendation / near-requirement for doing this: randomize your test order (prevents cross-test dependencies from leaking in), and consider running them twice in CI. Once with a clean DB (ensures you can bootstrap your test environments - it's easy to lose this unless you have something checking it), and once without cleaning (helps ensure you don't have imprecise tests, and just rerunning is a trivial way to get a decently-strong assertion, without having to resort to truly persistent shared databases). Or just do a weekly test with a clean db or something - bootstrapping problems are usually easy to identify and fix, it's fine if they leak temporarily. --- If you don't want to do this, or want a magic performance hack for db-bound tests while improving things: throw your test database onto a ramdisk. It's particularly easy with a dockerized DB: just use the ramdisk as the DB's volume.

chii 5y ago
> consider running them twice in CI that's an interesting suggestion that i have not thought about before! And it makes sense - running a test twice doesn't take _that_ much extra time, but it can catch problems with the test itself!

charcircuit 5y ago

>assert db_session.query(User).count() == 1 >assert db_session.query(User).get(user.user_id) is not None These aren't testing the same thing. Wind database clearing you are observing count going from 0 to 1, but without clearing the database you way still pass if it goes from 1 to 1 instead of 1 to 2. If you don't clear your database your database state will be considered an input to the test and it may be needed to reproduce a test.

mbell 5y ago

Truncating the DB between every test is indeed horrifically slow. However it's much faster to wrap the test in a transaction and roll it back at the end. Transaction based cleaning also allows parallel testing. That mostly leaves the argument of not writing tests that rely on the state of the database being clean. I have mixed feelings on this one. Just last week I opened a PR to fix some tests that should not have been passing but were due to an issue along these lines. The tests were making assertions about id columns from different tables and despite the code being incorrect the tests were passing because the sequence generators were clean and thus in sync. The order in which the test records were created happened to line up in the right way that an id in one table matched the id in another table. So, I get the pain. But I'm not yet convinced it's worth a change. Another option that I think isn't a bad approach is the default testing setup that Rails uses. Every test runs in a transaction but the test database is also initially seeded with a bunch of realistic data (fixtures in Rails lingo). This makes it impossible to write a test that assumes a clean database while also starting every test with a known state.

commandlinefan 5y ago
> Truncating the DB between every test is indeed horrifically slow Using a database at all in unit tests is horrifically slow - one of the (many) reasons you shouldn’t.
- srer 5y ago
  One supposes horrifically slow might be a bit subjective. I notice in a VM on my laptop establishing the initial connection to postgres seems to take 2-3ms, and running a trivial query takes 300-1000us. I routinely involve the database in unit tests, it is certainly slower but my primary concern is the correct behavior of production code which uses real databases.
  - edgyquant 5y ago
    If testing using the db is slowing you down that means the test has discovered slow code, and worked, not that you should get rid of the test.

barrkel 5y ago

Test data only has a "realistic shape" in some applications; applications with short small transactions and low cardinality relationships. I agree keeping the data isn't a bad idea in those cases. It's probably normal in B2C apps. Some B2B apps have large background transactions with 100k to 10s of millions of rows inserted or updated (with application controlled queuing rather than only relying on database locking), and you won't get realistically shaped data long term on unit tests designed to run in reasonable time. It's just as easy to end up with a performance problem in test code that you never see in production. Except when you have a customer who tests your service remotely on an automated basis. That can be annoying.

blondin 5y ago

people keep re-discovering these things over and over again. i wonder if this is due to microservices and the desire to avoid "big" frameworks? as for loading test data, you could go hybrid by loading all of it once, upfront, and then using database transactions.

hk1337 5y ago

Unit tests should be small, specific enough tests that they should not ever require using the database. End-to-end tests, behavioral tests, integration tests however can involve the database as you can actually test how the application responds to requests. I don’t know if it’s laziness, IDGAF, ignorance, stubbornness, or what but the above tests seemed to have merged recently where we now have “unit tests” that are actually behavior tests.

dragonwriter 5y ago
> Unit tests should be small, specific enough tests that they should not ever require using the database. That's somewhat architecture dependent; in a microservice architecture, it can be quite reasonable to see the service boundary as the unit boundary, in which case a DB-involved “unit test” makes some sense. You probably still also want fairly complete DB-independent tests that you can run automatically as you code, but the idea that the latter fully encompass unit tests is, I think, an artifact of predominantly monolithic architecture of the time the language developed, where you’d have multiple logical units in monolithic app that would then connect to a database (which even the monolith might not fully own).
- goblin89 5y ago
  The “unit” in “unit testing” is unambiguous across the industry, referring to a function or a class that you want to verify the implementation of. Trying to impose another meaning onto the word is a sure way to complicate knowledge exchange with unnecessary misunderstanding (I certainly would think twice about joining a team that invents its own bespoke language to reference common concepts). Testing a unit of any complexity is already a can of worms; with multitudes of factors like hardware conditions, memory state, or phases of the moon causing Heisenbugs what we need is more predictability, not less. So if some runtime quirk at the database server, unavailable connection, etc. has the power to fail your tests, call those tests “integration”, “component”, “service”, “behavior”, “e2e”, anything else—the available vocabulary is rich enough that it doesn’t really call for overloading existing designations.
cwbrandsma 5y ago

sa46 5y ago

If we could wave a medium-sized magic wand to make the database fast for tests (as least as fast as the proposed alternative), would we still use a single shared database for all tests? I would use a separate database per test since I don't find the arguments about realistic data shape convincing. Back the real world: since the database isn't fast, we can either make the test infrastructure faster or hoist the problem into the application domain by using tricks like the OP. I'm a platform engineer at heart so I lean towards making the infra faster in order to accelerate app development. My hot take is that developers are too comfortable with slow test databases and we're due for an "esbuild moment". With appropriate elbow grease, some databases, like Postgres, can be quite speedy (~300ms to start a cluster for a suite and ~40ms per test). The main tricks: - Avoid Docker. Postgres is cross platform and Docker on macOS is sloooow. - Create a single database cluster (Postgres lingo). Initialize a fresh database in the cluster with the seed data. Use that database as a template for all future tests. Creating from a template was 12x faster than creating from scratch in a benchmark I ran. - Use a ram-backed file system. Using /tmp is probably good enough since its backed by tmpfs which should use RAM for small files. - Disable fsync for the database. I wrote up a more detailed take for Postgres here: https://news.ycombinator.com/item?id=28472062 https://news.ycombinator.com/item?id=28472062.

dataflow 5y ago
I'm not sure what distro you use but /tmp isn't generally tmpfs (like on Ubuntu for example), so you might want to double check before relying on that.
- sa46 5y ago
  Nice catch, that was a neat rabbit hole [1]! I run an Ubuntu derivative so you're correct that /tmp is not mounted using tmpfs. Looks like Arch and Fedora use tmpfs and the Debian flavors do not. [1]: https://lwn.net/Articles/499410/ https://lwn.net/Articles/499410/

deleted 5y ago

[deleted]

vlovich123 5y ago

In my experience such tests quickly become unmaintainable and suddenly the correctness of the test becomes dependent on the order tests are run in preventing you from running subsets, running them in parallel, being extremely flaky (since the shape of the data is random on each machine it’s run on). This is discussed but dismissed, but tests need to be as maintainable as the code itself. I tried this on a codebase I came into that had maybe 10 end to end integration tests. This was a bit worse because each test relied on the one declared before it running. Refactored it so that we spun up a unique s3rver and tore it down for each test. Sure, this means only the test cases we write are tested. That’s a feature not a bug. If you want coverage of weird states then use proper testing tools (eg fuzzing and property testing) to generate those complex test cases. Doing so formally is a much better recipe for success then hoping upon success by incidental randomness. I went from spending a day and a half and failing to add one new unit tests of some functionality to a day of pretty straightforward test case writing once I refactored each test to be standalone. Make it easy to write and debug tests not harder and let your testing frameworks handle the heavy lifting of finding corner cases. Also, converting serial tests to parallel isn’t as complicated as the author makes it sound. You would start piecemeal and let that grow by enforcing policies in code review about what tests look like going forward while you go through the backlog of converting brokenness (running the parallel pieces in parallel and the serial pieces serially)

cabalamat 5y ago
> suddenly the correctness of the test becomes dependent on the order tests are run in preventing you from running subsets I once write a test library where you could annotate tests with TestA requires TestB which in turn requires TestC, and it automatically runs all the requirements for each test. This seemed to work quite nicely. > tests need to be as maintainable as the code itself. Yes, if not more so. Tests that are a hassle to use won't get used, and so are useless.
- NickNameNick 5y ago
  In the Java ecosystem, you can do that with TestNG

von_lohengramm 5y ago

I'd like to follow this advice, but it falls apart when you have a service that interacts with or worse relies on state in external services. Best case, you can create new test data on each run in those services as part of your tests if they expose an API. However, far more often you need to prearrange test data in those external services to cover the various possible states.

paulryanrogers 5y ago
Why not stub or double the external service? For truly end to end that may be too fake but could help with less rigorous integration tests. Or could be an option for frequent ongoing testing, then be turned off to use real 3P services for final pre-deployment tests.
- petepete 5y ago
  I wouldn't want my main test suite to be dependent on anything external. Ensuring a third party API is still doing what you think it's doing is a legitimate concern, but that test should be standalone and scheduled.

twhitmore 5y ago

Good article. Data & state are hugely important for testing, and it's good to see others engaging with the reality of these. I had key responsibility for a very large software system (10M LOC) which was highly configurable; everything was data-dependent, both for product configuration and the actual accounts being tested. Others had tried building test datasets using in-memory DBs, but the cost of maintaining these test datasets had already exceeded their value without even achieving 10% test coverage. Rather than avoiding the database we took the architectural choice to acknowledge it, leverage the already-available standard configuration datasets, manage & minimize the degree of changes committed (test in transactions & rollback wherever possible), and manage & minimize dependence on pre-existing account state. Within six months we were able to achieve a level of coverage & reliability where >60% of bugs were detected promptly on being introduced. Prior to this they had taken 3-5 days before end-to-end testing had confirmed detection. Use of a database in testing is not completely easy, but sometimes it can be the most efficient solution to the problem at hand.

deleted 5y ago

[deleted]

herpderperator 5y ago

Yes, this is slow because of how many database roundtrips the author is doing: @before_test def clean_session: for table in all_tables(): db.session.execute("truncate %s;" % table.name) db.session.commit() return db.session But if you instead modify your before_suite (not before_test) flow to dynamically query the table names and subsequently create/update a stored procedure in the database that has your truncate queries in it: delimiter // create procedure truncate_tables() begin truncate table1; truncate table2; truncate table3; truncate table4; ...etc... end delimiter ; and then calling `db.session.execute("call truncate_tables()")` ONCE in your before_test instead, it will be orders of magnitudes faster. I improved the test suite speed 15x at my previous job with this approach. We had a lot of tests and a lot of tables. O(n^2) to O(n)... :)

mping 5y ago

Nobody mentioned the biggest disadvantage, which is losing determinism. Good luck debugging CI tests that run locally because some other PR added some quirky row to the db. Rollbacks are not perfect because sometimes you need data to be visible outside of the tx. Tests must be simple and as close to the real thing as possible. The main reason we have unit tests instead of integration is because they are simpler to exercise and maintain, thus giving a quicker feedback in the long run.

AtNightWeCode 5y ago

Most tests should test only one aspect of the system and that aspect is most often correctness. Dirty databases make the tests non-deterministic. You can use a pre-seeded database for testing. SQLite can be used sometimes for instance. Create load tests to test the performance. Data can and sometimes should be reused for a specific set of tests. This is needed for browser tests for instance. As a person who worked on systems with thousands of tests. I cannot stress enough that there should never be any randomness added to the automatic tests. Most times it is better to add more tests then to make a test do many things.

ryanbrunner 5y ago
> Dirty databases make the tests non-deterministic. If this is the case, is the test really testing something useful? Tests should verify that the code works as it's intended to work. If pre-existing (but presumably valid) data can break the test, then surely there's states where whatever behaviour is being tested won't work in production? Provided the test is creating and verifying its own data, it's not really non-deterministic except in a very technical sense.
- bsaul 5y ago
  I personnally use tests for two things : - in tdd, to make sure my code works in expected scenarios - in CI to make sure new code don't break old features. In both case, i need to control my testing environment. Randomizing inputs could be used in a third scenario for extensive testing, a bit like fuzzing. But it's yet another different usage.
  - ryanbrunner 5y ago
    I guess the disconnect is that I don't think of the state of the database as inputs, per se, but more the environment the test is running in. I wouldn't for instance necessarily need to set my clock to a fixed time (provided I'm not specifically testing code where that is relevant), or wipe out the hard drive that the code is running on for a test.

deleted 5y ago

[deleted]

helge9210 5y ago

I wonder if "xUnit Test Patterns" (http://xunitpatterns.com/ http://xunitpatterns.com/) is still relevant.

majkinetor 5y ago

I never clear database, use fuzzy tests, and combined its VERY hard in practice. The reason you do it is mostly DEVELOPER HAPPINESS and QUUICK TEST WRITTING/DEBUGGING which I can't state enough how important it is. So, integration tests. I have REST API which i test via PowerShell and pester so no transactions are possible. There are 2 options: 1. The database is dropped before each run and recreated via migrations and seed 2. The database is never dropped 3. This one possible to: run thousands of times then drop the database. It helps with randomization exhaustion of certain limited set of objects. First step lasts from 20s to several minutes depending on the size of the db so its not that bad during CI/CD run, but its BAD during development because you can't just run single test 10 times quickly but you need to wait for DB cleaning in between. That clearly sux. Oracle db is the worst here. Another thing that sux is once you have it running longer then 5 minutes, you again block development AND deployment. That sux big time. Now you want to parallelize which is mega problem since its ONE THING to run single test on database, and ANOTHER THING to run multiple tests on the single database in parallel. It can be done, but its better to forget this IMO and just make it in parallel on different databases (I did this one tests run on 10 servers each using its own db and taking set of suites of tests). Now, WRITING TESTS LIKE THIS REQUIRES LOTS OF THOUGHT, REFACTORING OF BOTH TESTS AND BACKENDS, particularly combined with FUZZY tests (where in essence you randomize test input on each run) Fuzzy tests with the non-dropping database are absolute astonishing both in its testing ability and its potential to produce flaky tests. Out of 10 failed tests, in my experience 1 is due to bug, 9 are due to flakiness of randomization and database state. And all could be fixed. But it requires infrastrucutre, tools, and methodology. 1. I log each test run on InfluxDb and dashboard them on Grafana so I can compare months or runs and see what tests are flaky and if there are any other patterns. 2. I require from developers to NOT drop the database and use randomization for any input of almost ALL attributes it has. 3. I ask for them to run serially any test thousands times without ANY error (to prevent flaky tests). Then I ask them to run them in combination with all other tests (so they don't get dependent). 4. I have tools like complete db export when tests fail, verbose communication details with any touched REST endpoints, option to quickly run single or set of test in a loop, option to change tests before run without any particular toolset (i.e. notepad/vim). This is VERY hard to do, honestly. But it totally rocks if you happen to do it right. We almost never had a bug found by a user.

deleted 5y ago

[deleted]

rvr_ 5y ago

I follow the same testing philosophy. No mocking, except for external dependencies (mainly 3rd party APIs). No cleanups, except for problematic cases. RDBMS cleanups, when needed, are done by enclosing tests with transactions and explicit rollbacks.

softwarebeware 5y ago

I can't even get past the first paragraph. It's not a "unit test" if it touches the database, by definition. It should say integration tests, or just stick with "tests."

stickfigure 5y ago

There is a much better way to reset the database: Postgres has template databases. You can clone a new database for each test like this: CREATE DATABASE test_run_1234 TEMPLATE "vanilla_database"; It's fast. Also, truncating tables doesn't reset the database; there is a lot of "static" data initialized in my migration scripts. Rerunning the migrations would be very slow. In CI, my template database is initialized with migration scripts at the beginning of each suite run. Locally, I reset the template database (and purge the generated test databases) by running a script manually.

grogers 5y ago
Wow, that's a fantastic feature for development! I remember once we explored various approaches to resetting the DB, because resetting the DB between tests was about half the time of the test suite. In our case the static data wasn't huge so just truncating every table then re-adding the static data was fast enough. I think originally it was doing something like a mysqldump restore.

kazinator 5y ago

def test_adding_a_user_2(session): user = make_user() db_session.add(user) # this is safe, doesn't assume no other users exist assert db_session.query(User).get(user.user_id) is not None If the database is dirty, and that user ID happens to exist in it, this test will continue to pass even if db_session.add(user) has suffered a regression such that it does nothing.