Write tests against your data or query
Follow a simple structure with text, actual, and expect common table expressions to write your tests. No stored procedures.
The Framework for Testing SQL Queries
Write complex SQL queries with confidence.
A simple tool for analysts inspried by software development best practices.
Write this:
WITH text AS (
SELECT 'all staff have a picture' AS value
), actual AS (
SELECT
COUNT(*) AS value
FROM staff
WHERE picture IS NULL
), expect AS (
SELECT 0 AS value
)
:evaluate_test;
Get this:
actual | expect | did_pass | text
--------+--------+----------+--------------------------
1 | 0 | f | all staff have a picture
(1 row)
Run the tests
Any .sql file in the test/ folder is automatically evaluated, so you can organize your tests.
Avoid common mistakes
You can't always control the quality of the source data. SQL Testing Framework allows you to automate the checks that you need to make sure your query result is correct.
Why you need it
When doing analysis on data that you don't control, avoid common sources of error.
Joining on tables with NULL values
NULL does not equal NULL. If you're joining on a field that can be null, joins may not work as expected.
Duplicate values
A common source of error is assuming that field values are unique when they are not. Joining tables with non-unique values will result in many more rows of ouput than you expected.
Inconsistent source data
When humans are entering data, you may get:
COMPANY NAME LLC.
Company Name LLC.
Company Name, LLC.
Company Name
Testing the source data with an ILIKE will help you find inconsistent data.
Assumtions about allowed values
A field with state abbreviations (e.g. OH) should only have 50 possible values, right? What if it includes abbreviations for Washington D.C.(DC) or the US Marshall islands (MH)?
Get to work
SQL Testing Framework is incredibly simple to get started with. The only external dependecies are Docker, and your database.
git clone git@github.com:markjlorenz/sqltestingframework.git
# write queries in `queries/`
# write your tests in `queries/test/`
export PG_PASSWORD="password" # password for your user
export PG_PORT="5432" # port the database server is running on
cd queries/test
./runner.sh
Or start with the sample code and database
git clone git@github.com:markjlorenz/sqltestingframework.com.git
export PG_PASSWORD="password"
export PG_PORT="5432"
# start the sample database
docker run -it --rm \
--publish "$PG_PORT":5432 \
--env POSTGRES_PASSWORD="$PG_PASSWORD" \
postgres
# get the sample database
cd sqltestingframework-com/sample
curl \
--location \
--remote-header-name \
--remote-name \
https://github.com/robconery/dvdrental/raw/master/dvdrental.tar > dvdrental.tar
# load the sample data
docker run -it --rm \
--env PGPASSWORD="$PG_PASSWORD" \
--volume "$PWD":/work \
--workdir /work \
postgres pg_restore \
-h host.docker.internal \
-p "$PG_PORT" \
-U postgres \
--verbose \
--dbname postgres \
./dvdrental.tar
# run the sample tests
cd test
./runner.sh
Contributing
This project is still very early, and we'd love to have more contributors.
View the source on Github