Appearance
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 aTable<T, P, name>: rows of the record typeT, policies about principals of typeP, namednamein SQL. Both type arguments are written;nameis a string written there (db-schema,E2024, otherwise). A table has a key (@primaryon one field, or on several: a composite key).- Columns are
T's fields in order. A column's type is a string, number, boolean,Bytesorjson, a literal union of one of them, or a validating type over one, maybenull(?fields are nullable); anything else isE2024(store structured values asjson). Its kind decides its SQL type (weft-db'sDialect::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)wherecis a constant,uuid(std/random) ornow(std/time) (the host fills them in at insert;nowis the host's clock, itsepochMs);@references(t.c),ta table declared before this one;@index;@unique;@immutable(no update changes it). t.con a table is a column reference, aColumn<C>(its value is the string"t.c").schema({ tables: { users, todos } })is aSchema<P, S>:Sthe record of tables,Pthe 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/_idsuffix (ownerId→owner), or else by the other table's key intables; 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, isE2024. - Descriptors:
table(…)andschema(…)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 migratediffs 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 aTask<Database<P, S> ! DbError>: a pool (Postgres, MySQL and Skein:poolconnections, 10 by default; SQLite: one). Skein'sdurabilityis"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);maxSizeis 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 withConnection.db.as(user)(scoped) is aDb<P, S, "user">, the handle queries go through, under the policies foruser;db.as(null)is no one signed in. It is for actions and scripts, which say whom they run for: in aquery fnormutation fnthe pool itself is the caller's handle (Server functions, below).admin(db)fromstd/db/adminis aDb<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 => …):txis 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 istransaction<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 plusDbError(BEGINorCOMMITfailing). 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)isfilter(q, f);asisscoped). 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, ornull, filters nothing (isNull()is the test).jsonandBytescolumns aren't filtered here. - A relation's name with
trueincludes 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.RisT, plus one field per relation asked:U[]for many,U?for one (a row the policies hide isnull),U[]for a join. Related rows go through their table's read policy. tnames a table of the schema, a filter's fields its columns,setno key and no@immutablecolumn (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[]| method | gives | |
|---|---|---|
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
filterorsortByaftertake/skipreads the rows the cut gave (a derived table,(SELECT … LIMIT n) AS q, keeping their order);mapbefore afilterorsortByis read through (`map(t => ({ twice: t.rank * 2 })).filter(r => r.twice6)
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'sQuery<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 outsidemap, 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)isSELECT 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'stests/chains.rsholds 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; aftermap, 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 outsidemapareE2026; ==/!=with a side that may be null is null-safe (IS,IS NOT DISTINCT FROM,<=>);!of a nullable boolean istruefornull; a condition that may be null counts as false;/divides as doubles;%is SQL's remainder (Postgres: onNUMERIC);startsWith,endsWith,includescompare exactly;toLowerCase,toUpperCase,trimare SQL'sLOWER,UPPER,TRIM(Unicode case mapping where the database has it);xs.includes(t.c)isIN(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);nullfirst 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 isin {xs}: one per item); the literal is oneselect,insert,updateordelete, 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@immutablecolumn, 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, orcast(… 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 withoutreturninggives the number of rows changed. The task fails withDbError, 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,memberand closures are false. - Reads carry
read: at every level (object queries and their relations,Query<T>chains, andsql, 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
updateis checked against the rows as they are now andinsertagainst the rows inserted; a row outside the policy rolls the write back and fails withDenied. So an update can't move a row out of what its policy allows (ownerto someone else), a delete or update outside the policy changes nothing, and an insert for someone else fails. A key or an@immutablecolumn inset(or asqlupdate) fails withDenied; a literalsetnaming one isE2026. - 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:flagorpublic()say that) is an error, and so is a building block naming a column the table lacks (orownedon a principal withoutid,rolewithoutrole/roles). Warnings: a write policy that reaches rowsreaddoesn'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 (byid) can read (r), update (u), delete (d) and insert (i) each row, todb/access/<table>.txt. A file that differs fails the test with both versions (anAssertionErrorpanic); a missing one is written;UPDATE_SNAPSHOTS=1rewrites. 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 ofstd/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"…") iscaller(pool), aDb<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 (sorequest()is the caller's) when the call's first statement on that pool needs it, and gives the principal ornull; 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, isdb-kindthere;admin(pool)stays allowed. The direction for v2, not built: async context.contextandprovideextend to the server's async call tree, the caller and the transaction become ordinary contextsstd/dbprovides around each call,identifybecomes sugar over that, and helpers no longer writecaller(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 ofstd/db.$scopewith 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, sorequest()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/SERIALIZABLEon Postgres,SET TRANSACTION ISOLATION LEVEL …thenSTART TRANSACTION [READ ONLY]on MySQL,BEGIN/BEGIN IMMEDIATEon SQLite. A transaction runs one statement at a time; the call's statements started while one runs wait their turn (soTask.allof queries in a query fn works). Writes are reported tostd/liveat 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, waiting2^runms (at most 128) times 1 to 2 between them; past that it fails withFailed { 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()anduuid()give the same values on every run. WEFT_DB_DEBUG=1prints each statement's context and transaction and each run's end (ok, conflict).weft-cli/tests/db/kindsis 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
Sendwork (the query, its values, the handle's transaction connection if it has one) and hands it to aSpawner(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. Underweft runthe 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 isBEGIN, its body, thenCOMMIT/ROLLBACK, chained withVm::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_binstrings,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, thesqlitemigrations, 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 (aTouch { 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 firstfiltertrees, through itsmap; asqltext 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 throughweft-cli's glue) a live call's reads go to it by async context, and every write once committed (a transaction's at itsCOMMIT);weft_db::livematches them (Read::hit), holds the Postgres trigger and the notification formats, and a pool on Postgres with live callsLISTENs for changes from outside the app. Without one (weft test) touches are kept inDbHost::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)NNNNis the next 4-digit number after the highest indb/migrations/;<name>is--namemade lowercase with_for anything but letters and digits (defaultmigration).- 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 exportedschema(…)to migrate; needed only when the project exports more than one (none, or several unnamed, is an error).dialects: the filesmigratewrites (default all three).url: the database forapplyandpush(--urloverrides it).$NAMEreads the environment variableNAME. A relative SQLite path or Skein directory is under the project. URLs:sqlite:<path>(or a path,:memory:),postgres://…,mysql://…,skein:<directory>(orskein::memory:; options?durability=relaxed&maxSize=<bytes>).
Commands
weft db migrate [project] [--name name]: checks the project, diffs its schema againstdb/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: printsno changesand 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 isNOT NULL. A@default(<constant>)is the column's SQLDEFAULT(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): SQLiteINTEGER PRIMARY KEY AUTOINCREMENTinline, PostgresBIGSERIAL, MySQLBIGINT AUTO_INCREMENT. - Names:
@indexisCREATE INDEX <table>_<column>_idx,@uniqueisCREATE UNIQUE INDEX <table>_<column>_key,@references(t.c)isCONSTRAINT <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 itsCREATE TABLE(so a reference uses them rather than an index of its own) and ends it inENGINE=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.
| Change | SQLite | Postgres | MySQL |
|---|---|---|---|
| table added / dropped | CREATE TABLE + indexes / DROP TABLE | same | same |
| column added | ADD COLUMN; rebuild for a key, or a reference that is NOT NULL or has a default | ADD COLUMN | ADD COLUMN |
| NOT NULL column added without a constant default | rebuild, existing rows get the kind's zero | ADD COLUMN … DEFAULT <zero>, then DROP DEFAULT | same as Postgres |
| column dropped | DROP COLUMN (a key or a reference: rebuild) | DROP COLUMN | DROP COLUMN |
| type changed | rebuild (values copied) | ALTER COLUMN … TYPE t USING c::t | MODIFY COLUMN |
| becomes NOT NULL | rebuild, nulls become the zero | UPDATE … SET c = <zero> WHERE c IS NULL, SET NOT NULL | the UPDATE, MODIFY COLUMN |
| becomes nullable | rebuild | DROP NOT NULL | MODIFY COLUMN |
| default changed | rebuild | SET DEFAULT / DROP DEFAULT | MODIFY COLUMN |
| numbered key on / off | rebuild | sequence <table>_<column>_seq created and set (nextval default, setval past the max) / default and sequence dropped | MODIFY COLUMN |
| primary key changed | rebuild | DROP CONSTRAINT <table>_pkey, ADD PRIMARY KEY (…) | DROP PRIMARY KEY, ADD PRIMARY KEY (…) |
@index / @unique added or dropped | CREATE [UNIQUE] INDEX / DROP INDEX | same | same (DROP INDEX … ON t) |
| reference added, dropped or retargeted | rebuild | ADD CONSTRAINT … FOREIGN KEY / DROP CONSTRAINT | ADD CONSTRAINT … FOREIGN KEY / DROP FOREIGN KEY |
- The zero:
'',0, false, empty bytes, JSONnull. - 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 inPRAGMA foreign_keys=OFF…PRAGMA foreign_key_check…PRAGMA foreign_keys=ON;applyandpushrun the pragmas outside the transaction and fail it whenforeign_key_checkfinds rows. - On MySQL a string becomes
VARCHAR(191)when it becomes a key or indexed (andTEXTagain when it stops being one): aMODIFY COLUMNbefore 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 madeVARCHAR(191)(a longer value fails); a changed primary key. @immutable, policies and table keys don't reach the DDL.