Native Types
Override most scalar column types with database-specific native types
Most scalar factories accept an optional native type as their first argument to override the default column type:
import { s } from "viborm";
import { PG, MYSQL, SQLITE } from "viborm/schema";
s.string(PG.STRING.VARCHAR(255)); // varchar(255) instead of text
s.string(MYSQL.STRING.LONGTEXT); // LONGTEXT on MySQL
s.json(PG.JSON.JSON); // json instead of jsonb
A native type only applies when it matches the database you run against (PG.* on PostgreSQL, MYSQL.* on MySQL, SQLITE.* on SQLite); otherwise the default mapping below is used. This keeps schemas portable across databases.
One native type per dialect
To choose a native type on several databases at once, pass a map keyed by dialect instead of a single constant:
import { s } from "viborm";
import { MYSQL, PG, SQLITE } from "viborm/schema";
s.string({
pg: PG.STRING.CITEXT,
mysql: MYSQL.STRING.LONGTEXT,
sqlite: SQLITE.STRING.TEXT,
});
s.dateTime({ sqlite: SQLITE.DATETIME.INTEGER }); // epoch ms on SQLite only
Each migration uses only its own dialect’s entry; a dialect the map leaves out
keeps the default mapping below. So the map above creates citext on
PostgreSQL, LONGTEXT on MySQL and TEXT on SQLite, and changing the mysql
entry alone is a migration on MySQL and nothing on PostgreSQL or SQLite. The
same entry decides how values are stored and read back — a SQLite INTEGER
DateTime is written and decoded as epoch milliseconds, and an identifier format
takes each dialect’s entry exactly as it takes a single native type.
The map is exact:
- its keys are
pg,mysqlandsqlite, at least one of them — an unknown key is a type error, beside a real key and in a map held in a variable, and an object with no dialect key at all must be a whole{ db, type }constant; - each entry is a constant of its own key’s dialect. A parameterized constant
keeps its dialect in its type, so
PG.STRING.VARCHAR(80)fills thepgentry and is refused as themysqlone; - a single
{ db, type }constant and a map are two spellings: an object that is both is refused. - an entry is checked against its dialect, not against the scalar. As with a
single constant, choosing a type that holds the scalar’s values is yours:
s.string({ pg: PG.BLOB.BYTEA }).uuid().id()stores an identifier as bytes on purpose, ands.string({ pg: PG.INT.INTEGER })is accepted just the same and gives that string anintegercolumn.
The same structural rules hold at runtime for untyped callers. A map and a
single tagged declaration both accept custom type spellings; each must name pg,
mysql or sqlite as its db and carry a string type. The factory keeps a
frozen copy of the map, so later changes to the object you passed do not reach
the schema.
Custom types are trusted SQL declarations in coded schemas. JSON schema documents accept only types produced by the shipped constants.
A variable typed NativeType | NativeTypeMap, or a helper parameter of that
type, is accepted as it is. A union of two maps that share no dialect key is
not: annotate such a variable with the exported NativeTypeMap type.
Like a single constant, a map applies to a scalar column. A list keeps its dialect’s container (a PostgreSQL array of the entry, JSON elsewhere), and an enum column keeps its enum spelling.
Fixed decimals are the deliberate exception. s.decimal({ precision, scale })
derives its physical type from that one portable descriptor and accepts no
native-type override.
Default Type Mapping
Without a native type override, scalars map to these column types:
| Scalar | TypeScript | PostgreSQL | MySQL | SQLite |
|---|---|---|---|---|
s.string() |
string |
text |
TEXT |
TEXT |
s.int() |
number |
integer |
INT |
INTEGER |
s.number() |
number |
double precision |
DOUBLE |
REAL |
s.decimal({ precision, scale }) |
Decimal |
NUMERIC(p,s) |
DECIMAL(p,s) |
checked scaled INTEGER |
s.bigInt() |
bigint |
bigint |
BIGINT |
INTEGER |
s.boolean() |
boolean |
boolean |
TINYINT(1) |
INTEGER |
s.dateTime() |
Date |
timestamptz |
DATETIME(3) |
TEXT |
s.date() |
Date |
date |
DATE |
TEXT |
s.time() |
string |
timetz |
TIME(3) |
TEXT |
s.json() |
unknown / T |
jsonb |
JSON |
JSON |
s.blob() |
Uint8Array |
bytea |
BLOB |
BLOB |
s.enum([...]) |
Union type | Native enum type | ENUM(...) |
TEXT |
s.vector() |
number[] |
vector(n) |
JSON |
JSON |
s.dateTime() and s.time() default to timezone-aware columns on PostgreSQL; chain .withoutTimezone() for timestamp / time. .array() appends [] to the column type on PostgreSQL and stores as JSON on MySQL/SQLite.
String
| Constant | Column type |
|---|---|
PG.STRING.TEXT |
text (default) |
PG.STRING.VARCHAR(n) |
varchar(n) |
PG.STRING.CHAR(n) |
char(n) |
PG.STRING.CITEXT |
citext (case-insensitive) |
PG.STRING.UUID |
uuid |
PG.STRING.BIT(n) / PG.STRING.VARBIT(n?) |
bit(n) / varbit(n) |
PG.STRING.XML |
xml |
PG.STRING.INET / PG.STRING.CIDR |
inet / cidr |
PG.STRING.MACADDR / PG.STRING.MACADDR8 |
macaddr / macaddr8 |
PG.STRING.TSVECTOR / PG.STRING.TSQUERY |
tsvector / tsquery |
MYSQL.STRING.VARCHAR(n) |
VARCHAR(n) |
MYSQL.STRING.CHAR(n) |
CHAR(n) |
MYSQL.STRING.TEXT |
TEXT (default) |
MYSQL.STRING.TINYTEXT / MEDIUMTEXT / LONGTEXT |
TINYTEXT / MEDIUMTEXT / LONGTEXT |
MYSQL.STRING.BIT(n) |
BIT(n) |
SQLITE.STRING.TEXT |
TEXT (only option) |
A string field that NAMES an identifier format — .uuid(), .uuidv7(),
.ulid(), .ksuid(), .nanoid(), .cuid() — chooses its own column, so the
override is narrower there than on an ordinary string: it may name a text-family
column (which keeps text storage with the format still validated, the opt-out
for a column that already holds text) or a binary one of that format’s exact
width. Anything else is refused where it is declared, with a message naming the
spellings the format does accept. See
String.
Integer
| Constant | Column type |
|---|---|
PG.INT.SMALLINT |
smallint |
PG.INT.INTEGER |
integer (default) |
PG.INT.OID |
oid |
MYSQL.INT.TINYINT / TINYINT_UNSIGNED |
TINYINT / TINYINT UNSIGNED |
MYSQL.INT.SMALLINT / SMALLINT_UNSIGNED |
SMALLINT / SMALLINT UNSIGNED |
MYSQL.INT.MEDIUMINT / MEDIUMINT_UNSIGNED |
MEDIUMINT / MEDIUMINT UNSIGNED |
MYSQL.INT.INT / INT_UNSIGNED |
INT (default) / INT UNSIGNED |
MYSQL.INT.YEAR |
YEAR |
SQLITE.INT.INTEGER |
INTEGER (only option) |
Float
| Constant | Column type |
|---|---|
PG.FLOAT.REAL |
real |
PG.FLOAT.DOUBLE_PRECISION |
double precision (default) |
MYSQL.FLOAT.FLOAT |
FLOAT |
MYSQL.FLOAT.DOUBLE |
DOUBLE (default) |
SQLITE.FLOAT.REAL |
REAL (only option) |
Decimal
Decimal native-type constants do not exist. The required descriptor is the one source of truth for validation, DDL, query lowering, results, and migrations:
s.decimal({ precision: 10, scale: 2 });
It maps to NUMERIC(10,2) on PostgreSQL, DECIMAL(10,2) on MySQL, and
a checked scaled INTEGER coefficient on SQLite-family providers. This keeps
one logical decimal domain exact on every database; a native override would
create a second provider-specific precision or representation. See
Decimal.
BigInt
| Constant | Column type |
|---|---|
PG.BIGINT.BIGINT |
bigint |
MYSQL.BIGINT.BIGINT |
BIGINT (signed) |
MYSQL.BIGINT.BIGINT_UNSIGNED |
BIGINT UNSIGNED |
SQLITE.BIGINT.INTEGER |
INTEGER |
Boolean
| Constant | Column type |
|---|---|
PG.BOOLEAN.BOOLEAN |
boolean |
MYSQL.BOOLEAN.TINYINT |
TINYINT(1) |
SQLITE.BOOLEAN.INTEGER |
INTEGER (0/1) |
DateTime
| Constant | Column type |
|---|---|
PG.DATETIME.TIMESTAMP(p?) |
timestamp(p) |
PG.DATETIME.TIMESTAMPTZ(p?) |
timestamptz(p) |
PG.DATETIME.DATE |
date |
PG.DATETIME.TIME(p?) / TIMETZ(p?) |
time(p) / timetz(p) |
MYSQL.DATETIME.DATETIME(p?) |
DATETIME(p) |
MYSQL.DATETIME.TIMESTAMP(p?) |
TIMESTAMP(p) |
MYSQL.DATETIME.DATE / TIME(p?) |
DATE / TIME(p) |
SQLITE.DATETIME.TEXT |
TEXT (ISO format, default) |
SQLITE.DATETIME.REAL |
REAL (Julian day) |
SQLITE.DATETIME.INTEGER |
INTEGER (Unix epoch milliseconds) |
JSON
| Constant | Column type |
|---|---|
PG.JSON.JSONB |
jsonb (binary, indexable, default) |
PG.JSON.JSON |
json (text-based) |
MYSQL.JSON.JSON |
JSON |
SQLITE.JSON.TEXT |
TEXT |
Blob
| Constant | Column type |
|---|---|
PG.BLOB.BYTEA |
bytea |
MYSQL.BLOB.TINYBLOB |
TINYBLOB (max 255 B) |
MYSQL.BLOB.BLOB |
BLOB (max 64 KB) |
MYSQL.BLOB.MEDIUMBLOB |
MEDIUMBLOB (max 16 MB) |
MYSQL.BLOB.LONGBLOB |
LONGBLOB (max 4 GB) |
MYSQL.BLOB.BINARY(n) / VARBINARY(n) |
BINARY(n) / VARBINARY(n) |
SQLITE.BLOB.BLOB |
BLOB |
GeoPoint has no native override
s.point() is one fixed EPSG:4326 language. It has no native-type argument and
there is no PG.POINT namespace. Migrations select the portable physical form:
PostGIS geography(Point,4326), MySQL POINT SRID 4326, or the checked SQLite
JSON carrier. See GeoPoint.