Comment by _hl_

Docker Compose is a super easy way to run Postgres, Redis, etc. alongside your tests, and most CI platforms can either use a Compose file directly or have a similar way of running service containers alongside your tests. Example: https://docs.github.com/en/actions/using-containerized-servi...

Typically you'd keep the database container itself alive, and you would run the schema migrations once at startup. Then your test runner would apply fixtures for each test class, which should set up and tear down any data they need to run or that they create while running. Restarting the database server between each test can be very slow.

The test data is a harder problem to solve. For unit tests, you should probably be creating specific test data for each "unit" and cleaning up in between each test using whatever "fixture" mechanism your test runner supports. However, this can get really nasty if there's a lot of dependencies between your tables. (That in and of itself may be a sign of something wrong, but sometimes you can't avoid it or prioritize changing it.)

You can attempt to anonymize production data, but obviously that can go very wrong. You can also try to create some data by using the app in a dev environment and then use a dump of that database in your tests. However, that's going to be very fragile, and if you really need hundreds of tables to be populated to run one test, you've got some big problems to fix.

Property-based testing is an interesting alternative, where you basically generate random data subject to some constraints and run your tests repeatedly until you've covered a representative subset of the range of possible values. But this can be complicated to set up, and if your tests aren't fast, your tests can take a very long time to run.

I think at the end of the day, the best thing you can do is decouple the components of your application as much as possible so you can test each one without needing giant, complicated test data.

We use TestContainers for this, and it's superb. It's a full instance of the DB, started for each unit test, running inside a docker container. TC does smart things to make sure it doesn't slow the suite too much.

we have the same strategy for testing against Kafka., etc.

where we care about data, we seed the db with data for a specific group of tests. Otherwise, we just nuke the db between each test.

Prior to doing this, we'd use in-memory db for tests, and a real db for runtime, using JPA / Hibernate to make things transferrable. But this was leaky, and some things would pass in tests then fail at runtime (or vice versa)

TestContainers has been so much better, as we're running against a real version of the database, so much smaller chance of test and runtime diverging.

TestContainers, or just assume there is a postgres running locally.

> - maintain this, eg have good, representative testing data lying around?

This can be tricky, but usually my advice is to never even being trying to do write seed data in the database unless its very static. It just gets annoying to maintain and will often break. Try to work out a clean way to setup state in your tests using code, and do not rely on magic auto increment ids. Some of the more effective ways I have found is to f.ex. have every test create a fresh customer, then the test does work on that customer. Avoid tests assuming that the first object you create will get id == 1, makes it very annoying to maintain.

> operate this? Do you spin up a new postgres DB for each unit test?

Generally I've seen a new database (schema in other dbs?) in postgres that is for testing, i.e "development_test" vs "development". The big thing is to wrap each of your tests in a transaction which gets rolled back after each test.

> maintain this, eg have good, representative testing data lying around

This is much harder. Maintaining good seed data - data that covers all the edge cases - is a large amount of work. It's generally easier to leave it up to each test to setup data specific to their test case, generalizing that data when possible (i.e if you're testing login endpoints, you have all your login test cases inherit from some logic specific data setup, and they can tweak as needed from there). You will end up with duplicated test setup logic. It's not that bad, and often you don't really want to DRY this data anyways.

That being said, if you have the time and resources to maintain seed data it's absolutely a better way to go about it. It's also beneficial outside of tests.

Create one DB for the whole test suite, and then re-instantiate tables/schemas on every unit test.

I have tried various approaches and here's what worked best, assuming that there is some natural way to partition most of the data (e.g. per account):

1. Init the DB with some "default" data - configuration, lookup tables, etc

2. Each test in the test suite owns its data. It creates a new account and inserts new records only for that account. It can for example create users on this account, new entities, etc. It can run multiple transactions, can do rollbacks if needed. It is important to only touch the account(s) created by the test and to avoid touching the initial configuration. There's no need to clean up the data after the test finishes. These tests can run concurrently.

3. Create a separate integration test suite which runs sequentially and can do anything with the database. Running sequentially means that these tests can do anything - e.g. test cross-account functionality, global config changes or data migrations. In practice there aren't that many of those, most tests can be scoped to an account. These tests have to clean up after themselves so the next one starts in a good state.

Other approaches had tons of issues. For example if each test is wrapped with a transaction which is later rolled back then testing is very limited - tests cannot use transactions on their own. Savepoints have similar issue.

At several places I worked at, we would snapshot the production DB, and use that for testing. You cannot get more ”real-world“ than that. We would also record real requests, and replay them (optionally at increased speed) for load testing.

Obviously, there are some caveats, e.g.:

* While this approach works perfectly for some tests (load testing, performance testing, …), it does not work for others (e.g. unit testing).

