Loading article…
Database & APIsSchema and migrations
DocsDocsLoading article…
The generated REST API supports ordinary tables with stable primary keys. Composite keys use a base64url-encoded JSON array in primary-key order. Views, virtual tables and custom-trigger tables are not automatically exposed.
PGlite provides embedded PostgreSQL. Studio and new migrations use PostgreSQL SQL. Studio SQL is a privileged developer operation. Reserved _qb_* objects, extensions, filesystem access, role/configuration changes and explicit transaction control are blocked through the console.
New tables are private. Exposure chooses the allowed public columns; all primary-key columns must be included. Policies are a separate requirement. Hiding a column rejects projections, sorting and filtering against that column.
Each migration records its identifier, checksum, author and application time. Reapplying the same identifier and content is idempotent. Changing the content of an applied migration returns a conflict.
Apply migrations under the branch owner transaction. Destructive migrations trigger a consistent backup first. Failed SQL or foreign-key validation rolls back the schema changes. Successful changes advance the schema version and regenerate managed event triggers.
Use forward migrations for corrections. There is no automatic destructive rollback. Restore a backup into an isolated branch when data recovery is necessary.
REST/SDK booleans remain 0/1, including native PostgreSQL BOOLEAN columns. TIMESTAMPTZ values return UTC ISO strings; JSON/JSONB values return JSON text. Legacy converted text timestamps and JSON retain their original representation. PostgreSQL integer, BIGINT, BYTEA and identity columns are supported. Integers outside JavaScript's safe range are returned as strings. Write these values as signed 64-bit decimal strings. Blob writes and equality filters use canonical base64; JSON columns require valid JSON text. Blob values are base64 encoded in row responses and in authorized realtime projections.
Types describe the exposed schema, nullable values, defaults and generated columns. Simple CHECK (column IN (...)) constraints on literal values generate enum unions. More complex constraint expressions require explicit application validation. Insert fields with defaults are optional; generated fields are read-only. Keep declarations under version control and regenerate when migrations change.
The branch records a fingerprint of user tables, indexes, views and triggers. A schema changed through direct file access returns SCHEMA_STALE on public reads, mutations, schema discovery and row subscriptions. Reconcile it with a reviewed forward migration through Studio or CLI. Trusted file access still bypasses application row policies.
Database's New table dialog configures columns, supported types, keys, defaults, nullability and supported constraints/foreign keys, with SQL preview before applying through the migration path. Creation defaults private; the dialog has an explicit exposure toggle. Exposing a table does not grant row access—add policies separately. The Schema Visualizer inspects the topology and detail sheet; it does not apply migrations. Destructive Schema Change Review, approval binding and WASM rehearsal remain expanded requirements.
Use the offline engine migration workflow. SQLite SQL is not accepted by the new engine. Applied migration SQL and checksums remain historical evidence; create new forward migrations using PostgreSQL SQL.