Something to consider (for Go users): Use the sqlc library, which makes whole classes of bugs/mistakes into compile-time errors.
Ask HN: How do you test SQL?
241–250 of 322 posts
Re: Ask HN: How do you test SQL?
#242There's pgTAP for Postgres [1], the same approach probably is valid for other databases. Here's [2] a slide deck by David Wheeler giving an introduction into how it works. [1] https://pgtap.org/ [2] https://www.slideshare.net/justatheory/unit-test-your-databa...
I was going to mention pgtap as i had used it in a previous role and it works but its cumbersome. I was hoping for a better solution by reading the comments
Re: Ask HN: How do you test SQL?
#243dbt does have testing built in, but of course there are only certain cases for which that kind of testing works. dbt can't know if your metrics 'look' right, only you will know.
As others have mentioned, you want to compare the results of your queries against a previously known 'good' state of the data. So, as you're making data model changes, you can regularly check your development environment against production to see how your changes affect the data.
Data profiling is the perfect tool for this, especially when your pipeline reaches a certain size, or you're dealing with very large datasets.
I work on the team creating PipeRider.io, which uses data profiling comparisons as a method of "code review for data".
It becomes particularly useful when you automate generating data profiles of development and production environments in CI, and attach the data profile comparison to the pull request comment. It makes seeing the impact of changes so much easier.
Here's an article that discusses the benefits of this: https://blog.infuseai.io/why-you-lack-confidence-merging-dbt...
Re: Ask HN: How do you test SQL?
#244We spin up a docker container running the DB technology we use, run our DB migration scripts on it, and then run integration tests against it. You get coverage of your migration scripts this way too.
Yup, same. Last time i set this up i used Sqitch¹ for migrations, which encourages you to write tests for each migration; caught a lot of bugs early that way, all in a local-first dev environment. Worked especially well for Postgres since plpgsql makes it easy to write tests more imperatively. ¹: https://sqitch.org/
At my job, we're breaking down a monolith into services with a hand-me-down database schema. DB changes are manual, every dev runs against a shared test DB, and everybody dreads doing schema changes. I've been looking for a way to transition into version controlled migrations and it looks like sqitch might be a solid option, as the language-specific frameworks are too opinionated. Thanks for recommending!
Re: Ask HN: How do you test SQL?
#245Back in the mid-noughties I decided to see if I could write SQL in a test-first manner (i.e. TDD). This resulted in me writing a 100% T-SQL based unit testing framework for SQL Server [1] which we then used for the SQL back-end at an investment bank.
On the back on that professional use I wrote a blog post [2] explaining why you might choose to go down this route as it wasn't the way database was developed way back then (SQL wasn't developed in the same way as the other front-end and back-end code).
A few years later I gave a short 20-minute talk (videoed) to show what writing SQL using TDD looked like for me. It's hard to show all the kinds of tests we wrote in practice at the bank but the talk is intended to show how rapid the feedback loop can be using a standard DB query tool and two code windows - production code and tests.
Be kind, it was a long time ago and I'm sure the state of the art has improved a lot in the intervening years :o).
Chris Oldwood
---
[1] SQL Server Unit: https://github.com/chrisoldwood/SS-Unit
[2] You Write Your SQL Unit Tests in SQL?: https://chrisoldwood.blogspot.com/2011/04/you-write-your-sql...
[3] Test-Driven SQL: https://www.youtube.com/watch?v=5-MWYKLM3r0
Re: Ask HN: How do you test SQL?
#246Try and write any complex SQL as a series of semantically meaningful CTEs. Test each part of the CTE pipeline with an in.parquet and an expected_out.parquet (or in.csv and out.csv if you have simple datatypes, so it works better with git). And similarly test larger parts of the pipeline with 'in' and 'expected_out' files. If you use DuckDB to run the tests, you can reference those files as if they were tables (select…
Doesn't that mean you have to copy all your data into duckdb? I'd imagine with even a modest data warehouse, loading the entire thing to duckdb would be unfeasible.
Re: Ask HN: How do you test SQL?
#247If you're using dbt, dbt tests are a good start: https://docs.getdbt.com/docs/build/tests You can hook up dbt tests to your CI and Git(hub|lab) for data PRs. Depending on your needs, you can also look into data observability tools such as Datafold (paid) or re_data (free)
I'm surprised dbt tests is this far down in the comments. This seems like the obvious place to start.
OP (or others) - If you've used dbt tests, I'm curious where it fell short? Tt doesn't cover everything, but it's pretty good in my experience.
Re: Ask HN: How do you test SQL?
#248Either a test environment or a mocking library
Re: Ask HN: How do you test SQL?
#249We spin up a docker container running the DB technology we use, run our DB migration scripts on it, and then run integration tests against it. You get coverage of your migration scripts this way too.
We do that too, in fact it's not only the DB that run in docker, but the whole build + CI process.
Our overall strategy is to create a master "test" DB with a test dataset, and for each test, copy this master DB to a test specific DB (CREATE DATABASE TEMPLATE ) so that tests can run in parallel without interfering with each other and without the significant overhead of a "from scratch" DB initialization.
For schema migrations, we build the "branch/PR" version and the "main" version, then we check that 'init DB with "main" + migration with the "branch/PR" version' results in the same schema as 'init the DB directly with the "branch/PR" version' using apgdiff.
This strategy could probably be extended to migrating from every older version by building each tag, but we don't have that need.
We could also probably improve checks on the data itself however as for now, we only check the schemas.
Few things to note:
* it's still possible to run the tests outside of docker and use a local DB instead with some light setup (it's faster than running everything in docker when developing)
* docker argument --tmpfs is quite good, assuming you have enough ram for your dataset
* few configuration tweaks on the DB, like max connection might be necessary.
Overall, we are quite happy with this setup as it permits to implement end to end integration tests quite easily without spending too much time mocking dependencies.
As a general pattern, I find instantiating dependencies internal to your service (like a DB or Queue) to be the way to go, with mocking only for external dependencies (like external APIs) or exceptionally to reach a specific code branch (specially error handling sections).
Re: Ask HN: How do you test SQL?
#250Try and write any complex SQL as a series of semantically meaningful CTEs. Test each part of the CTE pipeline with an in.parquet and an expected_out.parquet (or in.csv and out.csv if you have simple datatypes, so it works better with git). And similarly test larger parts of the pipeline with 'in' and 'expected_out' files. If you use DuckDB to run the tests, you can reference those files as if they were tables (select…
Doesn't that mean you have to copy all your data into duckdb? I'd imagine with even a modest data warehouse, loading the entire thing to duckdb would be unfeasible.
The tests are being run against small test datasets of 10s of rows rather than the real data that test whether the behaviour of transforms/joins etc. is as expected. You're right, this approach wouldn't be sensible if the tests have to use large production tables