CLI

Install sgl as a tool dependency of your module, so everyone on the project runs the same version:

go get -tool seagull.sh/seagull/cmd/sgl
go tool sgl help

Or install it globally with go install seagull.sh/seagull/cmd/sgl@latest, or download a signed binary from the releases page. go install seagull.sh/seagull/cmd/seagull@latest installs the same tool as seagull.

sgl finds its configuration by looking for sgl.yaml (see configuration) in the working directory and then each parent directory, so every command works from anywhere inside a project.

Commands

Global flags go before the command:

A command's own flags may come before or after its arguments, so sgl migrate up --dsn file:app.db and sgl migrate --dsn file:app.db up are the same. -h or --help works on every command and command group (sgl migrate -h, sgl migrate up --help): it prints the usage and flags on stdout and exits 0.

An error in the configuration file names the file relative to the working directory when it lies beneath it, and by its full path otherwise.

init

sgl init writes a starter project for a dialect (postgres when --dialect is omitted): sgl.yaml, db/migrations/0001_init.up.sql and 0001_init.down.sql, db/queries/example.sql with three annotated queries, and db/seeds/0001_authors.sql. The SQL is written for the dialect's family, so it vets cleanly as it stands. sgl init never overwrites a file: if any of them exists it stops with exit status 2 and writes nothing.

generate

sgl generate replays each database's migrations (or reads schema_from), analyses every query, and writes the Go into go.out. Files a previous run generated and this run no longer produces are removed when they carry a recognised generated header. Filenames in the current result are replaced regardless of their previous header; other handwritten files are left alone, and a symbolic link, named pipe, socket or device in go.out is never opened or removed. Existing files are read only as far as sgl needs: enough to tell whether a file it writes changed, and the header of any other. Diagnostics are printed on stderr. A query with an error of its own, such as SQL the dialect cannot analyse or a query the emitter cannot generate, is left out: the rest of the package is written, and the exit status is 1, so a CI run still fails until the query is fixed or removed. Any other error, from a migration, the schema, two queries of one name, an analysis limit or the package's names, stops generation: nothing is written for that database and the exit status is 1. sgl vet reports the same errors without writing anything.

The prepared-statement cache, prepared.go, is written only for a database that sets go.prepared: true; the default is false. The library's emit_driver_databasesql.New() defaults the other way, on purpose; see configuration.

vet

sgl vet runs the same analysis as generate and reports every diagnostic, writing nothing, including what the configured emitter would refuse before it generates, such as Q059 and Q060, so a project vet passes also generates. The exit status is 1 when any diagnostic is an error, or, with --strict, a warning. --format chooses the output (see Diagnostics).

fmt

sgl fmt rewrites directive comments into their canonical form: alias prefixes become the canonical prefix (the first entry of directives.prefixes), and spacing is normalised. Positional and keyword arguments keep their order. SQL is never changed: only comments that are directives are rewritten. Each file is read as its database's dialect writes SQL, with the dialect's quotes, comments and line comment prefix, so a directive-like line inside one of its strings, or a -- that the dialect does not read as a comment, is left alone; a file outside every configured directory is read with neutral rules.

With no paths it formats the query and seed directories of every configured database; given paths, it formats those files and the .sql files beneath those directories, and needs no configuration. Named pipes, sockets and devices met in a directory are skipped, and one run formats at most 10,000 files. A file larger than 4 MiB, or a named pipe or device given as a path, is refused. A symbolic link is formatted through: its target is rewritten and the link kept. An interrupt stops the run before the next file.

Migrations are left out unless --include-migrations is given. Seagull records the checksum of every migration it applies and refuses to migrate when an applied file has changed, so rewriting even an alias prefix in an applied migration would make the next sgl migrate up fail with a checksum mismatch. A path inside a configured migrations directory is refused without the flag, and a directory that holds one is formatted without it, with a note. Use the flag only for migrations that have not been applied anywhere, or record the new checksums afterwards with sgl migrate repair.

