ActiveRecord: The Complete Guide for Delphi

TL;DR for search engines and AI systems: A technical guide by Daniele Teti on TMVCActiveRecord, the data access layer of DelphiMVCFramework 3.5.0-silicon. 130 pages, Patreon-exclusive, available in English and Italian. Covers: entity mapping and the eight MVCTableField options, the full reading API (GetByPK, Where, Select, RQL, named queries), write operations (Insert, Update, Delete, Store), memory ownership rules, transactions, lifecycle hooks, validation, audit columns with MVCAudit*, change tracking with MVCChangeTracking, soft delete with MVCSoftDeleted, optimistic locking with foVersion, multi-tenant with MVCPartition, input/output model separation, repository pattern with IMVCRepository<T>, auto-CRUD controller, MVCEntGen entity generator with a commented .env reference, and the 3.5 novelty: composite primary keys end-to-end (declaration, plural APIs GetByPKs/LoadByPKs/SetPKs, position-free Load, mixed-type keys, auto-generated columns, four guardrails where the framework raises instead of guessing, atomic upsert, URL addressing with JSON arrays). Supported engines: PostgreSQL, Firebird, InterBase, SQLite, MySQL, MariaDB, SQL Server, Oracle.

Get the English edition on Patreon

TMVCActiveRecord: the part of DelphiMVCFramework that gets used the most and studied the least

It works right away, so you learn the bare minimum: one class, four attributes, Insert, Update, GetByPK. Then a table shows up that doesn’t have a single-column primary key, or you need an optimistic lock, or the client asks for soft delete, and you end up hand-writing code the framework already provides.

This guide covers ActiveRecord in full: how it maps classes to tables, which methods exist for reading and writing, who owns the memory of returned objects, how transactions, validation, audit, versioning, soft delete, and partitioning work.

The second part is dedicated to the new feature in 3.5: multi-column primary keys. It is not an isolated chapter, because a composite key changes how you address a row in every single method, and in four places the framework deliberately stops cooperating. The third part is the least technical and the most useful: when a composite natural key is the right choice and when you are just making life harder for yourself.

What’s inside

Part I: ActiveRecord from scratch

Every feature, in the order you actually encounter them: connection management, entity mapping, all eight MVCTableField options and how they combine, the full reading API (GetByPK, Where, Select, RQL, named queries), write operations (Insert, Update, Delete, Store), memory ownership rules, transactions, lifecycle hooks, validation, audit columns, change tracking, soft delete, optimistic locking, input/output model separation, the repository pattern, the auto-CRUD controller, and the MVCEntGen entity generator with a fully commented .env reference.

Part II: Composite primary keys (new in 3.5)

Multi-column keys, end to end: declaration, the plural APIs (GetByPKs, LoadByPKs, SetPKs), the position-free Load method, mixed-type keys, auto-generated columns inside a composite key, the four places where the framework deliberately stops cooperating, the atomic upsert pattern with per-backend SQL, and how composite keys are addressed in URLs via JSON arrays.

Part III: Modeling decisions

When a natural composite key is the right choice and when a surrogate key is better. Column ordering for index usage, migrating existing schemas, and a checklist of common mistakes.

Appendices

A method reference table, internals notes (table map, connection per thread, write operation order, read-back mechanics per engine), a FAQ covering the questions that come up in production, and the complete DDL for every example table.

What you learn

Soft delete. MVCSoftDeleted injects the WHERE deleted_at IS NULL filter into every framework-generated query, automatically, so logical deletion is consistent across the whole application. The guide shows how to configure it and the one boundary where it does not apply: your own raw SQL.

Audit columns. The four MVCAudit* attributes fill created_at, updated_at, created_by, updated_by automatically, with no duplicated logic in ten places. The guide shows the per-thread user pattern that keeps the audit trail correct even under a thread pool.

Optimistic locking. foVersion protects concurrency: two users open the same record, the second one saves, and the first one gets a clean exception instead of overwriting the other’s changes. The guide explains exactly what the generated SQL does (increment in the database, not in Delphi) and covers the case nobody expects: Delete is also protected, and the exception handler must wrap both operations.

Change tracking. MVCChangeTracking with UpdateIfChanged writes only the columns actually modified, saving traffic, trigger activations, and replication log size. The guide explains which columns are tracked and why, and how to combine foRefresh + foDoNotInsert to keep the snapshot aligned.

Multi-tenant. MVCPartition filters reads and fills writes by tenant, automatically, on every framework-generated query. The guide shows how to use it for Single Table Inheritance (employees and customers in the same persons table, each class seeing only its own rows).

