The checker finds every sqlshape.Query[R, P](template), sqlshape.One[R, P](template),
postgres.Copy[R](...) and postgres.MatView(...) (PostgreSQL) in a package, expands each template
into every combination of its branches (templates.md), analyzes each expansion
against schema.sql, and compares the result with the Go types. This page goes through what you
write, situation by situation, and shows what is rejected and what passes, together with the
diagnostic you will see. The rules are the same for PostgreSQL and MySQL; the examples are
PostgreSQL's, and where a rule uses the database's names, numbers or types (the constraint names,
the Go type table, the error codes in the diagnostics), postgres.md and
mysql.md have each database's.
The checker has two entry points. go vet -vettool=sqlshape (or sqlshape ./...) runs it over Go
packages, where the SQL lives in Query / One templates and is compared with the Go types.
sqlshape check file.sql runs the same analyzer and the same schema rules over SQL that is not in Go
at all (Part 2).
It has three parts. Part 1 is what every statement gets, with no declaration: the shape of the
result and the parameters, the meaning of the types, the failure modes, the One proof. Part 2
is what schema.sql can additionally demand of the statements that touch a table: predicates, pinned
columns, aggregates, state machines, labelled columns. Part 3 is what is checked outside a
statement: the Go code around it, and the schema itself.
- Part 1 — Checks on every statement
- Part 2 — Rules the schema declares
- Directives in
schema.sql - How a declaration works
- Every read carries the visibility predicate (
visible where) - A column is pinned on every statement (
pinned,-require-columns) - Tables are read through views (
via view,-no-table-reads/-no-tables) - A predicate across tables has a witness (
EXISTS) - An aggregate is reached through its root, one per statement (
aggregate) - A status column moves along its declared transitions (
transitions) - Append-only tables, paired writes, single-row deletes (
never,paired,single) - Labelled columns are read only where allowed (
sensitive,may read) - Different callers, different rules (
context) - The same rules for SQL outside Go (
sqlshape check)
- Directives in
- Part 3 — Outside the statement
For the row type R, the checker verifies that every result column has a field to receive it,
every field has a column, and types and NULL handling agree. With branches in the template, this
holds for every expansion.
The match is looked up by the col:"..." tag, then the db:"..." tag, then the field name in
snake_case.
Rejected
type Order struct {
ID int64
CustomerName string // field Order.CustomerName has no result column
}SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id
-- ^ result column "name" has no field in OrderPasses: alias the column, or name the column in the tag.
SELECT o.id, c.name AS customer_name FROM orders o JOIN customers c ON c.id = o.customer_idtype Order struct {
ID int64
CustomerName string `col:"name"`
}Rejected
SELECT id, id + 1 FROM orders
-- ^ result column 2 has no name: give it an alias (... AS name) so it can bind to a field of Order
SELECT o.id, c.id FROM orders o JOIN customers c ON c.id = o.customer_id
-- ^ result columns 1 and 2 are both named "id": alias one of them (... AS other_name)Passes
SELECT id, id + 1 AS n FROM orders
SELECT o.id, c.id AS customer_id FROM orders o JOIN customers c ON c.id = o.customer_idFields that can hold NULL: pointers, slices, maps, sql.Null*, the driver's nullable value types such as pgtype.*, and any type that
implements sql.Scanner.
Rejected
type User struct {
ID int64
DeletedAt time.Time // field DeletedAt is time.Time but column "deleted_at" may be NULL (use a pointer, or tag it `col:",notnull"` if you know better)
}SELECT id, deleted_at FROM usersPasses
type User struct {
ID int64
DeletedAt *time.Time
}Whether a column may be NULL is derived from NOT NULL constraints and primary keys, from the WHERE
clause (deleted_at IS NOT NULL or deleted_at = ... rules NULL out), from outer joins (the inner
side may be NULL), from functions (a strict function of non-NULL arguments is not NULL, nor is
coalesce(x, 0); a handful of strict built-ins and operators, meta ->> 'key' among them, can
still return NULL when there is nothing to report), and from a view's own WHERE clause. A view
tracks its underlying column's NOT NULL live, the way PostgreSQL itself does: a later
ALTER TABLE ... DROP NOT NULL on the base table reaches it too, even through a chain of views
and past whatever the view's own WHERE clause does. If you know better than the checker, override
it on the Go side with the col:",notnull" tag (on an array field it asserts the elements too)
or on the SQL side with a -- sqlshape: not null deleted_at line in the template. For a function's result, put
-- sqlshape: not null above its CREATE FUNCTION in schema.sql. In a RETURNING list, old.col after an INSERT and new.col after a DELETE (PostgreSQL 18) may be NULL whatever the column declares: the row does not exist on that side of the write.
Rejected
type Order struct {
ID int64
Total string // field Order.Total is not selected in every branch [if@11:else]: make it a pointer so those branches leave it nil
}SELECT id {{if .WithTotal}}, total{{end}} FROM ordersPasses
type Order struct {
ID int64
Total *string
}The branches that do not select the column leave the field nil. A field no branch selects is
has no result column.
A binding that can lose information (a numeric into a float64, a bigint into an int32, ...)
is accepted with a note, not rejected: only a genuinely incompatible pair (a text column into an
int64 field, say) is.
Passes, with a note
type Order struct {
ID int64
Total float64 // field Total: numeric into float64 loses precision
}SELECT id, total FROM ordersPasses, silently
type Order struct {
ID int64
Total string // keeps every digit; decimal.Decimal (shopspring/decimal) works too
}array_agg(row(...)), array_agg(t), row(...) and composite-typed columns are received by a
struct or a slice of structs. An anonymous row(...) matches fields by position; a named composite
type by name and order.
Rejected: the struct's fields are not in the composite type's order.
-- schema.sql
CREATE TYPE order_item AS (sku text, qty integer);type Item struct {
Qty int32 // field Items.Qty is at position 1 but the row type's column 1 is "sku" (fields are scanned in order)
Sku string
}
type Order struct {
ID int64
Items []Item
}SELECT o.id, array_agg((i.sku, i.qty)::order_item) AS items
FROM orders o JOIN order_items i ON i.order_id = o.id
GROUP BY o.id(Swapping two adjacent fields misplaces both, not just the first: Sku also lands on the
order_item type's column 2, "qty", so a second diagnostic names that position mismatch too.)
Passes
type Item struct {
Sku string
Qty int32
}(PostgreSQL never guarantees an array's own elements are non-NULL, even when the column holding
the array is NOT NULL, and the checker still carries a standing note for that in general (see
T[] in PostgreSQL types). Here the checker proves it anyway: order_item's row
constructor is never NULL itself, whatever its own fields' nullability, so array_agg((i.sku, i.qty)::order_item) carries no such note and this Passes example is accepted with none.)
Passes
var Count = sqlshape.Query[int64, struct{}](`SELECT count(*) FROM orders`)Rejected
var Count = sqlshape.Query[int64, struct{}](`SELECT id, total FROM orders`)
// R is int64 but the query returns 2 columnsPasses
type Base struct {
ID int64
CreatedAt time.Time
}
type Order struct {
Base
Total string
}SELECT id, created_at, total FROM ordersRejected: two fields would receive the same column.
type Order struct {
Base
ID int64 // Order: fields Base.ID and ID both bind to column "id"
Total string
}A named struct field, or an embedded field carrying a col:"..." tag, is not flattened: it is
treated as a nested row.
The Go types a column may be received with, and a parameter passed as, are the database's, as its
driver scans and encodes them: PostgreSQL's table,
MySQL's table. Both apply to receiving columns and to passing
parameters alike. On PostgreSQL a type not in the table, or one you want to receive with your own
type, is bound with // sqlshape: type <pg type> on the Go type; conversion is then the type's own
sql.Scanner / driver.Valuer (MySQL has no named types to bind to).
{{.X}} becomes a parameter in the SQL ($n on PostgreSQL, ? on MySQL; the checker numbers them
$n either way). The checker infers the SQL type each parameter needs from where it is used
(WHERE id = $1 needs bigint, = ANY($1) an array) and verifies that the corresponding field of
P fits, by the database's type table.
Rejected
type Params struct {
ID int64 // parameter .ID is int64 but SQL expects uuid
}SELECT id, email FROM users WHERE id = {{.ID}} -- id is uuidPasses
type Params struct {
ID uuid.UUID
}A string can be passed as a parameter of any type (it is sent in text form and interpreted by
PostgreSQL). A parameter wider than the column it feeds gets an overflow note: an int64 value
going into an integer column is parameter .ID: int64 into integer may overflow (the same for a
float64 value going into a real column). This applies element-wise to an array parameter too:
[]int64 into smallint[] gets parameter .Tags: int64 into smallint may overflow.
A field whose absence is expressed by a branch, {{if .Name}} AND name = {{.Name}} {{end}}, is a
pointer or a slice. A plain string cannot send NULL, so it assumes there is always a value.
Rejected: an enum parameter that is not a pointer (-strict).
type Params struct {
Status OrderStatus // parameter .Status is a non-pointer OrderStatus: its zero value "" is not a label of enum order_status and fails at runtime (SQLSTATE 22P02) when unset
}Passes
type Params struct {
Status *OrderStatus
}(Under -strict, a package that touches orders.status also gets that column's own enum
advisory, orders.status is enum order_status: a seeded lookup table ... -- a separate finding
from the one above, about the column rather than the parameter.)
{{.Filter.Name}} is the field Name of the field Filter of P. Inside
{{range .Items}} … {{.Sku}} … {{end}}, {{.Sku}} is a field of the element type of the slice
Items.
Passes
type Params struct {
Filter struct{ Name *string }
Items []struct{ Sku string; Qty int32 }
}SELECT id FROM products
WHERE true {{if .Filter.Name}} AND name = {{.Filter.Name}} {{end}}
AND (false {{range $i, $it := .Items}} OR sku = {{$it.Sku}} {{end}})A plain sku IN ({{range}}...{{end}}) is not empty-safe: with zero .Items, it expands to
sku IN (), a syntax error. The checker's branch-state exploration tries every length .Items
could have (0, 1, 2, ...) and reports exactly that error when it finds it. A range used this way
needs a form that stays valid SQL at every length, such as the false {{range}} OR ... {{end}}
above (or WHERE true {{range}} OR (...) {{end}}).
Where the SQL expects a composite type, pass a struct; where it expects an array of one, a slice of structs. Fields line up with the type's columns as for nested rows.
Passes
-- schema.sql
CREATE TYPE order_item AS (sku text, qty integer);
CREATE FUNCTION place_order(customer_id bigint, items order_item[]) RETURNS bigint ...type Item struct {
Sku string
Qty int32
}
type Params struct {
CustomerID int64
Items []Item
}SELECT place_order({{.CustomerID}}, {{.Items}})type Params struct {
ID int64
Limit int32 // sqlshape: parameter field Limit is never used by the template
}SELECT id FROM orders WHERE id = {{.ID}}type NewOrder struct {
CustomerID int64
Status OrderStatus // parameter .Status always sends a value into orders.status, so its DEFAULT never applies: decide which side owns the default (make the column conditional with {{if}} to use the database's)
}INSERT INTO orders (customer_id, status) VALUES ({{.CustomerID}}, {{.Status}})(Status here is also a non-pointer OrderStatus, so the "A parameter that may be NULL is a
pointer" advisory above fires too, and so does orders.status's own enum advisory: three findings
for this one Rejected example, not one.)
Passes: to use the database's default, make the column conditional. If the application always
decides, drop the column's DEFAULT.
INSERT INTO orders (customer_id {{if .Status}}, status{{end}})
VALUES ({{.CustomerID}} {{if .Status}}, {{.Status}}{{end}})(The Passes example still gets orders.status's enum advisory alone, the same as the previous
section's Passes example.)
Values a bigint or text cannot tell apart, such as an enum label, a table's ID or an amount in
some unit, are named types in Go. The checker binds a named type to the meaning on the SQL side
(an enum, a lookup table's key, a CHECK value set, a primary key, a domain) and reports every use
that disagrees.
There is no place where a Go type is declared to "be" a table's ID or an enum. A plain named type acquires its meaning the first time a statement passes or receives it against a column that has one, and keeps it from then on, in every package that uses the type.
type UserID int64 // nothing more: no tag, no comment, no registration// 1. the first meeting binds: UserID now stands for users.id
var User = sqlshape.One[User, struct{ ID UserID }](`SELECT id, email FROM users WHERE id = {{.ID}}`)
// 2. every later meeting is checked against that binding
var Total = sqlshape.One[int64, struct{ ID UserID }](`SELECT total FROM orders WHERE id = {{.ID}}`)
// sqlshape: parameter .ID is UserID, which stands for key users.id elsewhere, but here meets key orders.idThe same goes for a string type meeting an enum column or the key of a seeded lookup table (its constants are then compared with the labels), and for an integer type meeting a domain column (it then carries the unit). Which statement comes first does not matter: all bindings of a type are collected and must agree. A type that never meets such a column is never checked. The binding is exported as an analysis fact, so a type declared in one package and used in another is one type with one meaning.
Why this way: sqlshape generates nothing and has no registration API, so the Go code stays plain Go; binding by use means a type is checked exactly where it matters and never has to be announced.
For a named string type used with an enum column, the key of a seeded lookup table, or a column
with CHECK (col IN (...)), the typed constants are compared with the labels in both directions.
-- schema.sql
CREATE TABLE order_statuses (code text PRIMARY KEY, label text NOT NULL);
INSERT INTO order_statuses VALUES ('pending', 'Pending'), ('paid', 'Paid'), ('shipped', 'Shipped');
CREATE TABLE orders (..., status text NOT NULL REFERENCES order_statuses(code));Nothing ties OrderStatus to order_statuses by name. The type is bound to the value set where
it meets the column in a statement: here {{.Status}} feeds orders.status, whose foreign key
points at order_statuses(code), so OrderStatus is now the Go side of that lookup table. The
binding is exported as a fact and holds in every package that uses the type; a type that never
meets a column is never checked.
var ByStatus = sqlshape.Query[Order, struct{ Status OrderStatus }](`
SELECT id, total FROM orders WHERE status = {{.Status}}`)Rejected
type OrderStatus string
const (
Pending OrderStatus = "pending"
Paid OrderStatus = "paid"
Canceled OrderStatus = "canceled" // sqlshape: OrderStatus has constant "canceled" which is not a label of value set of order_statuses.code (lookup table)
)
// sqlshape: value set of order_statuses.code (lookup table) has label "shipped" but OrderStatus has no constant for itPasses
const (
Pending OrderStatus = "pending"
Paid OrderStatus = "paid"
Shipped OrderStatus = "shipped"
)A conversion such as OrderStatus("typo") is reported as
sqlshape: OrderStatus("typo") is not a label of ..., and a switch that does not handle every
label as sqlshape: switch on OrderStatus does not handle ... labels: shipped. If the type
implements Known() bool, the row mapper returns *UnknownLabelError at run time for a label this
build does not know. A seeded lookup table is the recommended home for a value set, over an enum
(migrations.md).
A named type used with a primary key column, or a column derived from one through a foreign key,
is treated as that table's ID. The binding comes from use, as with value sets: UserID below stands
for users.id because some statement passed it where users.id (or a foreign key to it) was expected.
type UserID int64
type OrderID int64
var User = sqlshape.One[User, struct{ ID UserID }](`SELECT id, email FROM users WHERE id = {{.ID}}`)Rejected
type Params struct {
ID UserID // sqlshape: parameter .ID is UserID, which stands for key users.id elsewhere, but here meets key orders.id
}SELECT total FROM orders WHERE id = {{.ID}}Passes
type Params struct {
ID OrderID
}A named type used with a domain column is bound to that domain. Inside SQL too, a domain is a unit distinct from its base type: PostgreSQL itself falls back to the base type and allows the operation, the checker reports it.
-- schema.sql
CREATE DOMAIN yen AS bigint;
CREATE DOMAIN gram AS integer;
CREATE TABLE products (id bigint PRIMARY KEY, price yen NOT NULL, weight gram NOT NULL);Rejected
SELECT id FROM products WHERE price + weight > 1000
-- ^ domain mismatch: yen + gram: operands must share the domain (cast to the base type to drop it)type Params struct {
Max int64 // sqlshape: parameter .Max carries domain yen as a plain int64; declare a named type to have it checked (-strict)
}Passes
type Yen int64
type Params struct {
Max Yen
}SELECT id FROM products WHERE price > {{.Max}}Literals and parameters take the unit of the other side: price + 100, price * 2, abs(price)
and coalesce(price, 0) stay yen. To drop the unit, cast to the base type explicitly
(price::bigint + weight::bigint).
type Event struct {
At time.Time // field At: timestamp without time zone into time.Time: which zone the value is in becomes the application's implicit choice (prefer timestamptz)
Day time.Time // field Day: date into time.Time: a zone conversion can move the day (keep it at UTC midnight or use a civil date type)
}timestamptz gets no such note. If a date is received as time.Time, decide in the code that it
is UTC midnight.
INSERT, UPDATE, DELETE and MERGE can fail on a constraint. For each expansion the checker lists the
constraints it can violate and verifies that the template declares them on a
-- sqlshape: expect line. At run time the violation comes back as a ConstraintError under the
declared name (runtime.md).
Rejected
INSERT INTO customers (email, name) VALUES ({{.Email}}, {{.Name}}) RETURNING id
-- ^ may violate customers_email_key (UNIQUE (email) on customers, SQLSTATE 23505); add `-- sqlshape: expect customers_email_key` to the template or make it impossiblePasses
-- sqlshape: expect customers_email_key
INSERT INTO customers (email, name) VALUES ({{.Email}}, {{.Name}}) RETURNING id_, err := postgres.First(ctx, db, CreateCustomer, p)
if postgres.Violates(err, "customers_email_key") { ... }Listed are unique constraints and primary keys, foreign keys in both directions (the inserted row
references a missing parent; the deleted row is still referenced by a child), EXCLUDE constraints,
CHECKs, domain CHECKs, and NOT NULL where the value written may be NULL. A NULL that would come from
a parameter is dropped when the field's Go type cannot be nil (a string, for instance). A CHECK or foreign key declared NOT ENFORCED (PostgreSQL 18) never raises and is not listed.
A DELETE or an UPDATE that changes a referenced key can also fail through what its foreign keys'
ON DELETE / ON UPDATE actions do to the referencing rows, not just through a foreign key that
rejects the change outright (NO ACTION / RESTRICT): SET NULL can hit a NOT NULL constraint on
the referencing column, SET DEFAULT can hit the same foreign key again (its default value need not
exist in the parent) and the same NOT NULL if there is no default, and CASCADE deletes (or updates)
the referencing rows, which is checked the same way one level further down -- so a chain of several
ON DELETE CASCADE foreign keys can still end in a failure several tables away.
A write through a view with WITH [LOCAL | CASCADED] CHECK OPTION can also fail with SQLSTATE
44000 (PostgreSQL's own error names no constraint here, so the SQLSTATE is the expect line's key,
the same way an unannotated trigger SQLSTATE is). CASCADED (the default when the option is given
without a qualifier) checks the WHERE clause of every updatable view further down the stack as
well, not just this one.
Rejected
-- sqlshape: expect orders_total_check
-- ^ expects orders_total_check but no expansion can violate it
UPDATE orders SET note = {{.Note}} WHERE id = {{.ID}}The expect line is kept as the exact list of reasons the statement can fail. Remove declarations that are no longer needed.
A failure mode is keyed by the constraint's name as the database names it, or table.column for
NOT NULL, so the diagnostic, the expect line and the run-time error all carry the same string.
The names an unnamed constraint gets, and the keys of the failures no constraint names (a
trigger's error, a view's WITH CHECK OPTION), are each database's:
PostgreSQL's, MySQL's.
The checker reads a PL/pgSQL trigger body, so a RAISE EXCEPTION ... USING ERRCODE = 'P0401'
inside it already adds P0401 to the failure modes (a RAISE without ERRCODE is P0001). A
-- sqlshape: error <code> = <Name> line above the trigger's function gives that code a name:
-- schema.sql
-- sqlshape: error P0401 = OrderTooLarge
CREATE FUNCTION check_order_size() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF NEW.total > 1000000 THEN RAISE EXCEPTION 'order too large' USING ERRCODE = 'P0401'; END IF;
RETURN NEW;
END $$;
CREATE TRIGGER order_size BEFORE INSERT OR UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION check_order_size();Name must be a Go identifier: the program mirrors it with sqlshape.Error, and reads the
failure back with it instead of the raw code:
var OrderTooLarge = sqlshape.Error("P0401")
if postgres.Violates(err, OrderTooLarge) { ... }An expect line may spell the failure by the code or by the Name, whichever reads better -- the two are interchangeable, and writing both leaves neither unmatched:
-- sqlshape: expect OrderTooLarge, orders_customer_id_fkey
INSERT INTO orders (customer_id, total) VALUES ({{.CustomerID}}, {{.Total}})The SQLSTATE joins the failure modes of the statements on the trigger's table for the events it fires on (here INSERT and UPDATE).
go vet checks the sqlshape.Error declaration both ways: that its code is one the schema
actually declares under that very Name, and that every named failure mode a package's
statements can raise has such a declaration somewhere in the program (its own, or another
package's that a reference reaches, the same reach a declared type's binding has).
Rejected
var Wrong = sqlshape.Error("P0401")
// sqlshape: schema names function check_order_size's error P0401 "OrderTooLarge", not "Wrong"var A = sqlshape.Error("P0401")
var B = sqlshape.Error("P0401")
// sqlshape: A and B both declare sqlshape.Error("P0401")var Ghost = sqlshape.Error("P9999")
// sqlshape: the schema declares no error "P9999"// no var declares P0401 anywhere the checker can reach from this package, though a
// statement's expect line names it
// sqlshape: P0401 (raised by trigger order_size on orders as OrderTooLarge, SQLSTATE P0401)
// has no `var OrderTooLarge = sqlshape.Error("P0401")` declared in this programNaming a code is optional: a schema with no -- sqlshape: error annotation is written and read
entirely by its codes, on both the expect line and Violates.
MySQL reads a trigger body the same way, and its own SIGNAL takes the annotation's place:
-- schema.sql
-- sqlshape: error 30001 = OrderTooLarge
CREATE TRIGGER order_size BEFORE INSERT ON orders FOR EACH ROW
BEGIN
IF NEW.total > 1000000 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'order too large', MYSQL_ERRNO = 30001;
END IF;
END;-- sqlshape: expect OrderTooLarge, fk_orders_customer
INSERT INTO orders (customer_id, total) VALUES ({{.CustomerID}}, {{.Total}})The key the expect line and mysql.Violates judge by is the one MySQL gives the SIGNAL itself --
the decimal MYSQL_ERRNO when it sets one (30001), else the SQLSTATE (45000) -- whether an
item spells it as that code or as the annotation's Name: neither Violates nor the runtime reads
the schema, so a sqlshape.Failure from sqlshape.Error("30001") still carries the code, and
judges by it. What the key is when the SIGNAL sets no MYSQL_ERRNO, and the numbers a SELECT ... INTO and a trigger writing its own table carry, are in mysql.md and mysql-errors.md.
A few PL/pgSQL statements can fail without a RAISE, and the checker adds their SQLSTATE to the
body's failure modes the same way it does for an explicit one:
SELECT ... INTO STRICTandEXECUTE ... INTO STRICTareP0002(no_data_found) when the query returns no rows, andP0003(too_many_rows) when it returns more than one;P0003is dropped when the query is provably at most one row (the same proofOneuses).- A
CASEstatement with no matchingWHENand noELSEis20000(case_not_found). - An
ASSERTisP0004(assert_failure) when its condition is false.
RAISE ... USING ERRCODE = <expr> resolves statically when <expr> is a string literal, a
variable declared with a literal default that is never reassigned, or, inside an EXCEPTION
handler, the bare SQLSTATE re-raising what the handler caught. Any other expression leaves the
SQLSTATE undetermined, which the checker reports rather than assuming P0001.
A BEGIN ... EXCEPTION WHEN ... END block catches whatever its WHEN conditions cover -- a
condition name, an error class name (matches every code of that class, so
integrity_constraint_violation catches any 23xxx), a literal SQLSTATE '...', or OTHERS --
so those failure modes do not reach the caller. Whatever the handler itself goes on to raise does.
A call to a user-defined function inherits the failure modes of its body (LANGUAGE sql or
plpgsql): the constraints its writes can violate, the triggers those writes fire, the functions
it calls, and the SQLSTATEs it raises.
SELECT place_order({{.CustomerID}}, {{.Note}})
-- ^ may violate orders_customer_id_fkey (FOREIGN KEY (customer_id) on orders REFERENCES customers, SQLSTATE 23503, through place_order()); add `-- sqlshape: expect orders_customer_id_fkey` to the template or make it impossibleA NOT NULL the body blames on a parameter is traced to the call's argument. A STRICT function is
not called with a NULL, so that argument's violation is dropped.
MySQL calls a stored FUNCTION or a CALLed PROCEDURE the same way: the body's SIGNALs, the
constraints its own writes can violate and the triggers those writes fire reach the calling
statement.
-- schema.sql
-- sqlshape: error 30001 = OrderTooLarge
-- sqlshape: not null
CREATE FUNCTION place_order(cust_id BIGINT UNSIGNED, amount DECIMAL(10,2)) RETURNS BIGINT
BEGIN
IF amount > 1000000 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'order too large', MYSQL_ERRNO = 30001;
END IF;
INSERT INTO orders (customer_id, total) VALUES (cust_id, amount);
RETURN LAST_INSERT_ID();
END;SELECT place_order({{.CustomerID}}, {{.Total}})
-- ^ may violate 30001 (raised by function place_order() as OrderTooLarge, MySQL error 30001);
-- add `-- sqlshape: expect OrderTooLarge` to the template or make it impossibleWhat is MySQL's about it -- a wrong argument count (1318), a function writing a table its own
caller reads or writes (1442, on every execution), CALL's own OUT argument and result-column
rules -- is in mysql-errors.md.
sqlshape.One[R, P] declares that the statement returns at most one row, and the checker proves it
from the schema for every expansion. If it cannot, that is an error. At run time Get returns
ErrNoRows when there is no row, Find reports presence, and both return ErrManyRows if the
database contradicts the proof with a second row.
Rejected
var ByName = sqlshape.One[User, struct{ Name string }](`
SELECT id, email FROM users WHERE name = {{.Name}}`)
// One: cannot prove at most one row: users: no unique key is fixed by equality (keys: (id), (email))Passes
var ByEmail = sqlshape.One[User, struct{ Email string }](`
SELECT id, email FROM users WHERE email = {{.Email}}`)A statement is single when, for every table in FROM, a unique key (primary key, UNIQUE, unique
index, or a partial unique index whose predicate the WHERE clause repeats) is fixed by equality to a
literal, a parameter, an outer reference or an uncorrelated scalar subquery. What counts as fixing:
col = NULLfixes nothing (it is never true);col IS NOT DISTINCT FROM vfixes aNOT NULLcolumn whenvis known and notNULL;col = ANY(ARRAY[v])with one element iscol = v;- a cast on the column (
GROUP BY status::text) is seen through; - equalities are followed through joins (an outer join's ON fixes only the nullable side), views, subqueries and CTEs.
Single on their own, with no key involved: an aggregate without GROUP BY, a constant LIMIT 0 /
LIMIT 1, a SELECT without FROM, a one-row VALUES and a one-row INSERT ... RETURNING.
Never single: a FULL JOIN, and a key declared DEFERRABLE: its uniqueness is not enforced until
commit, so a transaction can hold two rows sharing it for its own lifetime.
A temporal key (PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), PostgreSQL 18) is fixed when its
scalar columns are fixed by equality and its range column either equals a known value or contains a
known point of the element type (valid_at @> {{.Day}}::date): no two rows with the same id
have overlapping ranges, so one point lies in at most one of them. A range on the known side does
not do (it could be empty, which every range contains), nor does an overlap.
Rejected
var Find = sqlshape.One[User, struct{ ID *int64 }](`
SELECT id, email FROM users WHERE true {{if .ID}} AND id = {{.ID}} {{end}}`)
// One: cannot prove at most one row: users: no unique key is fixed by equality (keys: (id), (email)) [if@45:else]In the branch where .ID is nil the condition disappears and every row comes back. Finding that is
the point of the check: make this a Query, or make .ID a non-pointer and drop the branch.
postgres.Copy[R]("order_items", "order_id", "line_no", ...) is checked like an INSERT: the table
and columns exist, each column's type fits the field that feeds it, and every column left out has a
default or is generated.
Rejected
type Item struct {
OrderID int64
Sku string
}
var Load = postgres.Copy[Item]("order_items", "order_id", "sku")
// Copy into order_items: column "line_no" is NOT NULL without a default and is not copiedPasses
type Item struct {
OrderID int64
LineNo int16
Sku string
}
var Load = postgres.Copy[Item]("order_items", "order_id", "line_no", "sku")Rules that show up in the shape of the SQL can be enforced by the checker: always filter
soft-deleted rows, always pin the tenant column, read tables only through views, move a status
column only along its transitions. They are declared in schema.sql, above the table they are about,
and every statement that touches the table must satisfy them. None applies until declared.
Every directive schema.sql can carry, with where it goes. The sections below explain the
rules; the directives a template itself carries are in templates.md.
| directive | placed | meaning |
|---|---|---|
-- sqlshape: postgres 17 / postgres 18 |
the first line | the PostgreSQL version the schema is written for; statements are judged by its grammar and catalog (below) |
-- sqlshape: mysql 8.4 |
the first line | the MySQL version the schema is written for (below) |
-- sqlshape: server sql_mode = '...' / server lower_case_table_names = 1 |
after the mysql line |
MySQL only: a setting the server runs with when it is not the default (below) |
-- sqlshape: visible where deleted_at IS NULL |
above CREATE TABLE |
every statement reading the table must carry this predicate (short for require deleted_at IS NULL on read) |
-- sqlshape: require pinned(tenant_id) / require pinned(version) on update, delete |
above CREATE TABLE / CREATE VIEW |
an obligation on every statement that touches the relation: a predicate, pinned(col), immutable(col), via view, never, paired(table) or single, optionally on select, insert, update, delete (below) |
-- sqlshape: aggregate orders (order_items, order_notes) [lock version] |
above the root's CREATE TABLE |
the tables form one aggregate: children are pinned to the root's key, a statement touches one aggregate only, and with lock every write names the root's version |
-- sqlshape: transitions status: draft -> submitted, submitted -> paid | cancelled |
above CREATE TABLE |
the column is a state machine: an UPDATE setting it must fix the current state to a predecessor in its WHERE |
-- sqlshape: sensitive pii: email, phone |
above CREATE TABLE |
the columns carry the label; only a context with may read pii may reference them (through views too; a masking expression drops the label) |
-- sqlshape: context ops: waive pinned(tenant_id); require id = $1 on delete |
above CREATE TABLE / CREATE VIEW |
the obligations that differ under the named context, selected by a package's // sqlshape: context ops comment, vet's -context, or check -context |
-- sqlshape: unfiltered orders / waive orders pinned(tenant_id) |
above CREATE VIEW |
the view's own definition opts out, as a statement would |
-- sqlshape: not null |
above CREATE FUNCTION |
the function's result is never NULL |
-- sqlshape: error P0401 = OrderTooLarge |
above the CREATE FUNCTION (on MySQL also CREATE TRIGGER / PROCEDURE) that raises it |
names an error code, so expect lines and Violates can use the name (below); on PostgreSQL a PL/pgSQL body's RAISE statements are found without it, under their code |
-- sqlshape: seed |
above INSERT ... VALUES |
PostgreSQL only: the seed is additive, rows the declaration does not list stay (migrations.md) |
-- @migrate ... |
anywhere | a migration intent (migrations.md) |
A table (or view) declares a rule that every statement touching it must satisfy with what it
provably does; we call the rule an obligation, and satisfying it discharging the obligation. The general form is a directive above CREATE TABLE or
CREATE VIEW:
-- sqlshape: require <what> [on <kinds>]<what> |
the statement must | default on |
|---|---|---|
an SQL boolean expression (deleted_at IS NULL, status <> 'closed' AND amount > 0, EXISTS (SELECT 1 FROM orders o WHERE o.id = order_id AND o.tenant_id = $1)) |
carry it for the table's rows: each conjunct is implied by the WHERE / ON (equalities, IS NULL, IS NOT NULL) or appears verbatim; an EXISTS needs a witness (below) |
read |
pinned(tenant_id) |
fix the column to one value (= {{.X}}, a literal, an outer reference) when reading, updating or deleting; assign it when inserting. An UPDATE that assigns the column must still fix it in its WHERE (otherwise the row moves to another value) |
all |
immutable(tenant_id) |
not assign the column in an UPDATE | update |
via view |
not reference the table directly (a write target is allowed unless on includes writes) |
read |
never |
not exist: an append-only table (require never on update, delete) |
write |
paired(outbox) |
write the named table in the same statement too (a data-modifying WITH) |
insert |
single |
provably touch at most one row (the One proof) |
delete |
<kinds> is a comma-separated list of select, insert, update, delete, or the groups read
(SELECT, and the target of an UPDATE / DELETE / MERGE, whose WHERE reads it), write and all. A
statement is judged by what it does to the table: each branch of a MERGE is the write of its own kind
(an INSERT-only MERGE owes nothing on update), an INSERT ... ON CONFLICT DO UPDATE is an insert and
an update, TRUNCATE is a delete of every row, and a write through an automatically updatable view is
a write to the base table -- which also means an obligation declared on a view binds the view's
readers only: a write through the view is judged against the base table's obligations, never the
view's own, so a rule that must hold for the writes too belongs on the base table. A
$n in a declaration stands for any value known before the row is examined -- a parameter, a
literal, an outer reference -- not for that parameter number; a Go template's {{.X}} is such a
value.
Three declarations are not require lines but expand into obligations: visible where <expr> (the
original spelling of require <expr> on read), aggregate and transitions; sensitive labels
columns. The vet flags -require-columns=tenant_id, -no-table-reads and -no-tables are
shorthands for require pinned(tenant_id) on every table that has the column, require via view on
every table, and require via view on all; a context's waive lifts them as it lifts a declaration.
A directive belongs to the CREATE TABLE, CREATE VIEW, CREATE FUNCTION or seed INSERT directly
below it. One written above another statement (an ALTER TABLE, a COMMENT ON) is a schema problem,
not a declaration.
An obligation is discharged one of six ways, and -strict reports the ones that deserve a look:
- by the statement's own WHERE / ON / SET;
- by a view: a view's definition is judged on its own when the schema loads, and readers of the
view are not judged again for the tables inside it (a function body is judged the same way, a
trigger function's included: vet reports the failures as schema problems,
sqlshape checkas findings); - by a row-level security policy whose USING establishes it, for roles subject to row security
(
-strictnotes the owner caveat unless the table hasFORCE ROW LEVEL SECURITY); - across a composite foreign key: with
FOREIGN KEY (order_id, tenant_id) REFERENCES orders (id, tenant_id), a join onorder_id = orders.idwhereorders.tenant_idis pinned pinsorder_items.tenant_idtoo, providedorder_items.tenant_idisNOT NULL(or the statement proves it so): a NULL in any column of a foreign key exempts the row from the constraint on both databases, so such a row joins the parent while agreeing on nothing; - the write-side counterpart of 2: a write through an auto-updatable view declared
WITH CHECK OPTIONis pinned by whatever the view's own WHERE fixes (CASCADED also by every underlying view's, joined or not; LOCAL only by an underlying view that declares a check option of its own) -- the server refuses any row that would not satisfy it, sopinnedis discharged even where the statement's own WHERE never mentions the column (PostgreSQL's SQLSTATE 44000, MySQL's 1369ER_VIEW_CHECK_FAILED; a view with no CHECK OPTION at all never discharges this way); - by an opt-out in the statement:
-- sqlshape: unfiltered orders(the predicate obligations) or-- sqlshape: waive orders pinned(tenant_id)(one obligation, spelled as declared;waive ordersalone waives every obligation on the table). Opt-outs are reported with-strict.
Each occurrence of a table is judged on its own: a self-join or a subquery that reads the table
again owes the obligation again. RETURNING lists are not judged. An obligation declared on a view
binds the view's readers; the view's own definition answers for the tables inside it.
-- schema.sql
-- sqlshape: visible where deleted_at IS NULL
CREATE TABLE memos (...);Rejected
SELECT id, body FROM memos WHERE user_id = {{.UserID}}
-- rows of memos are visible where deleted_at IS NULL: add that predicate for memos, or opt out with `-- sqlshape: unfiltered memos`Passes
SELECT id, body FROM memos WHERE user_id = {{.UserID}} AND deleted_at IS NULL-- a statement that deliberately reads every row
-- sqlshape: unfiltered memos
SELECT id, body FROM memos WHERE id = {{.ID}}Views are checked by the same rule; reading through a view that carries the predicate satisfies it
for the view's readers. RETURNING columns are not checked: they are rows just written.
-- sqlshape: require pinned(tenant_id)
CREATE TABLE orders (...);Rejected
SELECT id, total FROM orders WHERE id = {{.ID}}
-- orders.tenant_id is not pinned: every statement on orders must fix tenant_id by equality (or assign it)Passes
SELECT id, total FROM orders WHERE id = {{.ID}} AND tenant_id = {{.TenantID}}An INSERT must assign tenant_id. A row-level security policy that fixes tenant_id satisfies the
rule too, but only for roles subject to row security: unless the table has
FORCE ROW LEVEL SECURITY, -strict notes
orders.tenant_id is pinned by policy ... for roles subject to row security, not for the table's owner: FORCE ROW LEVEL SECURITY if the application connects as the owner.
Optimistic locking is the same declaration on a version column, for writes only:
-- sqlshape: require pinned(version) on update, delete
CREATE TABLE orders (..., version int NOT NULL DEFAULT 1);
CREATE TRIGGER orders_bump_version BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION bump_version();UPDATE orders SET status = {{.Status}} WHERE id = {{.ID}} AND version = {{.Version}}
-- passes; without `AND version = ...`:
-- orders.version is not pinned: every statement on orders must fix version by equality (or assign it)The database increments version (the trigger); the statement must name the version it saw
(pinned); a One UPDATE that matched nothing returns ErrNoRows (someone got there first).
-- sqlshape: require via view
CREATE TABLE orders (...);Rejected (-no-table-reads)
SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id
-- table orders is read directly; with -no-table-reads application code reads views (tables are written, not read)Passes
SELECT id, customer_name FROM order_summary-no-table-reads still allows a table as the target of INSERT, UPDATE, DELETE and MERGE.
-no-tables forbids every table reference, writes included.
A predicate that reaches across tables is written as EXISTS, and the statement must have a
witness for it: a table of the same level joined so that the body holds (an inner join; an outer
join's ON does not restrict the subject's rows), or an unnegated EXISTS / IN (SELECT ...) of its
own whose body holds.
-- sqlshape: require EXISTS (SELECT 1 FROM orders o WHERE o.id = order_id AND o.tenant_id = $1)
CREATE TABLE shipments (...);Passes, all three
SELECT s.carrier FROM shipments s JOIN orders o ON o.id = s.order_id WHERE o.tenant_id = {{.T}}
SELECT carrier FROM shipments s WHERE EXISTS (SELECT 1 FROM orders x WHERE x.id = s.order_id AND x.tenant_id = {{.T}})
SELECT carrier FROM shipments WHERE order_id IN (SELECT id FROM orders WHERE tenant_id = {{.T}})Rejected: an outer join, a negated subquery, a join that never fixes the tenant
SELECT s.carrier FROM shipments s LEFT JOIN orders o ON o.id = s.order_id AND o.tenant_id = {{.T}}
SELECT carrier FROM shipments s WHERE NOT EXISTS (SELECT 1 FROM orders x WHERE x.id = s.order_id AND x.tenant_id = {{.T}})Some tables only make sense together. An order has its line items and its notes; nobody adds a line to an order without going through the order, and an invariant like "the total matches the lines" holds for the order as a whole, not for a line on its own. Domain-driven design calls such a cluster an aggregate: one table is the root, the others are its children, the outside world holds the root's ID and nothing else, and one change touches one aggregate. Applications enforce this by convention (a repository per aggregate, a service layer), which holds until someone writes the SQL directly. sqlshape can enforce the part of it that shows in the shape of a statement, and it is a bundle of obligations declared once above the root:
-- sqlshape: aggregate orders (order_items, order_notes)
CREATE TABLE orders (...);It expands to require pinned(<foreign key to orders>) on all on each child (a child is reached
through its root: pin the key, or join on the root's key) and to alone on every table of the
aggregate: one statement touches one aggregate. Reading across aggregates is what a view is for;
tables in no aggregate (lookups) are free to join. A child must have a foreign key to the root, or to
another member: a grandchild pins its key to the parent it hangs off, and a join up the chain
discharges it link by link.
Rejected
SELECT o.status, v.id FROM orders o JOIN invoices v ON v.order_id = o.id WHERE o.id = {{.ID}}
-- orders belongs to aggregate orders and this statement also touches invoices of aggregate invoices: one statement, one aggregate (read across aggregates through a view)lock <column> makes the root's version the lock of the whole aggregate:
-- sqlshape: aggregate orders (order_items, order_item_tags) lock versionThe root owes pinned(version) on update, delete (name the version you saw), and every child owes,
on UPDATE / DELETE, an EXISTS witnessing the root row at that version through its foreign key
(through its parent's, for a grandchild):
UPDATE order_items i SET qty = {{.Q}} FROM orders o WHERE o.id = i.order_id AND o.version = {{.V}} AND i.id = {{.ID}}Incrementing the version stays the database's job (a trigger on the root, fired by the children's
triggers if they must bump it); a One write that matched nothing returns ErrNoRows.
-- sqlshape: transitions status: draft -> submitted, submitted -> paid | cancelled, paid -> refunded
CREATE TABLE orders (...);A status column is a small state machine: an order goes from draft to submitted, then to paid or
cancelled, never from draft straight to paid and never back. The declaration writes the machine down.
The check is that an UPDATE setting the column to a state fixes the current state in its WHERE to
one of that state's predecessors (status = 'submitted', or status IN ('shipped', 'delivered') when
every listed state is a predecessor). This is compare-and-set: if two requests both try to mark the same
order paid, the second one's WHERE status = 'submitted' no longer matches and it updates nothing,
instead of silently paying twice. A One UPDATE reports that as ErrNoRows.
Passes
UPDATE orders SET status = 'paid' WHERE id = {{.ID}} AND status = 'submitted'Rejected
UPDATE orders SET status = 'paid' WHERE id = {{.ID}}
-- orders.status: SET status = 'paid' must fix the current state in WHERE (status = 'submitted')
UPDATE orders SET status = 'paid' WHERE id = {{.ID}} AND status = 'draft'
-- orders.status: draft -> paid is not a declared transition (paid comes from submitted)The target must be a literal naming a declared state; SET status = {{.S}} is refused (no predecessor
set can be checked for it). An INSERT is not a transition and is not checked; its ON CONFLICT DO UPDATE SET status = ... is an update and is. The declaration is the
same shape as a seeded (from_status, to_status) lookup table, which a trigger can enforce on the
database side.
-- sqlshape: require never on update, delete
CREATE TABLE ledger (...);
-- sqlshape: require paired(outbox) on insert
-- sqlshape: require single on delete
CREATE TABLE orders (...);never says the statement must not exist: an UPDATE or DELETE on ledger is rejected
(ledger is declared \require never on update, delete`: no statement may do this to it), inside a WITHas much as on its own, as anON CONFLICT DO UPDATE, as a TRUNCATE`, or through an
updatable view.
paired(outbox) is for the outbox pattern. When a write must also notify the outside world (a
message queue, a webhook, another service), sending the notification directly risks the two getting
out of step: the row is written but the message is lost, or the message goes out and the write
rolls back. The outbox pattern writes the message into a table in the same transaction as the
change, and a separate process delivers it from there. paired makes the pairing a rule: an INSERT
into orders must write outbox in the same statement, so the two cannot be separated even by a
forgotten line, and no transaction layer is needed:
WITH o AS (INSERT INTO orders (...) VALUES (...) RETURNING id)
INSERT INTO outbox (id, payload) SELECT id, 'created' FROM o
-- passes; a bare INSERT INTO orders:
-- a write to orders must also write outbox in the same statement (a data-modifying WITH): require paired(outbox) on insertThe rule is that the statement writes both tables; what it writes into outbox is not compared
with the orders row (an outbox row carrying unrelated values passes). The pairing is the promise,
the payload is the statement's.
single says a DELETE must provably touch at most one row, by the same proof as One:
DELETE FROM orders WHERE id = {{.ID}} -- passes
DELETE FROM orders WHERE status = 'cancelled'
-- orders requires a single-row DELETE: fix a unique key by equality (the One proof)-- sqlshape: sensitive pii: email, phone
CREATE TABLE orders (...);
-- sqlshape: context billing: may read pii
CREATE VIEW order_contacts AS SELECT id, email, left(phone, 3) || '***' AS phone_masked FROM orders;A statement may reference a labelled column -- in its SELECT list or in a WHERE alike -- only in a
context that may read the label (contexts); storing a
value into it is not reading it, but RETURNING it is (the value leaves the database, whoever
supplied it). The label follows a column through a view that passes it through
and through a function returning the table's rows (RETURNS SETOF orders),
and stops at an expression (a masked column).
SELECT email FROM orders WHERE id = {{.ID}} -- fails outside billing
-- orders.email is pii: this context may not read it (a context with `may read pii`, or a view that masks it)
SELECT email FROM order_contacts WHERE id = {{.ID}} -- fails too: the view passes email through
SELECT phone_masked FROM order_contacts -- passes: an expression carries no labelAn operator's script may run without a tenant, an analyst may only read views, billing may read
personal data. A context declares the difference per table, and a package or a check run selects
one:
-- sqlshape: require pinned(tenant_id)
-- sqlshape: context ops: waive pinned(tenant_id); require id = $1 on delete
-- sqlshape: context analyst: require via view
-- sqlshape: context billing: may read pii
CREATE TABLE orders (...);// Package ops runs the operator's scripts.
//
// sqlshape: context ops
package opswaive <body> [on <kinds>] lifts a base obligation (spelled as declared) inside the context, for the
kinds named or for all of them (waive pinned(tenant_id) on select leaves the writes bound);
require ... adds one that holds there only; may read <label> permits a label. A package names its
context in its package comment, on a line of its own; otherwise vet's -context flag applies;
sqlshape check -context ops selects one for a file. A context name the schema does not declare, or
a sqlshape: context line that does not read as the directive, is reported. Without a context, the
base obligations alone apply.
The same judgment is available for SQL that is not in Go code: an operator's UPDATE, a backfill, a query an agent is about to run.
$ sqlshape check -schema schema.sql ops.sql # or: ... < ops.sql
ops.sql:2: ok orders: require pinned(tenant_id)
ops.sql:6: waived orders: require pinned(tenant_id): orders: `require pinned(tenant_id)` is waived by this statement
ops.sql:8: FAIL orders: visible where deleted_at IS NULL: rows of orders are visible where ...
sqlshape: 2 finding(s)
Every judgment is printed, with the path that discharged it (ok, ok(policy), ok(fk),
waived), so the output doubles as the audit of what a script was allowed to do; -quiet prints
failures only. The exit code is 1 when a statement fails an obligation or does not analyze (a
statement that does not parse fails on its own line; the others are still judged). The schema's own
function and view bodies are judged first, as vet judges them. The -- sqlshape: lines above a
statement belong to it, and -context, -require-columns, -no-tables and -no-table-reads are
accepted as in vet.
schema.sql declares, once, the PostgreSQL major version it is written for. The declaration
decides how everything else is judged: the grammar that parses the schema and every statement, the
catalog of types, functions and operators they resolve against, and the PostgreSQL the migration
commands boot. A schema without it is not read. What is PostgreSQL's in the rules (the
Go type table, the constraint names, the One proof's materials, the runtime, the migration
commands) is gathered in postgres.md.
-- sqlshape: postgres 18
CREATE TABLE ...Rejected
- no declaration anywhere in
schema.sql(or in the directory's*.sqlfiles) - a version sqlshape does not embed (the message lists the supported ones: 17 and 18)
- two declarations that disagree
OK
- the declaration anywhere in the file, repeated with the same value
A statement that only a newer version accepts (RETURNING old.*, WITHOUT OVERLAPS, NOT ENFORCED, VIRTUAL generated columns are 18's) is a syntax error under an older declaration, as
it is on that server. Moving to a new PostgreSQL is changing the number and reading what the
checker reports.
The same declaration can name MySQL instead. Then MySQL's own grammar parses the schema and every
statement, MySQL's rules type the expressions, and the statements run through
github.com/kr9ly/sqlshape/mysql/v2 on database/sql.
-- sqlshape: mysql 8.4
CREATE TABLE ...Rejected
- a version sqlshape does not embed (8.4 is the one embedded)
- two declarations that disagree, or one that also names
postgres
Every rule in Part 1 and Part 2 applies to a MySQL schema the same way, judged by the MySQL
analyzer instead of the PostgreSQL one. Where the rules use a name, a number or a type
(constraint names and error numbers, the Go type table, the materials of the One proof, the
ONLY_FULL_GROUP_BY check), MySQL's are in mysql.md, together with what does not
exist on MySQL and is not checked there.
A server variable that changes how a statement is judged is declared next to the version, one per
line, so the checker and the production connection agree on it. Without the line
the checker assumes the defaults of a freshly initialized MySQL 8.4 server on Linux, not those of
the server you connect to: the default sql_mode
(ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION)
and lower_case_table_names = 0.
-- sqlshape: mysql 8.4
-- sqlshape: server sql_mode = 'ANSI,STRICT_ALL_TABLES'
-- sqlshape: server lower_case_table_names = 1
CREATE TABLE ...Rejected
- a variable the dialect does not read: for MySQL anything but
sql_mode,lower_case_table_namesandmax_sp_recursion_depth; for PostgreSQL every variable, for now (its judgments follow the server's defaults, andsearch_pathis the schema's ownSET) - a
sql_modename 8.4 does not have, alower_case_table_namesother than 0, 1 or 2 - a value with spaces not written as a string literal, a variable declared twice
Accepted
sql_mode: the names in any case, separated by commas, the empty string, and the combination modesANSIandTRADITIONAL, expanded as the server expands themlower_case_table_names: 0, 1 or 2
What the checker does with them:
- The parser takes the lexer's bits: under
ANSI_QUOTESa double-quoted name is an identifier, underPIPES_AS_CONCATthe||isCONCAT(anORotherwise), andIGNORE_SPACE,NO_BACKSLASH_ESCAPESandHIGH_NOT_PRECEDENCEread as they do on the server. UnderREAL_AS_FLOATaREALcolumn is aFLOAT(float32), not aDOUBLE. ONLY_FULL_GROUP_BYturns the group check above on and off (1055, 1140 and theDISTINCTrule 3065); theHAVINGresolution rule (1054) and the aggregate-in-ORDER BYrules (3029, 3028) hold in every mode, as they do on the server.- Strict mode (
STRICT_TRANS_TABLESorSTRICT_ALL_TABLES) decides two things. A string function (CONCAT,SUBSTRING,LOWER, ...) is nullable only in strict mode, so without itCONCAT(name, 'x')over aNOT NULLcolumn isstring, not*string. And aNULLstored into aNOT NULLcolumn is a failure mode (1048) in strict mode; without it only a single-rowINSERTorREPLACE(itsON DUPLICATE KEY UPDATEincluded) still rejects theNULL, while more rows,INSERT ... SELECTandUPDATEstore the type's implicit default with a warning, so the checker lists no 1048 for them. NO_UNSIGNED_SUBTRACTIONmakes the difference of unsigned operands signed (int64, notuint64).- The remaining names (
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH, ...) act at run time only; the checker accepts them as written. lower_case_table_names = 1stores table and view names lower-cased, as the server reports them (SELECT * FROM Usersreads the tableusers, and so do the facts and the boundary checks);2keeps the declared spelling and compares without case;0distinguishesUsersfromusers(1146). A directive (unfiltered,waive, the obligations) names a table by the same rule: under 1 or 2 any spelling reaches it, under 0 theCREATE's.
The declaration is a promise about the server the statements run on, and mysql.Verify(ctx, db, schemaSQL) checks it: it reads the connection's session @@sql_mode (a DSN or a pool's setup
may override it) and the server's lower_case_table_names, and reports a difference from the
declaration; a mode name 8.4 does not define is left out of the comparison (mysql.md).
Calling pgx's or database/sql's Query / Exec with a string built at run time opens a hole the
template guarantee does not cover.
Rejected (the default, -raw-sql=constant)
rows, err := pool.Query(ctx, "SELECT id FROM orders WHERE "+where)
// sqlshape: SQL passed to Query must be a constant: a string built at run time can carry injected SQL; write the dynamic parts as a sqlshape.Query template (or pass -raw-sql=allow)Passes
rows, err := pool.Query(ctx, "SELECT id FROM orders WHERE status = $1", status)-raw-sql=forbid rejects every statement that does not go through sqlshape, constant or not
(pgx.Query executes SQL outside sqlshape; with -raw-sql=forbid every statement goes through sqlshape.Query / One / postgres.Copy (or list the package in -raw-sql-allow)).
Packages still migrating are exempted with -raw-sql-allow=pkg/....
-schemas=a_api,b_private restricts the PostgreSQL schemas a package may reference: a service
boundary over one database.
SELECT id FROM c_private.orders
-- c_private.orders is outside the schemas this code may reference (a_api,b_private)schema.sql is analyzed once when loaded. The bodies of LANGUAGE sql and LANGUAGE plpgsql
functions, views and policies get the same type check PostgreSQL performs at CREATE time, and seed
INSERTs are checked for idempotency and types. Problems are reported at the first Query of the
package as sqlshape: schema ....
A PL/pgSQL body is checked statement by statement with its variables in scope: DECLAREd
variables by their type (%TYPE and %ROWTYPE resolved against the schema), record variables by
the shape of the query that filled them (FOR r IN SELECT ..., SELECT ... INTO r), NEW and
OLD in a trigger function by the row type of each table a CREATE TRIGGER attaches it to,
function parameters, FOUND, TG_OP and the other trigger variables, SQLSTATE and SQLERRM
in an exception handler. Assignments and RETURN are checked against the declared types,
RETURN QUERY against RETURNS TABLE, and a name that is both a variable and a column is
ambiguous, as in PostgreSQL. EXECUTE of a constant string is checked like the statement it runs;
a string built at run time cannot be, and -strict says so:
CREATE FUNCTION purge(tbl text) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
EXECUTE 'DELETE FROM ' || quote_ident(tbl);
-- sqlshape: schema: function purge: line 3: EXECUTE runs SQL built at run time, which is not checked (a constant string would be)
END $$;-- schema.sql
CREATE VIEW order_summary AS
SELECT o.id, c.nmae AS customer_name FROM orders o JOIN customers c ON c.id = o.customer_id;
-- sqlshape: schema schema.sql: view order_summary: 42703: column c.nmae does not exist (at <byte offset in schema.sql>)A MySQL trigger or stored PROCEDURE/FUNCTION body is checked the same way, once per schema:
NEW/OLD, DECLAREd variables and parameters, control flow, SELECT ... INTO, cursors,
CALL and SIGNAL/RESIGNAL are all in scope; what the server itself refuses when the body is
created is reported as a schema problem too. What is MySQL's about the body -- the constraint
names and error numbers, the SIGNAL key rules -- is in mysql-errors.md and mysql.md.
Row-level security policies are checked here too: a CREATE POLICY predicate must be boolean,
contain no aggregates or window functions, and respect domain units. A policy on a table whose row
security is off is reported. The checker does not ask statements to repeat a policy's predicate; the
database does the filtering.
-strict adds advisory findings about the schema and the statements, listed in
flags.md.