> 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.
if random users have creds to touch the prod database at all, much less delete data / drop tables, you had a big problem before you were running tests.
You've made multiple assumptions here that couldn't be further from reality. You don't have to use production for testing or share test credentials between environments for automated tests to exploit an unintended environment, you just have to forget to clean up a `.env` file after testing a hotfix.
... is that good? Hell no. But it's a much more common version of reality than you're assuming is happening.
What you’re saying is tautological: your assumption is that tests were running in production, and your evidence is that somebody accidentally ran tests in production. It reads a bit like “Oh my, an accident? Why’ve you done that.”
Anyway, the missed point is that you can’t just do anything in tests and just expect the best of intentions to ensure it doesn’t backfire. One must also consider the security situation, infrastructure, secrets management, shared environments, and more. It’s not as simple as just plopping down a test database and expecting everything to go smoothly. You wouldn’t be that careless with other things, so don’t do it with tests, and don’t rely on “don’t run tests in production” as your only safeguard.
Do not delete develoment_test on your tests, it's supposed to be stable on your machine.
But, the one important thing is, do not give people direct access to production. And for the few that must have it, it should not be easy to connect to it.
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.