Data types

DataTypes is the vocabulary you use to declare a column. Each type is a small
class that knows how to render itself as SQL, and each dialect gets the final
say — getDataTypeSql() on the dialect turns a DataTypes.BOOLEAN() into
BOOLEAN on PostgreSQL, TINYINT(1) on MySQL, BIT on SQL Server and
INTEGER on SQLite. Writing DataTypes.BOOLEAN() once therefore gives you a
column that behaves the same everywhere without you tracking the differences.

import { Prorm, DataTypes } from 'ts-prorm-orm';

const prorm = new Prorm({ dialect: 'postgres', database: 'shop' });

const Product = prorm.define('Product', {
  id:        { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  sku:       { type: DataTypes.STRING(64), allowNull: false, unique: true },
  price:     { type: DataTypes.DECIMAL(10, 2), allowNull: false },
  inStock:   { type: DataTypes.BOOLEAN(), defaultValue: true },
  tags:      { type: DataTypes.ARRAY(DataTypes.STRING(50)) },
  attributes:{ type: DataTypes.JSONB() },
  releasedAt:{ type: DataTypes.DATE() },
});

The same model on four engines. Note what each one does with the array and the
JSON column:

[!WARNING]
On MySQL the tags array became a VARCHAR(255). An ARRAY of fifty tags
will not fit, and nothing in the database knows it is a list. If the model
must run on more than PostgreSQL, declare lists as JSON() instead.

A type travels two ways: out through the dialect when the table is created, and
back in through the coercion step when a row is read.

Every factory is also exported at the package root, so DataTypes.STRING(100)
and STRING(100) are the same thing:

import { STRING, INTEGER, JSONB } from 'ts-prorm-orm';

Cross-dialect SQL mapping

The exact SQL each type produces, taken from the dialects' own
getDataTypeSql(). Split by family rather than given as one wide grid, because
what you usually want is one row, not a comparison of all twenty-one.

In 4.x the default typeMapping: 'legacy' keeps 4.8's DDL for existing
databases; see Type mapping for how it differs from
'modern' and which columns need a manual migration.

Text

DataType	SQLite	PostgreSQL	MySQL	SQL Server	Oracle
`STRING(100)`	`VARCHAR(100)`	`VARCHAR(100)`	`VARCHAR(100)`	`NVARCHAR(100)`	`VARCHAR2(100)`
`STRING()`	`VARCHAR(255)`	`VARCHAR(255)`	`VARCHAR(255)`	`NVARCHAR(255)`	`VARCHAR2(255)`
`CHAR(2)`	`CHAR(2)`	`CHAR(2)`	`CHAR(2)`	`CHAR(2)`	`CHAR(2)`
`TEXT()`	`TEXT`	`TEXT`	`TEXT`	`NVARCHAR(MAX)`	`CLOB`

Numbers

DataType	SQLite	PostgreSQL	MySQL	SQL Server	Oracle
`INTEGER()`	`INTEGER`	`INTEGER`	`INT`	`INT`	`NUMBER(10)`
`BIGINT()`	`BIGINT`	`BIGINT`	`BIGINT`	`BIGINT`	`NUMBER(19)`
`FLOAT()`	`FLOAT(10,2)`	`REAL`	`FLOAT`	`FLOAT`	`FLOAT`
`DOUBLE()`	`DOUBLE(10,2)`	`DOUBLE PRECISION`	`DOUBLE`	`FLOAT`	`FLOAT`
`DECIMAL(10,2)`	`DECIMAL(10,2)`	`DECIMAL(10,2)`	`DECIMAL(10,2)`	`DECIMAL(10,2)`	`NUMBER(10,2)`

Only DECIMAL is exact. FLOAT and DOUBLE are binary floating point on
every engine here — never use them for money.

Dates, times and booleans

DataType	SQLite	PostgreSQL	MySQL	SQL Server	Oracle
`BOOLEAN()`	`INTEGER`	`BOOLEAN`	`TINYINT(1)`	`BIT`	`NUMBER(1)`
`DATE()`	`DATETIME`	`TIMESTAMP`	`DATETIME`	`DATETIME2`	`DATE`
`DATEONLY()`	`DATE`	`DATE`	`DATE`	`DATE`	`DATE`
`TIME()`	`TIME`	`TIME`	`TIME`	`TIME`	`VARCHAR2(8)`

Oracle's DATE carries a time component, so DATEONLY() and DATE() are the
same column type there; the difference is in how prorm reads the value back.

Structured and binary

DataType	SQLite	PostgreSQL	MySQL	SQL Server	Oracle
`JSON()`	`TEXT`	`JSON`	`JSON`	`NVARCHAR(MAX)`	`CLOB`
`JSONB()`	`TEXT`	`JSONB`	`JSON`	`NVARCHAR(MAX)`	`BLOB`
`BLOB()`	`BLOB`	`BYTEA`	`BLOB`	`VARBINARY(MAX)`	`BLOB`
`UUID()`	`TEXT`	`UUID`	`CHAR(36)`	`UNIQUEIDENTIFIER`	`RAW(16)`
`ARRAY(INTEGER())`	`TEXT`	`INTEGER[]`	`VARCHAR(255)`	`NVARCHAR(255)`	`VARCHAR2(255)`

Where JSON is stored as TEXT, values still round-trip as parsed JavaScript
values — the encoding happens in prorm rather than the engine.

Special

DataType	SQLite	PostgreSQL	MySQL	SQL Server	Oracle
`ENUM('a','b')`	`TEXT CHECK(col IN ('a','b'))`	`VARCHAR(255) CHECK (col IN ('a', 'b'))` †	`ENUM('a','b')`	`NVARCHAR(255)`	`VARCHAR2(255)`
`GEOMETRY()`	`BLOB`	`geometry`	`GEOMETRY`	`GEOMETRY`	`SDO_GEOMETRY`
`VIRTUAL()`	(no column)	(no column)	(no column)	(no column)	`VARCHAR2(255)`

† PostgreSQL has no inline ENUM(...) column syntax, so under the default
'legacy' mapping ENUM() is a string column with a CHECK on the value set
(CockroachDB too). Before 4.9.2 it was a bare VARCHAR(255) that accepted any
value. For a real enum type, create it first and reference it by name — see
Enums below.

Two more mappings worth knowing because they change the column, not just its
type name:

INTEGER()/BIGINT() with autoIncrement: true become SERIAL/BIGSERIAL
on PostgreSQL, and INTEGER PRIMARY KEY AUTOINCREMENT on SQLite.

ARRAY only exists natively on PostgreSQL. On every other dialect the column
degrades to a string; store JSON instead if you need portability.

The type catalogue

Strings

Type	Notes
`STRING(length?, binary?)`	`VARCHAR(length)`. With no length it renders `TEXT`. `binary: true` adds a `BINARY` collation.
`VARCHAR(length = 255, binary?)`	Always `VARCHAR`, never degrades to `TEXT`.
`CHAR(length = 1)`	Fixed width.
`TEXT('tiny' \| 'medium' \| 'long')`	Unbounded text; the size hint maps to `TINYTEXT`/`MEDIUMTEXT`/`LONGTEXT` on MySQL.
`TINYTEXT()`, `MEDIUMTEXT()`, `LONGTEXT()`	Explicit MySQL text sizes; portable fallbacks elsewhere.

Numbers

Type	Notes
`INTEGER()`, `TINYINT()`, `SMALLINT()`, `MEDIUMINT()`, `BIGINT()`	Integer family. All support `.UNSIGNED()` and `.ZEROFILL()` chaining on MySQL/MariaDB.
`FLOAT(length?, decimals?)`, `DOUBLE(...)`, `REAL(...)`	Approximate numerics.
`DECIMAL(precision, scale)`	Exact numerics — use this for money.

{ views: { type: DataTypes.INTEGER().UNSIGNED() } }  // INT UNSIGNED on MySQL

BIGINT values are handed back as JavaScript BigInt when the driver returns
one; Prorm deliberately does not coerce them to Number, which would lose
precision past 2^53.

[!CAUTION]
Whether the driver returns a BigInt is the driver's choice. With the default
SQLite driver settings, writing 9007199254740993n and reading it back gives
the number 9007199254740992 — off by one, silently. Keep identifiers that can
exceed 2^53 (snowflake IDs, external account numbers) in a STRING column, or
enable your driver's big-number mode.

Booleans

BOOLEAN() — read back as a real true/false regardless of whether the
database stored 1, '1' or true.

Dates and times

Type	Notes
`DATE(precision?, timezone?)`	Date and time. `timezone: true` gives a zoned type under `typeMapping: 'modern'` (see below).
`DATEONLY()`	Calendar date, no time.
`TIME(precision?, timezone?)`	Time of day.
`DATETIME()`, `TIMESTAMP()`	Dialect-specific spellings when you want them explicitly.
`NOW`	Not a column type — a default value meaning "the database's current timestamp".

Values come back as Date instances even on SQLite, which stores them as ISO
strings.

The time zone flag is one of the places where the type mapping
mode changes the column. DataTypes.DATE(3, true) renders:

`typeMapping`	SQLite	PostgreSQL	MySQL	SQL Server	Oracle
`'legacy'` (default)	`DATETIME`	`TIMESTAMPTZ(3)`	`DATETIME(3)`	`DATETIME2(3)`	`DATE`
`'modern'`	`DATETIME`	`TIMESTAMPTZ(3)`	`TIMESTAMP(3)`	`DATETIMEOFFSET(3)`	`TIMESTAMP(3) WITH TIME ZONE`

[!IMPORTANT]
Under the default 'legacy' mapping, PostgreSQL (and the rest of the
PostgreSQL family) keeps the zone: DATE(3, true) is TIMESTAMPTZ(3).
Before 4.9.2 the legacy mapping dropped it and created TIMESTAMP(3), so a
column created by an older release has no zone until you migrate it. MySQL, SQL Server
and Oracle still drop the zone under 'legacy'; opt in with
new Prorm({ …, typeMapping: 'modern' }) for new databases.

const attributes = { createdAt: { type: DataTypes.DATE(), defaultValue: DataTypes.NOW } };

DataTypes.NOW, DataTypes.NOW() and fn('NOW') all become the engine's
current-timestamp default: DEFAULT CURRENT_TIMESTAMP on most engines,
now() on ClickHouse and CURRENT_TIMESTAMP() on BigQuery.

[!NOTE]
Older releases wrote the uncalled DataTypes.NOW as the text
DEFAULT '() => new NOW()' on the PostgreSQL and MySQL families, HANA, Db2,
BigQuery, Snowflake, ClickHouse and DuckDB, and createTable() failed on
Firebird, Teradata, Vertica and several others. A table created that way
still has the text default; replace it with changeColumn or a migration.

Identifiers

Type	Notes
`UUID()`	Native `UUID`/`UNIQUEIDENTIFIER` where available, `CHAR(36)`/`TEXT` elsewhere.
`UUIDV1`, `UUIDV4`	Default-value generators, not column types. Use them as a `defaultValue` (see below).

A generator default — DataTypes.UUIDV4, DataTypes.UUIDV1 or any
function such as () => randomUUID() — is evaluated in JavaScript by
create() and written in the INSERT. It is not a database default, so the
CREATE TABLE carries no DEFAULT for it:

const Order = prorm.define(
  'Order',
  {
    publicId: { type: DataTypes.UUID(), allowNull: false, unique: true, defaultValue: DataTypes.UUIDV4 },
    total: { type: DataTypes.DECIMAL(10, 2), allowNull: false },
  },
  { tableName: 'orders', timestamps: false },
);

await Order.create({ total: 12.5 });

Rows inserted outside Prorm (raw SQL, another service) get no value, so keep
allowNull: false on such a column. DataTypes.NOW is different: it stays a
SQL default (CURRENT_TIMESTAMP).

[!NOTE]
Before 4.9.2, define() models did not send a generator default in the
INSERT, and CREATE TABLE carried the generator's source text as the
column default (DEFAULT '() => new UUIDV4()'), which is what those rows
stored. Tables created by an older release may still have that default;
drop it with changeColumn or a migration.

JSON

Type	Notes
`JSON()`	Objects and arrays are serialised on write and parsed back on read.
`JSONB()`	PostgreSQL binary JSON — indexable, supports containment operators. Falls back to `JSON`/`TEXT` elsewhere.
`HSTORE()`	PostgreSQL key/value column.

Assigning a plain object is enough — it is serialised on write:

await Product.create({ attributes: { colour: 'red', sizes: [1, 2, 3] } });

You get a JavaScript value back on read, on every dialect:

const p = await Product.findByPk(1);
p.attributes.colour;   // 'red' — already parsed

Where the driver returns a real object (PostgreSQL JSON/JSONB, MySQL JSON)
it is passed through untouched. Where the column is stored as text — SQLite, and
any dialect whose mapping in the table above is TEXT/NVARCHAR(MAX)/CLOB —
the string is parsed. Text that is not valid JSON is returned verbatim rather
than throwing.

Behaviour change. Models built with prorm.define() used to hand back the
raw JSON string on text-backed dialects. If you have code doing its own
JSON.parse() on SQLite, drop it — it would now be parsing an object.
PostgreSQL and MySQL are unaffected.

Filter inside a document with Op.json(path) nested under the column (not a
dotted attribute name — a dotted key is treated as a column name and the
database will reject it):

import { Op } from 'ts-prorm-orm';

await Product.findAll({ where: { attributes: { [Op.json('$.colour')]: 'red' } } });

Which engines can render that path predicate today:

Not rendered: the call throws UnsupportedWhereError ("the operator
'.colour' is not implemented by its WHERE builder. The statement was not
sent…"). Use the PostgreSQL JSON operators from
Query operators instead.

Not rendered: the call throws UnsupportedWhereError before anything is sent,
rather than running the query without the condition.

Not rendered: the call throws UnsupportedWhereError before anything is sent.

The full set of JSON predicates — Op.contains, Op.keyExists,
Op.jsonHasKey, Op.jsonPathExists and the PostgreSQL @> / -> / #>
family — is in Query operators.

Binary

BLOB('tiny' | 'medium' | 'long'), TINYBLOB(), MEDIUMBLOB(),
LONGBLOB(), BINARY(length), VARBINARY(length).

Enums

ENUM(...values) renders a real ENUM on MySQL/MariaDB, a
TEXT CHECK(col IN (...)) on SQLite and a VARCHAR(255) CHECK (col IN (...))
on PostgreSQL — all three enforce the value set in the database.

On PostgreSQL, create the type once and reference it by name:

const pg = prorm.getDialectInstance();
await pg.createEnumType('user_role', ['admin', 'user'], { ifNotExists: true });

prorm.define('User', {
  role: { type: 'user_role' as any, defaultValue: 'user' },
});

dropType(name, { ifExists, cascade }) removes it again. A plain string is
always accepted as a type and passed through verbatim, which is the escape
hatch for any type Prorm has no factory for.

Collections and structures

Type	Available on
`ARRAY(subtype)`	PostgreSQL (`INTEGER[]`), degrades elsewhere
`LIST(subtype)`, `STRUCT(fields)`, `MAP(key, value)`, `UNION(...)`	DuckDB / ClickHouse / Databricks-style nested types
`SET(...values)`	MySQL/MariaDB
`RANGE(subtype)`	PostgreSQL range types (`int4range`, `tstzrange`, …)

Network types

INET(), INET6(), CIDR(), MACADDR() — PostgreSQL native, text elsewhere.

Spatial

GEOMETRY(type?, srid?), GEOGRAPHY(type?, srid?), plus the concrete shapes
POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING,
MULTIPOLYGON, GEOMETRYCOLLECTION.

{ location: { type: DataTypes.GEOGRAPHY('POINT', 4326) } }

On PostgreSQL this needs PostGIS — await prorm.createExtension('postgis').

Virtual columns

VIRTUAL() and VIRTUALSTRING() declare an attribute that has no database
column at all: getDataTypeSql() returns an empty string and the column is
omitted from CREATE TABLE and from every INSERT/UPDATE. The value is
computed in JavaScript by the attribute's get. See
Virtual fields.

const attributes = {
  firstName: { type: DataTypes.STRING(50) },
  lastName:  { type: DataTypes.STRING(50) },
  fullName:  {
    type: DataTypes.VIRTUAL(),
    get(this: { firstName: string; lastName: string }) {
      return `${this.firstName} ${this.lastName}`;
    },
  },
};

Type coercion on read

Rows arriving from the driver are cast back to the JavaScript type the
attribute declares, because wire formats differ per dialect. A round trip on
SQLite, where every one of these is stored as text or an integer:

const Line = prorm.define('Line', {
  sku: DataTypes.STRING(64),
  price: DataTypes.DECIMAL(10, 2),
  inStock: { type: DataTypes.BOOLEAN(), defaultValue: true },
  attributes: DataTypes.JSON(),
  at: DataTypes.DATE(),
});
await prorm.sync();

await Line.create({ sku: 'A', price: '19.99', attributes: { colour: 'red' }, at: new Date('2026-03-01T09:30:00Z') });
const row = await Line.findOne({ where: { sku: 'A' } });

typeof row.price;          // 'number'  (19.99; sent as '19.99')
row.inStock;               // true      (stored as 1)
row.attributes.colour;     // 'red'     (stored as '{"colour":"red"}')
row.at instanceof Date;    // true      (stored as an ISO string)

Declared type	Coerced to
`INTEGER`, `SMALLINT`, `MEDIUMINT`, `TINYINT`	`number`
`BIGINT`	`number`, or left as `BigInt` when the driver returns one
`FLOAT`, `DOUBLE`, `DECIMAL`, `REAL`	`number` — so a `DECIMAL(20,4)` beyond 15–16 significant digits loses precision in JavaScript
`BOOLEAN`	`true` / `false` (from `1`/`0`/`'1'`/`'0'`)
`DATE`, `DATEONLY`	`Date`
`JSON`, `JSONB`	parsed object / array; unparseable text is returned verbatim

Unparseable values are left untouched rather than turned into NaN or an
Invalid Date.

TypeScript

Attribute types are declared separately from the runtime data types; the
InferAttributes helpers keep the two in step:

import { Model, InferAttributes, InferCreationAttributes, CreationOptional } from 'ts-prorm-orm';

class User extends Model<InferAttributes<User>, InferCreationAttributes<User>> {
  declare id: CreationOptional<number>;   // optional on create, present on read
  declare email: string;
  declare bio: string | null;
}

See TypeScript types for the full set.

Next: Querying — the finders, and how one FindOptions becomes SQL.

Defining models

Virtual fields

Query operators — JSON and array operators

Dialects — per-engine type notes and caveats

Type mapping — typeMapping: 'legacy' vs 'modern'

Cross-dialect SQL mapping#

Text#

Numbers#

Dates, times and booleans#

Structured and binary#

Special#

The type catalogue#

Strings#

Numbers#

Booleans#

Dates and times#

Identifiers#

JSON#

Binary#

Enums#

Collections and structures#

Network types#

Spatial#

Virtual columns#

Type coercion on read#

TypeScript#

Related reading#

Cross-dialect SQL mapping

Text

Numbers

Dates, times and booleans

Structured and binary

Special

The type catalogue

Strings

Numbers

Booleans

Dates and times

Identifiers

JSON

Binary

Enums

Collections and structures

Network types

Spatial

Virtual columns

Type coercion on read

TypeScript

Related reading