Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: How do you test SQL?

news.ycombinator.com

Post reply on HN
Print view View original

141–150 of 322 posts

Re: Ask HN: How do you test SQL?

#141

Post by JackFr »

pgoggijr wrote: post #41
ramenmeal wrote: post #18

We 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/

I rolled my own with docker for a few years and recently made the switch to testcontainers. So far so good - but if you’re in an environment or language where test containers are difficult, rolling your own really it ant to hard. It also keeps you honest with maintaining good migration scripts.

Re: Ask HN: How do you test SQL?

#143

Post by jesseryoung »

I started in the software engineering space and move into data engineering, and I was floored with the complete lack of tooling. There is a HUGE gap between software engineering and data engineering when it comes to both tooling and practice. Even the simplest "unit" test of "Is the SQL statement valid" is not all that common in frameworks and tooling but in practice is like 90% of the production failures that I've seen.

Starting with a framework that is programming language first (IE Spark) can help you build your own tooling to help you actually build unit tests. It's frustrating though, that this isn't just common across other ETL tooling.

Re: Ask HN: How do you test SQL?

#144

Post by gfody »

spamtarget wrote: post #120
preseinger wrote: post #105

Earlier quoted context omitted.

> if sql was declarative, than the code of the SQL above would be: GIVE ME ALL THE COST OF THE EMPLOYEES --, I DONT CARE ABOUT THE DETAILS This is exactly what SQL is. There are exceptions, but they're exceptions. > an SQL query itself (if no structured language used) is just a big function applied on a set. I don't think you have a correct model of SQL. SQL is a language that expresses definitions, not functions. It…

>This is exactly what SQL is. There are exceptions, but they're exceptions. no, i meant the query is literally this: GIVE ME ALL THE COST OF THE EMPLOYEES or if we want to formalize more: ALL COST OF ALL EMPLOYEES the point here that in a declarative mindset EMPLOYEE is a word that SQL server understands, like HTML understand paragraph or table with rows and cells >I don't think you have a correct model of SQL. SQL i…

it is possible to reduce your query w/case statements to essentially "all cost of all employees" if you would model every type of employee and cost relationally (and then some view for all-employees which is the union of each type projecting the approptiate cost and so on) then your query gets to just ask for records again without having to "tell how" those things are to become records.

I dont think html is anything like sql (only in the sense that all markup languages are also declarative) but a comprable situation would be if you were trying to render some ascii art in html and had to use pre tags in order to tell the layout engine how to show it.

Re: Ask HN: How do you test SQL?

#147

Post by nicoburns »

colanderman wrote: post #132
RobinL wrote: post #43

Try 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.

This is a good point, although it’s worth noting that there are definitely cases where you want the sun table to be materialised (esp. when that table is small and referenced many times)

Re: Ask HN: How do you test SQL?

#148

Post by preseinger »

spamtarget wrote: post #120
preseinger wrote: post #105

Earlier quoted context omitted.

> if sql was declarative, than the code of the SQL above would be: GIVE ME ALL THE COST OF THE EMPLOYEES --, I DONT CARE ABOUT THE DETAILS This is exactly what SQL is. There are exceptions, but they're exceptions. > an SQL query itself (if no structured language used) is just a big function applied on a set. I don't think you have a correct model of SQL. SQL is a language that expresses definitions, not functions. It…

>This is exactly what SQL is. There are exceptions, but they're exceptions. no, i meant the query is literally this: GIVE ME ALL THE COST OF THE EMPLOYEES or if we want to formalize more: ALL COST OF ALL EMPLOYEES the point here that in a declarative mindset EMPLOYEE is a word that SQL server understands, like HTML understand paragraph or table with rows and cells >I don't think you have a correct model of SQL. SQL i…

You do not understand SQL. HTML and SQL are essentially unrelated. It would be to your benefit to spend some time reading and learning on these topics.

Re: Ask HN: How do you test SQL?

#149

Post by dirtybirdnj »

in production, on a friday before I leave for vacation

for real though I love tools like SequelPro or TablePlus that let me work out a query before I bake logic or stuff into my apps. Also sometimes I use it to work out the data needed for reports. I am working with salesforce for the first time in my life and apparently there are tools that let me treat it like I'm used to SequelPro.

Re: Ask HN: How do you test SQL?

#150

Post by nicoburns »

fatneckbeard wrote: post #113
MBCook wrote: post #35

Earlier quoted context omitted.

This is what we did at my last job. You can catch DB specific issues that a false implementation wouldn’t show and make sure all your code paths work as expected. Every time new issues cropped up we would put new data in the test data designed to reproduce it. Every edge case we would run into. It provided so much confidence because it would catch and trigger so many edge cases that testing with mocks or by hand woul…

thats a huge amount of work and money. at my company they just told us to stop reporting edge cases. much easier, much cheaper.

It’s not that much work
Post reply on HN