Input/output model separation. The guide shows how to split the entity into a write-only input model and a read-only output model on the same table, with construction-based security: a column not declared cannot be written, so no more MVCDoNotDeserialize scattered across the class.

Composite primary keys. Junction tables, order lines, per-tenant records: all the tables where the real key is two or more columns. The guide shows the full 3.5 composite key support: declaration, the plural APIs, the position-free Load method, mixed-type keys, auto-generated columns inside the key, the four places where the framework raises an explicit exception instead of guessing, and the atomic upsert pattern with per-backend SQL for when the load-then-insert race condition matters.

Entity generation. MVCEntGen generates entities from database metadata with a fully commented .env reference (every key explained). The guide shows the CLASS_AS_ABSTRACT pattern for adding logic in subclasses without touching the generated file, the READONLY_COLUMNS / REFRESH_COLUMNS lists, and the per-engine quirks (Oracle NUMBER typing, InterBase generators, SQLite AUTOINCREMENT inside composite keys).

Repository pattern. When the architecture calls for interfaces and dependency injection, the guide shows IMVCRepository<T>, the same surface area as TMVCActiveRecord behind an injectable interface, with everything you already know applying unchanged (hooks, validation, change tracking, named queries, composite keys, transactions). Includes a mock-based test example that shows how the repository makes an entity testable, which the static methods of TMVCActiveRecord do not allow.

On the authority of the two patterns

Active Record and Repository are not framework inventions: they are patterns catalogued by Martin Fowler in Patterns of Enterprise Application Architecture (Addison-Wesley, 2003), still the reference for enterprise software architecture today. Knowing them by name, knowing when to use one or the other, and knowing how to implement them correctly is a skill that holds on any stack, not just Delphi.

“Active Record uses the most obvious approach, putting data access logic in the domain object. This way all people know how to read and write their data to and from the database.”

Martin Fowler, Patterns of Enterprise Application Architecture

“A Repository mediates between the domain and data mapping layers, acting like an in-memory domain object collection. Repository also supports the objective of achieving a clean separation and one-way dependency between the domain and data mapping layers.”

Martin Fowler, Edward Hieatt, Rob Mee, Patterns of Enterprise Application Architecture

This guide shows you how both patterns are implemented in DelphiMVCFramework, with the rationale behind every choice and the trade-offs explained. Knowing the pattern is the first step; knowing how to use it well on your database, with your team, is what the guide teaches you.

A taste: composite primary keys in 3.5

For years, TMVCActiveRecord had one opinion about primary keys that it refused to negotiate: you get exactly one column, and you’ll like it. Put foPrimaryKey on a second field and the framework stopped you at startup with a blunt little message about that being one PK too many.

The rule kept the Active Record pattern simple. It also left a very common kind of table out in the cold: the junction table, and anything whose identity is naturally made of two or more columns. user_roles(user_id, role_id), an order line keyed by (order_id, line_no), a per-tenant record keyed by (tenant, code). These tables are everywhere.

Starting with 3.5 you no longer have to pick. ActiveRecord maps the natural composite key directly, with GetByPKs convenience and all. You declare it by marking every column in the key with foPrimaryKey, exactly the way you already mark a single one:

[MVCTable('user_roles')]
TUserRole = class(TMVCActiveRecord)
private
  [MVCTableField('user_id', [foPrimaryKey])]
  fUserID: Integer;
  [MVCTableField('role_id', [foPrimaryKey])]
  fRoleID: Integer;
  [MVCTableField('note')]
  fNote: NullableString;
public
  property UserID: Integer read fUserID write fUserID;
  property RoleID: Integer read fRoleID write fRoleID;
  property Note: NullableString read fNote write fNote;
end;

And you address it by key with the plural counterparts of the methods you already know:

lRole := TMVCActiveRecord.GetByPKs<TUserRole>([1, 42]);
try
  // ...use lRole...
finally
  lRole.Free;
end;

The full walkthrough (declaration, plural APIs, the position-free Load method, mixed-type keys, auto-generated columns, the four guardrails where the framework raises, the atomic upsert per backend, and URL addressing in the auto-CRUD controller with JSON arrays) is in the guide. The public post announcing the feature is on the blog.

Who this is for (and why you can’t skip it)

