Database mocks are not worth it • Hacker News Board

Re: Database mocks are not worth it

#51

Post by maxmcd » Mon, Dec 30, 2024, 9:12 PM UTC

senex wrote: post #4

Does anyone have experience making tests against real databases fast? I resonate with the sentiment of this article, but have struggled to find an alternative that’s fast enough as the test suite grows, isn’t flakey in CI, and is able to share the production schema definition for relevant relations. I’d love to hear more from anyone that’s solved for some of these constraints!

Tests are usually embarrassingly parallel. Instead of creating one test db (app_test) create many (app_test_0, app_test_1, ...). Run tests in many threads/processes, db per thread.

This works in a lot of cases. In some cases this might not address your bottleneck.

Also someone should write a real, performant, in-memory postgres storage driver. Then we can all be happy (with pg at least).

Re: Database mocks are not worth it

#52

Post by devoutsalsa » Mon, Dec 30, 2024, 9:13 PM UTC

ariofrio wrote: post #39

rikthevik wrote: post #16

Earlier quoted context omitted.

> But there should be very few of those if you're using a framework that abstracts away the database. But I really want that database-specific behaviour. :) PostgreSQL does so many amazing things (recursive CTEs, jsonb, etc) that actively make our system better. If there was a fork of Django that optimized for leveraging advanced postgres features, I'd use it.

Does something like PGlite work for your use case? https://pglite.dev/

I want test with the same API as my database, which means using the same database. Setting up an instance of Postgres/MySQL/whatever isn't hard. Maybe harder if you're using an online database that doesn't fit into a container, but that's a different problem.

Re: Database mocks are not worth it

#53

Post by theogravity » Mon, Dec 30, 2024, 9:13 PM UTC

senex wrote: post #4

Does anyone have experience making tests against real databases fast? I resonate with the sentiment of this article, but have struggled to find an alternative that’s fast enough as the test suite grows, isn’t flakey in CI, and is able to share the production schema definition for relevant relations. I’d love to hear more from anyone that’s solved for some of these constraints!

We use Testcontainers (https://testcontainers.com/) in our node.js / vitest / kysely stack. Really fast to spin up a temporary postgres instance, and we use kysely migrations to init and seed the db.

Re: Database mocks are not worth it

#54

Post by liontwist » Mon, Dec 30, 2024, 9:14 PM UTC

diddid wrote: post #49

The whole point of the mocking the database is to not test the database! If you need to test the database then test the database! Just like mocking an API can hide hidden issues with the API… which is again the exact point of mocking the API. This article should really be named “mocking your database isn’t testing your database” which seems like it should be obvious.

Which is why any testing involving mocking should have low purity.

You get 90% of benefit from two kinds of tests: - verifying components work (unit tests) - verifying the system works from its interface (automation)

Re: Database mocks are not worth it

#55

Post by perrygeo » Mon, Dec 30, 2024, 9:14 PM UTC

Mocking the database can be done, but only if you use the database as a generic storage layer. It's not trivial to keep the mocks updated in most applications.

Some ORMs and frameworks provide magic to do this, but as soon as you deviate from the framework it gets complex. You can build geospatial applications, time series, document, and full text search - iow leverage advanced features of Postgres extensions that most ORMs can't touch. Their behaviors can be complex and are part of the application, not simply a storage layer.

Re: Database mocks are not worth it

#56

Post by smackeyacky » Mon, Dec 30, 2024, 9:14 PM UTC

ariofrio wrote: post #39

rikthevik wrote: post #16

Earlier quoted context omitted.

> But there should be very few of those if you're using a framework that abstracts away the database. But I really want that database-specific behaviour. :) PostgreSQL does so many amazing things (recursive CTEs, jsonb, etc) that actively make our system better. If there was a fork of Django that optimized for leveraging advanced postgres features, I'd use it.

Does something like PGlite work for your use case? https://pglite.dev/

You can just simply run postgres, why bother with pglite?

postgres installs easily on WSL2 or whatever Linux distribution you're using.

Re: Database mocks are not worth it

#57

Post by nerdponx » Mon, Dec 30, 2024, 9:14 PM UTC

senex wrote: post #4

Does anyone have experience making tests against real databases fast? I resonate with the sentiment of this article, but have struggled to find an alternative that’s fast enough as the test suite grows, isn’t flakey in CI, and is able to share the production schema definition for relevant relations. I’d love to hear more from anyone that’s solved for some of these constraints!

What I've done is make a clone of the real database, with a sample of data that has enough variety/size to test whatever it is you need to test, but no bigger. It definitely takes some thinking, planning, and writing of code, but it's worth doing.

Unfortunately I maintain an app where the database (read-only) is Snowflake, and being more of a "warehouse" than "database" there's always a lot of overhead in running any query at all. Even just `select 1` can take a few seconds. So there's only so much you can do with that, but setting up your data so that tests can be parallelized helps as well.

However your tests against a proper OLTP database should be plenty fast, unless your app itself is slow or your test fixtures require some really complicated setup.

Re: Database mocks are not worth it

#58

Post by zeroonetwothree » Mon, Dec 30, 2024, 9:16 PM UTC

rikthevik wrote: post #16

btilly wrote: post #3

I've found that replacing the database with in memory SQLite for tests is a sweet spot. Almost as fast as a mock, catches a lot of database issues. And it's really easy to do if you're using something like Django that makes automatically generating database migrations easy. It won't help you with database specific differences. But there should be very few of those if you're using a framework that abstracts away the d…

> But there should be very few of those if you're using a framework that abstracts away the database. But I really want that database-specific behaviour. :) PostgreSQL does so many amazing things (recursive CTEs, jsonb, etc) that actively make our system better. If there was a fork of Django that optimized for leveraging advanced postgres features, I'd use it.

Sqlite supports recursive CTE

Re: Database mocks are not worth it

#59

Post by pdimitar » Mon, Dec 30, 2024, 9:16 PM UTC

Deukhoofd wrote: post #24

pdimitar wrote: post #14

Earlier quoted context omitted.

Why a separate DB for each test? Just have only one DB and each test opens a transaction and then rolls it back when it's done. That way you also achieve isolation of tests so they don't interfere with each other.

The code being tested also uses transactions internally at times, so it'd mean additional complexity in the code being tested to allow for unit testing, which is not great. In my experience throwing up a database including all tables in an in-memory SQLite db is extremely fast, so it's not really a major concern.

OK, but is your production DB also SQLite? If not, I would not. I found the differences between it and PostgreSQL too big and was getting too many false positives.

Also code complexity on a few levels of recursion of transactions is an easy thing to abstract away with almost zero performance penalty -- depending on your programming language of choice.

Re: Database mocks are not worth it

#60

Post by napsterbr » Mon, Dec 30, 2024, 9:16 PM UTC

andy_ppp wrote: post #13

As per usual Elixir does this correctly and even has a setup that allows all tests to run against the database in a pristine way in parallel: https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.Sandbox.html

A hobby project of mine (in Elixir) uses SQLite as primary database. Each test runs in its own fully isolated SQLite database. No mocking (or transaction rolling back) needed. Most of these tests take less than 1ms to run (and when they take longer, it's because of something else).

This kind of setup makes the usual Ecto Sandbox approach feel slow, but I do agree that the way Elixir approaches this is great!