Skip to content

std/db ​

From the spec

This page is spec/std/db.md, the specification the compiler and the backends are written from. It is rebuilt from the spec with every deploy (how).

The database module (docs/plan.md, Data): the schema written in Weft, three levels of typed queries (objects, queries read like lists, SQL), policies applied to every query of a scoped handle, migrations, on SQLite, Postgres, MySQL and Skein behind one API. std/db/admin is the one handle around the policies. The module's signatures are core/crates/weft-check/src/std/db.weft; what they can't say is typed by the front end (weft-check's db.rs, db_sql.rs; hir.md, std/db); the host is weft-db. An example project: examples/todo-db.

The schema ​

weft
import { table, schema, primary, references, index, immutable, default, unique, owned, flag, public } from "std/db"

export type User = { @primary id: string, name: string, role: string }
export type Todo = {
  @primary @default(uuid) id: string,
  @references(users.id) @index @immutable owner: string,
  title: string,
  @index done: boolean,
}
export let users = table<User, User>("users", { read: public(), update: owned("id") })
export let todos = table<Todo, User>("todos", { read: owned("owner"), write: owned("owner") })
export let app = schema({ tables: { users, todos } })
  • table<T, P>(name, policies = {}) is a Table<T, P, name>: rows of the record type T, policies about principals of type P, named name in SQL. Both type arguments are written; name is a string written there (db-schema, E2024, otherwise). A table has a key (@primary on one field, or on several: a composite key).
  • Columns are T's fields in order. A column's type is a string, number, boolean, Bytes or json, a literal union of one of them, or a validating type over one, maybe null (? fields are nullable); anything else is E2024 (store structured values as json). Its kind decides its SQL type (weft-db's Dialect::column_type): numbers are doubles, except keys and the columns referencing a numeric key, which are integers; a single numeric key without a default is numbered by the database (auto).
  • Attributes (declared by std/db): @primary; @default(c) where c is a constant, uuid (std/random) or now (std/time) (the host fills them in at insert; now is the host's clock, its epochMs); @references(t.c), t a table declared before this one; @index; @unique; @immutable (no update changes it).
  • t.c on a table is a column reference, a Column<C> (its value is the string "t.c").
  • schema({ tables: { users, todos } }) is a Schema<P, S>: S the record of tables, P the one principal type of its tables (two differ: E2024). Every reference names a table of the schema; table names are unique.
  • Relations, by name: a column referencing another table gives this table a relation to one row, named by the column without an Id/_id suffix (ownerId → owner), or else by the other table's key in tables; and gives the other table a relation to many rows, named by this table's key (users.todos), when one column of this table references it. A relation named like a column, or another relation, is E2024.
  • Descriptors: table(…) and schema(…) get the front end's JSON description as their last argument, { tables: [{ key, name, columns: [{ name, type, nullable, primary, auto, default, references, index, unique, immutable }] }] } (weft_db::model); weft db migrate diffs the same (weft_check::db::schemas).

Connections and handles ​

  • sqlite(schema, path) (a file, or ":memory:"), postgres(schema, url, { pool }), mysql(schema, url, { pool }), skein(schema, path, { pool, durability, maxSize }) (a directory, or ":memory:") give a Task<Database<P, S> ! DbError>: a pool (Postgres, MySQL and Skein: pool connections, 10 by default; SQLite: one). Skein's durability is "full" (the default: a commit is answered once it is on disk) or "relaxed" (answered at once, on disk within about 10 ms; a crash can lose the last moments, never corrupt); maxSize is the most it may grow to, in bytes (64 GiB by default; that much address space is reserved while it is open). A bad URL, or a database that can't be reached, fails here with Connection.
  • db.as(user) (scoped) is a Db<P, S, "user">, the handle queries go through, under the policies for user; db.as(null) is no one signed in. It is for actions and scripts, which say whom they run for: in a query fn or mutation fn the pool itself is the caller's handle (Server functions, below). admin(db) from std/db/admin is a Db<P, S, "admin">, around them.
  • A handle's principal and rights are the host's, behind an opaque handle: copying or rebuilding the record changes neither. Handles are interned by (pool, principal, kind, transaction).
  • push(db): Task<void ! DbError>: the schema applied directly (development, tests: weft db push's work). close(db).
  • h.transaction(async tx => …): tx is the same handle in a transaction, committed when the body returns and rolled back when it fails (the transaction then fails with the body's error) or panics (the panic goes on). Its type is transaction<P, S, K, R, E>(db: Db<P, S, K>, body: escaping async (tx: Db<P, S, K>) => R ! E): Task<R ! E | DbError>: the body's error type plus DbError (BEGIN or COMMIT failing). A transaction inside one runs in it (a savepoint). A handle of a transaction used after it ended panics.
  • Methods of std/db's types are the module's functions, imported or not (q.filter(f) is filter(q, f); as is scoped). Every query is effectful (not in derived values or markup).

Queries ​

Rows come back as the call's type, converted as as converts json (validating types included; a row that doesn't check fails with Invalid); the host converts SQL's values by the column's kind (SQLite's and MySQL's 0/1 booleans, Postgres's NUMERIC). Every query's task can fail with a DbError:

weft
export type DbError =
  | Denied { table: string, access: string }          // a policy refused it: "read", "insert", "update", "delete"
  | Conflict { constraint: string, message: string }  // a primary key, @unique or @references refused a write
  | Invalid { path: string, expected: string }        // a row that doesn't check against its type (CastError's fields)
  | Connection { message: string }                    // no connection: a bad URL, unreachable, lost, or 30 s waiting for one
  | Failed { code: string, message: string }          // anything else the database said (`code`: its SQLSTATE or error code)

constraint is the constraint's or index's name (users_email_key, todos_owner_fkey), or its columns (users.email) where the database names none (SQLite). A query the schema can't run (a table the database lacks that the checker couldn't see) is a Failed. Misuse is a panic: a transaction's handle after it ended, a second statement on a transaction's connection while one runs.

Level 1, object queries, on a handle h:

h.query.t(filter, options?)Task<R[] ! DbError>; options: { orderBy?: column, desc?, limit?, offset? }, ordered by the key otherwise; with no arguments, h.query.t() is level 2's Query<T> (awaited, the same rows)
h.query.t.first(filter?, options?)Task<R? ! DbError>
h.insert.t(row), h.insert.t([rows])Task<T ! DbError>, Task<T[] ! DbError>: the rows as stored (fields with a default, and a numbered key, may be left out)
h.update.t(filter, set)Task<T[] ! DbError>: the rows changed, as they are now; set gives the fields it writes (null writes null)
h.delete.t(filter)Task<T[] ! DbError>: the rows deleted
  • A filter is a record of columns: a value (equality; on a nullable column null-safe), or an operator: gt gte lt lte, like(p) (% any run, _ one character, \ escaping either; case-sensitive on every dialect), oneOf(xs) (none: no row), isNull(), not(x) (of a value or an operator). A field left out, or null, filters nothing (isNull() is the test). json and Bytes columns aren't filtered here.
  • A relation's name with true includes it; a key of its own with a join, todos(todos.owner, users.id, { done: false }) (a table called: their column, then ours, then its own filter), includes that. R is T, plus one field per relation asked: U[] for many, U? for one (a row the policies hide is null), U[] for a join. Related rows go through their table's read policy.
  • t names a table of the schema, a filter's fields its columns, set no key and no @immutable column (db-query, E2026).

Level 2, queries read like lists: h.query.t() (no arguments) is a Query<T>, the table's rows, lazy. Its methods give queries and nothing runs until it is awaited; then the chain is one statement:

weft
let open = db.query.todos().filter(t => !t.done && t.title.startsWith(q)).sortBy(t => t.created, { desc: true })
let page = try await open.skip(20).take(20)       // Todo[]: one SELECT … WHERE … ORDER BY … LIMIT 20 OFFSET 20
let n = try await open.count()                    // number
let titles = try await open.map(t => t.title)     // string[]
methodgives
filter(t => …)Query<T>the rows where it's true (several are &&)
map(t => …)Query<U>a record of the row's fields and expressions (t => ({ id: t.id, late: t.due < now })), or one value
sortBy(t => …, { desc?, binary? })Query<T>several: then by
take(n), skip(n)Query<T>the first n; all but the first n
await q, q.all()Task<T[] ! DbError>the rows
first(), find(t => …)Task<T? ! DbError>the first row (where it's true), or null
count(t => …?)Task<number ! DbError>how many (where it's true)
some(t => …), every(t => …)Task<boolean ! DbError>a row where it's true; no row where it isn't (null counts as false)
  • A query is a value: Query<T> (import type { Query } from "std/db") is passed and returned like any value (fn open(q: Query<Todo>): Query<Todo> { return q.filter(t => !t.done) }); it holds its handle, so it runs under that handle's policies (and in its transaction).
  • It means what the list would: each method applies to the rows the chain gives so far, in order. A filter or sortBy after take/skip reads the rows the cut gave (a derived table, (SELECT … LIMIT n) AS q, keeping their order); map before a filter or sortBy is read through (`map(t => ({ twice: t.rank * 2 })).filter(r => r.twice

    6)filters onq.rank * 2); skipaftertakecuts from the taken rows. Rows come ordered by thesortBy`s (the last one's cut first), then by the key, so every dialect gives one order.

  • Closures become SQL: each method's closure is a query closure (hir.md, Query trees; QueryFn<F> in std/db's signatures is the prelude's Query<F>), translated keeping Weft's meaning (below). On SQLite, Postgres and MySQL a closure that doesn't translate (a call of a function, a field of a column, string +, a record outside map, the whole row) is an error at that expression (db-query, E2026) with a fix-it: fetch the rows with .all(), then use the list's own methods, which run in the app over every row ((try await q.all()).filter(t => isUrgent(t))).
  • The same SQL: a chain is the statement written by hand: db.as(anna).query.todos().filter(t => !t.done).sortBy(t => t.rank, { desc: true }).take(20) is SELECT q.id, … FROM todos AS q WHERE (q.owner = ?) AND (NOT q.done) ORDER BY q.rank DESC, q.id LIMIT 20 (the read policy first; weft-db's tests/chains.rs holds the three dialects' texts and times both: the chain costs building the statement, about 4 µs, and reading its rows).

Query trees (hir.md) become SQL keeping Weft's meaning:

  • a tree reads the row's columns (t.done; after map, the mapped row's fields) and values (captured names, evaluated when the query is made, bound as parameters); t.a.b, a field of a column, string +, and a record outside map are E2026;
  • ==/!= with a side that may be null is null-safe (IS, IS NOT DISTINCT FROM, <=>); ! of a nullable boolean is true for null; a condition that may be null counts as false;
  • / divides as doubles; % is SQL's remainder (Postgres: on NUMERIC);
  • startsWith, endsWith, includes compare exactly; toLowerCase, toUpperCase, trim are SQL's LOWER, UPPER, TRIM (Unicode case mapping where the database has it); xs.includes(t.c) is IN (an empty list: no row);
  • order: strings by the database's collation (SQLite: code points; Postgres: the database's; MySQL: utf8mb4_bin, the columns' collation), { binary: true } by code points everywhere (COLLATE BINARY, "C", utf8mb4_bin; UTF-8 byte order, which is Weft's UTF-16 order except between U+E000–U+FFFF and the astral planes); null first going up; ties by the key, so every dialect gives one order.

Level 3, SQL: h.sql"select t.title, u.name from todos t join users u on u.id = t.owner where t.done = {done}".

  • Every {…} is a parameter (a list is in {xs}: one per item); the literal is one select, insert, update or delete, checked at compile time (tag-check, E2022): it parses, every table and column exists (unique or qualified), values fit the columns they meet, no update writes a key or an @immutable column, an insert names its columns and gives every column without a default, a computed result column has a name, functions are ones the checker knows (count sum avg total min max coalesce ifnull nullif lower upper trim ltrim rtrim substr substring replace concat length char_length abs round floor ceil ceiling, or cast(… as type)).
  • The rows' type: one field per result column, its column's declared type (or the expression's kind), ? where it may be null (outer joins, aggregates); a write without returning gives the number of rows changed. The task fails with DbError, as every query's.
  • Names are written bare and quoted by the host ("rank"), so they mean the same on every dialect; strings are '…'.

Policies ​

A table's policies say who may read, insert, update and delete its rows; write stands for the three writes not given. Building blocks, combined with .and(p), .or(p) and not(p):

the condition, for principal user
public()everyone, signed in or not
owned("c")row.c == user.id
flag("c")row.c is true (a boolean column; for everyone)
role("r")user.role == "r", or user.roles holds "r"
member("c", m.x, m.u)row.c in (select x from m where u = user.id) (m's own policies don't apply here)
(row, user) => …a closure (a Query<(row: T, user: P) => boolean>), written in table's policies
  • Deny by default: a scoped handle reading or writing a table without that policy fails with Denied; for no one (db.as(null)), owned, member and closures are false.
  • Reads carry read: at every level (object queries and their relations, Query<T> chains, and sql, whose every table read, in joins and subqueries too, becomes (select * from t where <read>) as t).
  • Writes go by key in one transaction (a savepoint inside one): the keys the filter and the write's policy reach are selected (locked on Postgres and MySQL), the write runs on them, then update is checked against the rows as they are now and insert against the rows inserted; a row outside the policy rolls the write back and fails with Denied. So an update can't move a row out of what its policy allows (owner to someone else), a delete or update outside the policy changes nothing, and an insert for someone else fails. A key or an @immutable column in set (or a sql update) fails with Denied; a literal set naming one is E2026.
  • The front end (db-policy, E2025): a closure that reads neither the row nor the principal (constant) or only the row (it ignores the principal: flag or public() say that) is an error, and so is a building block naming a column the table lacks (or owned on a principal without id, role without role/roles). Warnings: a write policy that reaches rows read doesn't (W0005; building blocks compared as independent conditions, a closure only with itself, so a closure the read policy doesn't share isn't judged) and a policy column without an index (W0006).
  • The access matrix: accessMatrix(schema, { principals, rows }), in a test, puts the sample rows (by table key or name) in an in-memory SQLite database and writes, per table, who of no one (anyone) and the principals (by id) can read (r), update (u), delete (d) and insert (i) each row, to db/access/<table>.txt. A file that differs fails the test with both versions (an AssertionError panic); a missing one is written; UPDATE_SNAPSHOTS=1 rewrites. The files are committed: a policy change shows as their diff.
  • Admin: admin(db) is the only way around the policies, and its own import. weft audit [project] lists every import and use of std/db/admin, with its line.
  • Policies aren't emitted as Postgres row-level security yet.

Server functions ​

query fn, mutation fn and action fn (language.md, Queries, mutations and actions) are how app code reads and writes; std/db runs the first two:

  • The caller's handle: in a query or mutation fn, a pool used as a handle (pool.query.todos(), pool.insert.…, pool.sql"…") is caller(pool), a Db<P, S, "user"> for whoever called. identify(pool, async () => …), set once where the pool is made, says who that is: its function runs in the call's context (so request() is the caller's) when the call's first statement on that pool needs it, and gives the principal or null; a call on a pool without it panics. caller(pool) written out is for a plain function a query calls, and panics outside a query or mutation fn. pool.as(…), or a scoped handle made outside, is db-kind there; admin(pool) stays allowed. The direction for v2, not built: async context. context and provide extend to the server's async call tree, the caller and the transaction become ordinary contexts std/db provides around each call, identify becomes sugar over that, and helpers no longer write caller(pool).
  • The call is $scope(kind, name, body, args): the bytecode compiler makes a query or mutation fn's body a function of its own and the function a call of std/db.$scope with it, so every caller (a client's call, another server function, a live call) gets the same rules. A call inside one (a query or mutation fn's async context) runs in it.
  • Its own async context: the body runs in a context forked from the caller's (Vm::call_value_in; the server runtime's fork serves the same request, so request() is the caller's, and in a live call its reads are the live call's). Every statement there, on any handle of a pool, runs on the call's transaction on that pool, begun by its first statement: BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY / SERIALIZABLE on Postgres, SET TRANSACTION ISOLATION LEVEL … then START TRANSACTION [READ ONLY] on MySQL, BEGIN / BEGIN IMMEDIATE on SQLite. A transaction runs one statement at a time; the call's statements started while one runs wait their turn (so Task.all of queries in a query fn works). Writes are reported to std/live at the commit.
  • The end: when the body's task settles, each transaction commits if it returned a value, and rolls back if it failed, panicked or was stopped, or the database reported a conflict.
  • Conflicts: a statement or a commit failing with a serialization failure or a deadlock (Postgres 40001, 40P01; MySQL 1213, 1205; SQLite busy) marks the call; a mutation then runs again, in a new context, up to ten runs, waiting 2^run ms (at most 128) times 1 to 2 between them; past that it fails with Failed { code: "40001", message }. A query isn't run again (its snapshot has no conflicts).
  • Deterministic sources: the call's time (Vm::now_here) and a seed (Vm::det, by context) are fixed at its first run and kept for the next ones: now(), std/time's clock, random() and uuid() give the same values on every run.
  • WEFT_DB_DEBUG=1 prints each statement's context and transaction and each run's end (ok, conflict).
  • weft-cli/tests/db/kinds is the scenario on every dialect: snapshots, a failing mutation's writes gone, nested calls, an action's mutations each their own, and 20 concurrent read-then-write mutations of one row losing none (on Postgres and MySQL they conflict and run again).

Sessions ​

sessionStore(db) is a SessionStore, { get, set, delete, deleteUser } returning tasks (a DbError becomes std/http's StoreError { message }), which std/http's sessions({ store }) takes (std/http.md, Sessions): sessions in the table _weft_sessions(id, data, expires, user_id) (made when first used; around the policies), set(id, value, maxAge) expiring maxAge seconds after the host's clock and keeping the value's user.id in user_id (null for anonymous sessions and std/auth's tokens), get of an expired session null (and deleting it), deleteUser(userId) every session of that user, through the index _weft_sessions_user_id (std/auth's sign-out everywhere). A table from before user_id gets the column, filled from each session's data, and the index when the store first uses it. std/auth builds on it.

The host ​

weft_db::register(vm, Options { root, update_snapshots, spawner }) registers the host functions (weft run, weft render, weft test call it; the module's hidden ones are $query $first $rows $insert $update $delete $join $rule $sql $scope $session.*). A Query<T> is { _query: { db, q }, _rows }: its handle and its chain as JSON (weft_db::exec::Built: stages of filter/sortBy/take/skip, each after the previous one's cut, and map's shape), built by the methods and run by all, first, find, count, some and every. A call that returns rows gets its rows' type descriptor first (bytecode.md, The host), kept as the VM's index (Vm::type_index) and converted with Vm::cast_to when the rows arrive.

  • Where statements run: a host function prepares Send work (the query, its values, the handle's transaction connection if it has one) and hands it to a Spawner (weft_db::spawn), returning its task; the work takes a pool connection, runs the VM-free query layer (exec, sqltag: JSON in, JSON out) and comes back on the Weft thread, where its rows become values and the task settles. Under weft run the spawner is the server runtime's (std/server.md, Host I/O: Io::spawn, each batch on a blocking thread of its I/O runtime), so queries of concurrent requests run at once, up to the pool's size, and wait (30 s) for a connection beyond it; elsewhere (weft test, renders) the work runs at once on the Weft thread. A transaction is BEGIN, its body, then COMMIT/ROLLBACK, chained with Vm::then_host; its connection runs one statement at a time (a second statement started before the first settles panics), and a body stopped by a limit (or that panicked) is rolled back and its task abandoned. SQLite pools have one connection, so other handles' statements wait for a transaction to end.
  • Drivers: SQLite (rusqlite, bundled; :memory: is one database per pool), Postgres (postgres, no TLS yet), MySQL 8 (mysql, prepared statements, utf8mb4_bin strings, ANSI_QUOTES), Skein (skein/sql, in the process, one database per directory and process). Skein speaks the SQLite dialect at this seam: the same statements, the sqlite migrations, and SQLite's types, conversions and error codes, checked statement by statement against SQLite (weft-db/tests/skein.rs). It differs in two ways: a primary key never holds NULL, and a real's 17th significant digit as text is correctly rounded (SQLite's can be off by one).
  • Live queries (std/live.md): every query records what it touched (a Touch { op, table, columns, filter, keys }, with the filter as something a row can be tested against and a write's row images): reads their tables, the filter (an object query's conditions, a chain's first filter trees, through its map; a sql text reads its tables whole) and the keys returned; writes the keys they changed and the rows before and after. With an observer (Options::observer, the server runtime's live engine through weft-cli's glue) a live call's reads go to it by async context, and every write once committed (a transaction's at its COMMIT); weft_db::live matches them (Read::hit), holds the Postgres trigger and the notification formats, and a pool on Postgres with live calls LISTENs for changes from outside the app. Without one (weft test) touches are kept in DbHost::touched. In a live call's batch identical queries (pool, principal, kind, query and values) run once.

Migrations ​

The schema's history is the project's: weft db migrate writes each step as reviewable SQL per dialect, weft db apply runs the steps a database hasn't had, weft db push (development) applies the schema directly without files.

Files ​

text
db/snapshot.json                          the schema as last migrated: the SchemaDesc, pretty JSON
db/migrations/<NNNN>_<name>/<dialect>.sql one migration, one file per dialect (sqlite.sql, postgres.sql, mysql.sql)
  • NNNN is the next 4-digit number after the highest in db/migrations/; <name> is --name made lowercase with _ for anything but letters and digits (default migration).
  • A file starts with -- <NNNN>_<name> (<dialect>): …, then a -- Note: … line per thing to review, then one statement a line, each ending in ;. The output is the same for the same two schemas. Files may be edited before they are applied (a rename, a backfill); never after.

weft.toml:

toml
[db]
schema = "db/schema"
export = "app"
dialects = ["sqlite", "postgres"]
url = "$DATABASE_URL"
  • schema/export: the module and the exported schema(…) to migrate; needed only when the project exports more than one (none, or several unnamed, is an error).
  • dialects: the files migrate writes (default all three).
  • url: the database for apply and push (--url overrides it). $NAME reads the environment variable NAME. A relative SQLite path or Skein directory is under the project. URLs: sqlite:<path> (or a path, :memory:), postgres://…, mysql://…, skein:<directory> (or skein::memory:; options ?durability=relaxed&maxSize=<bytes>).

Commands ​

  • weft db migrate [project] [--name name]: checks the project, diffs its schema against db/snapshot.json (empty when missing), writes the migration for each dialect and the new snapshot, and prints what it wrote and the notes. No change: prints no changes and writes nothing (a change with no SQL, such as @immutable, rewrites only the snapshot).
  • weft db apply [project] [--url url]: runs the migrations not in _weft_migrations, in order, from the files of the URL's dialect (a missing file is an error). On SQLite and Postgres each runs in a transaction with its record, so a failing one changes nothing; MySQL commits each DDL statement, so there the statements before a failure stay.
  • weft db push [project] [--url url]: diffs the schema against the one last pushed (_weft_schema; empty when absent), runs the statements in one transaction (SQLite, Postgres) with the new snapshot, and prints them. For development: it writes no files, and drops what the schema no longer has.

Tables kept in the database ​

sql
_weft_migrations(id VARCHAR(191) PRIMARY KEY, applied_at TEXT NOT NULL)  -- id: "<NNNN>_<name>"; applied_at: "YYYY-MM-DDTHH:MM:SSZ" (UTC)
_weft_schema(id INTEGER PRIMARY KEY, snapshot TEXT NOT NULL)               -- one row, id 1: the SchemaDesc as JSON (MySQL: INT, LONGTEXT)

The DDL ​

  • Types are the dialect's column_type. Not nullable is NOT NULL. A @default(<constant>) is the column's SQL DEFAULT (MySQL: an expression in parentheses on TEXT, BLOB and JSON columns, 8.0.13+); @default(uuid) and @default(now) have none: the host fills them in at insert.
  • Primary key: PRIMARY KEY (…) (composite in field order). A numbered key (auto): SQLite INTEGER PRIMARY KEY AUTOINCREMENT inline, Postgres BIGSERIAL, MySQL BIGINT AUTO_INCREMENT.
  • Names: @index is CREATE INDEX <table>_<column>_idx, @unique is CREATE UNIQUE INDEX <table>_<column>_key, @references(t.c) is CONSTRAINT <table>_<column>_fkey FOREIGN KEY (…) REFERENCES t (c) in the table. A name over 63 characters is cut to 54 and ends in _ and 8 hex digits of its hash. MySQL declares the indexes in its CREATE TABLE (so a reference uses them rather than an index of its own) and ends it in ENGINE=InnoDB DEFAULT CHARSET=utf8mb4.
  • Tables are created referenced first (a cycle is broken at its first table) and dropped in reverse.

The diff ​

Tables and columns are matched by SQL name. Statements run in phases: references dropped, tables dropped, indexes dropped, columns changed (SQLite: tables rebuilt), tables created, indexes created, references added.

ChangeSQLitePostgresMySQL
table added / droppedCREATE TABLE + indexes / DROP TABLEsamesame
column addedADD COLUMN; rebuild for a key, or a reference that is NOT NULL or has a defaultADD COLUMNADD COLUMN
NOT NULL column added without a constant defaultrebuild, existing rows get the kind's zeroADD COLUMN … DEFAULT <zero>, then DROP DEFAULTsame as Postgres
column droppedDROP COLUMN (a key or a reference: rebuild)DROP COLUMNDROP COLUMN
type changedrebuild (values copied)ALTER COLUMN … TYPE t USING c::tMODIFY COLUMN
becomes NOT NULLrebuild, nulls become the zeroUPDATE … SET c = <zero> WHERE c IS NULL, SET NOT NULLthe UPDATE, MODIFY COLUMN
becomes nullablerebuildDROP NOT NULLMODIFY COLUMN
default changedrebuildSET DEFAULT / DROP DEFAULTMODIFY COLUMN
numbered key on / offrebuildsequence <table>_<column>_seq created and set (nextval default, setval past the max) / default and sequence droppedMODIFY COLUMN
primary key changedrebuildDROP CONSTRAINT <table>_pkey, ADD PRIMARY KEY (…)DROP PRIMARY KEY, ADD PRIMARY KEY (…)
@index / @unique added or droppedCREATE [UNIQUE] INDEX / DROP INDEXsamesame (DROP INDEX … ON t)
reference added, dropped or retargetedrebuildADD CONSTRAINT … FOREIGN KEY / DROP CONSTRAINTADD CONSTRAINT … FOREIGN KEY / DROP FOREIGN KEY
  • The zero: '', 0, false, empty bytes, JSON null.
  • SQLite's rebuild is sqlite.org's procedure: CREATE TABLE new_<t> with the new shape, INSERT INTO new_<t> (…) SELECT … FROM <t> (common columns; COALESCE(c, <zero>) where a column became NOT NULL; the zero for a new NOT NULL column), DROP TABLE <t>, ALTER TABLE new_<t> RENAME TO <t>, its indexes created. A migration that rebuilds or drops a table is wrapped in PRAGMA foreign_keys=OFF … PRAGMA foreign_key_check … PRAGMA foreign_keys=ON; apply and push run the pragmas outside the transaction and fail it when foreign_key_check finds rows.
  • On MySQL a string becomes VARCHAR(191) when it becomes a key or indexed (and TEXT again when it stops being one): a MODIFY COLUMN before the index is created, after it is dropped.
  • Notes: a dropped table or column (its data goes); a drop and an add in the same migration ("if it was a rename, edit the migration": RENAME TO / RENAME COLUMN); a NOT NULL column added without a default, or a column that becomes NOT NULL (which value existing rows get); a changed kind or numeric type (values are converted); a MySQL string made VARCHAR(191) (a longer value fails); a changed primary key.
  • @immutable, policies and table keys don't reach the DDL.

Weft is in development. The reference is the spec as it stands today.