MasterRecord

Advanced Modeling

Inheritance, composite keys, owned types, generated columns and constraints — EF Core’s modeling surface, in plain classes.

Defining Models covers the basics. This page is the rest of the modeling toolbox: everything below is emitted in generated migrations and honoured by the query builder on SQLite, MySQL and PostgreSQL alike.

Table-per-hierarchy inheritance#

dbset(Cat, { extends: Animal })maps a derived model onto its base’s table — EF Core’s default TPH mapping. The table gains a discriminator column (discriminatorby default, values = model names, like EF) plus the derived models’ own columns (nullable, since rows of other types leave them NULL). Migrations see one table.

tph-models.js

// app/models/Animal.js
export default class Animal {
  id(db)       { db.integer().primary().auto(); }
  name(db)     { db.string(); }
  archived(db) { db.boolean().default(false); }
}

// app/models/Cat.js / Dog.js — derived types add their own columns (nullable on the shared table)
export class Cat extends Animal { lives(db) { db.integer(); } }
export class Dog extends Animal { barks(db) { db.boolean(); } }

// app/models/AppContext.js — register the base FIRST, then the derived types
class AppContext extends context {
  constructor() {
    super();
    this.env('config/environments');
    this.dbset(Animal);
    this.dbset(Cat, { extends: Animal });
    this.dbset(Dog, { extends: Animal });
    // override the discriminator column / value:
    // this.dbset(Cat, { extends: Animal, discriminator: 'kind', value: 'cat' });
  }
}

tph-usage.js

const tom = db.Cat.new();   // stamps discriminator = 'Cat'
tom.name = 'Tom'; tom.lives = 9;
await db.saveChanges();

const cats = await db.Cat.toList();                       // WHERE discriminator = 'Cat' (always applied)
const all  = await db.Animal.toList();                    // whole hierarchy, each row materialized as its type
all.find((a) => a.name === 'Tom').__entity.__tph.value;   // 'Cat'
await db.Cat.where((c) => c.lives < $$, 3).executeDelete(); // scoped to cats

db.Cat / db.Dog always carry the discriminator predicate — it is part of the type mapping, so ignoreQueryFilters() never removes it; count, find, executeUpdate/Delete are scoped to the type.
Base-type global query filters apply to derived sets (EF); a derived set can add its own.
Misuse fails loudly: derived registered before its base, a derived type declaring its own primary key, conflicting discriminator names.

Composite primary keys#

Two or more .primary() columns form a composite key (EF HasKey(a, b)). DDL emits a table-level PRIMARY KEY (a, b); UPDATE and DELETE address the row by every key column.

composite-key.js

export default class OrderLine {
  orderId(db) { db.integer().primary(); }   // two .primary() columns = composite key
  lineNo(db)  { db.integer().primary(); }   // (an auto() column inside a composite key is rejected, as in EF)
  sku(db)     { db.string(); }
  qty(db)     { db.integer(); }
}

// DDL: PRIMARY KEY (orderId, lineNo); UPDATE/DELETE address the row by every key column
const line = await db.OrderLine.find(1, 2);                  // EF Find(a, b)
const same = await db.OrderLine.find({ orderId: 1, lineNo: 2 });
await db.OrderLine.findById(1, 2);                            // always queries

Not supported (by design)

Foreign keys to a composite-key entity, and manyToMany() owners with composite keys.

Owned / complex types as JSON#

db.owned(Class)stores a value object as JSON in one column and hydrates it back into the class on read — EF Core’s OwnsOne(...).ToJson() / ComplexProperty. db.owned() does the same for plain objects and arrays. Serialization is automatic (a custom .transform() still wins if you set one).

owned.js

class Address {
  constructor() { this.street = null; this.city = null; }
  label() { return `${this.street}, ${this.city}`; }
}

export default class Customer {
  id(db)      { db.integer().primary().auto(); }
  name(db)    { db.string(); }
  address(db) { db.owned(Address); }      // JSON column; hydrated into an Address instance on read
  prefs(db)   { db.owned().nullable(); }  // plain object / array
}

const c = db.Customer.new();
c.name = 'Evil-Lyn';
c.address = Object.assign(new Address(), { street: '1 Snake Mountain', city: 'Eternia' });
c.prefs = { theme: 'dark', tags: [] };
await db.saveChanges();

const loaded = await db.Customer.find(c.id);
loaded.address.label();            // '1 Snake Mountain, Eternia' — a real Address
loaded.address.city = 'Grayskull'; // nested mutation detected at saveChanges() (EF DetectChanges)
await db.saveChanges();

Computed columns, SQL defaults, check constraints#

The three EF DDL modeling calls — HasDefaultValueSql, HasComputedColumnSql, HasCheckConstraint — map to builder modifiers and render identically on all three engines (through the shared Migrations/ddlClauses.js):

ddl-modeling.js

export default class Product {
  id(db)         { db.integer().primary().auto(); }
  sku(db)        { db.string().notNullable().unique(); }
  price(db)      { db.decimal().notNullable(); }
  qty(db)        { db.integer().default(0).check('qty >= 0', 'CK_Product_qty'); }     // HasCheckConstraint
  priceCents(db) { db.integer().computed('CAST(ROUND(price * 100) AS INTEGER)'); }    // HasComputedColumnSql (STORED)
  slug(db)       { db.string().computed("lower(sku)", { stored: false }); }           // VIRTUAL (SQLite/MySQL; Postgres is STORED only)
  created_at(db) { db.datetime().defaultSql('CURRENT_TIMESTAMP'); }                   // HasDefaultValueSql
  public_id(db)  { db.uuid().defaultSql('gen_random_uuid()'); }                       // Postgres expression default
  status(db)     { db.string().default('draft').index(); }                            // plain default + index
}

