Data Types

The Thirteen Members

There are exactly thirteen, and they are not open-ended — a column needs one of these:

import { DataTypes } from "stabilize-orm";

DataTypes.STRING    DataTypes.TEXT      DataTypes.INTEGER
DataTypes.BIGINT    DataTypes.FLOAT     DataTypes.DOUBLE
DataTypes.DECIMAL   DataTypes.BOOLEAN   DataTypes.DATE
DataTypes.DATETIME  DataTypes.JSON      DataTypes.UUID
DataTypes.BLOB

// There is no DataTypes.TIME. A time-only column has no member here --
// use DATETIME, or a STRING with a format of your own.

The Mapping

What each member becomes per backend. The widths shown are the defaults — length widens STRING, and precision with scale sizes DECIMAL. See below:

An unrecognised type falls back rather than failing: TEXT on Postgres, MySQL and SQLite, NVARCHAR(MAX) on SQL Server — T-SQL's own TEXT is deprecated and unusable in most expressions, so it is never emitted — and string on MongoDB.

The MongoDB column is a list rather than a single type because it is a $jsonSchema validator rather than a column declaration, and several members accept more than one BSON type on purpose: a JavaScript integer arrives as an int inside 32-bit range and a double beyond it, so accepting only int would reject every id past two billion.

length, precision and scale

These three options shape the emitted type, and the value written to it is checked against the same limit. Declaring { type: DataTypes.STRING, length: 50 } produces VARCHAR(50) on MySQL, and a 60-character string is rejected rather than stored.

const Post = defineModel({
  tableName: "posts",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    title: { type: DataTypes.STRING, length: 5 },
    price: { type: DataTypes.DECIMAL, precision: 12, scale: 4 },
  },
});

// Emitted DDL
//   MySQL     title VARCHAR(5)             price DECIMAL(12,4)
//   MSSQL     title NVARCHAR(5)            price DECIMAL(12,4)
//   Postgres  title TEXT                   price DECIMAL(12,4)
//   SQLite    title TEXT                   price NUMERIC

// And a value over the limit is rejected:
await postRepo.create({ id: "p1", title: "a great deal longer than five" });
// StabilizeError: Field title too long   (code: VALIDATION_ERROR)

Postgres and SQLite have no width to write. TEXT and VARCHAR(n) are the same type in Postgres with no performance difference, and SQLite's NUMERIC keeps no scale — so emitting a width there would claim a constraint the server does not enforce. The limit is applied in process instead, so the rule holds on every dialect even where the DDL cannot express it.

length and maxLength together

Each keeps the meaning it already had, which matters for models written before any of this was enforced:

• length alone is both the column width and the validation limit.
• maxLength alone stays validation-only. It changes no DDL, exactly as before.
• Given both, length sets the column width and maxLength is what a value is checked against.

minLength, pattern and customValidator are unaffected and remain checked by the library rather than by the database. See Validation.

Where the Dialects Disagree

The mapping is not a tidy one-to-one. These are the differences that change behaviour:

STRING is bounded on MySQL and SQL Server, unbounded elsewhere

DataTypes.STRING becomes TEXT on Postgres and SQLite — no length limit at all — but VARCHAR(255) on MySQL and NVARCHAR(255) on SQL Server. That 255 is the default, not a ceiling: declaring length: 1000 widens the column to VARCHAR(1000) and the value is checked against 1000. Leave length off and the 255 stands, so a value that fits comfortably on Postgres is truncated at 255 characters on MySQL, or rejected outright on SQL Server, depending on the server mode. Use DataTypes.TEXT when the value might be long, or name the width you need.

SQLite collapses several types

SQLite has no native boolean, date or decimal type, so BIGINT becomes INTEGER, BOOLEAN becomes INTEGER, and DATE, DATETIME, JSON and UUID all become TEXT. Dates are stored as strings, which is why the ORM binds ISO strings rather than Date objects on this dialect.

DECIMAL defaults to 10,2 everywhere

DataTypes.DECIMAL becomes DECIMAL(10,2) on MySQL, SQL Server and Postgres — ten digits with two decimal places — and NUMERIC on SQLite, which is dynamically typed and keeps no scale. Declare precision and scale to size it yourself; without them the 10,2 default stands, and a value needing more than 10 significant digits will not fit. On SQLite the declared precision and scale are enforced by the ORM instead, since the column itself will not.

FLOAT and DOUBLE swap places

