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

111–120 of 322 posts

Re: Ask HN: How do you test SQL?

#111

Post by karmasimida »

munk-a wrote: post #103

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 a…

I think the tricky part is DB itself might get funny as time goes by, downstream logic changes might invalidate top level queries that built on now dated assumption

Overall data integrity is hard.

Re: Ask HN: How do you test SQL?

#112

Post by dbsmith83 »

waste_monk wrote: post #76
rc_mob wrote: post #57

Earlier quoted context omitted.

problem is that its not always compatible with features you use on your production database

Can split test regime so that as much as possible is covered with SQLite, and then have a second test phase with a heavyweight db only if the first phase passes. So code errors, malformed SQL, etc. cause it to fail fast and early, and you only test with the real DB once you know everything else is working. Or along similar lines you could divide it such that developers can test things locally on their machines with S…

That still doesn't fix the compatibility issues. Postgres has features/syntax that sqlite does not have, so you can't test postgres syntax with sqlite sometimes

Re: Ask HN: How do you test SQL?

#113

Post by fatneckbeard »

MBCook wrote: post #35
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.

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.

Re: Ask HN: How do you test SQL?

#114

Post by spamtarget »

gfody wrote: post #104
spamtarget wrote: post #75

Earlier quoted context omitted.

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?

Oh, i see, i did not get it. However i don't understand how "You tell SQL what you want, not how to get it. That's declarative" related to that list

iif and case by itself is not declarative or imperative. only an entire language can be described as such

Re: Ask HN: How do you test SQL?

#115

Post by spamtarget »

indymike wrote: post #96
spamtarget wrote: post #75

Earlier quoted context omitted.

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’ve not encountered your definition of declarative before. SQL is often cited in cs texts as an example of a declarative language. That said, SQL does have a lot of imperative features, but those features are used to declare the result of the sql dml query.

SQL does not make sense as a declarative language to me. How you can say that SQL and HTML is the same by nature? Also I can't see why we can't argue about this against the literature. Saying "you are wrong because 20 years old textbook say you are wrong" is not an argument for me

Re: Ask HN: How do you test SQL?

#116

Post by ChadMoran »

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 used this exact same method. Not only does it help me but those who come after trying to understand what's going on.

Re: Ask HN: How do you test SQL?

#118

Post by taeric »

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.

I mean, I get what you are aiming for. HTML, though, is a markup language. You can call it declarative, but you don't get anything other than the HTML that you create. That is, it is not generating anything. You type what you get. Put differently, it is not a program.

SQL is far and away understood as a declarative language for what data you want out of a relational database. I challenge you to find any literature that does not describe it as a declarative language.

Now, can we munge definitions and pull in an odd true scotsman argument about it not being a "true declarative" language? I mean, yeah. But, this is like arguing that LISP is not a functional language by some specific modern view of that term. Certainly true, but far from useful. And almost certainly not what anyone you would talk to expects from those terms.

Re: Ask HN: How do you test SQL?

#119

Post by Dowwie »

claytonjy wrote: post #94
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.

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/

I resented writing the verify scripts for my migrations, after writing unit and integration tests, but yes it is valuable

Re: Ask HN: How do you test SQL?

#120

Post by spamtarget »

preseinger wrote: post #105
spamtarget wrote: post #90

Earlier quoted context omitted.

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…

>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 is a language that expresses definitions, not functions. It is absolutely declarative.

that just not true. without defining the how through transformations and conditions, the only thing you get out of the sql server is a table how it is stored. until you reach a certain complexity you are true, it can be seen as declarative, but my point here, is the approach of problem solving is the separation here. you dont even have to speak about computers:

cooking recipe VS assembly lines in factories VS a robot with ai, which can do whatever is programmed to, but in certain cases work out the details by itself when not given specifically (not the best example, but started to get exhausted)

Also how would you say that by nature HTML and SQL is the same?

Post reply on HN