---
title: Native Types
description: Override most scalar column types with database-specific native types
icon: database
---

Most scalar factories accept an optional native type as their first argument to
override the default column type:

```ts
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:

```ts
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](/docs/schema/scalars/string#storage).

## 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:

```ts
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](/docs/schema/scalars/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](/docs/schema/scalars/point).
