Side note: I believe it is good form to always have unit tests that test the "up" and "down" for every single migration in your app. It's not always possible but if you're strict about it you can avoid a lot of bad patterns and have a much healthier set of migrations
Ask HN: How do you test SQL?
201–210 of 322 posts
Re: Ask HN: How do you test SQL?
#202Try 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…
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-...
Re: Ask HN: How do you test SQL?
#203Earlier quoted context omitted.
I'm sorry, i'm speaking with multiple people on multiple threads here, and don't have too much time to express myself perfectly. Every language is declarative is an interpretation that can make sense in certain angle, like when you use some imperative language, you create mini libraries for yourself, and that's already steps to a declarative variation, but this does not changes the fundamental design of the language.…
That's quite the slippery slope though. You don't literally draw a rectangle with HTML, but you also don't tell the database's query planner how to do its job. You're also not comparing things on equal terms. HTML isn't any kind of programming or scripting language. If I understand you correctly, SQL would only be declarative if the user literally wrote the entire result set of the query. This would then obviate the…
Technical and implementation details again. When we speak about programming paradigm, we speak about the language alone, not any software system, this is a theoretical discussion. It does not matter how it is executed, what matter is how you express yourself in that language to achieve your goals.
There are no bits, bytes and cpus in this conversation. This is classification and taxonomy of artificial languages created for other purpose than communication between 2 persons.
In SQL you gives instruction in a functional way of thinking. Functional programming is a restricted variation of imperative one for a purpose the same way how object oriented programming is basically structured programming with restrictions imposed on the programmer (for a purpose again, and a very good reason for that). So fundamentally SQL is imperative, but the details of that completely hidden and therefore irrelevant.
In HTML you describe the result itself. So "SQL would only be declarative if the user literally wrote the entire result set of the query" is not completely, but somewhat true. The way how you define a result can vary, but SQL definitely does not do that. But the way how i can imagine SQL as declarative, if i "programming by example" or ask chatgpt to translate my natural language and create the query. But these are forced examples, because IMO SQL is absolutely not declarative, and cant really be.
If HTML is a programming language or not, it's debatable, but i think it's enough that is a language (programming or not) which is designed to describe a flow-kind of a text layout (like a word file) in the first versions, then other visible elements, so i think it's not completely, but kinda irrelevant question from the viewpoint of paradigm.
Re: Ask HN: How do you test SQL?
#204Great Expectations and Datafold
Re: Ask HN: How do you test SQL?
#205We 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.
I've used this same approach as well. Testcontainers is a nice way to help with this! https://www.testcontainers.org/
What is testcontainer? Reading the first page I don't understand what benefits it buys me:
> Testcontainers for .NET is a library to support tests with throwaway instances of Docker containers for all compatible .NET Standard versions. The library is built on top of the .NET Docker remote API and provides a lightweight implementation to support your test environment in all circumstances.
Edit: Ok, example helps. https://dotnet.testcontainers.org/examples/aspnet/
I can declare docker infrastructure from my code, right?
Re: Ask HN: How do you test SQL?
#206Earlier quoted context omitted.
Running sqlite in memory as a test db speeds up your test runner as crazy. You can do this if you use an sql query builder library, because it can translate your queries to the specific database.
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…
On Postgres you can run
SET SESSION synchronous_commit TO OFF;
(Update: I just looked in our test code, you can also replace the CREATE TABLE commands with "CREATE UNLOGGED TABLE" to disable write-ahead logging.)
There are possibly other tricks?
I slightly disagree with the tech debt comment, though. If you get a huge speed up, it may be worth paying for the occasional bug, depending on the circumstances. Or you could do both, and only run the test on Postgres occasionally.
Re: Ask HN: How do you test SQL?
#207Earlier quoted context omitted.
I've used this same approach as well. Testcontainers is a nice way to help with this! https://www.testcontainers.org/
What is testcontainer? Reading the first page I don't understand what benefits it buys me: > Testcontainers for .NET is a library to support tests with throwaway instances of Docker containers for all compatible .NET Standard versions. The library is built on top of the .NET Docker remote API and provides a lightweight implementation to support your test environment in all circumstances. Edit: Ok, example helps. http…
Yep instead of starting the container in the build script it’s nice to start it in the test code file itself.
Re: Ask HN: How do you test SQL?
#208Test the queries your application is making. I wouldn't put much effort into this. You have to trust that other people test their stuff anyway so why make a difference with a database? I'd much rather test that your backups work.
And that can be done by dumping the database (possibly verifying the content of that dump), taking a backup, restoring the backup to a fresh container, then comparing dump of that freshly restored database to the one you took at the start.
Re: Ask HN: How do you test SQL?
#209I write mostly batch ETL stuff. All plain psql and bash. We don’t have a good testing setup to be honest. What we do use instead: Plenty of constraints, uniques and foreign keys and not nulls. Enum types. Visuals, dump to csv and plot some graphs. Much easier to find gaps and strange distributions visually. Asserts in DO blocks, mostly counts being equal. Build tables in a a _next suffix schema and swap when done. Ne…
I admit it's not the best but I do almost like you. Graphs are definitely helpful.
I also have a few 'test' queries that insert there results into a 'test_results' table. Most of the queries check the cardinality of the table since to me wrong cardinality is where the biggest errors come from. I do something like :
insert into test_table
select case when count(*) = count(distinct users) then 'pass' else 'fail' end as result, 'test_cardinality_temporary_table_a' as test_name from temporary_table_a
Re: Ask HN: How do you test SQL?
#210I regret making an integration in SQL Server Integration Services, with some nice addon tools that can make http requests and such. In the end it is untestable piece of hard to follow solution.
Anyone have any recommendations on testing SSIS ?