* You have to be careful about PII, and sanitize your data.

I run a replicated copy of the production database on top of zfs and snapshot it before starting tests. PostgreSQL takes a few seconds to start on the snapshot and then you're off to the races with real production data. When the test suite finishes, the snapshot is discarded. This also ensures that migrations apply correctly to the production db before an actual prod is used.

I feel that trying to maintain "representative testing data" is generally not a good idea; set up the data you want/need in the test instead.

Just run PostgreSQL on your local machine, connect to that, setup a new schema for every test (fairly cheap-ish) inside a test database.

  def Test1:
    setupdb()
    obj1 = createObj1()
    obj2 = createObj2()
    have = doStuff(obj1, obj2)
    if have != want: ...

  def Test1:
    setupdb()
    obj = createObj1()
    have = doOtherStuff(obj1)
    if have != want: ...

Creating reasonably scoped reasonably contained "unit-y tests" like this means you will actually be able to understand what is going on. Too often have I seen people set up huge wads of "mock data" and then run all their tests on this. Then Test1 does something Test2 doesn't expect and you're screwed. Or worse: Test42 does something that screws Test185. Good luck with that. Or you introduce a regression somewhere and now you've got tons of data to understand.

The ideal experience is that you anonymize prod and sync it locally. Whether it's for testing or debugging, it's the only way to get representative data.

When you write mock data, you almost always write "happy path" data that usually just works. But prod data is messy and chaotic which is really hard to replicate manually.

This is actually exactly what we do at Neosync (https://github.com/nucleuscloud/neosync). We help you anonymize your prod data and then sync it across environments. You can also generate synthetic data as well. We take care of all of the orchestration. And Neosync is open source.

(for transparency: I'm one of the co-founders)

I made https://github.com/boustrophedon/pgtemp to solve this for myself

Maybe use pglite (which wraps postgres-wasm)? Readme indicates it can run in-memory.

Doesn't solve your testing-data question, but it'll save you from spinning up a new DB.

https://github.com/electric-sql/pglite/

https://github.com/electric-sql/postgres-wasm

Great answers below (test containers for example).

However, it’s not always possible.

For example:

- you use oracle db (takes minutes to start, license, hope the containers run on ARM fine, etc.) - sometimes an in memory fake is just much faster, and can be an official db on its own for people to try the product - your storage might be only available through a library by a third party provider that is not available locally.

I've been on teams where we've done this (very successfully in my opinion!) by creating helper code that automates creating a separate Postgres schema for each test, running all migrations, then running your test function before tearing it all down again. This all runs on CI/CD and developer machines, no credentials to any actual environments.

A major benefit of doing separate schemas for each test is that you can run them in parallel. In my experience, unless you have a metric ton of migrations to run for each test, the fact that your database tests can now run in parallel makes up (by a lot!) for the time you have to spend running the migrations for each test.

EDIT: usually we also make utilities to generate entities with random values, so that it's easy to make a test that e.g. tests that when you search for 5 entities among a set of 50, you only get the 5 that you know happen to match the search criteria.

My integration tests expect the db to run. If I need fixture data, those are sql and read in at the start of the suite. Each test uses its own temp db/tables and/or clears potentially old data before running.

Firstly, I'm seeing all these answers that say spin up a new server, and I have to wonder "WTF?"

No need to spin up a new server, not in a container, not in a new directory, not at all. It's pointless busywork with too many extra points of failure.

Nothing is stopping you using an existing server and creating a new DB, which takes about 1/100th the time that starting up a new server (whether in Docker or otherwise) takes.

Secondly, I don't actually do unit-testing on the database layer - there's little point in it. I test workflows against the database, not units!

What I do is create multiple 'packages' of tests, each with multiple tests. A single 'package' creates a temp db, runs its tests sequentially and then drops the temp db. Each package will setup itself with SQL statements.

This lets the tests perform tests of actual workflows, instead of testing in isolation that an object can be (de)serialised. IOW, I can test that the sequence of `addUser(); setProfilePassword(); signIn(); viewInfo(); signOut();` work as expected, and that `removeUser(); signIn();` fail with the correct error.

> Do you spin up a new postgres DB for each unit test?

Yes.

> So how do you > ... > - maintain this, eg have good, representative testing data lying around?

This one can be very easy, depending on the kind of data you're working with. Many places shall simply dump a part (or the whole if it's not too big) of the production DB into dev and pre-prod environments.

Now if there are sensitive, non-encrypted, data that even the devs cannot see, than it can get tricky (but then arguably they cannot see the logs in the clear either, etc.).

But yeah: a recent dump of the prod DB is good, representative data.

I've worked at places where pre-prod had a daily dump of the prod DB. Simple.