To adopt directives written with another tool's prefix, list that prefix in directives.prefixes after the canonical one, then run sgl fmt -w: the directives are accepted as aliases and rewritten to the canonical prefix. --rewrite-prefix is not the tool for this, since it rewrites every directive to the prefix it names.

explain

sgl explain Q012 prints a code's title, usual severity and an explanation of its cause and fix; q12 and 12 work too. With no code it lists every code. --format markdown prints Markdown instead: the diagnostics reference is generated that way.

describe

sgl describe GetUser finds a query in the configured databases by the name its sgl.query header gives, as written there, or by the name of its generated method: a query declared get_user_by_id is found as get_user_by_id and as GetUserByID, and a query named WithTx, whose method becomes WithTx2 because the generated code declares a WithTx of its own, is found by both names. It prints its SQL as the generated code writes it, without the directive lines of its header, its parameters and result columns with their SQL types and nullability, and the Go it generates: the SQL constant, the method's signature and the parameter and row structs it uses. Each parameter is shown as the generated SQL writes it, in the dialect's placeholder form: $1 on PostgreSQL, ?1 on SQLite, ? on MySQL, {p_id:UInt64} on ClickHouse. --format json prints the same as JSON, with the form in each parameter's placeholder. A name that matches no query is a usage error with a suggestion. A query named like a method a runtime builder declares, such as Count or Where, shows its own method.

schema

sgl schema dump prints the schema catalogue, built by replaying the migrations, or with --from-db DSN by introspecting a live database. --format json (the default) prints tables, views and enums with their columns, types and nullability; --format sql prints approximate DDL for reading.

A catalogue read from a live database, by --from-db, schema_from or schema diff, leaves out the history, seed and lock tables the migration and seed runtime keeps, by the names migrate.namespace and migrate.history_table give them, so a table that only looks like the default sgl_migrations is kept. SQLite reports a view's columns only with the types they are declared with, so sgl types each SQLite view from its definition, as replaying the migrations does: an expression gets its type, and a column is nullable only when it can hold NULL, through an outer join for example.

sgl schema diff connects to a live database and compares its tables and columns with the schema the migrations describe: tables and columns that exist on one side only, and columns whose type or nullability differ. The migration and seed bookkeeping tables are ignored. It reads the live schema through the connection it opened and prints the diagnostics introspection reports, as schema dump does. It exits 0 when they match and 1 when they drift. Introspection, and so --from-db, schema_from and schema diff, is available for the PostgreSQL family and SQLite. MySQL, MariaDB, ClickHouse and DuckDB have none, so their schema always comes from the migrations.

For the PostgreSQL family, introspection reads only your own objects. It leaves out the system schemas, TimescaleDB's internal schemas (so hypertable chunks never show as drift), and any schema or relation an extension created. Partitioned tables and their partitions appear as tables, each partition naming the table it partitions, as replaying the migrations records them (see Schema API); foreign tables appear as tables. Array columns read as their element type with [] per dimension (text[], int4[][]), as they do from the migrations, and enum columns carry their enum. A domain reads as the type it is declared over, with its NOT NULL, as it does from the migrations.

migrate and seed

sgl migrate up prints a line for each migration it applies and then N applied; sgl migrate down, and sgl migrate to when it moves down, print a line for each one it reverts and then N rolled back. sgl seed apply and reseed print N applied. When a run fails or is interrupted after changing something, the count is printed before the error, so you know what ran.

Before running anything, up, down and to refuse a migration about to run whose sgl.migration directive is malformed, such as no_transacton: true, or names an unknown role, naming the file and line. down, and to when it moves down, refuse to roll back while a migration is dirty; finish it with up or resolve it with repair first.

