-1

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?

  • 1
    It is impossible to check SQL query fully without tables structures, because the query validity may depend on them. – Akina Nov 12 at 8:02
  • 2
    What Akina wrote: plus: you also need data in the tables, otherwise testing the SQL statements is also no really helpful. E.g some types of sub-queries can fail depending on what they return. – a_horse_with_no_name Nov 12 at 8:48
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.