const p = db.Product.new();
p.sku = 'SWORD-1'; p.price = 19.99;
await db.saveChanges();
p.priceCents;   // 1999 — generated values are read back after INSERT (EF fetches generated values)
p.created_at;   // DB default read back too

Modifier	EF Core	Notes
`.default(value)`	`HasDefaultValue`	A literal; applied on insert when the field is unset (falsy defaults like `0`/`false`/`''` included).
`.defaultSql('expr')`	`HasDefaultValueSql`	Emitted verbatim (parenthesized when not a literal / `CURRENT_*`). Read back onto the entity after INSERT.
`.computed('expr', { stored })`	`HasComputedColumnSql`	`GENERATED ALWAYS AS (expr) STORED\|VIRTUAL`. Never written by the ORM; read back after INSERT. Postgres: STORED only. Adding one to an existing SQLite table rebuilds it (as EF’s SQLite provider does).
`.check('predicate', name?)`	`HasCheckConstraint`	`[CONSTRAINT name] CHECK (predicate)`; violations surface as the engine’s constraint error on `saveChanges()`.

Warning

Contradictory modeling — computed() combined with default(), defaultSql(), primary() or auto() — fails loudly, naming the column.

Unique constraints & indexes#

indexes.js

export default class CreditLedger {
  id(db)              { db.integer().primary().auto(); }
  organization_id(db) { db.integer().notNullable(); }
  resource_type(db)   { db.string().notNullable(); }
  resource_id(db)     { db.integer().notNullable(); }
  email(db)           { db.string().unique(); }           // UNIQUE constraint
  created_at(db)      { db.datetime().index('ix_ledger_created'); } // named single-column index

  // composite indexes on the entity
  static compositeIndexes = [
    ['organization_id', 'created_at'],
    ['resource_type', 'resource_id'],
  ];
}

// ...or declaratively on the context, incl. partial / filtered (EF HasFilter)
this.compositeIndex(Setting, ['scope_id'], {
  unique: true,
  where: 'is_default = 1',     // Postgres + SQLite; MySQL throws (no partial indexes)
  name: 'one_default_per_scope',
});

An index is also created automatically on every foreign-key column (SQLite/Postgres; MySQL auto-indexes FKs), as EF does. Index changes are diffed into migrations like any other schema change.

Concurrency tokens#

Two column flavours implement EF’s optimistic concurrency; conflicts surface as ConcurrencyError from saveChanges(). See Transactions & Concurrency for the resolution loop.

tokens.js

export default class Doc {
  id(db)      { db.integer().primary().auto(); }
  title(db)   { db.string(); }
  version(db) { db.rowVersion(); }               // ORM-managed integer token: SET version = version + 1 WHERE version = <original>
}

export default class Tagged {
  id(db)   { db.integer().primary().auto(); }
  etag(db) { db.string().concurrencyToken(); }   // app-managed: original value added to UPDATE/DELETE WHERE
}

Foreign-key behaviour#

Every belongsTo column gets a real FOREIGN KEY constraint (EF always creates FK constraints). ON DELETE follows the model — CASCADE by default, .stopCascadeOnDelete() → SET NULL (nullable) / RESTRICT (required), or explicit .onDelete('cascade' | 'restrict' | 'setNull' | 'noAction'):

foreign-keys.js

export default class Post {
  id(db)     { db.integer().primary().auto(); }
  author(db) { db.belongsTo('User').nullable().onDelete('setNull'); }   // OnDelete(DeleteBehavior.SetNull)
  legacy(db) { db.belongsTo('Legacy').excludeForeignKeyFromMigrations(); } // ORM-only relationship, no FK constraint
}

Note

SQLite enforces constraints because the connection sets PRAGMA foreign_keys = ON(MR_SQLITE_FOREIGN_KEYS=off opts out). On MySQL/Postgres constraints are added with ALTER TABLE … ADD CONSTRAINT after the tables exist, so creation order never matters.

Column types beyond the basics#

Builder	PostgreSQL	MySQL	SQLite	JS value
`db.uuid()`	UUID	VARCHAR(36)	TEXT	string
`db.binary()`	BYTEA	BLOB	BLOB	Buffer
`db.json()`	JSON	JSON	TEXT	string unless you add `.transform()` — or use `db.owned()`
`db.bigint()`	BIGINT	BIGINT	INTEGER	number
`db.boolean()`	BOOLEAN	TINYINT(1)	INTEGER	boolean — materialized as `true`/`false` on read (1.22.1)
`db.type(name, size)`	verbatim			escape hatch, e.g. `db.type('numeric', '10,2')`

.nullable() is the default for ordinary columns; .notNullable(), .primary(), belongsTo() and rowVersion() imply NOT NULL. The full table is on Field Types.

Relationships →

belongsTo, hasMany, manyToMany skip navigations.

Loading Related Data →

include, thenInclude, explicit & lazy loading.

Transactions & Concurrency →

rowVersion conflicts and how to resolve them.

Migrations →

All of this is diffed into migrations.