MasterRecord

Field Types

One portable type vocabulary across three databases.

MasterRecord resolves each builder type to the right column on every engine, so the same model runs unchanged on SQLite, MySQL, and PostgreSQL. (Temporal types are the deliberate exception — they are stored as TEXT everywhere for portability; see below.)

Reference table#

Builder	PostgreSQL	MySQL	SQLite	JS type
`integer()`	INTEGER	INT	INTEGER	number
`bigint()`	BIGINT	BIGINT	INTEGER	number
`float()`	REAL	FLOAT	REAL	number
`decimal()`	DECIMAL	DECIMAL	REAL	number
`string()`	VARCHAR(255)	VARCHAR(255)	TEXT	string
`text()`	TEXT	TEXT	TEXT	string
`mediumtext()`	TEXT	MEDIUMTEXT	TEXT	string
`longtext()`	TEXT	LONGTEXT	TEXT	string
`boolean()`	BOOLEAN	TINYINT(1)	INTEGER	boolean
`date()`	TEXT	TEXT	TEXT	string
`time()`	TEXT	TEXT	TEXT	string
`datetime()`	TEXT	TEXT	TEXT	string
`timestamp()`	TEXT	TEXT	TEXT	string
`json()`	JSON	JSON	TEXT	object*
`uuid()`	UUID	VARCHAR(36)	TEXT	string
`binary()`	BYTEA	BLOB	BLOB	Buffer

Note

* Pair json() with a transformer(.set()/.get() or .transform()) to (de)serialize objects — or use db.owned(Class) / db.owned(), which serializes automatically and hydrates the class on read (see Advanced Modeling).

Boolean handling#

On SQLite and MySQL there is no native boolean, so MasterRecord stores 1/0(INTEGER / TINYINT(1)) and, since 1.22.1, materializes the column back as a real true/false on every read path (toList(), find(), single(), first(), tracked or asNoTracking()) — EF Core’s value conversion. null is preserved, a custom .transform() still wins, and boolean query parameters (where((x) => x.flag == $$, true)) bind on every engine. Before 1.22.1 an entity read back on SQLite/MySQL exposed 1/0, so an API could echo { published: true } after insert but { published: 1 } after a later read.

uuid, binary, json, bigint#

uuid() — native UUID on Postgres, VARCHAR(36) on MySQL, TEXT on SQLite; pair with .defaultSql('gen_random_uuid()') on Postgres or set it in a hook.
binary() — BYTEA / BLOB; read and write Buffers.
json() — native JSON on Postgres/MySQL, TEXT on SQLite; use owned() for typed value objects.
bigint() — BIGINT on Postgres/MySQL, INTEGER on SQLite (64-bit); values arrive as JavaScript numbers.

Dates & times#

Date/time columns are stored as TEXT on every engine (SQLite, MySQL, and PostgreSQL) for cross-engine portability — the same value round-trips identically everywhere. Write ISO-8601 strings or epoch milliseconds (or set them in a lifecycle hook). This is why a model that works on SQLite also works unchanged on MySQL/Postgres: a nativeTIMESTAMP/DATETIME column would reject the epoch-millis / ISO strings the app writes.

timestamps.js

export default class Post {
  id(db)         { db.integer().primary().auto(); }
  published_at(db) { db.datetime(); }

  beforeSave() {
    if (this.__state === 'insert') this.published_at = new Date().toISOString();
  }
}

The generic escape hatch#

Need a type not listed? Use db.type(name, size) directly:

javascript

price(db) { db.type('numeric', '10,2'); }

Defining Models →

Use these types in your entities.

Advanced Modeling →

Owned types, computed columns, constraints.

Transformers →

Serialize complex values.