A tool to run unit tests on PostgreSQL sql files.
- You have a bunch of SQL files with INSERT and SELECT in them. You also know what the expected output is. Put the source queries in a directory, and the expected output in another directory, use the same name for the files.
- Run sqltest on the directory with the source queries and the directory with the expected output.
- sqltest: create a temporary database
- sqltest: run setup instructions (a command specified in --setup) while providing the database name as an environment variable.
- sqltest: run the source queries in the source directory, and compare the output to the expected output. a. If different, print the diff and exit with an error code.
- sqltest: run --teardown command while providing the database name as an environment variable.
- sqltest: drop the temporary database.
| Source | Expected |
SELECT 1 + 1 as data;
BEGIN;
SELECT 2+2 as data;
COMMIT; |
data
------
2
(1 row)
BEGIN
data
------
4
(1 row)
COMMIT |
go install github.com/kovetskiy/sqltest/cmd/sqltest@latest
Usage:
sqltest [options] <in> <expected>
sqltest -h | --help
sqltest --version
Options:
-d --db <uri> PostgreSQL connection URI [default: postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable]
--setup <command> Command to run before test.
--teardown <command> Command to run after test.
--no-rm Do not remove output files.
--approve <filter> Approve results of testcases matching filter].
Example: --approve . to approve all testcases.
--debug Enable debug logging.
-h --help Show this screen.
--version Show version.
sqltest ./testdata/testcases ./testdata/expected -d 'postgres://a:b@host/dev?sslmode=disable'
2023-02-13 12:01:35.989 INFO PASS simple-math (772.895681ms)
2023-02-13 12:01:35.989 INFO PASS 1 testcases (772.933208ms)
If you see that your test failed, you can approve the result by running:
sqltest ./testdata/testcases ./testdata/expected -d 'postgres://a:b@host/dev?sslmode=disable' --approve .
The --approve flag will copy the output of the test that matches the given pattern (.) to the expected directory.
Environment variables are supplied to --setup and --teardown commands. This is useful for supplying database schema.
| Name | Description |
|---|---|
SQLTEST_DATABASE_NAME |
The name of the database used for the test |
SQLTEST_DATABASE_URI |
The connection string used for the test |
SQLTEST_TESTCASE_NAME |
The name of the test |
SQLTEST_TESTCASE_FILENAME |
The filename of the test |
SQLTEST_TESTCASE_DIR_IN |
The directory where the test is located |
SQLTEST_TESTCASE_DIR_OUT |
The directory where the expected output is located |
MIT