sqlate
SQL templating for Go: authored .sql files made dynamic and composable, with a PostgreSQL
dialect and a conventions linter.
github.com/standards-lab/sqlate is the base module, and it imports only the standard library.
The PostgreSQL dialect is the postgres sub-module and the linter is the sqlint sub-module,
each released on its own tags.
The problem
Web services usually avoid real SQL. Queries are embedded as string literals in the host
language or generated by an ORM, and either way the SQL is written in a language that is not
SQL. sqlate keeps queries in .sql files, so SQL is expressed in its own language. It layers a
small syntax onto the files, so a file can express reusable fragments and compose dynamic
queries. The name combines SQL and template, for the composition the library adds to .sql
files.
Documentation
- Concepts: patterns and statements by example, and the grammar the files are written in.
- Quick start: a working program from
go getto a linted, unit-tested store, each feature shown once. - Features: every feature, package by package, in the detail needed to use it without reading the source.
- Glossary: the vocabulary the library introduces, by layer.
The package documentation, go doc github.com/standards-lab/sqlate/..., is the reference for
each exported name.
How it works
A statement is a .sql file with a header of declarations and a body. The library reads the
header; the body is the SQL the engine receives. The engine is the database server the driver
sends SQL to, PostgreSQL through the postgres sub-module. This statement edits a row under an
optimistic-concurrency guard:
--| tier: standard
UPDATE team
SET name = {{name}}, {{> sql.guard_set}}
WHERE {{> sql.guard_where}}
{{name}} is a parameter, bound by name. {{> sql.guard_set}} and {{> sql.guard_where}}
are includes of two patterns the library publishes under its own namespace, sql:
--| tier: standard
-- The protocol columns a guarded command advances; the command's own SET
-- list precedes it.
updated_at = CURRENT_TIMESTAMP, version = version + 1
--| tier: standard
-- The optimistic-concurrency guard's predicate, over a guarded table's id
-- and version columns; a command includes it as {{> sql.guard_where}}.
id = {{id}} AND version = {{version}}
At compile time the library splices the patterns' bodies into the statement, and the pattern's parameters become the statement's. Compiled for PostgreSQL, the engine receives:
UPDATE team
SET name = $1, updated_at = CURRENT_TIMESTAMP, version = version + 1
WHERE id = $2 AND version = $3
In Go, the statement directory is compiled once against the catalog of patterns, the statement
is fetched by its file name and bound to the value that runs it, and the value runs with its
arguments by name. db is the session over the program's own *sql.DB.
//go:embed statements/*.sql
var files embed.FS
catalog := query.MustCatalog(query.Patterns())
stmts := catalog.MustCompile(files, "statements", db.Dialect())
edit := stmts.Statement("edit").Guarded(stmts.Statement("version"), "version")
// id is the row's key and current the version the caller read.
version, err := edit.Run(ctx, db, current, query.Args{"id": id, "name": "Core Platform"})
Run returns the new version, sql.ErrNoRows when no row has that id, and
query.ErrVersionMismatch when the row exists at another version than current.
Conventions
Three conventions the library keeps are stricter than a reader might expect:
- The base module depends on the standard library alone. A sourced dependency enters only
through a sub-module's
go.mod: pgx throughpostgres, the TOML parser throughsqlint. sqltestis a public package: the scripted driver the library's own tests run over is the one a consumer's tests run over too, so a unit test needs no database.- Every
.sqlfile declares its tier, standard or native, and a native file names the engine feature it uses and the port to other engines. The port list of a code base is one search for--| tier: native.
Packages
sqlate(the root) is the session layer:DBover a plain*sql.DB, transactions, theDialectinterface, and the error taxonomy every engine returns.headerreads the--|declaration header of a.sqlfile.querycompiles authored statements against the pattern catalog and binds them to typed values: rows, projections with request directives, and guards.migrateversions a schema over authored SQL under the engine's lock, and replaces a third-party migration dependency.sqltestis the scripteddatabase/sqldriver for hermetic tests.postgres(sub-module) is the PostgreSQL dialect: placeholders, error classification, the advisory lock, and the engine's native forms.sqlint(sub-module) is the conventions linter as a package, with the command atsqlint/cmd/sqlint.
Development
The repository is a Go workspace of three modules. Tasks run through mise:
mise run check # build, vet, format, fix, tidy, unit tier, lint, sqlint; writes nothing
mise run currency # report requirements, Go, tools, actions, and images behind their latest
mise run upgrade # upgrade every module's go directive and requirements, and the tools, to their latest
mise run db:up # build and start the compose PostgreSQL and wait until it is healthy
mise run db:down # stop PostgreSQL, keeping its data
mise run db:reset # stop PostgreSQL and drop its data
mise run integration # run the integration tier against the running PostgreSQL
mise run acceptance # run db:up and integration, then db:reset whatever the result
compose.yml runs PostgreSQL on port 5433; POSTGRES_PORT moves it. The service builds from
compose/postgres/Dockerfile, whose FROM line is its one image pin and which carries its
configuration and health check; mise run currency reports a pin behind its latest. mise.toml
sets SQLATE_DSN, whose port follows the same variable. The integration tier is not part of CI.