SQLpp

`SQLpp` - SQL Preprocessor

SQLpp (available in ./lib/pre-process/mod.ts) is a cross-database utility designed for pre-processing SQL files. Its functionality mirrors a subset of the meta-commands provided by PostgreSQL’s psql utility.

psql is a terminal-based front-end to PostgreSQL. It enables users to type in SQL queries interactively, issue them to PostgreSQL, and see the query results. psql also provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.

Key among these meta-commands are \set and \include, which allow for setting variables and including other SQL scripts respectively.

SQLpp adopts the similar meta-command functionalities offered by psql for pre-processing SQL files. It enables you to define and override \set variables within your SQL source code and support include statements, akin to psql.

However, unlike psql, which directly executes SQL queries on PostgreSQL databases, SQLpp focuses on preparing SQL files for execution.

The primary advantage of SQLpp over psql is its database-agnostic nature. While psql is specifically designed for PostgreSQL databases, SQLpp’s output can be utilized across almost all databases. This includes SQLite, DuckDB, Clickhouse local, Dolt, MySQL, SQL*Server, among others.

SQLpp provides a unique and efficient solution for SQL file pre-processing. While it does not execute SQL like psql, its focus on SQL preparation and its wide compatibility with almost all databases make it an incredibly useful utility in various SQL-related operations.

This does not mean that SQLpp is a substitute for SQL generators in all scenarios, but rather that it serves a distinct purpose and offers unique advantages in certain situations.

SQLpp, with its simple syntax mirroring a subset of PostgreSQL’s psql utility meta-commands, offers a more intuitive and easier to learn approach to SQL pre-processing. This makes it an ideal tool for those new to SQL or those that know existing tools like psql well.

SQLpp is an excellent choice for situations where complex SQL generation is not necessary. This might include scenarios where you’re dealing with simpler queries, or when you need to quickly prototype SQL scripts without type-safety in mind.

Usage Summary

Use the help command for usage instructions:

deno run -A ./lib/pre-process/mod.ts help   # explicity run in Deno
./lib/pre-process/mod.ts help               # use the built-in shebang
./lib/pre-process/mod.ts help psql          # get help on a specific command

Usage: mod.ts psql <src>

Options:

-h, --help                     - Show this help.
-s, --src      <psql-file...>  - additional SQL source file(s)
-i, --inspect                  - show inspection output instead of emitting SQL source
--set.*        <variable>      - --set.XYZ=value would override XYZ with value provided

Examples:

$ lib/pre-process/mod.ts psql lib/sql/pg/upsert.sql
$ lib/pre-process/mod.ts psql lib/sql/pg/upsert.sql --set.unit_test_schema=some_other_schema

`psql`-style Usage

Preprocess text in a way that psql would. Specifically, allow \set variables to be defined in the source and overridden via CLI.

`\include`

Supports these types of include statements (must be at the start of a line):

\i filename.sql
\include ../filename.sql
\include '../filename.sql'
\include "../filename.sql"

Similar to psql, \i and \include are synonyms; relative files are resolved from the current working directory but other rules can be provided as well.

`\ir`

Supports these types of include relative statements:

\ir filename.sql
\ir 'filename.sql'
\ir "filename.sql"

Similar to psql, \ir includes files relative to the location of the source file. Because source file might not be clear in the context of SQLpp you may need to provide a callback function to the preprocessor to define what “relative” means.

`\set`

Supports these types of set statements (must be at the start of a line):

\set name John
\set count 42
\set url 'https://example.com'
\set var = value
\set greeting 'Hello, \'world!'
\set greeting2 "Hello, \"world!"

Similar to psql, supports this type of replacement:

Style	Replacement
`:name`	John
`:'name'`	’John’
`:"name"`	”John”

Examples

Assume this file:

-- fixture-01

\set name 'John Doe'
\set table users
\set unit_test_schema dcp_assurance

SELECT * FROM users WHERE username = :'name';
SELECT * FROM :table WHERE username = :'name';
SELECT * FROM :"table" WHERE username = :'name';
SELECT * FROM :"table" WHERE username::"varchar" = :'name';
SELECT email FROM :unit_test_schema.upsert_test_user((1, 'Updated Name', 'john.doe@example.com'):::"unit_test_schema".test_user);

Assume these declarations in a *.psql file:

-- *** before first include ***

\\include './include_test.fixture-01.psql'

-- *** after first include ***

Running this command:

deno run ./pre-process/mod.ts psql test.sql --set.name="Shahid Shah"

Produces this output:

-- *** before first include ***

-- fixture-01

-- \\set name 'John Doe' (variable: name, value: John Doe, srcLine: 5)
-- \\set table users (variable: table, value: users, srcLine: 6)
-- \\set unit_test_schema dcp_assurance (variable: unit_test_schema, value: dcp_assurance, srcLine: 7)

SELECT * FROM users WHERE username = 'Shahid Shah';
SELECT * FROM users WHERE username = 'Shahid Shah';
SELECT * FROM "users" WHERE username = 'Shahid Shah';
SELECT * FROM "users" WHERE username::"varchar" = 'Shahid Shah';
SELECT email FROM dcp_assurance.upsert_test_user((1, 'Updated Name', 'john.doe@example.com')::"dcp_assurance".test_user);

-- *** after first include ***