Describe queries and diff schemas
On this page
On SQLite:
- SQLite types print in their normalised form: INTEGER PRIMARY KEY is bigint, as the rowid, and other INTEGER columns integer, and every one maps to int64
- schema diff opens the database with --dsn file:drift.db; + marks what only the database has, - what only the migrations have
- SQLite can be introspected, so schema diff and schema dump --from-db work against a live file
Seagull learns a great deal before it writes any Go: the type and nullability of every column, what each parameter is compared with, the whole schema your migrations build. These commands let you ask for that knowledge directly. sgl describe answers "what will this query's method look like?", sgl schema dump answers "what schema do my migrations add up to?", and sgl schema diff answers "does the real database still match them?". None of them writes a file.
The project
A small library: authors, and books that belong to them. A book's subtitle is the one column that can be NULL, which matters below.
db/migrations/0001_authors.up.sql
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
db/migrations/0002_books.up.sql
CREATE TABLE books (
id INTEGER PRIMARY KEY,
author_id INTEGER NOT NULL REFERENCES authors (id),
title TEXT NOT NULL,
subtitle TEXT,
published INTEGER NOT NULL
);
Two queries use them, one that reads and one that writes:
db/queries/books.sql
-- sgl.query(ListBooksByAuthor, many)
SELECT books.id, books.title, books.subtitle, books.published
FROM books
JOIN authors ON authors.id = books.author_id
WHERE authors.name = ?
ORDER BY books.published;
db/queries/books.sql
-- sgl.query(AddBook, exec)
INSERT INTO books (author_id, title, subtitle, published) VALUES (?, ?, ?, ?);
The session
sgl describe ListBooksByAuthor finds the query by name, in whichever database of sgl.yaml declares it, and prints four sections. SQL is the query as written. Parameters lists each placeholder with its name, type and nullability: the ? is compared with authors.name, so it is called name and typed text. Parameters are numbered $1, $2 and so on here, even though the SQL writes ?. Columns gives each output column its type, whether it can be NULL and the table column it comes from. Go is the constant, the row struct and the method signature that sgl generate would write. subtitle is nullable, so its field is a *string.
sgl describe AddBook shows the other side. With more than one parameter, the method takes an AddBookParams struct, again with Subtitle as a pointer because the column can be NULL. An exec query has no columns, and its method returns only an error.
sgl describe ListBookByAuthor has a typo. sgl suggests the nearest query name and exits 2, its status for a usage mistake.
sgl schema dump --format sql prints the schema the migrations build, as SQL. This is Seagull's own model of it, after every migration has been replayed: types in their normalised names, tables qualified with SQLite's main schema. Without --format sql the dump is JSON, for other tools to read.
sgl schema diff --dsn file:drift.db compares a live database with that model. Before the session, the example's test applied the migrations to drift.db and then changed it by hand, adding an isbn column to books and dropping subtitle, the kind of hotfix that never makes it back into a migration. The diff lists each difference, + for what only the database has and - for what only the migrations have, and exits 1. A database that matches prints a line saying so and exits 0, which makes the command a useful check before a deploy. sgl schema dump --from-db prints a live database's schema in the same way.
sgl dialects lists the dialects this sgl knows, with each one's family, placeholder style, whether it supports RETURNING, migrations and introspection, and the database driver the stock sgl opens for it. Only the dialects marked INTROSPECT yes can be diffed or dumped from a live database.
sgl config validate checks sgl.yaml against the configuration schema without running anything else. sgl config schema prints that JSON Schema, which you can point your editor at for completion.
Here is the session as recorded. Each $ line is a command, the lines after it are what it printed, and # exit N is its exit status.
# describe shows what sgl inferred for one query: its parameters, its columns with
# their nullability and source, and the Go it generates. Nothing is written.
$ sgl describe ListBooksByAuthor
ListBooksByAuthor (many) in database "library", db/queries/books.sql:1
SQL:
SELECT books.id, books.title, books.subtitle, books.published
FROM books
JOIN authors ON authors.id = books.author_id
WHERE authors.name = ?1
ORDER BY books.published;
Parameters:
?1 name text not null
Columns:
id bigint not null books.id
title text not null books.title
subtitle text null books.subtitle
published integer not null books.published
Go:
const listBooksByAuthorSQL = `SELECT books.id, books.title, books.subtitle, books.published
FROM books
JOIN authors ON authors.id = books.author_id
WHERE authors.name = ?1
ORDER BY books.published;`
type ListBooksByAuthorRow struct {
ID int64 `json:"id"`
Title string `json:"title"`
Subtitle *string `json:"subtitle"`
Published int64 `json:"published"`
}
func (queries *Queries) ListBooksByAuthor(ctx context.Context, name string) ([]ListBooksByAuthorRow, error)
# exit 0
# Two or more parameters become a Params struct; a nullable column becomes a pointer.
$ sgl describe AddBook
AddBook (exec) in database "library", db/queries/books.sql:8
SQL:
INSERT INTO books (author_id, title, subtitle, published) VALUES (?1, ?2, ?3, ?4);
Parameters:
?1 author_id integer not null
?2 title text not null
?3 subtitle text null
?4 published integer not null
Columns:
none
Go:
const addBookSQL = `INSERT INTO books (author_id, title, subtitle, published) VALUES (?1, ?2, ?3, ?4);`
type AddBookParams struct {
AuthorID int64
Title string
Subtitle *string
Published int64
}
func (queries *Queries) AddBook(ctx context.Context, params AddBookParams) error
# exit 0
# A name that does not match suggests the nearest one.
$ sgl describe ListBookByAuthor
sgl: no query named "ListBookByAuthor"; did you mean "ListBooksByAuthor"?
# exit 2
# schema dump prints the schema the migrations build, as SQL here or as JSON.
$ sgl schema dump --format sql
-- database library
CREATE TABLE main.authors (
id bigint NOT NULL,
name text NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE main.books (
id bigint NOT NULL,
author_id integer NOT NULL,
title text NOT NULL,
subtitle text,
published integer NOT NULL,
PRIMARY KEY (id)
);
# exit 0
# drift.db had the migrations applied and was then changed by hand: books gained an
# isbn column and lost subtitle. schema diff compares it with the migrations.
$ sgl schema diff --dsn file:drift.db
+ column main.books.isbn is in the database but not the migrations
- column main.books.subtitle is in the migrations but not the database
# exit 1
# dialects lists every dialect this sgl knows and what it can do.
$ sgl dialects
NAME FAMILY PARAMETERS RETURNING MIGRATE INTROSPECT DRIVER
clickhouse clickhouse {name:Type} no yes no clickhouse
cockroachdb postgres $1 INSERT, UPDATE, DELETE yes yes pgx
duckdb duckdb $1 INSERT, UPDATE, DELETE yes no none
mariadb mysql ? INSERT, DELETE yes no mysql
mysql mysql ? no yes no mysql
postgres postgres $1 INSERT, UPDATE, DELETE yes yes pgx
sqlite sqlite ?1 INSERT, UPDATE, DELETE yes yes sqlite
timescaledb postgres $1 INSERT, UPDATE, DELETE yes yes pgx
# exit 0
# config validate checks sgl.yaml without running anything.
$ sgl config validate
sgl.yaml is valid (1 database(s))
# exit 0
Try it
Turn the join in ListBooksByAuthor around, FROM authors LEFT JOIN books ON books.author_id = authors.id, and describe it again. An author with no books still gives a row, so every book column can now be NULL and every field of the row struct becomes a pointer. sgl describe shows that before you regenerate anything; Nullability explains the rules.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/tooling/043_describe_and_schema && go run seagull.sh/seagull/cmd/sgl vet
The files
Every file of the example, as it is in the Seagull repository.
sgl.yaml
# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.
version: 1
databases:
- name: library
dialect: sqlite
migrations: db/migrations
queries: db/queries
go:
package: store
out: internal/store
db/migrations/0001_authors.up.sql
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
db/migrations/0002_books.up.sql
CREATE TABLE books (
id INTEGER PRIMARY KEY,
author_id INTEGER NOT NULL REFERENCES authors (id),
title TEXT NOT NULL,
subtitle TEXT,
published INTEGER NOT NULL
);
db/migrations/0001_authors.down.sql
DROP TABLE authors;
db/migrations/0002_books.down.sql
DROP TABLE books;
db/queries/books.sql
-- sgl.query(ListBooksByAuthor, many)
SELECT books.id, books.title, books.subtitle, books.published
FROM books
JOIN authors ON authors.id = books.author_id
WHERE authors.name = ?
ORDER BY books.published;
-- sgl.query(AddBook, exec)
INSERT INTO books (author_id, title, subtitle, published) VALUES (?, ?, ?, ?);
transcript.txt
# describe shows what sgl inferred for one query: its parameters, its columns with
# their nullability and source, and the Go it generates. Nothing is written.
$ sgl describe ListBooksByAuthor
ListBooksByAuthor (many) in database "library", db/queries/books.sql:1
SQL:
SELECT books.id, books.title, books.subtitle, books.published
FROM books
JOIN authors ON authors.id = books.author_id
WHERE authors.name = ?1
ORDER BY books.published;
Parameters:
?1 name text not null
Columns:
id bigint not null books.id
title text not null books.title
subtitle text null books.subtitle
published integer not null books.published
Go:
const listBooksByAuthorSQL = `SELECT books.id, books.title, books.subtitle, books.published
FROM books
JOIN authors ON authors.id = books.author_id
WHERE authors.name = ?1
ORDER BY books.published;`
type ListBooksByAuthorRow struct {
ID int64 `json:"id"`
Title string `json:"title"`
Subtitle *string `json:"subtitle"`
Published int64 `json:"published"`
}
func (queries *Queries) ListBooksByAuthor(ctx context.Context, name string) ([]ListBooksByAuthorRow, error)
# exit 0
# Two or more parameters become a Params struct; a nullable column becomes a pointer.
$ sgl describe AddBook
AddBook (exec) in database "library", db/queries/books.sql:8
SQL:
INSERT INTO books (author_id, title, subtitle, published) VALUES (?1, ?2, ?3, ?4);
Parameters:
?1 author_id integer not null
?2 title text not null
?3 subtitle text null
?4 published integer not null
Columns:
none
Go:
const addBookSQL = `INSERT INTO books (author_id, title, subtitle, published) VALUES (?1, ?2, ?3, ?4);`
type AddBookParams struct {
AuthorID int64
Title string
Subtitle *string
Published int64
}
func (queries *Queries) AddBook(ctx context.Context, params AddBookParams) error
# exit 0
# A name that does not match suggests the nearest one.
$ sgl describe ListBookByAuthor
sgl: no query named "ListBookByAuthor"; did you mean "ListBooksByAuthor"?
# exit 2
# schema dump prints the schema the migrations build, as SQL here or as JSON.
$ sgl schema dump --format sql
-- database library
CREATE TABLE main.authors (
id bigint NOT NULL,
name text NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE main.books (
id bigint NOT NULL,
author_id integer NOT NULL,
title text NOT NULL,
subtitle text,
published integer NOT NULL,
PRIMARY KEY (id)
);
# exit 0
# drift.db had the migrations applied and was then changed by hand: books gained an
# isbn column and lost subtitle. schema diff compares it with the migrations.
$ sgl schema diff --dsn file:drift.db
+ column main.books.isbn is in the database but not the migrations
- column main.books.subtitle is in the migrations but not the database
# exit 1
# dialects lists every dialect this sgl knows and what it can do.
$ sgl dialects
NAME FAMILY PARAMETERS RETURNING MIGRATE INTROSPECT DRIVER
clickhouse clickhouse {name:Type} no yes no clickhouse
cockroachdb postgres $1 INSERT, UPDATE, DELETE yes yes pgx
duckdb duckdb $1 INSERT, UPDATE, DELETE yes no none
mariadb mysql ? INSERT, DELETE yes no mysql
mysql mysql ? no yes no mysql
postgres postgres $1 INSERT, UPDATE, DELETE yes yes pgx
sqlite sqlite ?1 INSERT, UPDATE, DELETE yes yes sqlite
timescaledb postgres $1 INSERT, UPDATE, DELETE yes yes pgx
# exit 0
# config validate checks sgl.yaml without running anything.
$ sgl config validate
sgl.yaml is valid (1 database(s))
# exit 0