Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

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, mysql and sqlite, 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 the pg entry and is refused as the mysql one;
  • 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, and s.string({ pg: PG.INT.INTEGER }) is accepted just the same and gives that string an integer column.

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.

Was this page helpful?