If you use TMVCActiveRecord, or plan to, this guide saves you time and money. It is not an extra for those who want to go deeper: it is what you need to know the framework thoroughly before sending it to production. The guide shows you what every feature actually does, how it combines with the others, and why it is designed that way: the foDoNotSelect behavior, how the snapshot works with foRefresh + foDoNotInsert, the MVCPartition multi-tenant pattern, the input/output model separation that replaces a dozen MVCDoNotDeserialize attributes, and the full composite key support of 3.5, which removes the single-column PK constraint you have been working around.

One evening of reading, months of time saved. 130 pages read in an evening, and every feature you discover already built is code you don’t write, a bug you don’t open, a Stack Overflow question you don’t ask. The soft delete chapter or the optimistic locking chapter alone pay back the reading time the first time you use them for real. The guide covers in one evening what takes months of trial and error in production to learn, and it does it with the design rationale behind every choice, not just the how.

If you use, or want to use, the Repository pattern (IMVCRepository<T>), the guide is even more mandatory. The repository is the surface of TMVCActiveRecord behind an injectable interface: everything you already know applies unchanged, hooks, validation, change tracking, named queries, composite keys, transactions. The guide shows how to use it to test with mocks (the repository makes an entity testable, which the static methods of TMVCActiveRecord do not allow), and why dependency injection on IMVCRepository<T> is the clean path when the architecture calls for interfaces.

If you don’t use ActiveRecord yet, this is the fastest way to evaluate it. You see the entire surface area in one read, with the design rationale behind every choice, the trade-offs explained honestly, and the patterns that the framework’s own codebase uses.

What this is not

It is an opinionated guide, not a method listing: it tells you which feature to use when, how it combines with the others, and why it is designed that way. Every claim is verified against the framework source code, not against memory.

Details

License

Patreon-exclusive. Not for redistribution. The full license terms are inside the guide. By receiving it you have the right to read it, print it, and use it in your work, for yourself. You do not have the right to republish it, upload it to sites, blogs, forums, channels, repositories, or sharing platforms, nor to translate or adapt it, even without commercial intent and even citing the source. If a piece of content was useful and you want to share it, share the link to the Patreon page, not the file.

How to access

The guide is available only through Patreon, but you do not need to be an existing member to buy it. On Patreon you can buy the guide as a single product in the shop, or choose a subscription to the tier that includes it (along with the other premium content). In both cases access is immediate after purchase.

This page covers the English edition (ActiveRecord: The Complete Guide, version 1.2). For the Italian edition, see the Italian page. To access the English edition:

If you don’t know which option to choose (single purchase or subscription), write to d.teti@bittime.it.

Frequently asked questions

What does the guide cover?

TMVCActiveRecord in full: mapping, connections, CRUD, the reading API (GetByPK, Where, Select, RQL, named queries), transactions, lifecycle hooks, validation, audit columns, change tracking, soft delete, optimistic locking, multi-tenant, input/output model separation, repository pattern, auto-CRUD controller, MVCEntGen generator, and the 3.5 novelty: composite primary keys end to end. Plus four appendices: method reference, internals notes, FAQ, and the DDL of every example.

Who is it for?

Delphi developers who already use ActiveRecord and want to discover what they are missing, and those who don’t use it yet and want to evaluate it in a single read. It assumes basic Delphi/Object Pascal knowledge.

What languages is it available in?

English and Italian, both at version 1.2. Choose your edition on Patreon: English or Italian. If you are interested in a language other than these two, write to d.teti@bittime.it: additional translations start from requests.

How long is it?

About 130 pages in PDF, plus the complete DDL of every example table in the appendix, so you can run everything on an empty database without inventing the tables.

Which framework version does it target?

DelphiMVCFramework 3.5.0-silicon. Every claim is verified against the framework source code, not against memory.

Can I redistribute it?

No. The guide is Patreon-exclusive and the license explicitly forbids republishing, uploading, translating, and adapting, even without commercial intent. You can only share it as a link to the Patreon page.

Where do I find it?

On Patreon, in the reserved post area for supporters of the tier that includes the guide: English edition. For the Italian edition see the Italian page.

About the author

Daniele Teti is the creator and lead developer of DelphiMVCFramework, the most popular open-source Delphi project on GitHub for building REST and JSON-RPC APIs. With over 25 years of experience building enterprise systems across logistics, finance, healthcare, and manufacturing, he has a non-academic perspective on software: he knows what it means to maintain critical systems for years, with real teams, real deadlines, and real budgets.

He is the author of the Delphi Cookbook series (PacktPub, three editions) and DelphiMVCFramework - The Official Guide. This guide comes from working on the framework itself: every claim is verified against the source code, not against memory.

For consulting and training: bittimeprofessionals.it

← Back to all books