[0.1 draft] [guide 15 · spec 15 pages]

spec/sql

SQL (sql-pre-data, sql-post-data, sql-dql)

txtab

P sql-pre-data postgres CREATE TABLE orders (id bigint, customer_id bigint, amount numeric(12,2))
P sql-post-data postgres ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id)
P sql-post-data postgres CREATE INDEX orders_customer ON orders(customer_id)
P sql-dql clickhouse SELECT customer_id, sum(amount) FROM orders GROUP BY customer_id

A file MAY carry SQL next to its data: DDL to recreate the tables, and queries for provenance or example use.

Keywords

Keyword Section What goes there Executed
sql-pre-data before data load CREATE TABLE, schemas, types, extensions yes, in order
sql-post-data after data load indexes, primary and foreign keys, constraints, triggers yes, in order
sql-dql — SELECT and friends: provenance, examples never

The section names are those of pg_dump --section.

Syntax

text

P <keyword> <dialect>[-<version>] <statement>
  • The first payload token is the dialect, mandatory: a lowercase ASCII identifier, optionally followed by -<version>; the version MAY contain dots (postgres-17, mysql-8.4, clickhouse-24.3). Missing: sql-dialect-missing (FAIL).
  • After one space, the rest of the line is the statement, raw text to end of line.
  • One line holds one statement. There are no continuation lines.
  • Every line is self-contained: there is no file-level default dialect. Generic SQL uses ansi.

Dialects

Parsers SHOULD recognise these tokens and MUST accept any other lowercase identifier as-is.

Token Database
ansi ANSI / ISO SQL
mysql MySQL
mariadb MariaDB
postgres PostgreSQL (read aliases: postgresql, pg)
mssql Microsoft SQL Server (read alias: sqlserver)
sqlite SQLite
oracle Oracle Database
db2 IBM Db2
clickhouse ClickHouse
snowflake Snowflake
bigquery Google BigQuery
duckdb DuckDB

Writers SHOULD emit the canonical token. Readers MUST treat aliases as equivalent.

The token is split into dialect and version by matching the longest well-known dialect prefix; an unknown token is kept whole.

Matching a target dialect

When a tool selects statements for target D, each line's token L is classified:

Kind Rule Default
exact same dialect, same version (or both unversioned) include
family same dialect, exactly one side unversioned include, warn
version-mismatch same dialect, both versioned, versions differ include, warn; skip with --strict
none different dialect skip

Versions are compared as strings.

Binding and order

  • sql-* in a table block belongs to that table. A foreign key is written in the block of the table that owns it, wherever the target table is in the file.
  • sql-* in the file block holds statements shared by the file: schemas, extensions.
  • Execution order for a target dialect:
    1. every matching sql-pre-data, in file order, across all blocks;
    2. data load (if the tool performs it);
    3. every matching sql-post-data, in file order, across all blocks.
  • sql-dql is never executed.

Tooling

Text Tables tools do not run SQL against a database. txtab sql <dialect> FILE prints matching statements to stdout in the order above — warnings to stderr — and MAY print one section at a time (--section pre-data|post-data), like pg_dump.

Computed columns in generated DDL

When a tool generates DDL from column lines (rather than printing sql-* lines), a column with formula= becomes a generated column:

State PostgreSQL ≥ 12 / MySQL ≥ 5.7 ClickHouse No generated-column support
Virtual GENERATED ALWAYS AS (<expr>) VIRTUAL ALIAS <expr> omit the column, or emit the expression as a comment
Materialized GENERATED ALWAYS AS (<expr>) STORED MATERIALIZED <expr> ordinary stored column

<expr> is the formula translated into the target dialect; the formula grammar maps onto ANSI SQL everywhere (columns.md). Generated DDL puts CREATE TABLE in pre-data and keys, indexes and references= foreign keys in post-data.

spec/sql.md on GitHub