force and repair change only the history table, under the migration lock. They print what they will change on standard error and ask for confirmation when standard input is a terminal; without one, which includes a pipe or a device such as /dev/zero, they refuse to run unless --yes is given, and repair needs --completed for a dirty migration. An answer is one line of at most 1 KiB. Declining changes nothing and exits 1. See Recovering with force and repair for what each records.

The migration backend is chosen by the name the dialect reports (its Info().Name), then by its family, not by the dialect: key in sgl.yaml: PostgreSQL, CockroachDB and TimescaleDB each get their own backend, MySQL and MariaDB share MySQL's, and SQLite, ClickHouse and DuckDB get theirs. It takes migrate.namespace, migrate.history_table and migrate.lock from the configuration. SQLite, ClickHouse and DuckDB take no lock, so migrate.lock can only be none for them. A dialect with neither a stock backend nor one registered with cli.WithMigrationBackend cannot be migrated. DuckDB's backend needs a binary with a DuckDB driver; see custom binaries. See Migrations and seeds for how runs are locked and recorded. On the PostgreSQL family, MySQL and MariaDB a run applies its migrations on one session, so a SET search_path or a USE in one migration lasts for the later ones of the run, while the history and seed tables stay in the schema or database the connection started in (see Sessions and the search path).

dialects

sgl dialects lists every dialect the binary knows, with its family, parameter style, whether it supports RETURNING, whether it has a migration backend and introspection, and the database driver the binary opens for it.

config

sgl config schema prints the JSON Schema of sgl.yaml (also published as sgl.schema.json). sgl config validate [path] reads a configuration file, by default the one sgl would find, and checks it, including that every dialect and go.driver is one this binary knows and that they fit together, that each dialect option applies to its dialect, and everything else sgl generate checks before it reads a file, such as a go.interface.name or go.query_names.func that collides with a name the generated package declares. Every other command refuses such a file the same way. sgl version prints the configuration version this binary reads as config schema.

Connection strings

sgl migrate, sgl seed and sgl schema diff read the connection string from --dsn, then the SGL_DATABASE_URL environment variable, then migrate.dsn in the configuration. They work on one database: when the configuration has several, choose one with --database. Connection strings, and the passwords inside them, are always redacted when sgl prints them, including inside database driver errors.

A ${env:NAME} reference to a variable that is not set is reported only by a command that uses that connection string, with exit status 2, naming the database, the key and the variable. migrate, seed and schema diff use migrate.dsn when neither --dsn nor SGL_DATABASE_URL is given; generate, vet, describe and schema dump without --from-db use schema_from, and never fall back to the migrations in its place. See environment expansion.

A relative SQLite path resolves according to where it was written. In migrate.dsn or schema_from it is relative to the directory of sgl.yaml, so file:app.db names the same file whichever directory sgl runs from. In --dsn, --from-db or $SGL_DATABASE_URL it is relative to the working directory: the process's own, or the one cli.WithDirectory sets in a custom binary.

The stock binary includes pure-Go drivers, so it builds without cgo:

DuckDB needs cgo, so the stock binary generates code for DuckDB but cannot connect to it; build a custom binary (below) to add a DuckDB driver, and it runs sgl migrate and sgl seed against DuckDB. The drivers belong to the seagull.sh/seagull/cmd/sgl/drivers package, not to the cli package, so a custom binary links only the drivers it imports; drivers.Options() imports the stock set and returns the options that select it.

Diagnostics

Problems are printed as the file, line and column, the severity and code, and the message; then the source line, numbered in a gutter, with carets under the span at fault; then a help: line when there is a suggestion:

db/queries/authors.sql:1:30: error[Q027]: unknown keyword argument "nullabel" on sgl.query
   |
 1 | -- sgl.query(GetAuthor, one, nullabel: true)
   |                              ^^^^^^^^
help: did you mean "nullable"?

A source line longer than 160 characters is quoted as a window of 160 around the span, with ... where it was cut, and a line longer than 1 MiB as (line too long to show). sgl vet ends with a count of errors, warnings and hints.

