sqlate

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

1. Concepts: patterns and statements by example, and the grammar the files are written in.
2. Quick start: a working program from go get to a linted, unit-tested store, each feature shown once.
3. Features: every feature, package by package, in the detail needed to use it without reading the source.
4. 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 through postgres, the TOML parser through sqlint.
• sqltest is 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 .sql file 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: DB over a plain *sql.DB, transactions, the Dialect interface, and the error taxonomy every engine returns.
• header reads the --| declaration header of a .sql file.
• query compiles authored statements against the pattern catalog and binds them to typed values: rows, projections with request directives, and guards.
• migrate versions a schema over authored SQL under the engine's lock, and replaces a third-party migration dependency.
• sqltest is the scripted database/sql driver 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 at sqlint/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.

License

Apache License 2.0.