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

251–260 of 322 posts

Re: Ask HN: How do you test SQL?

#251

Post by davedx »

Deukhoofd wrote: post #231
davedx wrote: post #230

SQL being relatively pure, functional, algebraic and whatnot, doesn't require the same rigor of automated test coverage that more "systems" programming languages do. (By "systems" I include all languages that people use to integrate the various parts of a software system - i.e. regular programming languages like Java, C#, TypeScript, C++ and so on. Not just low level languages.) Stored procedures are a different beas…

> on Windows SQL Management Studio lets you set breakpoints, on Mac you're SOL Good news! Since SSMS18 you're SOL on Windows too, as Microsoft completely removed that feature :)

Ugh :)

Sounds like an opportunity though…

Re: Ask HN: How do you test SQL?

#253

Post by kakwa_ »

feike wrote: post #228
neonate wrote: post #224

Earlier quoted context omitted.

Out of curiosity, how fast is really fast?

Just did a sequential run (to get some better measurements), and this is an excerpt of the things happening in the PostgreSQL instance inside the Docker container, for creating and dropping the databases: 08:25:37.114 UTC [1456] LOG: statement: CREATE DATABASE "test_1675239937111796557" WITH template = test_template [noise] 08:25:48.002 UTC [1486] LOG: statement: DROP DATABASE "test_1675239947937354435" Start time of…

We apply the overall same strategy (individual DB for each test created from a template).

Our whole test suite (integration + unit tests) takes ~80 seconds to run for ~800 integration tests (each with their own DB) and 300 unit tests. And that's on my dev laptop (T14s, cpu: i7-1185G7) without much optimization (mainly fsync = off in postgresql.conf).

In fact, I just ran a quick test, and just putting the DB on a tmpfs cuts that time to ~40 seconds.

So overall 0.1 to 0.05 second per test on average, same ballpark as parent (and it's kind of an over estimation actually since we have a dozen or so of slow tests taking 5 to 10 seconds).

Note that the tests are run in parallel however.

Re: Ask HN: How do you test SQL?

#254

Post by nicoburns »

bolanyo wrote: post #221
nicoburns wrote: post #147

Earlier quoted context omitted.

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)

Is 'sun table' a particular concept here? A typo?

It's a typo. I was trying to write 'CTE table' and iOS "helpfully" autocorrected it.

Re: Ask HN: How do you test SQL?

#255

Post by mritchie712 »

RobinL wrote: post #250
mritchie712 wrote: post #246

Earlier quoted context omitted.

Doesn't that mean you have to copy all your data into duckdb? I'd imagine with even a modest data warehouse, loading the entire thing to duckdb would be unfeasible.

The tests are being run against small test datasets of 10s of rows rather than the real data that test whether the behaviour of transforms/joins etc. is as expected. You're right, this approach wouldn't be sensible if the tests have to use large production tables

How do you get the small datasets? Selecting a random sample from the source?

Re: Ask HN: How do you test SQL?

#256

Post by MrPowers »

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…

Spark makes it easy to wrap SQL in functions that are easy to test. I am the author of the popular Scala Spark (spark-fast-tests) and PySpark (chispa) testing libraries. Some additional tips to speed up Spark tests (can speed up tests between 70-90%):

* reuse the same Spark session throughout the test suite

* Set shuffle partitions to 2 (instead of default which is 200)

* Use dependency injection to avoid disk I/O in the test suite

* Use fast DataFrame equality when possible. assertSmallDataFrameEquality is 4x faster than assertLargeDataFrameEquality. Some benchmarks here: https://github.com/MrPowers/spark-fast-tests#why-is-this-lib...

* Use column equality to test column functions. Don't compare DataFrames unless you're testing custom DataFrame transformations. See the spark-style-guide for definitions for these terms: https://github.com/MrPowers/spark-style-guide/blob/main/PYSP...

Spark is an underrated tool for testing SQL. Spark makes it really easy to abstract SQL into unit testable chunks. Configuring your tests properly takes some knowledge, but you can make the tests run relatively quickly.

Re: Ask HN: How do you test SQL?

#257

Post by jiggywiggy »

monsieurbanana wrote: post #65
jiggywiggy wrote: post #60

To be honest. I would love testing to work. Have set up and maintained several unit test suites in Jest. Wrote several large e2e test suites in Cypress. I don't think anyone won time from simply having a manual checklist and testing manually. Maybe me and my former teammates are doing it wrong. Talking 8+ teams, from corporate to startup. But loved de proven wrong. E2e def. catched most issues.

I'm not sure I understand your comment, you seem to say that automating testing isn't better than manual testing, yet the e2e tests caught most issues?

Yeah could have been clearer, e2e from all testing suites i mean. My point is not so much that human testing it's better, which it is, but also it ends up being more time efficiënt the maintaining test suites. Which can het quite time consuming.

Also testing with a checklist by human is easy to outsource to cheap labor. Whereas testing engineers are pretty expensive

Re: Ask HN: How do you test SQL?

#258

Post by xpil »

stormdennis wrote: post #226
gpvos wrote: post #199

Earlier quoted context omitted.

CTE = common table expression, i.e. a WITH clause (possibly recursive) before your SELECT (or other) statement. https://learnsql.com/blog/what-is-common-table-expression/

Yes, I didn't know what it was so I had looked it up before I saw yours. The definition I got is below: A common table expression, or CTE, is a temporary named result set created from a simple SQL statement that can be used in subsequent SELECT, DELETE, INSERT, or UPDATE statements.

> (...) in subsequent SELECT, DELETE, INSERT, or UPDATE statements

The annoying part is that certain RDMBS engines (MySQL for instance) require you to write the INSERT keyword before any CTEs.

So, your T-SQL or PGSQL query:

`with cte1 as (...) insert into ... select ... from cte1;`

becomes:

`insert into ... with cte1 as (...) select ... from cte1;`

I know the difference is minor but when you deal with many different DB engines, it is simply annoying.

Re: Ask HN: How do you test SQL?

#260

Post by totalhack »

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.

Same. That's one of the benefits of being able to replicate your environment with something like docker compose and then use a tool like alembic to manage migrations both locally and in production.
Post reply on HN