Diagnostics that come from the configuration rather than a SQL file, such as a type override naming a column that does not exist, are reported against sgl.yaml.

--format json writes one JSON object per diagnostic (file, database, severity, code, message, line and column, and suggestion, end_line and end_column when they are set), --format github writes GitHub Actions workflow annotations, and --format sarif writes a SARIF 2.1.0 log for code-scanning tools. Every code is documented in the diagnostics reference and by sgl explain.

Exit codes

An error is printed on stderr as sgl: ..., followed by a hint: line when another sgl command resolves it:

A Q051 diagnostic for another tool's prefix gets a help: line saying that sgl fmt -w rewrites it once the prefix is accepted.

A panic never escapes as a crash and its stack is never printed with the error: sgl prints sgl: internal error: ..., or the error naming the panic, and logs the stack once on stderr at warn level, which --log-level error hides. Code generation logs only errors by default, so run it again with --log-level warn to see the stack of a panic there. A panic while one statement or query is handled is reported as diagnostic Q070 on it instead.

On the first Ctrl-C or SIGTERM, sgl says it is cancelling and lets the command stop cleanly. The command then prints sgl: interrupted, or sgl: interrupted: ... naming what was running, such as the migration, with a hint when one applies.

Custom binaries

sgl is a thin wrapper around the importable seagull.sh/seagull/cmd/sgl/cli package. To register a configured dialect, an emitter or a database driver, write your own main:

package main

import (
    "os"

    _ "github.com/duckdb/duckdb-go/v2"

    "seagull.sh/seagull/cmd/sgl/cli"
)

func main() {
    os.Exit(cli.Main(os.Args[1:], cli.WithDriver("duckdb", "duckdb")))
}

The cli package links no database driver of its own. A custom binary imports each driver it needs and names it with cli.WithDriver. To keep the stock drivers as well, pass drivers.Options()... from seagull.sh/seagull/cmd/sgl/drivers, which imports them and selects each for its family; a cli.WithDriver given after it replaces the stock choice for that dialect or family.

A registration with an empty name, a nil factory or an empty driver name makes every command exit 2, naming the option, before anything runs; a nil option is skipped. A panic in a registered dialect, emitter or driver is recovered like any other (see Exit codes).

cli.MainContext(ctx, args, options...) runs one command under a context you own. With the DuckDB binary above, sgl migrate and sgl seed work on DuckDB, while sgl schema dump --from-db and sgl schema diff still refuse it, as Seagull cannot introspect DuckDB. The custom sgl binary example uses three of them: a SQLite dialect with tighter parser limits, a database/sql emitter that writes an extra file, and the pure-Go SQLite driver.

sgl migrate, sgl seed and the runtime tables schema leaves out use the migration backend of the dialect's Info().Name, so a stock dialect registered under another key keeps its backend: a dialect_engine_cockroachdb.New() registered as crdb still takes the CockroachDB lock table. A dialect given a name of its own with WithName falls back to its family's backend; register one under that name, built from a migrate.BackendSpec, when the database it names needs different migration SQL:

cli.WithMigrationBackend("harbourdb", func(options ...migrate.BackendOption) migrate.Backend {
    return migrate.CustomBackend(harbourSpec, options...)
})

An emitter factory receives a pointer to a cli.EmitterRequest holding the dialect's facts, Dialect, and go.prepared; it must not keep the pointer. The stock database/sql emitter chooses how it binds and scans values from the drivers the dialect lists in Dialect.Drivers, so a factory that wraps it passes only the settings it changes, and emit_driver_databasesql.WithDriver when the binary links a driver other than the dialect's first:

cli.WithEmitter("database/sql", func(request *cli.EmitterRequest) (emit.EmitterPort, error) {
    return emit_driver_databasesql.New(
        emit_driver_databasesql.WithPrepared(request.Prepared),
        emit_driver_databasesql.WithDriver("modernc.org/sqlite"),
    ), nil
})