Data types

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. Every factory is also exported at the package root, so DataTypes.STRING(100) and STRING(100) are the same thing: 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. 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')) | ENUM('a','b') † | ENUM('a','b') | NVARCHAR(255) | VARCHAR2(255) | GEOMETRY() | BLOB | GEOMETRY | GEOMETRY | GEOMETRY | SDOGEOMETRY | VIRTUAL() | (no column) | (no column) | (no column) | (no column) | VARCHAR2(255) | † PostgreSQL has no inline ENUM(...) column syntax. Create the type 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. | 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. 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 TIMESTAMP WITH TIME ZONE on PostgreSQL. | 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. Identifiers Type | Notes | --- | --- | UUID() | Native UUID/UNIQUEIDENTIFIER where available, CHAR(36)/TEXT elsewhere. | UUIDV1, UUIDV4 | Default-value generators, not column types: { type: DataTypes.UUID(), defaultValue: DataTypes.UUIDV4 }. | 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: You get a JavaScript value back on read, on every dialect: 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): 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 and a TEXT CHECK(col IN (...)) on SQLite — both enforce the value set in the database. On PostgreSQL, create the type once and reference it by name: 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. 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. 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: 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 | 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: See TypeScript types for the full set. Next: Querying — the finders, and how one FindOptions becomes SQL. Related reading Defining models Virtual fields Query operators — JSON and array operators Dialects — per-engine type notes and caveats