Unit Tests on Transient Data

When using SQL for more than just CRUD, it might be worth adding unit tests for complex queries. These tests need well crafted test data so that the test query returns the expected result.

The test data is often created in a set-up procedure before the test and removed in a tear-down procedure after the test. With this approach test cases can interfere with each other: on the one hand, if they run in parallel, on the other hand if the tear-down procedure fails to clean up.

Designing the tests to run on transient data that will automatically vanish after the test is one way to cope with these issues.

Option 1: Open Transactions

The easiest and furthermore most flexible solution is to do the set-up and testing in a single open transaction, which is rolled back in tear-down.

This approach as many advantages:

  • Relatively easy to implement

    The essential part is to use the same database session for the set-up, test, and tear-down functions. Furthermore, unintended commits must be prevented. That are especially commits done by the tested code itself. Mocking the database connectivity can help to capture these commit. However, client side code cannot capture implicit commits done by the database—e.g, due to execution of DDL statements.

  • Complete procedures can be tested

    The only limit to the complexity of the test case is that it must suffice with a single transaction.

  • Parallel testing is possible

    The test data is only visible within the open transaction0 and can thus not affect any other activity in the database. Constraints can cause delays, however—if two test cases insert the same primary key value, for example.

    The parallel execution might even help finding poorly chosen transaction isolation levels.

  • Performance tests are possible

    The tested code itself is unchanged and can be used for performance analysis and stress testing.

Option 2: with and values

This method combines two elements: (1) the table accesses of a query are “hijacked” by with queries of the same name; (2) the with queries use the values clause to generate the test data “out of nothing”.

WITH cart (product_id, qty) AS (
     VALUES (1, 2)
  FROM cart

In this example a with clause was put in front of the query to be tested. The essential detail is that the with clause overloads the table name cart, which is used by the test query. Note that this overloading does not work if the table is schema-qualified or accessed through a view (note: Compatibility).

If the test query itself uses a with clause—e.g., because it is literate SQL—, it can be wrapped to provide the test data in an outer with clause:

WITH cart (product_id, qty) AS (
     VALUES (1, 2)
  FROM (  WITH ...
        SELECT ...
          FROM cart
       ) test_query

The test data is created by the values clause. If the target database does not support values without insert, the conforming or proprietary alternatives shown in select without from can be used instead.

Speed is the main advantage of this method: a single database call combines the test data with the query. Tests can run in parallel without influencing each other—not even lock contention.

The striking disadvantage is that it only works for functional testing of single queries (not processes). Conclusions about non-functional aspects like performance are impossible. Even the implementation is very unconventional: the query string is prefixed by the with clause or even wrapped into a subquery.


BigQueryaeDb2 (LUW)bMariaDBaceMySQLdeOracle DBPostgreSQLceSQL ServereSQLitecewith on top-levelwith in subqueriesWith t as (values …)select without from
  1. Seems like CTE in subquery cannot see global CTEs
  2. Requires column names in with clause • Only without keyword row
  3. Only without keyword row
  4. Requires keyword row: values row('r1c1','r1c2'), row('r2c1', 'r2c2')
  5. Proprietary extension (non-standard!)

You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-⁠sql.com on your radar.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »


  1. Unless the transaction isolation level is set to READ_UNCOMITTED.

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license