dbt-unit-testing VS integresql

If you use dbt for transformations Dbt Unit Testing (https://github.com/EqualExperts/dbt-unit-testing) is getting some attention (https://www.thoughtworks.com/radar/languages-and-frameworks?blipid=202304042)

dbt-unit-testing

We use this and take an example-based tests approach for any non-trivial tables: https://github.com/EqualExperts/dbt-unit-testing

> How do you test some SQL logic in isolation?

I do this using sql

1. Extracting an 'ephemeral model' to different model file

2. Mock out this model in upstream model in unit tests https://github.com/EqualExperts/dbt-unit-testing

3. Write unit tests for this model.

This is not different than regular software development in a language like java.

I would argue its even better better because unit tests are always in tabular format and pretty easy to understand. Java unit tests on other hand are never read by devs in practice.

I haven't done it yet but there are some popular blogs as well as a DBT package someone created.

> I really don’t understand the communities obsession with unwieldy tools like DBT.

It lets me write test first sql transforms. I never thought TDD sql would be possible. My sql is so much more readable with common logic extracted into ephmeral models. I practice same method to write clear code to write sql, eg: too many mocks = refactor into separate model ( class) .

I think DBT made this possible with refs that can be swapped out with mocks. This is the awesome library I am using https://github.com/EqualExperts/dbt-unit-testing

For the case of PostgreSQL I've found IntegreSQL and its Javascript client really helpful because it can create a copy of the database per test case, which it helps to write integration tests with real DB calls.

Mocking has some advantages, but so does using a real database, at work we use https://github.com/allaboutapps/integresql and I quite like the approach that integresql has, since it makes possible to have a fresh database with your dummy data for every test without impacting the execution speed (compared to dropping an re-creating the database).

Happy to hear that! When it comes to testing services that depend on PostgreSQL, this is still my preferred solution.

https://github.com/allaboutapps/integresql

disclaimer: author

I haven't had a change to try it yet, but IntegreSQL[0] looks like this on steroids. It allows you to create a template (runs migrations and seed dates), and then uses Postgres's built in cloning functionality to maintain a pool of fresh databases. They claim 500ms to clone a database without the pool, and that the pool pretty much hides the latency entirely.

[0]: https://github.com/allaboutapps/integresql