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

221–230 of 322 posts

Re: Ask HN: How do you test SQL?

#221

Post by bolanyo »

nicoburns wrote: post #147
colanderman wrote: post #132

Earlier quoted context omitted.

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.

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?

Re: Ask HN: How do you test SQL?

#222

Post by feike »

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.

We do this too for PostgreSQL: to ensure the tests are really fast:

    - we create a template database using the migrations
    - for *every* integration test we do `CREATE DATABASE test123 TEMPLATE test_template;`
    - we tune the PostgreSQL instance inside Docker to speed up things, for exampling disabling synchronous_commit
On a successful test, we drop the test123 database. On a failed test, we keep the database around, so we can inspect it a bit.

The really great thing about this approach (IMHO), is that you can validate certain constraint violations.

For example, exclusion constraints are great for modelling certain use cases where overlapping ranges should be avoided. In our (go) code, the test cases can use the sqlstate code, or the constraint name to figure out if we hit the error we expect to hit.

This approach is pretty much as fast as our unit tests (your mileage may vary), but it prevents way more bugs from being merged into our codebase.

Re: Ask HN: How do you test SQL?

#223

Post by marijnz0r »

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…

I really like this answer because it tests CTEs in a modular way. One question I have is: how do you test a the CTE with an in.csv and out.csv without altering the original CTE? Currently I have a chain of multiple CTEs in a sequence, so how would I be able to take the middle CTE and "mock" the previous one without altering the CTE actually used in production? I prefer not to maintain a CTE for the live query and the same CTE adapted for tests.

Re: Ask HN: How do you test SQL?

#224

Post by neonate »

feike wrote: post #222
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.

We do this too for PostgreSQL: to ensure the tests are really fast: - we create a template database using the migrations - for *every* integration test we do `CREATE DATABASE test123 TEMPLATE test_template;` - we tune the PostgreSQL instance inside Docker to speed up things, for exampling disabling synchronous_commit On a successful test, we drop the test123 database. On a failed test, we keep the database around, so…

Out of curiosity, how fast is really fast?

Re: Ask HN: How do you test SQL?

#226

Post by stormdennis »

gpvos wrote: post #199
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…

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.

Re: Ask HN: How do you test SQL?

#227

Post by nephton »

Write a toolbox for your tests that is able to spin up and tear down an instance of the database that you use (eg. on a random port and random password). Make sure that the new instance is really independent, especially storing its data in a separate (temporary) ditectory.

During test: - At the start of the test (fixture), run a new DB instance - Apply DB schema. - possibly: Remove constraints that that would disturb your tests (eg. unimportant foreign keys) - possibly: Add default values for columns that are not important for your test (but do with caution) - run you test - Assert results (maybe also directly as access to databse or via a dump of tables). - Tear down database possibly removing all data (except error logs).

I used this pattern to test software that uses MySQL or MariaDB server. For Microsoft SQL server it may be enough to create a new database instead of running a new instance (possible but not as easy as for MySQL/MariaDB).

On CI server this can be used to run tests against all required DB server types and versions.

Re: Ask HN: How do you test SQL?

#228

Post by feike »

neonate wrote: post #224
feike wrote: post #222

Earlier quoted context omitted.

We do this too for PostgreSQL: to ensure the tests are really fast: - we create a template database using the migrations - for *every* integration test we do `CREATE DATABASE test123 TEMPLATE test_template;` - we tune the PostgreSQL instance inside Docker to speed up things, for exampling disabling synchronous_commit On a successful test, we drop the test123 database. On a failed test, we keep the database around, so…

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 first test: 2023-02-01 08:25:03.633 UTC

Finish time of last test: 2023-02-01 08:26:13.861 UTC

82 tests, or 0.856 seconds per test (sequentially).

In parallel, we take 6.941 seconds for 82 tests, or 0.085 seconds per test.

Re: Ask HN: How do you test SQL?

#229

Post by summerlight »

Google internally has developed a SQL language dialect and some investments have been made to have a first class language supports for SQL. It has several interesting functionalities that made SQL nice for ETL use cases.

  * It has a language level module support, similar to other languages. Thus SQL functions are reusable across multiple codebases without depending on code generation tricks. One of the major blocker for SQL adoption has been complex domain specific business logic and now the situation is better.
  * It has an official unit test support. Google use Blaze (which is known as Bazel externally), so adding a unit test for SQL code is as simple as adding a SQL module (and its test input) dependency to SQL test target, write a test query and its expected output in a format of approval testing. Setting up the DB environment is all handled by the testing framework.
  * It has an official SQL binary support. It's just a fancy name for handling lots of tedious stuffs for running a SQL query (e.g. putting everything needed into a single package, performing type checks, handling input parameters, managing native code dependencies for FFI etc etc).
None of those are technically too sophisticated at least in theory, actually these combined together become pretty handy. Now I can write a simple SQL module which mostly depends on other team's SQL module, do a simple unit test for it then run a SQL binary just as other languages. I haven't worried a single time on how to set up a DB instance. This loop is largely focused on OLAP so it's a bit different for OLTP, which has another type of established testing patterns.

Re: Ask HN: How do you test SQL?

#230

Post by davedx »

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 beast though. Having significantly struggled to debug stored procedures running in MSSQL on a Macbook (on Windows SQL Management Studio lets you set breakpoints, on Mac you're SOL), if I was building an application based on them I'd definitely try to spin up some kind of testing framework around them. I guess what I'd probably do is have a temporary database and some regular testing framework that nukes the db, then calls the stored proc(s) with different inputs and checks what's in the tables after each run. Sounds slow and clunky?

Post reply on HN