Skip to content

Latest commit

 

History

History
1572 lines (1218 loc) · 67.9 KB

checks.md

File metadata and controls

1572 lines (1218 loc) · 67.9 KB

What the checker verifies

日本語

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.

Contents

Part 1 — Checks on every statement

Receiving a SELECT

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.

Result columns bind to fields by name

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 Order

Passes: 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_id
type Order struct {
	ID           int64
	CustomerName string `col:"name"`
}

Unnamed and duplicate columns need an alias

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_id

A column that may be NULL needs a field that can hold NULL

Fields 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 users

Passes

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.

A column only some branches select needs a field that can hold NULL

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 orders

Passes

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.

Column and field types follow the table below

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 orders

Passes, silently

type Order struct {
	ID    int64
	Total string // keeps every digit; decimal.Decimal (shopspring/decimal) works too
}

Nested rows are received by structs

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.)

A single-column statement can be received by a scalar

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 columns

Embedded structs are flattened

Passes

type Base struct {
	ID        int64
	CreatedAt time.Time
}
type Order struct {
	Base
	Total string
}
SELECT id, created_at, total FROM orders

Rejected: 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 type table

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).

Passing parameters

{{.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.

A parameter's type follows where it is used

Rejected

type Params struct {
	ID int64 // parameter .ID is int64 but SQL expects uuid
}
SELECT id, email FROM users WHERE id = {{.ID}}   -- id is uuid

Passes

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 parameter that may be NULL is a pointer

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.)

Nested paths and range

{{.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}}).

Composite parameters are structs

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}})

Do not leave unused fields in P (-strict)

type Params struct {
	ID    int64
	Limit int32 // sqlshape: parameter field Limit is never used by the template
}
SELECT id FROM orders WHERE id = {{.ID}}

Do not always send a value into a column with a DEFAULT (-strict)

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.)

Giving types a meaning

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.

Meaning comes from use, not from a registry

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.id

The 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.

Enum and lookup values agree with the named type's constants

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 it

Passes

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).

Do not pass another table's ID

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
}

Do not mix domains of different units (PostgreSQL)

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).

Do not receive with a type that loses information (-strict)

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.

Preparing for a write to fail

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).

Declare the constraints a write can violate

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 impossible

Passes

-- 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.

Do not declare a violation that cannot happen

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.

Constraint names

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.

Name the errors a trigger raises

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 program

Naming 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.

PL/pgSQL statements that fail on their own (PostgreSQL)

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 STRICT and EXECUTE ... INTO STRICT are P0002 (no_data_found) when the query returns no rows, and P0003 (too_many_rows) when it returns more than one; P0003 is dropped when the query is provably at most one row (the same proof One uses).
  • A CASE statement with no matching WHEN and no ELSE is 20000 (case_not_found).
  • An ASSERT is P0004 (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 statement that calls a function declares the function's failure modes too

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 impossible

A 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 impossible

What 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.

Returning one row (One)

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.

Fix a unique key by equality

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 = NULL fixes nothing (it is never true);
  • col IS NOT DISTINCT FROM v fixes a NOT NULL column when v is known and not NULL;
  • col = ANY(ARRAY[v]) with one element is col = 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.

Every branch must be provable

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.

Bulk loading with COPY (PostgreSQL)

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 copied

Passes

type Item struct {
	OrderID int64
	LineNo  int16
	Sku     string
}
var Load = postgres.Copy[Item]("order_items", "order_id", "line_no", "sku")

Part 2 — Rules the schema declares

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.

Directives in schema.sql

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)

How a declaration works

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:

  1. by the statement's own WHERE / ON / SET;
  2. 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 check as findings);
  3. by a row-level security policy whose USING establishes it, for roles subject to row security (-strict notes the owner caveat unless the table has FORCE ROW LEVEL SECURITY);
  4. across a composite foreign key: with FOREIGN KEY (order_id, tenant_id) REFERENCES orders (id, tenant_id), a join on order_id = orders.id where orders.tenant_id is pinned pins order_items.tenant_id too, provided order_items.tenant_id is NOT 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;
  5. the write-side counterpart of 2: a write through an auto-updatable view declared WITH CHECK OPTION is 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, so pinned is discharged even where the statement's own WHERE never mentions the column (PostgreSQL's SQLSTATE 44000, MySQL's 1369 ER_VIEW_CHECK_FAILED; a view with no CHECK OPTION at all never discharges this way);
  6. 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 orders alone 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.

Every read carries the visibility predicate (visible where)

-- 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.

A column is pinned on every statement (pinned, -require-columns)

-- 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).

Tables are read through views (via view, -no-table-reads / -no-tables)

-- 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 across tables has a witness (EXISTS)

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}})

An aggregate is reached through its root, one per statement (aggregate)

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 version

The 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.

A status column moves along its declared transitions (transitions)

-- 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.

Append-only tables, paired writes, single-row deletes (never, paired, single)

-- 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 insert

The 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)

Labelled columns are read only where allowed (sensitive, may read)

-- 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 label

Different callers, different rules (context)

An 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 ops

waive <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 rules for SQL outside Go (sqlshape check)

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.

Part 3 — Outside the statement

The schema names its PostgreSQL version (postgres)

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 *.sql files)
  • 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 schema names its MySQL version (mysql)

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.

The schema names the server's settings (server)

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_names and max_sp_recursion_depth; for PostgreSQL every variable, for now (its judgments follow the server's defaults, and search_path is the schema's own SET)
  • a sql_mode name 8.4 does not have, a lower_case_table_names other 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 modes ANSI and TRADITIONAL, expanded as the server expands them
  • lower_case_table_names: 0, 1 or 2

What the checker does with them:

  • The parser takes the lexer's bits: under ANSI_QUOTES a double-quoted name is an identifier, under PIPES_AS_CONCAT the || is CONCAT (an OR otherwise), and IGNORE_SPACE, NO_BACKSLASH_ESCAPES and HIGH_NOT_PRECEDENCE read as they do on the server. Under REAL_AS_FLOAT a REAL column is a FLOAT (float32), not a DOUBLE.
  • ONLY_FULL_GROUP_BY turns the group check above on and off (1055, 1140 and the DISTINCT rule 3065); the HAVING resolution rule (1054) and the aggregate-in-ORDER BY rules (3029, 3028) hold in every mode, as they do on the server.
  • Strict mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES) decides two things. A string function (CONCAT, SUBSTRING, LOWER, ...) is nullable only in strict mode, so without it CONCAT(name, 'x') over a NOT NULL column is string, not *string. And a NULL stored into a NOT NULL column is a failure mode (1048) in strict mode; without it only a single-row INSERT or REPLACE (its ON DUPLICATE KEY UPDATE included) still rejects the NULL, while more rows, INSERT ... SELECT and UPDATE store the type's implicit default with a warning, so the checker lists no 1048 for them.
  • NO_UNSIGNED_SUBTRACTION makes the difference of unsigned operands signed (int64, not uint64).
  • 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 = 1 stores table and view names lower-cased, as the server reports them (SELECT * FROM Users reads the table users, and so do the facts and the boundary checks); 2 keeps the declared spelling and compares without case; 0 distinguishes Users from users (1146). A directive (unfiltered, waive, the obligations) names a table by the same rule: under 1 or 2 any spelling reaches it, under 0 the CREATE'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).

Do not run SQL that bypasses sqlshape (-raw-sql)

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/....

A package references only its schemas (-schemas, PostgreSQL)

-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)

Problems in the schema itself

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.