Automated testing of SQL scripts

Question

I'd like to run some simple tests on SQL scripts in CI to just ensure the scripts are valid SQL (PostgreSQL). While I could run Postgres in Docker or even use an in-memory DB such as Sqlite (it's close enough), that'd require creating the tables etc. first and the people writing these scripts are too lazy to put in all that effort.

I've done a lot of Googling and to my dismay, I wasn't able to find a single tool to do only syntax validation and ensure parsability. Does anyone know of a tool/framework, in any language, that can do this?

Answer 1

Testing database queries in a CI context pretty much requires that you provision a fresh database and load it with test fixtures so that the database system is in a known good state at the start of the test cycle.

One trick that can speed things up with postgresql is creating a template database with your test fixtures and then issuing CREATE DATABASE project_test WITH TEMPLATE project_test_template, OWNER test_user; After provisioning your postgres server as this will ensure you have a clean copy of the same test fixture every time and works pretty much the same if you are running a persistent database server that doesn't get reprovisioned on every CI run or in a docker container where you are creating a new database server every run. And dropping and recreating the test fixtures database can happen between test series too. So if you have multiple tests that might interfere with each others edits to the data store dropping and recreating the database for each one is a straightforward way to guarantee you are isolating your tests from each other.