From an existing database
Read a PostgreSQL, MySQL, SQLite or Snowflake database into a JSON schema document, and query it at once
viborm/schema/introspect reads an existing database into a JSON schema
document. The document gives a client directly:
import { createClient } from "viborm/pg";
import { introspectSchema } from "viborm/schema/introspect";
import { parseSchema } from "viborm/schema/json";
const { document, skipped, warnings } = await introspectSchema({ url });
const db = createClient({ schema: parseSchema(document), databaseUrl: url });
Or from the command line:
npx viborm pull --json --url "$DATABASE_URL" --out schema.json
npx viborm pull --json --tables account post tag # only these tables
npx viborm pull --json --light --out schema.json # only what a client needs
pull writes the document (to --out, else stdout) and prints what it left
out and every warning on stderr. --url defaults to DATABASE_URL, read from
.env like every command.
The document is the ordinary format, in canonical
form: there is no second format, the
client is an UntypedClient, and
push, serializeSchema and every other consumer read it unchanged. A
TypeScript schema is not generated yet; it will be written from this document.
The connection string
| URL | Driver |
|---|---|
postgres://…, postgresql://… |
pg |
mysql://…/database |
mysql2 |
file:./app.db, sqlite:./app.db, ./app.db (.db, .sqlite, .sqlite3) |
better-sqlite3, opened read-only; a missing file is an error, never created |
libsql://…, https://…, ws(s)://… (?authToken=…) |
@libsql/client |
The driver’s package is imported only for the URL that names it, and the
connection is always closed, success or failure. To read through a driver you
built — a D1 binding, a PostgreSQL namespace, a custom pool — pass it instead:
introspectSchema({ driver }). That driver is yours and stays open.
What it returns
interface IntrospectSchemaResult {
document: SchemaDocument;
skipped: SkippedObject[]; // { kind, name, table?, reason }
warnings: IntrospectionWarning[]; // { kind: "approximated" | "unstated" | "drift", table?, name?, message }
dialect: "postgresql" | "sqlite" | "mysql";
}
Nothing is invented. A fact the catalog does not prove is not in the document. Everything else is said:
skippedlists each object the document does not carry, with the reason.- An
approximatedwarning names a field whose meaning is weaker than the database proves. - An
unstatedwarning names what a table carries that changes how it behaves and that a document has no spelling for: a column or table collation other than the one VibORM writes (SQLiteCOLLATE NOCASE, a PostgreSQL column collation other than its type’s, a MySQL collation other thanutf8mb4_0900_bin, which is every column of a table created with MySQL 8’s defaultutf8mb4_0900_ai_ci), SQLite’sSTRICTandON CONFLICT, a trigger, a PostgreSQL rule, row-level security,UNLOGGEDstorage or aDEFERRABLEkey, a MySQL engine other than InnoDB. The database keeps it and a push of the document plans nothing for it, but a push that recreates the table or the column cannot keep it. - A
driftwarning names a place wherepushwith this document would change the database. The document is parsed, serialized exactly aspushserializes a schema, and diffed against the snapshot the database was read into, with push’s own differ. A result with no drift warning pushes as a no-op.
Names
Table and column names are kept as they are: no camelCase, no mapping. A model’s
key is its table name and a field’s key is its column name. A column named like
a reserved member (_count, _distance) gets another key and keeps its name
through column. A table or column whose name is not an identifier (ASCII
letters, digits and _, at most 63 bytes) cannot be mapped at all and is
skipped.
A relation has no database name, so it is named from its foreign key, the same way every time:
| Foreign key | To-one on the owner | Inverse on the target |
|---|---|---|
post.author_id → account (one to account) |
author (the column without _id / Id) |
post |
post.author_id, post.reviewer_id → account |
author, reviewer |
post_by_author, post_by_reviewer |
category.parent_id → category |
parent |
category_by_parent |
profile.account_id → account, unique |
account |
profile, a to-one |
a column not ending in _id / Id |
the target’s key | as above |
A name already taken by a column or another relation gets _2, _3 …. Where two
models share more than one relation, or a model relates to itself, each pair
carries the foreign key’s name as its pairing label (name). Referential
actions are kept as declared.
Many-to-many. A pure join table — two foreign keys that together are all of
its columns and its primary key — becomes a many-to-many on both ends (post.tag
and tag.post). When the table is exactly the junction VibORM would generate
for that pair, the document states it as one (junction: { table, source, target }). Otherwise the table stays a model and the many-to-many walks through
it (junction: { model, source, target }, see a model as the
junction), so its
own indexes and actions are kept.
PostgreSQL
| Column | Field |
|---|---|
text |
string |
varchar(n), char(n), citext, inet, cidr, macaddr(8), xml, tsvector, tsquery, bit(n), varbit |
string with that native type |
uuid |
string in the uuid domain (generate: { kind: "uuid", generate: false }); DEFAULT gen_random_uuid() makes it generated |
integer, smallint, oid |
int (native for the last two) |
bigint |
bigint |
serial, GENERATED … AS IDENTITY |
int / bigint with generate: { kind: "increment" } |
double precision, real |
number (native: real) |
numeric(p, s) |
decimal with precision and scale |
boolean, date, bytea |
boolean, date, blob |
jsonb, json |
json (native: json) |
timestamptz(3), timestamp(3) |
datetime, withoutTimezone for the second; any other precision keeps its native type |
time(p), timetz(p) |
time with its native type |
| an enum type | enum: inline when it is the column’s own derived type (<table>_<column>_enum), else a named enums entry shared by its columns |
T[] |
the element’s field with array: true |
Defaults read: literals of every type above (strings, numbers, booleans, JSON,
arrays written '{…}', timestamps), now() / CURRENT_TIMESTAMP (generate: now), gen_random_uuid(), sequences and identity. A unique constraint named
the way PostgreSQL names one by default (<table>_<columns>_key) is unique /
uniques; any other name is kept as a unique index of that name, which enforces
the same key but is not a findUnique key (an approximated warning).
MySQL
| Column | Field |
|---|---|
TEXT, and VARCHAR(191) on a keyed column (VibORM’s spelling of an indexed string) |
string |
VARCHAR(n), CHAR(n), TINYTEXT, MEDIUMTEXT, LONGTEXT, BIT(n) |
string with that native type |
TINYINT(1) |
boolean |
INT; TINYINT, SMALLINT, MEDIUMINT, their UNSIGNED, INT UNSIGNED, YEAR |
int (native for all but INT) |
BIGINT, BIGINT UNSIGNED |
bigint (native for the second) |
DOUBLE, FLOAT |
number (native: FLOAT) |
DECIMAL(p, s) |
decimal |
JSON |
json |
BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB, BINARY(n), VARBINARY(n) |
blob (native for all but BLOB) |
BINARY(16) key, or a foreign key to one |
string uuid key (approximated: VibORM’s uuid key storage) |
DATE, DATETIME(3), TIME(3) |
date, datetime, time; another precision, and TIMESTAMP, keep their native type |
ENUM(…) |
enum, inline: MySQL has no enum object |
AUTO_INCREMENT is increment, CURRENT_TIMESTAMP(3) is now(), and literal
defaults are read with MySQL’s escapes. MySQL has one unique namespace: a unique
index named the way VibORM names a key (<table>_<columns>_key) is that key.
A list is JSON on MySQL, as on SQLite, and reads back as json.
SQLite
SQLite proves less: a column’s declared type is free text, and VibORM stores
several domains in one storage class. VibORM’s own spellings are read exactly:
TEXT, INTEGER (AUTOINCREMENT as increment), REAL, BLOB, JSON, the
enum TEXT CHECK("col" IN (…)), a decimal’s checked scaled INTEGER, and a
DateTime whose default is VibORM’s now(). Any other declared type
(VARCHAR(20), BOOLEAN, DATETIME, NUMERIC) is read by SQLite’s affinity
rules and is an approximated warning, since push would retype it.
What a SQLite catalog cannot tell apart:
| Stored as | Read as | Could also be |
|---|---|---|
TEXT |
string |
a datetime, date or time without now() |
INTEGER |
int |
a boolean or bigint |
JSON |
json |
a list (array: true) of any scalar |
BLOB key, or a foreign key to one |
string uuid key (approximated) |
a key of other bytes |
A database VibORM pushed from a TypeScript schema therefore comes back with the same tables and an empty push plan, but those fields in their storage domain.
A key that admits NULL. SQLite lets a primary key column hold NULL unless it
is declared NOT NULL, except a rowid table’s INTEGER PRIMARY KEY (CREATE TABLE t (id TEXT PRIMARY KEY)). A model key is never nullable, so the document
declares each such column, and each member of such a compound key, NOT NULL,
with an approximated warning. push of that document against the unchanged
database then plans a destructive alterColumn per column, a rebuild of the
table that adds NOT NULL (a drift warning says so). The rebuild fails while
a row’s key is NULL, and cannot keep what the table carries beyond the document
(its unstated warnings: a collation, STRICT, a trigger).
Snowflake
Pass a SnowflakeDriver: a snowflake:// URL carries
no key or token, and introspectSchema({ url }) refuses one. Snowflake declares
keys and enforces none: each declared foreign key becomes a relation, and an
approximated warning says the document’s keys may not hold. A key into
another schema or database is listed in skipped. Nothing is ever
pushed to Snowflake, so no drift warning is reported. The
Snowflake page lists the types.
What is skipped
| Object | Why |
|---|---|
| a view or materialized view | a document declares tables |
| a table without a primary key | a model is addressed by one |
| a table migration introspection refuses (generated column, partitioned table, an index it cannot spell, a type from another schema, PostGIS) | push refuses it too; the rest of the database is still read |
a column of a type with no scalar (interval, money, unconstrained numeric, bare varchar, json[], vector, …) |
push would drop it: the drift warning says so |
a default with no document spelling (lower(…), CURRENT_DATE, ARRAY[…]) |
the column is kept, the default is not |
a partial index, a raw (.rawIndex()) index |
a document has no where and no raw index |
a foreign key to a skipped or unselected table, to columns that are not a key, or with SET DEFAULT |
a relation needs both ends and a key |
| a CHECK constraint (PostgreSQL) | the database keeps enforcing it |
| an enum no translated column uses | a document declares an enum through its fields |
Some exact facts a document cannot carry are drift rather than skips: a
timestamptz DEFAULT now() (precision 6) is VibORM’s now(), which truncates
to milliseconds, so push would rewrite the default; a foreign key with no
index gets the one VibORM creates; a constraint whose name is not the one
VibORM derives is refused by push rather than renamed.
To push a document against the database it came from, scope the push to the
tables it declares (createMigrationClient(client, { tables })), or push
would drop the tables it skipped.
A light document
introspectSchema({ url, light: true }) (viborm pull --json --light) writes
only what a client reads, for an application that queries a database whose
schema something else migrates:
const { document } = await introspectSchema({ url, light: true });
const db = createClient({ schema: parseSchema(document), databaseUrl: url });
The rule is what the client reads at run time, checked against its code:
| Kept (a client reads it) | Left out (only migrations read it) |
|---|---|
models, their table names, field keys and column names |
every index in indexes: plain, unique, typed (a unique index is not a findUnique key) |
each field’s type, nullable, array, enum, precision/scale |
|
keys: id, ids, unique, uniques (findUnique) |
|
default and generate (what makes a field optional on create) |
|
native types: the client reads them to encode and compare values (SQLite date storage, uuid key storage, PostgreSQL case folding) |
|
withoutRowid: the client reads it ($rows addresses a rowid table’s rows by rowid) |
|
relations whole, junctions, referential actions, enums |
Raw indexes, partial indexes, CHECK constraints and collations are never in a
document, light or not. Without light, the output is exactly the full
document.
A light document carries "light": true, and so does serializeSchema of any
schema holding one of its models. Every migration path refuses it: the
migration serializer, which push, migrate and every other command go through
to state the desired schema, fails with V11009 naming the model, before
planning anything, since a push of it would drop every index.
skipped and warnings keep only what changes what a client sees: no index
or CHECK constraint in skipped, no unstated or drift warning, and no
approximated warning about a unique index the document leaves out. A key
column that admits NULL keeps its approximated warning: the client reads it
as never NULL.
Not yet
SurrealDB is not translated yet (introspectSchema refuses it by name), nor
are pgvector and PostGIS columns. A TypeScript schema file will be
generated from the document later.