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

101–110 of 322 posts

Re: Ask HN: How do you test SQL?

#101

Post by Izkata »

spamtarget wrote: post #58
luhn wrote: post #38

Earlier quoted context omitted.

> Although SQL is essentially a declarative language (4GL), it also includes procedural elements. https://en.wikipedia.org/wiki/SQL

-3 votes shows how this is a common misunderstanding. If SQL was a declarative language, there would not be expression in it, for example SELECT (1+2) , only SELECT 3 These categories based on the approach of the programming, not the underlying technology. Imperative languages (high level): a step-by-step description of a process, like giving orders to someone/something in a sequence, organized further with loops and…

> Declarative language: your approach here is instead of describing how to do something, you only describe what you want as a result. CSS is a perfect example, however it is picking up non-declarative elements nowadays, original it was only capable to describe how a font a paragraph look like. HTML is also declarative. Instead of actually drawing a rectangle, you just say, this width, height on this position, etc.

SQL fits this perfectly. For example JOINs don't describe actions, they describe relationships. Have you ever looked at what the query planners come up with to satisfy those relationships?

Re: Ask HN: How do you test SQL?

#102

Post by uticus »

henryfjordan wrote: post #52
uticus wrote: post #33

Related - how is any declarative language tested? Quick web search confirms suspicions, it is not easy https://www.metalevel.at/prolog/testing

Your link has the answer, declarative testing. You need to think about why you wrote that code and declare some outputs for given inputs that give you confidence in your code. The other tests from your link are just silly. You did not write code to terminate or to provide an answer for all inputs, you wrote code to provide the right answer. To test some HTML for instance, think about what information you want the pag…

> You did not write code to terminate or provide an answer for all inputs...

Definitely not an expert. But to my eyes checking for non-termination seems very close to things that happen in SQL all the time.

For example, a parent query joins two subqueries that both map data, and then maps on that data. To test, I could just test one specific scenario with specific values in all columns. That would be more of a concrete test case.

But I might want something more robust checking the outer bounds of acceptable data (like a min and a max). That seems much closer to the non-termination test.

Of course, I'm open to being corrected on this.

Re: Ask HN: How do you test SQL?

#103

Post by munk-a »

Use a testing framework to mock some database into the DB, run your queries, verify the result. Make sure you have a variety of data you use for tests to fully exercise the surface of logic you expect to hit.

Basically, treat the query and database as a black-box for testing like you would another third party API call.

I would strongly suggest having a layer of code in your application that is exclusively your data access and keeping any logic you can out of it. Data level tests are pretty onerous to write in the best circumstances and the more complexity you allow to grow around the raw SQL the worse of a time you'll have - swapping out where clauses and the like dynamically is a cost you'll need to eat, and sometimes having a semi-generic chunk that you reuse with some different joins can be more efficient than writing ten completely different access functions with completely different internal logic so judgement is required.

At the end of the day a database is like any other third party software component - data goes in, data comes out... the nice thing is that SQL is well defined and you've got all the definitions so it's easier to find the conditional cases you need to really closely tests... but databases are complex beasties and it'll never be easy.

Re: Ask HN: How do you test SQL?

#104

Post by gfody »

spamtarget wrote: post #75
mLuby wrote: post #48

Earlier quoted context omitted.

You tell SQL what you want, not how to get it. That's declarative. SQL : CSV :: GraphQL : JSON :: React : HTML

Really? SELECT CASE WHEN employee.type = 'contract' THEN salary CASE WHEN employee.type = 'full-time' THEN salary + benefit_costs END CASE FROM employee Tell me how is this declarative? -SQL is functional -CSV is not a language, it's a data format -GraphQL, im not familiar -JSON is literally executable javascript code, arguable -React is a javascript framework, binding is a declarative nature (if it has it, i dont kn…

I'm not the person you were replying to but you've misunderstood their comment - they were saying that CSV is to SQL what JSON is to GraphQL (and HTML to React) these being declarative languages for records, objects, and markup

A "case" statement in SQL (or an "iif") is still declarative, how else would you express specific cases when necessary?

Re: Ask HN: How do you test SQL?

#105

Post by preseinger »

spamtarget wrote: post #90
preseinger wrote: post #85

Earlier quoted context omitted.

Does the presence of conditional expressions mean that the language which permits them is necessarily not declarative?

no, the approach makes the difference. when you create html, you dont care how the rectangle is drawn, you focus on defining the result itself. 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 the details, which in this example the difference between fulltime/contract employees would be handled by the framework (SQL server) which…

> 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 is absolutely declarative.

Some extensions of SQL can provide functional capabilities, yes, but they are exceptional.

Re: Ask HN: How do you test SQL?

#106

Post by uticus »

spamtarget wrote: post #37
uticus wrote: post #33

Related - how is any declarative language tested? Quick web search confirms suspicions, it is not easy https://www.metalevel.at/prolog/testing

SQL is not a declarative language. It is a functional language, and structured language on the top as extensions. HTML is a declarative language.

Okay, I'll grant it's not pure declarative.

But the part that is declarative is sure difficult to test.

Re: Ask HN: How do you test SQL?

#107

Post by spamtarget »

Izkata wrote: post #101
spamtarget wrote: post #58

Earlier quoted context omitted.

-3 votes shows how this is a common misunderstanding. If SQL was a declarative language, there would not be expression in it, for example SELECT (1+2) , only SELECT 3 These categories based on the approach of the programming, not the underlying technology. Imperative languages (high level): a step-by-step description of a process, like giving orders to someone/something in a sequence, organized further with loops and…

> Declarative language: your approach here is instead of describing how to do something, you only describe what you want as a result. CSS is a perfect example, however it is picking up non-declarative elements nowadays, original it was only capable to describe how a font a paragraph look like. HTML is also declarative. Instead of actually drawing a rectangle, you just say, this width, height on this position, etc. SQ…

you can force SQL to fit in this, but this way everything become a declarative language. the approach of solving problems is the difference.

SQL does not fit my definition, because you reach your goals through multiple transformation and filtering, and this is how you reach your goal. you define the way, the process, not the end result. under there are some comments where i speak about this.

Re: Ask HN: How do you test SQL?

#109

Post by spamtarget »

uticus wrote: post #106
spamtarget wrote: post #37

Earlier quoted context omitted.

SQL is not a declarative language. It is a functional language, and structured language on the top as extensions. HTML is a declarative language.

Okay, I'll grant it's not pure declarative. But the part that is declarative is sure difficult to test.

everything is partly declarative. you can draw a rectangle line by line, or you can put this code into a function or a procedure, and call it declarative, because it is something like that.

i did not speak about testing, however what a declarative language can do is defined by the framework under it. so you test that I guess?

Re: Ask HN: How do you test SQL?

#110

Post by curiousllama »

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…

> Try and write any complex SQL as a series of semantically meaningful CTEs

I find this helps a heck of a lot with maintainability + debugging as well

Post reply on HN