Concepts
sqlate has two kinds of .sql file, statements and patterns, and one grammar they share. This
document shows each construct by example. The glossary defines the terms; the
features document states every rule.
A .sql file
A file is a header of --| declarations followed by a body. The library reads the header; the
engine receives the body only.
--| tier: standard
--| key: id
--| field: id uuid
--| field: code text
--| field: name text
--| field: version bigint
--| field: created_at timestamp
-- The read model. The SELECT list is the scan order.
SELECT id, code, name, version, created_at, updated_at
FROM team
tier is required on every file. key and field make this file a projection base, which a
collection read wraps as a query; they are explained below. A plain -- comment in the header is prose,
skipped. The header ends at the first line that is neither blank, a comment, nor a declaration.
Parameters
A parameter is {{name}}. Arguments bind by name, and the library renders each parameter as
the engine's placeholder in order of appearance.
--| tier: standard
SELECT version FROM team WHERE id = {{id}}
Compiled for PostgreSQL, the engine receives:
SELECT version FROM team WHERE id = $1
Written {{name:type}}, a parameter binds through CAST, so the engine parses the value as
the declared type and rejects one it cannot read:
--| tier: standard
INSERT INTO organization (parent_id, code, name)
VALUES ({{parent_id:uuid}}, {{code}}, {{name}})
INSERT INTO organization (parent_id, code, name)
VALUES (CAST($1 AS uuid), $2, $3)
Written {{name...}}, a parameter expands to one placeholder per element of a non-empty slice,
so an IN list binds as values and never as text:
--| tier: standard
SELECT id, code, name, version, created_at, updated_at
FROM team
WHERE code IN ({{codes...}})
ORDER BY code
Bound with two codes, the engine receives:
SELECT id, code, name, version, created_at, updated_at
FROM team
WHERE code IN ($1, $2)
ORDER BY code
Patterns and includes
A pattern is a .sql file that shares SQL every statement would otherwise repeat. It is
published under a namespace, and a statement includes it with {{> namespace.name}}; the
library splices the pattern's text into the statement at compile time. An application's own
pattern, published under the namespace app:
--| tier: native
--| native: postgres, RETURNING. Ports: OUTPUT INSERTED (SQL Server), a second read (MySQL).
-- The identity every command returns: the row's key and its version.
RETURNING id, version
The statement that includes it:
--| tier: native
--| native: postgres, RETURNING through app.identity.
INSERT INTO team (code, name)
VALUES ({{code}}, {{name}})
{{> app.identity}}
The library publishes its own patterns under the namespace sql. Two of them are the
optimistic-concurrency guard: guard_where is id = {{id}} AND version = {{version}}, and
guard_set is updated_at = CURRENT_TIMESTAMP, version = version + 1. A guarded command
includes both:
--| tier: standard
UPDATE team
SET name = {{name}}, {{> sql.guard_set}}
WHERE {{> sql.guard_where}}
A pattern's parameters pass through the include and compile as parameters of the including
statement, so the statement above binds name, id, and version. A pattern never includes
another pattern.
Sources, namespaces, and the catalog
A source is one namespace's patterns: a directory of .sql files. The catalog is the set of
registered sources, read and validated once where the program starts, and every statement
compiles against it.
//go:embed statements/*.sql patterns/*.sql
var files embed.FS
catalog := query.MustCatalog(query.Patterns(), query.Publish("app", files, "patterns"))
stmts := catalog.MustCompile(files, "statements", db.Dialect())
query.Patterns() is the library's source under sql. Publish declares an application's
directory under the namespace it chooses. Two sources cannot share a namespace; when they would,
As registers one under an alias, the way an import is aliased:
query.Publish("app", files, "patterns").As("shared")
Tiers and the native declaration
Every file declares its tier. A standard file uses only standard SQL and runs on any engine. A
native file uses one engine's own feature, and its native declaration names the feature and
the port, the way the same effect is reached on other engines:
--| tier: native
--| native: postgres, pg_advisory_xact_lock over hashtext. Ports: sp_getapplock (SQL Server), GET_LOCK (MySQL).
--| transaction: required
SELECT pg_advisory_xact_lock(hashtext({{name}}))
transaction: required makes the statement refuse to run outside a transaction. The port list
of a whole code base is one search for --| tier: native, and the linter refuses a standard file
that uses a form the engine declares native.
Returning the changed row
A command that changes one row often needs the row back. Some engines return it from the command
itself (RETURNING); on others, the program runs the command and then reads the row. A standard
file covers both cases by naming, in its returning declaration, the statement that reads the row:
--| tier: standard
--| returning: order_by_id
UPDATE orders
SET status = 'shipped', {{> sql.guard_set}}
WHERE {{> sql.guard_where}} AND status = 'packed'
The read is an ordinary statement in the same directory, of the form SELECT <columns> FROM …,
and the command takes every parameter the read takes. The read's column list, less its one
qualifier, is the list the engine returns, so the command's table needs no alias and both forms
scan with the same function. The dialect chooses the form when the file compiles. On an engine
that returns rows, the dialect renders the single-statement form, the command with its clause
appended; the file stays standard tier, because the clause is the dialect's text, not the file's.
On any other engine, the fallback runs the command and then the read in one transaction, whose
lock on the changed row keeps another writer out until the read has it. The load checks that the
read reads the command's own table.
Both forms return the same row when the declaration is sound: the read selects exactly the row the command changed, from the command's own table. They differ where it is not, or where the session cannot hold a unit:
- a command that changes more than one row outside a transaction: the single-statement form's
change has committed before it reports
ErrNotOneRow, while the fallback rolls its own transaction back; - a read that does not find the changed row: the single-statement form returns the row the engine
returned, the fallback
ErrNotOneRow; - a session that is neither a
*sqlate.Txnor asqlate.Beginner: only the single-statement form runs.
The read's parameters are the command's, so a command whose key the engine generates cannot be a returning command; the caller supplies the key.
A guarded command can also be a returning command. The guard takes the row it needs to tell a refusal from a version mismatch from the returning command's handle, so on an engine that returns rows, a successful guarded write is one statement.
Overlays
An engine respells a library pattern by publishing a file of the same name with the same
parameters. The standard paging pattern is OFFSET {{offset}} ROWS FETCH NEXT {{fetch}} ROWS ONLY; an engine without that form overlays it:
--| tier: native
--| native: mysql, LIMIT and OFFSET.
LIMIT {{fetch}} OFFSET {{offset}}
query.Patterns().Overlay(mysqlPatterns, ".")
An overlay can only respell what the source defines: a file naming no pattern of the source, or
declaring different parameters, is a catalog error. A pattern may declare an alternate set of
parameters that an overlay may use instead. The library's keyset predicate, which continues a
read past a cursor, declares one. Its standard body is a chain of disjuncts, and PostgreSQL
overlays it as one row-value comparison over the alternate set:
--| tier: native
--| native: postgres — row-value comparison
--| port: the standard tier's expanded chain of disjuncts, query/patterns/keyset.sql
-- The keyset predicate as one row-value comparison; op is > or <.
({{columns}}) {{op}} ({{values}})
postgres.Patterns() returns the library's source with this overlay applied, and a program
passes it to NewCatalog in place of query.Patterns(). PostgreSQL accepts every other library
pattern as written.
The projection base and the collection read
A projection base declares its key, the identity column and the sort tie-breaker, and one
field per column a request may filter or sort by, with the SQL type the request's text is cast
to. A key may name several fields, --| key: org, id, and a field whose column never holds a
null declares it, --| field: id uuid not null. The declarations are an allow list: a request
naming any other field is rejected before any SQL exists.
At request time the library composes the collection read from its own patterns, with the base as a derived table. A request for page 2 of 10, sorted by code then newest first, filtered by name and by a set of codes, becomes:
SELECT * FROM (
SELECT q.*, COUNT(*) OVER () AS sqlate_total FROM (
SELECT id, code, name, version, created_at, updated_at
FROM team
) q
WHERE q.name LIKE CAST($1 AS text) AND q.code IN (CAST($2 AS text), CAST($3 AS text))
) q
ORDER BY q.code, q.created_at DESC, q.id
OFFSET $4 ROWS FETCH NEXT $5 ROWS ONLY
Request values never enter as text: each is bound through its field's declared type, so a value the engine cannot read as that type is a rejected request, not a server error. The total is a window count over the filtered base in the page's own statement, so it cannot disagree with the page; a request that declines the total gets the read without the inner layer. The single-row read is the base under one equality predicate.
A page can also continue from a cursor, the previous page's last row, in place of an offset.
The read then replaces the offset with the keyset predicate, the rows past that row in the sort
order. It needs the sort terms through the last key field to run in one direction over fields
declared not null, and the features document states the rules.
Migrations
A migration is a .sql file too, in the NNNN_name.up.sql and NNNN_name.down.sql layout.
Its header has one optional declaration. transaction: none runs the migration outside a
transaction, for DDL an engine refuses inside one, and such a file contains exactly one
statement:
--| transaction: none
CREATE INDEX CONCURRENTLY team_code_ix ON team (code)
The reserved delimiter
{{ means a parameter or an include wherever it appears in a body, inside string literals and
comments included, so the body needs no lexer. A {{ that forms neither is a load error, and
the linter reports it at the line.
Alternatives the design rejected
ORMs such as gorm, bun, and ent are frameworks: they move the queries out of SQL and into the framework's vocabulary.
Runtime query builders such as goqu, squirrel, bob, and jet are framework-sized runtime dependencies, each with its own dialect model and none with a split between standard and native SQL. bob and jet generate against a live schema and still keep the builder at runtime.
SQL-to-Go generators such as sqlc are the strongest alternative: no runtime footprint, native
.sql files, and schema verification at generate time. sqlc supports only the engines its tooling
parses, it adds a build-time dependency with a C parser behind it, and it needs a second authoring
convention wherever a query must be composed at run time. sqlate performs the one sqlc check a
runtime library seems unable to replace, schema verification, at startup: Verify prepares every
statement against the schema.
A Go vocabulary of statement types that renders SQL keeps queries out of SQL, the problem sqlate exists to solve. sqlate took its typed request errors, its directives, and its generic scan and query functions from such a vocabulary, and left the renderer behind.
Runtime text templating over .sql files, with text/template or similar, makes interpolating
text into SQL an ordinary operation with no SQL-aware escaping, so injection safety rests on
discipline instead of structure. It also defeats editor support, lint support, and the verification
that prepares the final text. sqlate resolves includes at compile time against published patterns,
composes request-time reads from library patterns only, and binds every value as a parameter.
PRQL is analytical only, has no Go binding, and adds a second language above SQL.