Mormot2 Orm

mormot2-orm

mORMot 2 ORM layer: declarative TOrm classes, TOrmModel schema definition, field attribute mapping, virtual tables, and CRUD/batch operations through TRest descendants. This skill is authoritative for the mormot.orm.* namespace and assumes the conventions defined in mormot2-core (RawUtf8 strings, custom RTTI, layered uses). Sibling skills extend this layer: mormot2-rest-soa exposes models over HTTP/REST, mormot2-db handles raw SQL when the ORM is the wrong tool.

When to use

• Defining a TOrm descendant with typed published properties.
• Building a TOrmModel from a list of TOrm classes (single or multi-database).
• Mapping field attributes (stored AS_UNIQUE, index N, width M, default).
• Configuring virtual tables (TOrmVirtualTableJson, TOrmVirtualTableCsv, TOrmVirtualTableExternal).
• ORM CRUD via TRest.Add, TRest.Update, TRest.Retrieve, TRest.Delete.
• Batch operations via TRestBatch (insert/update/delete sequences with one round-trip commit).

When NOT to use

• Raw SQL queries, joins, or custom DDL. Use mormot2-db.
• Exposing the ORM as REST endpoints, interface-based services, or SOA contracts. Use mormot2-rest-soa.
• Authentication, sessions, JWT, password hashing. Use mormot2-auth-security for the crypto, mormot2-rest-soa for the REST session lifecycle.
• Foundational types (RawUtf8, TDocVariant), unit ordering, custom record RTTI. Use mormot2-core.

Core idioms

1. Defining a TOrm class

Every persisted field is a published property with a mORMot-friendly type. RowID is implicit; do not declare it.

uses
  mormot.core.base,
  mormot.orm.core;

type
  TOrmUser = class(TOrm)
  private
    fEmail: RawUtf8;
    fRole:  RawUtf8;
    fLogon: TDateTime;
  published
    property Email: RawUtf8   read fEmail write fEmail;
    property Role:  RawUtf8   read fRole  write fRole;
    property Logon: TDateTime read fLogon write fLogon;
  end;

2. Building a TOrmModel

A model is the schema. Pass it to the TRest (server or client). Free it during shutdown after the TRest is gone.

uses
  mormot.orm.core;

var
  Model: TOrmModel;
begin
  Model := TOrmModel.Create([TOrmUser, TOrmRole, TOrmAuditLog]);
  try
    // pass Model to TRestServerDB.Create / TRestClientDB.Create
  finally
    Model.Free; // after the TRest is destroyed
  end;
end;

3. Unique index and width attributes

stored AS_UNIQUE adds a SQL UNIQUE constraint; index N sets max length for RawUtf8 (SQL VARCHAR(N)). Both are declarative and read by the ORM at model-creation time.

type
  TOrmUser = class(TOrm)
  published
    property Email: RawUtf8 index 120 read fEmail write fEmail
      stored AS_UNIQUE; // SQL: VARCHAR(120) UNIQUE NOT NULL
    property Slug:  RawUtf8 index 64  read fSlug  write fSlug;
  end;

4. Registering a virtual table

A virtual table maps a TOrm class to a non-SQLite backend (JSON file, CSV, external SQL connection). Register it on the model before opening the database.

uses
  mormot.orm.core,
  mormot.orm.storage;

var
  Model: TOrmModel;
begin
  Model := TOrmModel.Create([TOrmCsvRow]);
  Model.VirtualTableRegister(TOrmCsvRow, TOrmVirtualTableJson); // or Csv / External
  // ORM verbs (Retrieve, RetrieveListJson, Add) work transparently against the backend.
end;

5. Batch insert with commit at end

TRestBatch queues operations and ships them in a single round trip. Use it for bulk imports, scheduled syncs, or any path that touches many rows.

uses
  mormot.orm.core;

var
  Batch: TRestBatch;
  User: TOrmUser;
  i: Integer;
begin
  Batch := TRestBatch.Create(Server.Orm, TOrmUser, 1000);
  try
    User := TOrmUser.Create;
    try
      for i := 0 to High(Inputs) do
      begin
        User.Email := Inputs[i].Email;
        User.Role  := 'member';
        Batch.Add(User, true);
      end;
    finally
      User.Free;
    end;
    Server.Orm.BatchSend(Batch); // single commit
  finally
    Batch.Free;
  end;
end;

Common pitfalls

• Forgetting the published modifier. Properties under public or private are invisible to RTTI and the ORM ignores them silently. Symptom: column missing from generated SQL, no error.
• Naming a field after a SQL reserved word. A property called Group, Order, User, or Index may compile but blow up at first SELECT. Either rename the Pascal property or alias the SQL name.
• Modifying TOrm.ID directly. The ORM owns RowID. Set it only via Add (which assigns the new ID back) or Retrieve (which reads it). Never declare a published property ID either; TOrm already exposes it.
• Using string for persisted text fields. Switch to RawUtf8. string will compile but every read/write pays a UTF-16 round trip, and JSON serialization may corrupt non-ASCII. See mormot2-core for the RawUtf8 boundary rule.
• Leaking the model on shutdown. TOrmModel is owned by your code, not by the TRest. After Server.Free, call Model.Free. Free in the wrong order and you double-free or leak RTTI structures.
• Calling Add with SendData=false then expecting the row to come back. SendData controls whether non-default fields are written. With false you get an empty row plus the new ID, not the values you set on the instance.

See also

• $MORMOT2_DOC_PATH/mORMot2-SAD-Chapter-05.md - TOrm and TOrmModel
• $MORMOT2_DOC_PATH/mORMot2-SAD-Chapter-06.md - ORM Daily Patterns
• references/torm-cheatsheet.md
• references/torm-model-patterns.md
• references/virtual-tables.md
• mormot2-core for RawUtf8 conventions and custom record RTTI
• mormot2-db for non-ORM SQL access
• mormot2-rest-soa for exposing the ORM as REST

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: flydev-fr
• Source: flydev-fr/mormot2-superpowers
• License: MIT

Install and usage instructions live in the source repository linked above.