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

131–140 of 322 posts

Re: Ask HN: How do you test SQL?

#131

Post by whalesalad »

ddyevf635372 wrote: post #54
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.

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 should be paid down as soon as possible, or as soon as that bug that manifests itself in PSQL but not sqlite appears :)

Re: Ask HN: How do you test SQL?

#132

Post by colanderman »

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.

Re: Ask HN: How do you test SQL?

#133

Post by gfody »

spamtarget wrote: post #123
gfody wrote: post #122

Earlier quoted context omitted.

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…

I think what you're saying is accurate for describing what style of programming you might be doing, afterall we can write C++ in a declarative style. if you're coding in SQL then you're doing functional programming, but that doesn't change the fact that SQL is a declarative language for describing recordsets. you wouldn't refer to SQL as a functional programming language because it's not general purpose.

Re: Ask HN: How do you test SQL?

#134

Post by danielheath »

ddyevf635372 wrote: post #54
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.

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.

You can get the 'in-memory' speed advantage by putting the datastore on a ramdisk (or even just disabling fsync, which is pretty easy to do in postgresql).

Re: Ask HN: How do you test SQL?

#135

Post by opportune »

I used to work on SQL pipelines, sadly I didn’t get to implement some of these suggestions, but we did implement others.

For testing:

Run your query/pipeline against synthetic/manual data that you can easily verify the correctness of. This is like a unit test.

Run your query/pipeline on sampled actual data (eg 0.1% of the furthest upstream data you care about). This is like an integration test or a canary. Instead of taking 0.1% of all records you might instead want to sample 0.1% of all USERID so that things like aggregate values can be sanity checked.

Compare the results of the new query to the results from the old query/pipeline. This is like a regression test. You may think this wouldn’t help for many changes because the output is expected to change, but you could run this only on e.g. a subset of columns.

Take the output of the new query (or sampled query, or the manual query) and feed it to whatever is downstream. This is like a conformance test.

For reliability:

If the cost is not prohibitive, consider persisting temporary query results (eg between stages of your pipeline) for 1-2 weeks. This way if you catch a bug from a recent change you only need to rerun the part of your pipeline after the breakage. May not make sense to do if your pipeline is not big

If the cost is not prohibitive you could also run both the new and old versions of the pipeline for ~a week so that you can quickly “rollback”. Ofc whether this is viable depends on what you’re doing.

The big failure modes with SQL pipelines IME are

1. unexpected edge cases and bad data causing queries to fail (eg you manually test the new query and it works fine, but in production it fails when handling Unicode)

2. not having a plan for what to do when a bug gets caught after the fact

3. barely ever noticing bugs or lost data because nobody is validating the output (for example, if you have a pipeline that aggregates a user’s records over a day, any USERID that’s in the input data for that day should also be in the output data for that day).

4. This can be very hard to solve depending on your circumstances, but upstream changes in data are the most annoying and intractable to solve. The best case here is you either spec out the input data closely OR have some kind of testing in place that the upstream folks run before shipping changes.

To address these, you need to take the approach of expecting things to fail, rather than hoping they don’t. This is common practice in many SWE shops these days but the culture in the data world hasn’t quite caught up. I think part of the problem is that automating this testing usually requires at least some scripting/programming which is outside the comfort zone for many people who “just write SQL.”

Re: Ask HN: How do you test SQL?

#136

Post by creakingstairs »

Phoenix/Elixir/Ecto seems to handle this really well. When you write a test in Phoenix, you can use `DataCase` which automatically creates sandboxed transactions [1] for each test. This makes writing integration tests a breeze.

[1] https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#module-sq...

Re: Ask HN: How do you test SQL?

#137

Post by DaiPlusPlus »

slaymaker1907 wrote: post #91
DaiPlusPlus wrote: post #29

Earlier quoted context omitted.

The problem with writing raw SQL (which I do personally prefer myself, too) is now you need to generate types and/or mappings for each distinct query’s resultset schema - doing that by-hand is tedious and error-prone (or use untyped dict objects for every row, ew) - so what you really need is a project build-step that finds every query in your project and runs it against a prototype database instance in order to get…

I like the approach of just using reflection to do the mapping and throw an error when things don't quite match up like with https://stackoverflow.com/a/21956222/7608007 . Combined with something like record types or Lombok, it's not that much effort to create a bean class for each result mapping you care about.

The problem with using something like runtime reflection is you lose compile-time type-safety (i.e.: the guarantees that a particular resultset contains a particular column, its type and nullability is correct, etc) .

Re: Ask HN: How do you test SQL?

#138

Post by urthor »

ahakanbaba wrote: post #125

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…

fwiw, you can share a spark session between unit tests. Even persist a spark session throughout the day so your tests run against a hot session.

Straight TDD with spark is perfectly fine if you know what you're doing. I'm not saying it's easy or there's an easy guide somewhere, but it's possible.

If you're using Pyspark via the API, it's likely an incredibly important part of your process.

Re: Ask HN: How do you test SQL?

#139

Post by quantified »

gardenhedge wrote: post #80
ddyevf635372 wrote: post #54

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

So you test against a different database technology than the one you software uses? I understand why that works but it seems odd

A real nice thing about Postgres and Mysql is that in the JVM world the H2 and HSQLDB engines have large compatibility, you can use them in-JVM for unit test speed in many cases. Doesn't help developing the SQL, does help with testing.

Snowflake, on the other hand, is just special.

Re: Ask HN: How do you test SQL?

#140

Post by taeric »

spamtarget wrote: post #128
taeric wrote: post #118

Earlier quoted context omitted.

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

Comparing to html is nonsensical. Period. It is not instructions on how to do anything, but markup of a document. Would be akin to asking if a bitmap is declarative. It is literally the data. It is not a declaration of what you want, but is a definition of what you have. In that vein, you would need a new taxonomy of "definitive" languages.

And again, I get where you are wanting to go. But you are literally arguing against all literature on sql. And your definition of declarative is restricted to only markup languages, evidently. Prolog would also not be declarative, by this view. Which is akin to arguing lisp is not functional. Technically true. The least useful way of being true.

If you want to know why sql is declarative, consider, how is a join performed? Table scan, hash join, merge? Yes, there are many ways to poke at the query plan, but that is like claiming c is assembly because there are extensions.

Even for the ddl statements, how is an insert actually performed into the backing store? When you create an index, what is created? How is the data copied to it? How is the data stored at all? Btree? Something else?

Post reply on HN