For a long time I've used similar approaches for testing against Postgres, especially when using tools like Testcontainers. It's saved a massive amount of time waiting for my tests to run over the years and it's incredibly simple to implement, even manually.
1. Set up a container image based on our prod Postgres DB's version with current prod migrations pre-applied.
2. Configure Testcontainers to be reuse the container between tests, at least within the same file.
3. ~25 lines of init code that apply local-branch migrations to a template DB and copy test data to it on the first test.
4. When the template DB already exists, test setup just drop the DB and copy it fresh from the template DB.
I find tests that actually perform the actions catch a heck of a lot more bugs and are easier to setup up than code that uses a lot of mocking or "test implementations" of a class. And with just a little care the performance penalty is negligible and way more than worth it.
Just want to say thank you, Brandur, for checking out pgtesdb and benchmarking it so thoroughly. I’m pleased that it performs so well and I’m going to throw some tokens and brainpower at potentially implementing a cleanup+reuse+pool of successful dbs instead of always tearing them down.
Some confusion in the threads below —
pgtestdb is just a primitive for “give my test a clean db, fast.” With your postgres running on ramdisk, I don’t think there’s any faster way to make a clean and fully migrated db — and your migrations only run one time, no matter how many test processes you have operating concurrently or how many tests are in parallel within those processes.
You can actually combine it with test transactions, you’re totally allowed to do anything you want with the db! It just so happens that it’s fast enough (in my experience) to Just Give Every Test Its Own Database, for quite a large number of tests.
Really cool upside of AI is enabling experiments like this one that previously would have been prohibitively time consuming. Thanks again, Brandur.
I haven't had time to try it, but I thought running PG on a copy-on-write filesystem with a specific "clone template" incantation would get you instant clones? Probably doable in a docker container?
https://boringsql.com/posts/instant-database-clones/
In the past few years I've been using a "dirty db" approach to testing where I run parallel integration tests against a single postgres-based backend without any cleanup between tests. Every test hits the same db with unique ID's. The constraint is that you can't assert exact counts, you need to assert that specific ID's exist. With this setup, the db just gets setup once and all the tests can run in parallel. Integration points are where I've seen the most breakage in prod, so I like to test with a real db. Parallelism makes it fast and incidentally this approach sometimes catches racy interactions that otherwise might only see with concurrent prod requests (heisenbugs in waiting). Many tests hitting the API's in parallel ends up being a better simulation of prod behavior.
I like this approach too, just have to build your tests around the expectation that there will be unrelated/residual test data. This is also very useful when you're testing the frontend too like Playwright tests where cleaning up data requires helper endpoints or db connection info and libraries.
What I really don't like is mocking dependencies and if a function gets called you pass, because so much more can actually go wrong like transaction locks, SQL issues, data issues, UI issues.
Same approach here. Sometimes it's hard to debug. "Why is this weird test failing for an unrelated change", but I have to remind myself that it's a real concurrency bug that would have otherwise gone unnoticed. Worth the effort in my book!
The original developer of a Rails project I inherited decided to seed the test db with db/seed.rb and tie every test to the content of the seed. There were a lot of tests so rewriting then was not an option, not immediately.
I wrote the new tests in the canonical way but I still had the problem of all those seconds spent seeding the db even when I run a single test. I wrote a couple of scripts that dumped the db at the end of the tests (if a dump did not exist yet) and reloaded it at the beginning of the tests. This is much faster. I still have to clear the test db and reseed it when I switch branch, because I don't have a dump list branch.
Maybe I can create a template with the data in it. Or finally rewrite every single old test.
As long as you're using Postgres, I would definitely try out an approach based on template databases in your situation. It should check all the boxes that a run with `db/seed.rb` would, except an order of magnitude or two compared to what Rails would be doing (i.e. parsing a big Ruby file and loading schema in table by table).
One of the best things about LLMs is they make these sorts of refactors entirely plausible even if you're not a subject area expert. You could probably prototype this and have a patch ready in an hour or two.
If you don't need SET LOCAL or different isolation levels in your tests, the fastest by far in my experience is to have a template DB per process (perhaps migrating it once and then using it as template to make copies), then run your tests wrapped in a transaction that you rollback at the end.
Postgres rollback is essentially instant, since all the cleanup is left to a later vacuum. Postgres can handle "nested transactions" (savepoints), so in most cases you don't have to modify your code.
For the small set of tests that aren't compatible with being wrapped in a transaction, you run them serially in each process and either DELETE or TRUNCATE CASCADE in between for cleanup. DELETE is a bit faster, but then you have to deal with foreign key issues yourself.
There's more that can go wrong when relying on transaction rollback. But in terms of speed, I don't know of a faster way.
Yeah, I've generally recommended using test transactions during test cases [1] as they're extremely fast. I'm open to alternatives like Peter's approach here though because the template approach has some major advantages. The biggest one is that because a database isn't rolled back, state is left around for a failing test, and that can occasionally be really useful when trying to debug a particularly difficult test bug.
Test transactions do occasionally cause other trouble too. DDL is theoretically transaction-safe in Postgres, but when running concurrent schema changes even in test isolation, you can still have tests that leak into each other. Testing anything based on listen/notify is also difficult in a test transaction.
Probably not a bad strategy is to have both tools available in your test helpers: (1) test transactions for the common case, and (2) template databases when you need more isolation.
However, as I alluded to in the second part of the article, in River we're currently using an approach similar to template databases in that every test case operates in its own isolated schema, but with the twist that we also reuse schemas after successful tests, saving us a lot of time in setup costs and bringing us back closer to test transaction performance. Great isolation and our tests are extremely fast, so it's working well.
Our db clearing takes like 5ms (large schema from mature company, not a toy). We start by restoring a production schema dump which ensures our test db / devdb schema is basically identical to what we run in production. Any migrations you are working on in your branch get added to the restore of the prod schema after it runs. Building a schema from ORM definitions is what you do if you don't care about your life or time. Clearing the test db takes something similar. This is fine, using template db's is a good way to make a scratch copy of a local database to test migrations (rsync is better if you are technical enough to use it to restore after destructive changes).
Timings:
- create testdb: 9ms
- restore prod schema: 500ms (done once per test process)
- clear test data in 96 tables between tests that write to db (5ms)
The fastest way to clear a test db is to run a query to get every schema/table name, then run ";".join("`DELETE FROM {schema}.{tablename};" for schema, table in my_tables) after putting the db in replica mode. This takes single digit ms a lot of the time even with a decent amount of test data. I've done it every which way and this is by far the fastest way to clear data between tests.
-- This will work on basically any postgresql database with basically any schema so just use it.
test_db_2235191=# CREATE OR REPLACE PROCEDURE public.delete_all_table_data()
LANGUAGE plpgsql
AS $procedure$
DECLARE
target record;
previous_replication_role text;
BEGIN
previous_replication_role :=
current_setting('session_replication_role');
PERFORM set_config('session_replication_role', 'replica', true);
BEGIN
FOR target IN
SELECT namespace.nspname AS schema_name,
relation.relname AS table_name
FROM pg_catalog.pg_class AS relation
JOIN pg_catalog.pg_namespace AS namespace
ON namespace.oid = relation.relnamespace
WHERE relation.relkind = 'r'
AND namespace.nspname NOT LIKE 'pg\_%' ESCAPE '\'
AND namespace.nspname <> 'information_schema'
ORDER BY namespace.nspname, relation.relname
LOOP
RAISE NOTICE 'Deleting %.%',
target.schema_name,
target.table_name;
EXECUTE format(
'DELETE FROM %I.%I',
target.schema_name,
target.table_name
);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
PERFORM set_config(
'session_replication_role',
previous_replication_role,
true
);
RAISE;
END;
PERFORM set_config(
'session_replication_role',
previous_replication_role,
true
);
END;
$procedure$;
CREATE PROCEDURE
Time: 0.840 ms
test_db_2235191=# CALL public.delete_all_table_data();
NOTICE: ... (notices removed for 96 tables)
CALL
Time: 5.855 ms
We do similar, although lean into our strict "every table as a sequence" and "all FKs are deferred" conventions and only issue DELETEs for tables that actually were inserted by the test
I concur with this approach. TRANSACTION-y tests (the default in Django) often don't quite line up with reality and make it hard to eg. drop in a breakpoint and run a server against the test's db state.
I've experimented (see below) with TEMPLATE dbs and such in Python (with inspiration from this library). IMHO the "around 100ms" mark is pretty slow for a big test suite. Interestingly, pg_restore is only twice as slow as TEMPLATEs.
We use pytest with xdist and we run as many as the system it is running on can handle. Each xdist process creates and sets up it's own test_db with a unique name (and drops it at the end of its run if possible). Setup and teardown are done via hooks in pytest. The advantage of this is the test run just needs a db running it can create a testdb on and connect to, so I can have tests running on 5 different branches or workdirs and they don't interact at all. On my threadripper machine with 256GB I could run about 60 concurrent tests, on my 9955hx machine I use day to day I can run 13. On a MBP I think it's about 8-16. There is diminishing returns with more processes.
If I was designing it from scratch I would use a single testdb and point all the python processes at it, and never clean up between tests or even between test runs. This is both faster and a better test, as I feel that clearing the db makes it very hard to detect overly broad queries unless you go out of your way to pack in a lot of extra harness data which people almost never do and is a chore.
actually no, and we have something weird for that too:
We have code that creates one master sequence then replaces every sequence in the testdb with that master sequence, so all tables pull from the same sequence. That way you can never accidentally swap two id's in an api response and have the tests pass because you coincidentally both had id 5 or whatever. We can run the tests either way (with sequences swapped out or not). We almost always leave the master sequence in place because the id-swap bug is very common and the alternative (bug caused by two id's being the same on different tables) basically never comes up.
It doesn't look like MySQL has any concept like a template database. You could still migrate up a pristine database, use mysqldump to produce a schema, then load that into a series of test databases. It of course won't be as quick as the low level copies that Postgres is doing with a template database, but it'd be faster than re-running all your migrations.
OOC — I've been trying to gather real world anecdotes on who is using MySQL these days given that even some of its biggest traditional champions like PlanetScale are talking a lot more about Postgres recently. Are you using MySQL as part of an existing project that was started years ago, or do you still intend to use it for new things going forward?
I'd just say that a nice thing about it on disk (even if you disable fsync) is that in case of a failing test, you can examine the post-run state which is occasionally extremely valuable.
Yup! If you keep your laptop on, failing dbs are investigable on tmpfs too — works fine for debugging a few tests in a loop, just can’t run the tests, sleep the computer, have lunch, and come back.
I've yet to be convinced all of this effort is worth it, compared to the ease of using a repository pattern and just using a fake for tests.
And, like, I don't think we shouldn't be doing these efforts, I guess, as they may still pay technical advancement dividends down the road or help with cheaper, faster QA envs, all-in-one e2e envs, etc... but for the unit test and service test layers, those bottom several layers of your testing pyramid, fakes for your repository interfaces is so much easier and orders of magnitude cheaper.
Some very significant disadvantages to that approach:
* It's common these days for a single operation to manipulate dozens or even hundreds of database records. Often these records are interrelated because they reference each other. So with fakes, you're faking initial inserts and then faking inserts based on other fake inserts, creating a fragile tree structure of fakes, which models reality very poorly.
* No data type validation on data inserts or updates. Put a string in the integer field? Find out in production.
* No foreign key validation (or just general capacity for checking referential integrity) so you don't find out that you're rows aren't referencing each other correctly until production.
* Similarly, no checks on primary keys, check constraints, triggers do not run, etc.
* Since you're not doing real inserts, you're not doing real updates or deletes on inserted rows. So if those latter operations are referencing the wrong ID, you don't find out until ... you guessed it, production.
* You can try to build up the fidelity of your repository/fake framework, but the more effort you put into it, the closer you are to just rebuilding a database and the slower it'll get. You'll also never achieve actual parity with what your database is doing.
* Building out these big fake frameworks is a lot of work relatively speaking (you didn't need to build out anything for your DB because your non-test code is already using it), and gets you negative gain.
There was a time a long time ago when disk I/O was a lot slower than it is now and maybe there was some argument for a repository/stub system, but that was at least a decade ago, and even then the rationale was thin. These days we have NVMes, and if you're a real speed demon and think those are too slow, you can just put an in-memory SQLite or Postgres in place for your testing and get all the performance advantages with none of the downside.
* You're just describing arranging the test setup, which is independent of storage medium.
* Is your repository interface untyped?
* Depends on the fidelity of your fake, but generally I find FKs to be an antipattern these days.
* What are you checking primary keys FOR? Uniqueness is easy and I avoid more complex constraints and triggers.
* ... I'm beginning to suspect we have a difference in terminology. I'm not saying a mock. A typical DB fake would be array or hash table backed in memory. So an insert is "real" and an update or delete would be too.
* Well, sure, but I can get 95% fidelity for 1% of the resources.
Once you've done it a time or two, setting up the "clone the db"/"erase the db for each test" pattern isn't that much work (plenty of libraries to help too).
And of course once its set up for a project, adding more tests to it is pretty straightforward. It is slower for each test run, but I had hundreds of tests running serially erasing a MySQL DB before each one and it only took a minute or so, which was well within my tolerance.
So overall I'm a fan; I think there's more benefits than drawbacks. Especially if it's SQLite, where setup is even easier.
For a long time I've used similar approaches for testing against Postgres, especially when using tools like Testcontainers. It's saved a massive amount of time waiting for my tests to run over the years and it's incredibly simple to implement, even manually.
1. Set up a container image based on our prod Postgres DB's version with current prod migrations pre-applied.
2. Configure Testcontainers to be reuse the container between tests, at least within the same file.
3. ~25 lines of init code that apply local-branch migrations to a template DB and copy test data to it on the first test.
4. When the template DB already exists, test setup just drop the DB and copy it fresh from the template DB.
I find tests that actually perform the actions catch a heck of a lot more bugs and are easier to setup up than code that uses a lot of mocking or "test implementations" of a class. And with just a little care the performance penalty is negligible and way more than worth it.
Just want to say thank you, Brandur, for checking out pgtesdb and benchmarking it so thoroughly. I’m pleased that it performs so well and I’m going to throw some tokens and brainpower at potentially implementing a cleanup+reuse+pool of successful dbs instead of always tearing them down.
Some confusion in the threads below —
pgtestdb is just a primitive for “give my test a clean db, fast.” With your postgres running on ramdisk, I don’t think there’s any faster way to make a clean and fully migrated db — and your migrations only run one time, no matter how many test processes you have operating concurrently or how many tests are in parallel within those processes.
You can actually combine it with test transactions, you’re totally allowed to do anything you want with the db! It just so happens that it’s fast enough (in my experience) to Just Give Every Test Its Own Database, for quite a large number of tests.
Really cool upside of AI is enabling experiments like this one that previously would have been prohibitively time consuming. Thanks again, Brandur.
I haven't had time to try it, but I thought running PG on a copy-on-write filesystem with a specific "clone template" incantation would get you instant clones? Probably doable in a docker container? https://boringsql.com/posts/instant-database-clones/
Possibly — but then you need a cowfs everywhere you run your tests. Templates are already really fast. Give it a shot and lmk if it’s faster!
No worries Peter. And thanks for putting together pgtestdb — such a great project! This conversion sprint was a fun little experiment.
In the past few years I've been using a "dirty db" approach to testing where I run parallel integration tests against a single postgres-based backend without any cleanup between tests. Every test hits the same db with unique ID's. The constraint is that you can't assert exact counts, you need to assert that specific ID's exist. With this setup, the db just gets setup once and all the tests can run in parallel. Integration points are where I've seen the most breakage in prod, so I like to test with a real db. Parallelism makes it fast and incidentally this approach sometimes catches racy interactions that otherwise might only see with concurrent prod requests (heisenbugs in waiting). Many tests hitting the API's in parallel ends up being a better simulation of prod behavior.
I like this approach too, just have to build your tests around the expectation that there will be unrelated/residual test data. This is also very useful when you're testing the frontend too like Playwright tests where cleaning up data requires helper endpoints or db connection info and libraries.
What I really don't like is mocking dependencies and if a function gets called you pass, because so much more can actually go wrong like transaction locks, SQL issues, data issues, UI issues.
Same approach here. Sometimes it's hard to debug. "Why is this weird test failing for an unrelated change", but I have to remind myself that it's a real concurrency bug that would have otherwise gone unnoticed. Worth the effort in my book!
The original developer of a Rails project I inherited decided to seed the test db with db/seed.rb and tie every test to the content of the seed. There were a lot of tests so rewriting then was not an option, not immediately.
I wrote the new tests in the canonical way but I still had the problem of all those seconds spent seeding the db even when I run a single test. I wrote a couple of scripts that dumped the db at the end of the tests (if a dump did not exist yet) and reloaded it at the beginning of the tests. This is much faster. I still have to clear the test db and reseed it when I switch branch, because I don't have a dump list branch.
Maybe I can create a template with the data in it. Or finally rewrite every single old test.
As long as you're using Postgres, I would definitely try out an approach based on template databases in your situation. It should check all the boxes that a run with `db/seed.rb` would, except an order of magnitude or two compared to what Rails would be doing (i.e. parsing a big Ruby file and loading schema in table by table).
One of the best things about LLMs is they make these sorts of refactors entirely plausible even if you're not a subject area expert. You could probably prototype this and have a patch ready in an hour or two.
If you don't need SET LOCAL or different isolation levels in your tests, the fastest by far in my experience is to have a template DB per process (perhaps migrating it once and then using it as template to make copies), then run your tests wrapped in a transaction that you rollback at the end. Postgres rollback is essentially instant, since all the cleanup is left to a later vacuum. Postgres can handle "nested transactions" (savepoints), so in most cases you don't have to modify your code.
For the small set of tests that aren't compatible with being wrapped in a transaction, you run them serially in each process and either DELETE or TRUNCATE CASCADE in between for cleanup. DELETE is a bit faster, but then you have to deal with foreign key issues yourself.
There's more that can go wrong when relying on transaction rollback. But in terms of speed, I don't know of a faster way.
Yeah, I've generally recommended using test transactions during test cases [1] as they're extremely fast. I'm open to alternatives like Peter's approach here though because the template approach has some major advantages. The biggest one is that because a database isn't rolled back, state is left around for a failing test, and that can occasionally be really useful when trying to debug a particularly difficult test bug.
Test transactions do occasionally cause other trouble too. DDL is theoretically transaction-safe in Postgres, but when running concurrent schema changes even in test isolation, you can still have tests that leak into each other. Testing anything based on listen/notify is also difficult in a test transaction.
Probably not a bad strategy is to have both tools available in your test helpers: (1) test transactions for the common case, and (2) template databases when you need more isolation.
However, as I alluded to in the second part of the article, in River we're currently using an approach similar to template databases in that every test case operates in its own isolated schema, but with the twist that we also reuse schemas after successful tests, saving us a lot of time in setup costs and bringing us back closer to test transaction performance. Great isolation and our tests are extremely fast, so it's working well.
---
[1] https://brandur.org/fragments/go-test-tx-using-t-cleanup
Integresql has been a very helpful tool for us - we spin up many temporary postgres dbs during e2e testing
https://github.com/allaboutapps/integresql
Our db clearing takes like 5ms (large schema from mature company, not a toy). We start by restoring a production schema dump which ensures our test db / devdb schema is basically identical to what we run in production. Any migrations you are working on in your branch get added to the restore of the prod schema after it runs. Building a schema from ORM definitions is what you do if you don't care about your life or time. Clearing the test db takes something similar. This is fine, using template db's is a good way to make a scratch copy of a local database to test migrations (rsync is better if you are technical enough to use it to restore after destructive changes).
Timings:
The fastest way to clear a test db is to run a query to get every schema/table name, then run ";".join("`DELETE FROM {schema}.{tablename};" for schema, table in my_tables) after putting the db in replica mode. This takes single digit ms a lot of the time even with a decent amount of test data. I've done it every which way and this is by far the fastest way to clear data between tests.We do similar, although lean into our strict "every table as a sequence" and "all FKs are deferred" conventions and only issue DELETEs for tables that actually were inserted by the test
https://github.com/joist-orm/joist-orm/blob/16cc73f148b6f962...
I forgot the speedup this got us on a 400-500 table schema, but it was noticeable -- curious if you could do the same / what the perf impact would be.
I concur with this approach. TRANSACTION-y tests (the default in Django) often don't quite line up with reality and make it hard to eg. drop in a breakpoint and run a server against the test's db state.
I've experimented (see below) with TEMPLATE dbs and such in Python (with inspiration from this library). IMHO the "around 100ms" mark is pretty slow for a big test suite. Interestingly, pg_restore is only twice as slow as TEMPLATEs.
https://github.com/leontrolski/postgresql-testing
I'd be interested about how all this compares to snapshotting the postrgres dir with ZFS and restoring to that, but don't have a Linux box to hand.
Interesting. How many database copies do you bring up when the test suite starts running, and how is parallelism handled?
We use pytest with xdist and we run as many as the system it is running on can handle. Each xdist process creates and sets up it's own test_db with a unique name (and drops it at the end of its run if possible). Setup and teardown are done via hooks in pytest. The advantage of this is the test run just needs a db running it can create a testdb on and connect to, so I can have tests running on 5 different branches or workdirs and they don't interact at all. On my threadripper machine with 256GB I could run about 60 concurrent tests, on my 9955hx machine I use day to day I can run 13. On a MBP I think it's about 8-16. There is diminishing returns with more processes.
If I was designing it from scratch I would use a single testdb and point all the python processes at it, and never clean up between tests or even between test runs. This is both faster and a better test, as I feel that clearing the db makes it very hard to detect overly broad queries unless you go out of your way to pack in a lot of extra harness data which people almost never do and is a chore.
Being to lazy to think or test it - does the above reset SEQUENCEs?
actually no, and we have something weird for that too:
We have code that creates one master sequence then replaces every sequence in the testdb with that master sequence, so all tables pull from the same sequence. That way you can never accidentally swap two id's in an api response and have the tests pass because you coincidentally both had id 5 or whatever. We can run the tests either way (with sequences swapped out or not). We almost always leave the master sequence in place because the id-swap bug is very common and the alternative (bug caused by two id's being the same on different tables) basically never comes up.
Anything similar possible with MySQL?
It doesn't look like MySQL has any concept like a template database. You could still migrate up a pristine database, use mysqldump to produce a schema, then load that into a series of test databases. It of course won't be as quick as the low level copies that Postgres is doing with a template database, but it'd be faster than re-running all your migrations.
OOC — I've been trying to gather real world anecdotes on who is using MySQL these days given that even some of its biggest traditional champions like PlanetScale are talking a lot more about Postgres recently. Are you using MySQL as part of an existing project that was started years ago, or do you still intend to use it for new things going forward?
Cloning a template is IO-heavy. You can speed it up further by putting postgres on a ramdisk.
Peter actually makes this exact point in the project's README. See this section here:
https://github.com/peterldowns/pgtestdb#how-do-i-make-it-go-...
I'd just say that a nice thing about it on disk (even if you disable fsync) is that in case of a failing test, you can examine the post-run state which is occasionally extremely valuable.
Yup! If you keep your laptop on, failing dbs are investigable on tmpfs too — works fine for debugging a few tests in a loop, just can’t run the tests, sleep the computer, have lunch, and come back.
Ah yep, good point. I was confusing "ramdisk" versus just "ephemeral in-memory".
Just turning off fsync is basically just as fast as a ramdisk and you can't use up all your memory with one big test.
Turning off synchronous_commit gets you most of the way without ever worrying about data corruption if something crashes mid way through.
https://www.postgresql.org/docs/current/wal-async-commit.htm...
Agreed, benchmarking, I only see a slight speedup on MacOS - https://github.com/leontrolski/postgresql-testing#:~:text=ra...
I've yet to be convinced all of this effort is worth it, compared to the ease of using a repository pattern and just using a fake for tests.
And, like, I don't think we shouldn't be doing these efforts, I guess, as they may still pay technical advancement dividends down the road or help with cheaper, faster QA envs, all-in-one e2e envs, etc... but for the unit test and service test layers, those bottom several layers of your testing pyramid, fakes for your repository interfaces is so much easier and orders of magnitude cheaper.
Some very significant disadvantages to that approach:
* It's common these days for a single operation to manipulate dozens or even hundreds of database records. Often these records are interrelated because they reference each other. So with fakes, you're faking initial inserts and then faking inserts based on other fake inserts, creating a fragile tree structure of fakes, which models reality very poorly.
* No data type validation on data inserts or updates. Put a string in the integer field? Find out in production.
* No foreign key validation (or just general capacity for checking referential integrity) so you don't find out that you're rows aren't referencing each other correctly until production.
* Similarly, no checks on primary keys, check constraints, triggers do not run, etc.
* Since you're not doing real inserts, you're not doing real updates or deletes on inserted rows. So if those latter operations are referencing the wrong ID, you don't find out until ... you guessed it, production.
* You can try to build up the fidelity of your repository/fake framework, but the more effort you put into it, the closer you are to just rebuilding a database and the slower it'll get. You'll also never achieve actual parity with what your database is doing.
* Building out these big fake frameworks is a lot of work relatively speaking (you didn't need to build out anything for your DB because your non-test code is already using it), and gets you negative gain.
There was a time a long time ago when disk I/O was a lot slower than it is now and maybe there was some argument for a repository/stub system, but that was at least a decade ago, and even then the rationale was thin. These days we have NVMes, and if you're a real speed demon and think those are too slow, you can just put an in-memory SQLite or Postgres in place for your testing and get all the performance advantages with none of the downside.
* You're just describing arranging the test setup, which is independent of storage medium.
* Is your repository interface untyped?
* Depends on the fidelity of your fake, but generally I find FKs to be an antipattern these days.
* What are you checking primary keys FOR? Uniqueness is easy and I avoid more complex constraints and triggers.
* ... I'm beginning to suspect we have a difference in terminology. I'm not saying a mock. A typical DB fake would be array or hash table backed in memory. So an insert is "real" and an update or delete would be too.
* Well, sure, but I can get 95% fidelity for 1% of the resources.
Once you've done it a time or two, setting up the "clone the db"/"erase the db for each test" pattern isn't that much work (plenty of libraries to help too).
And of course once its set up for a project, adding more tests to it is pretty straightforward. It is slower for each test run, but I had hundreds of tests running serially erasing a MySQL DB before each one and it only took a minute or so, which was well within my tolerance.
So overall I'm a fan; I think there's more benefits than drawbacks. Especially if it's SQLite, where setup is even easier.