On MySQL FLOAT is FLOAT and DOUBLE is DOUBLE. On SQL Server FLOAT is REAL (4-byte) and DOUBLE is FLOAT (8-byte) — T-SQL's FLOAT means double precision. Reading the emitted DDL alone is therefore misleading; the DataTypes member is what states your intent.

Booleans are encoded differently everywhere

Postgres has a real BOOLEAN; MySQL uses TINYINT(1), SQLite an INTEGER and SQL Server a BIT. All four round-trip to a JavaScript boolean through the ORM, so this is only worth knowing when you write raw SQL against the table — a WHERE isActive = true that works on Postgres will not parse on SQLite.

MongoDB validates the type instead of declaring it

There is no column to declare on a document store, so the declared type becomes the $jsonSchema validator on the collection and a value of the wrong BSON type is rejected by the server, where a SQL backend would have coerced or truncated it. Two consequences follow from the mapping above. Nothing is bounded: DataTypes.STRING is a string of any length, so the 255-character limit MySQL and SQL Server impose does not exist here. And DataTypes.DECIMAL is a double, because MongoDB has no exact decimal unless the caller supplies a Decimal128 — so a money column loses precision the way any binary float does. Store the smallest unit as an INTEGER/BIGINT, or the value as a STRING. See MongoDB.

JSON Is the Least Portable Member

DataTypes.JSON maps to a genuine JSON type on only two dialects — JSONB on Postgres and JSON on MySQL. On SQLite it is TEXT and on SQL Server NVARCHAR(MAX), and the ORM does not parse a value back into an object on read: whatever the driver returns is what you get.

MariaDB is worth singling out because it is easy to mistake for MySQL: its JSON is an alias for LONGTEXT with a validity check, not a real type, so the driver reports the column as text and hands you the raw string. The same DataTypes.JSON declaration therefore round-trips as an object on MySQL and as a string on MariaDB.

// Fails on SQLite:
await postRepo.create({ id: "p1", meta: { tags: ["a"] } });
// Query failed after 1 attempt: Binding expected string, TypedArray,
// boolean, number, bigint or null

// Works -- serialise it yourself when targeting SQLite:
await postRepo.create({ id: "p1", meta: JSON.stringify({ tags: ["a"] }) });

The Column Named id Is Special

A column whose key is exactly id is treated as the primary key, and its type decides who assigns it. That decision is made from the DataTypes member, and it changes the emitted column in ways the table above does not show:

-- id: DataTypes.INTEGER -- the database generates it
Postgres      id SERIAL PRIMARY KEY
MySQL         id INT AUTO_INCREMENT PRIMARY KEY
SQLite        id INTEGER PRIMARY KEY AUTOINCREMENT
SQL Server    id INT IDENTITY(1,1) PRIMARY KEY
MongoDB       _id from stabilize_counters

-- id: DataTypes.STRING -- the caller supplies it
Postgres      id UUID PRIMARY KEY
MySQL         id VARCHAR(255) PRIMARY KEY
SQLite        id TEXT PRIMARY KEY
SQL Server    id NVARCHAR(255) PRIMARY KEY
MongoDB       _id string, caller-supplied

-- id: DataTypes.UUID -- the caller supplies it
Postgres      id UUID PRIMARY KEY
MySQL         id VARCHAR(255) PRIMARY KEY
SQLite        id TEXT PRIMARY KEY
SQL Server    id UNIQUEIDENTIFIER PRIMARY KEY
MongoDB       _id string, caller-supplied

An integer or otherwise non-string id is generated, so it must be omitted from a create payload. A string or UUID id is not, so it stays required — omitting it fails validation with Field id is required. Which side of that line you are on is decided solely by the type. On SQL Server there is an extra wrinkle for the generated case, since an IDENTITY column rejects an explicit value; see SQL Server. On MongoDB the same id column is stored as the document's _id, which is a storage detail inside the backend rather than something the API exposes — and an integer id there is allocated from a counters collection rather than by the server.

Encrypted Columns

An encrypted: true column stores Base64 ciphertext, so the string it holds is longer than the plaintext — roughly 1.4× plus about 55 characters of framing. A default STRING leaves you VARCHAR(255) / NVARCHAR(255) on MySQL and SQL Server, which a moderately long secret will overflow; you can widen it with length, but DataTypes.TEXT is the better answer because the ciphertext cannot be sized from the plaintext you have in mind. See Column Encryption.