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

121–130 of 322 posts

Re: Ask HN: How do you test SQL?

#121

Post by taeric »

zabzonk wrote: post #56
RobinL wrote: post #49

Earlier quoted context omitted.

You can know what the answer is against a small test dataset though. Obviously the challenge is ensuring it's representative, that you hit the edge cases of real data etc. But it's better than nothing

i have never worked with a small dataset. mocking one doesn't work, for the reasons you suggest, and others. basically you cannot test queries against a big database. you just have to hope for the best.

Just, what? This sounds terrible. Why can't you run a test query against a large dataset? If need be, anonymize your data set into an integ environment, and go nuts. Could there be a data combination that surprises you someday? Certainly. Most queries, though, are much much simpler than that and can, in fact, be tested.

Re: Ask HN: How do you test SQL?

#122

Post by gfody »

spamtarget wrote: post #114
gfody wrote: post #104

Earlier quoted context omitted.

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

SQL is declarative in the sense that you dont specify the strategy to employ in retrieving the data (ie which index to access and how, whether to sort and merge or build a hash table etc etc) however you DO need to specify how to represent your data as records (which could entail specific cases for when certain values go in the same cell as you pointed out).

Re: Ask HN: How do you test SQL?

#123

Post by spamtarget »

gfody wrote: post #122
spamtarget wrote: post #114

Earlier quoted context omitted.

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

SQL is declarative in the sense that you dont specify the strategy to employ in retrieving the data (ie which index to access and how, whether to sort and merge or build a hash table etc etc) however you DO need to specify how to represent your data as records (which could entail specific cases for when certain values go in the same cell as you pointed out).

You don't care about how the SQL server gets the data the same way how you don't care about what is happening in the background when you use File.ReadAllText The technology behind does not define the programming paradigm. You can speak about these paradigms without computers.

Imperative programming: really detailed cooking recipe

Functional programming: assembly line of a car factory

Declarative programming: a robot what can do what is programmed for, and sometimes can work with really unspecific instructions (like this one line in a file "Hello world" is not a valid HTML, but the browser still renders it)

Re: Ask HN: How do you test SQL?

#124

Post by zip1234 »

uticus wrote: post #19

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

Re: Ask HN: How do you test SQL?

#125

Post by ahakanbaba »

The hard part about testing SQL is decoupling from infrastructure and big data sources. We use DuckDB, and pandas dataframes mock data sources to unit test SQL. Python testing frameworks (or simple assert statements) can be used to compare inputs and outputs.

When the tests pass, we can change from DuckDB to Spark. This helps decouple testing Spark pipelines from the SparkSession and infrastructure, which saves a lot of compute resources during the iteration process.

This setup requires an abstraction layer to make the SQL execution agnostic to platforms and to make the data sources mockable. We use the open source Fugue layer to define the business logic once, and have it be compatible with DuckDB and Spark.

It is also worth noting that FugueSQL will support warehouses like BigQuery and Snowflake in the near future as part of their roadmap. So in the future, you can unit test SQL logic, and then bring it to BigQuery/Snowflake when ready.

For more information, there is this talk on PyData NYC (SQL testing part): https://www.youtube.com/watch?v=yQHksEh1GCs&t=1766s

Fugue project repo: https://github.com/fugue-project/fugue/

Re: Ask HN: How do you test SQL?

#126

Post by osigurdson »

jimmygrapes wrote: post #68
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…

My ignorance of the topic (and experience with a mostly unrelated one) is showing, but all I could think of when you said CTE was "chronic traumatic encephalopathy". This made a lot more sense when you generalized answering the question as if it's a given that Python is necessary (I know that's not your intent, but that's how it comes off). Not much more to say, just observing, sorry if this is irrelevant commentary.

>> I could think of when you said CTE was "chronic traumatic encephalopathy"

The title is "Ask HN: How do you test SQL"

Re: Ask HN: How do you test SQL?

#128

Post by spamtarget »

taeric wrote: post #118
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.

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

"don't get anything other than the HTML that you create" that's the point of declarative languages

"That is, it is not generating anything." if you speak about code generation like code behind in VS, it has nothing do with the paradigm. If you speak about underlying technology, it does not matter. In case of declarative languages, the framework determines what the language is capable of. And that's the point of them, they are generalizing the solution for common problems. The browser do a lot of things in the background when it draws a rectangle the same way how SQL server is doing a lot things when you execute a query, but these things have nothing to do with programming paradigm, which can be understood without computers.

Imperative: cooking recipe

Functional: assembly line in a factory

Declarative: your assistant

(compare the real world examples to the things i mentioned)

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

Ok, and I challenge you to compare HTML and SQL. Are they the same by nature. Are the steps, the approach of problem solving is the same? Because if SQL is declarative, than it is really similar to HTML

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

We are speaking about semantics and taxonomy here. These kind of a questions are always debatable, so i can't really say anything about this, because you are speaking of ways of discussion. What i'm saying that my taxonomy makes the most sense to me, and if someone truly can challenge it, it's gonna make my mind change, but "literature mentions it somehow else" is not an argument makes me change my mind.

Re: Ask HN: How do you test SQL?

#129

Post by nishs »

MuffinFlavored wrote: post #69
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.

Do you do this in place of unit tests (where you have to mock/stub the DB interactions) or do you do both?

Sorry for the digression first. (If anyone has different definitions for the ideas here, I would love to learn.)

I think the answers would depend on the types of tests that the term "this" encompasses. From how I understand it, calling something a unit test or an integration test depends on the context of what is is being tested. For example, if a developer is writing a unit test for a HTTP handler, and the handler implementation includes calls to an external database, then one would have to use a mock for the database, in order for the test to be deemed a true unit test [1]. If the developer is writing an integration test for the same HTTP handler, then the database would have to be a real database implementation [2].

On other hand, if the developer were testing SQL queries for syntactical or logical correctness against a database, these tests would want to use a real database implementation. Note that though the test uses a real database, it is still a true unit test [3]. Additionally, note that using a mocked database here would not serve the purpose of the tests, which is to catch syntactical and logical errors against a database engine. This can, of course, only be achieved by using a real database—or, if you insisted on using a "mock", then, by implementing an entire SQL engine, with the exact same quirks as the real database's engine, inside the mock!

On the original question:

> Do you do this in place of unit tests (where you have to mock/stub the DB interactions) or do you do both?

I guess the answer would be: It would depend on the objectives of and types of tests. Do both of them, because some tests, such as unit tests on the HTTP handler, would use use mocks, while other tests, such as the SQL query correctness tests, would use the real database.

[1] A true unit test is one that has no external interactions, neither directly nor transitively, besides interactions with the system under test (SUT). The SUT here is the HTTP handler.

[2] An integration test should include external system interactions, if any. That's what integration means.

[3] The SUT is each SQL query and the its interaction with the database. There are no interactions in the test with systems outside the SUT, so it is still a true unit test.

Re: Ask HN: How do you test SQL?

#130

Post by cwp »

Two ideas here:

1) The same way you'd write any other tests. Use your favourite testing framework to write fixtures and tests for the SQL queries:

  - connect to the database
  - create tables
  - load test data
  - run the query
  - assert you get the results you expect
For insert or update queries, that assertion step might involve running another query.

2) DBT has support for testing! It's quite good. See https://docs.getdbt.com/docs/build/tests

Post reply on HN