Fork me on GitHub
#testing
<
2023-10-25
>
pesterhazy19:10:36

A perennial topic is how to test code that is using an SQL db. There are two schools of thought: 1. Hit the db: This has the advantage of catching errors in SQL queries and schemas. However, it's slow: talking to an out-of-process db is orders of magnitude slower than unit tests that don't cross process boundaries. 2. Mock queries: This is fast so you get quick feedback. On the other hand, mocking DB calls manually is tedious and error-prone, and your queries are still untested (unit tests pass but prod breaks). Here's my shower thought: What if there's a 3rd option? With playback testing, we run the tests as in (1) but we record the queries and inputs as well as their outputs (a transcript of the session). Then in the next run, if input and query are remembered, we return the results from cache without making a JDBC call. (Results can be cached in a JSON file) As a result, you get fast feedback (except for the first run of each test) but you also gain confidence that your queries are correct. Has anyone used a setup like this? Any downsides I'm missing?

respatialized21:10:21

Option 3 seems useful if you can't do Option 4: which is run the DB in (or closer to) process: https://eli.naeher.name/embedded-postgres-in-clojure/

mauricio.szabo04:10:09

If you use transactions (open one at the beginning of the test, rollback at the end) it's not that slow

mauricio.szabo04:10:16

But if you want to try the third possibility, you can use vcr-clj. I actually did use in a project, it's quite powerful (but it takes some time to get the serialization and deserialization right)

pesterhazy05:10:07

Isn't EmbeddedPostgres running postgres in a docker container, i.e. out of process?

pesterhazy07:10:00

Good point about transactions @U3Y18N0UC. Some of our tests open their own transactions, which makes this option a little tricky. But you're right they cleaning tables and setup work is part of what makes the tests slow

pesterhazy07:10:17

Thanks for the pointer to vcr – that's what I was looking for. I now remember it's popular in Ruby

Phillip Mates09:10:01

To add another option: In the setup I work on, we use sqlite for local tests / dev systems and postgresql in production. There is deviation in behavior between the two, but for our use cases it is minimal and when needed we are able to inject another code-path to account for them (via integrant/component/etc.)

👍 1
pesterhazy11:10:56

Yeah definitely another option, though I'd be worried about schema differences and behavior differences (sqlite being weakly typed)

pesterhazy11:10:42

Although running code on both, i.e. sqlite (in local dev and CI) and postgres (in CI), might be an option

mauricio.szabo14:10:53

@U06F82LES if you used VCR in Ruby, you'll have a pleasant surprise - vcr-clj is *light years better* than Ruby's!

mauricio.szabo14:10:51

As for the SQLite3 - I insanely, strongly, abolutely advise against it - there are a lot of differences (transaction handling, parallel inserts and updates, SQL syntax differences, features that exist in PG that are interesting but are not that much used, datatypes, extensions, even locking) and that makes the whole situation of "supporting two databases" weird.