Live in production!
A production replica if you want to be safe
Ask HN: How do you test SQL?
271–280 of 322 posts
Re: Ask HN: How do you test SQL?
#272Earlier quoted context omitted.
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
How do you get the small datasets? Selecting a random sample from the source?
The test itself would provide a test dataset. That way you can cook up the interesting cases and ensure your queries work on them.
Re: Ask HN: How do you test SQL?
#273I've had great success with this
https://news.ycombinator.com/item?id=34580675
I am always baffled by why this ins't more popular way of writing SQL.
Re: Ask HN: How do you test SQL?
#274Try 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…
I'm a fan of CTEs too. Here's a pattern that I use in Sql Server for testing/debugging when using CTE pipelines. Same approach would work with "FOR JSON"
declare @debug bit = 1;
;with cte1 as (
select
@debug AS Debug1,
...
),
cte2 as (
select
@debug AS Debug2,
... from cte1
),
cte3 as (
select
@debug AS Debug3,
... from cte2
)
select
-- dump intermediate if debug
(
select * from cte1 where Debug1=1 for xml raw ('row'), root ('cte1'), type
)
,(
select * from cte2 where Debug2=1 for xml raw ('row'), root ('cte2'), type
)
,(
select * from cte3 where Debug3=1 for xml raw ('row'), root ('cte2'), type
)
-- final results
,(
select ...
from cte3 for xml raw ('row'), type
)
for xml raw ('results'), type;Re: Ask HN: How do you test SQL?
#275Earlier quoted context omitted.
One caution with PostgreSQL, CTEs under some circumstances (and in all circumstances, prior to PostgreSQL 12) act as optimization barriers. Specify `NOT MATERIALIZED` before the CTE definition to ensure that they are optimized same as a sub-SELECT would be.
Just to flag this behaviour changes in v12: https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
Not really. CTEs are still optimization barriers after v12 if they're referenced more than once. That blog post is confused, but the commit message in the post is clear enough.
Re: Ask HN: How do you test SQL?
#276My favorite interview question. No, I mean when I'm being interviewed. The sheepish grins let me know I'm not alone. Best ideas IMO (no particular order): - make SQL dumber, move logic that needs testing out of SQL - use an ORM that allows composing, disconnect composition & test (ie EF for .NET groups, test the LINQ for correct filtering etc, instead of testing for expected data from a db) (I see this has already be…
EF core makes it simple to test against a real sqlite db. It is super fast to run the tests and gives a reasonably realistic outcome.
It does and I understand. But in my experience most of what should be tested is not "does the database contain x?" but "does this where clause filter y?"
Those sorts of things are programmed in EF by LINQ queries. All I'm saying is, test the LINQ queries the same way you would test non-EF LINQ queries. LINQ doesn't require a database.
Re: Ask HN: How do you test SQL?
#277Earlier quoted context omitted.
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!
You're very welcome!
FWIW there are other general migration frameworks worth considering; the two most popular seem to be Flyway and Liquibase. I've heard good things about both, and don't have a particularly strong defense for my sqitch preference. I like that it's simple, has great docs, and has verification as a natural step in the workflow.
Re: Ask HN: How do you test SQL?
#278Earlier quoted context omitted.
This can be a good fast/local test or maybe a sanity test ... but there are definitely differences between databases that need to be accounted for. You wanna take that green test pass with a bit of skepticism. So you always want to test on the same DB engine that is running your prod workloads. If your surface area is small, you can get by with the approach you mentioned, but it would need to be marked tech debt that…
Sqlite is the most popular database in the world by a large margin. While you are correct for those who use other databases, the majority case you are wrong. Of course most people who have complex queries are probably not using sqlite and so may not care about testing the database.
> Sqlite is the most popular database in the world by a large margin.
That is by installed instances. Whether that translates to the amount of developers is not so clear.
Re: Ask HN: How do you test SQL?
#279If 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)
And if you want/need to take it further, I love this package: https://github.com/calogica/dbt-expectations
Re: Ask HN: How do you test SQL?
#280Earlier quoted context omitted.
I think you are talking about two different DBTs. OP is referring to this one I believe: https://www.getdbt.com/
Yeah, I went to read more comments and figured as much. I don't know much about this tool, but since it is certainly newer than original DBT, and chosen to use the same name, while operating in the same domain... I wouldn't trust these people. I have a feeling they simply didn't know the older tool existed, and I generally don't want to trust people who don't know stuff about what seems like their primary business.
To be fair to them it's "dbt" in lowercase