Composite Primary Keys in Delphi MVC Framework ActiveRecord

For years DMVCFramework's ActiveRecord kept primary keys deliberately simple: one column, no exceptions. Version 3.5 lifts that limit.

If you have designed a relational database of any size, you have met this table:

CREATE TABLE user_roles (
  user_id INTEGER NOT NULL,
  role_id INTEGER NOT NULL,
  PRIMARY KEY (user_id, role_id)
);

A junction table. Its identity is not one column, it is two. And for years, TMVCActiveRecord enforced one firm rule: exactly one primary key column per entity. Add foPrimaryKey to a second field and it stopped you at startup with an error.

The rule kept the ORM simple. It also left out a very common kind of table: 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.

The workarounds everyone knew

To be fair, there was always an escape hatch. You could declare no primary key at all, map two plain fields, and read them with RQL and Where<T> like anything else. You just gave up addressing a row by its key: no GetByPK, so every lookup went through an explicit filter.

Or you added a surrogate autoincrement id as the primary key, with a UNIQUE constraint on the natural columns. ActiveRecord was happy, you got by-key CRUD back, and the database still guaranteed the real key stayed unique. The id column then spent the rest of its life being queried by nobody, existing purely so the ORM would stop complaining.

Both do the job. Plenty of good schemas run exactly like that today. But neither one lets you say the obvious thing: that (user_id, role_id) is the key, and have the ORM treat it that way.

That is also why this feature waited so long. The workarounds were good enough, and a good enough workaround is the natural enemy of the real fix: while the escape hatch works, nobody puts the missing door at the top of the list. For years a surrogate id quietly absorbed the demand, and I let it. This time I got tired of explaining the workaround, so I sat down and made ActiveRecord map the natural key directly.

Starting with 3.5, you no longer have to pick

Here is the entire change to your model:

[MVCTable('user_roles')]
TUserRole = class(TMVCActiveRecord)
private
  [MVCTableField('user_id', [foPrimaryKey])]
  fUserID: Integer;
  [MVCTableField('role_id', [foPrimaryKey])]
  fRoleID: Integer;
  // ...
end;

No new attribute to memorize. You mark both columns with foPrimaryKey, the same way you already mark one. If you can add one foPrimaryKey, you already know how to declare a composite key: the learning curve is a step with no height. From then on ActiveRecord treats (user_id, role_id) as the identity of the row, and every generated WHERE, INSERT, UPDATE, and DELETE covers the full key.

And because a single value can no longer point at a row, the by-key methods you know grew plural counterparts:

lRole := TMVCActiveRecord.GetByPKs<TUserRole>([1, 42]);

That call, GetByPKs, is a class method, which points at something worth knowing: this is all one engine, and you are not stuck with the static Active Record style. DMVCFramework ships a ready-made repository over the very same entities, IMVCRepository<T>, in the MVCFramework.Repository unit. Its real edge over the class methods is that it is an interface: unlike a static call, you can inject it straight into your controllers and services through the DI container. You register it once and let [MVCInject] hand it to whoever needs it:

// Register the repository once, in the .dpr, before the server starts
Container.RegisterType(TMVCRepository<TUserRole>, IMVCRepository<TUserRole>,
  TRegistrationType.SingletonPerRequest);

// Then inject it wherever you need it, a controller or a service
type
  [MVCPath('/user-roles')]
  TUserRolesController = class(TMVCController)
  private
    fRepo: IMVCRepository<TUserRole>;
  public
    [MVCInject]
    constructor Create(UserRolesRepository: IMVCRepository<TUserRole>); reintroduce;
  end;

Same entities, same composite-key support underneath, now reached through a dependency you can swap in a test instead of a static call you cannot. Writing your own repository classes stays perfectly valid too, the point is that you rarely have to.

Those plural array methods were the obvious part. The harder question was how you even name a key that is now made of several values. So alongside the array methods there is now a different way to address a row: you set each key field by its property name, in whatever order you like, and then call Load, a brand-new method that reads the key straight off the entity instead of from an argument list. It looks like a small change on the page. Underneath it is a real shift in paradigm, because for the first time the entity owns its own key instead of receiving it as a positional argument.

That is the gist, and it is also where the easy part ends. Marking the two columns takes a minute. The questions hiding behind that minute are why the full article runs as long as it does:

• The positional array methods (GetByPKs, LoadByPKs) and the property-first Load both exist for a reason: which trap does one avoid, and when should you still reach for the other?
• What do Load and Refresh do when the row simply is not there, and why did I pick the answer that feels less convenient?
• Can the columns of one key be different types, and how many of them is the database allowed to fill in for you?
• With the auto-CRUD controller, how do you build the URL to address a row whose key has two columns, and how are string or GUID keys written into it?
• What is the single breaking change in the whole release, and which narrow kind of code actually has to care about it?

You will find the answers to these, and to a few questions you have not thought of yet, in the full article on Patreon.

A superpower only a few developers know

This is a good moment to point at a piece of DMVCFramework I think of as a quiet superpower: TMVCActiveRecordController. Everyone who starts using it stays with it, yet most developers I talk to have never heard it exists.

What it solves is the most repetitive code in any data-backed API. For every table you would otherwise hand-write the same controller: a GET for the list, a GET by id, a POST to create, a PUT to update, a DELETE, plus paging, filtering and sorting, multiplied by every entity in the schema. It is boilerplate you have written a hundred times and will get subtly wrong on the hundred-and-first, always in the one endpoint that skipped code review.

The controller replaces all of it with one line:

FMVC.AddController(TMVCActiveRecordController, '/api/entities');
FMVC.AddMiddleware(TMVCActiveRecordMiddleware.Create(CON_DEF_NAME));

From that point every registered ActiveRecord entity is a REST resource. You get full CRUD, RQL queries straight from the URL to filter, sort and page the results, and a Swagger description generated for you. No per-entity controller, no per-entity DTO, no per-entity route.

The part that makes it safe to use in a real application is that it is not a dumb passthrough shoveling rows in and out of the database behind the ORM’s back. The controller drives your entities through their normal lifecycle, so whatever business logic you put in the entity still runs: the validation, the OnBeforeInsert and OnBeforeUpdate hooks, the computed and read-only fields, the serialization rules. A value the entity refuses to accept is refused just as firmly over HTTP as it would be from Delphi code. You are exposing your model, not going around it.

Pair that with the new entity generator and the arithmetic gets a little absurd. You point the generator at an existing database and it writes the TMVCActiveRecord classes for you, table by table; you register the controller once, and a schema with thousands of tables becomes a RESTful API in about the time it takes to make coffee. The limit is clear: a table that is the root of an aggregate, one with children or other relationships whose consistency has to be kept (think of an invoice header with its invoice lines), should not be exposed one row at a time like this. It belongs behind its Aggregate Root, the way Domain-Driven Design (Eric Evans’ famous book) argues, so that the root can enforce the aggregate’s invariants. Those cases still deserve a dedicated, hand-written controller. But most schemas are mostly plain, independent tables, and for every one of those this low-code path hands you a working, validated API for roughly zero effort.

And this is exactly where composite keys had to earn their place. An auto-CRUD controller is only as general as the keys it can address, so a feature that stopped at single-column keys would have left junction tables outside the one part of the framework whose whole job is to treat every entity the same way.

Read the full walkthrough

I wrote the whole thing up, with the code, the reasoning behind each choice, and the tests it ships with (SQLite, Firebird and PostgreSQL, Win32 and Win64), as a deep-dive article for DelphiMVCFramework subscribers on Patreon.

If you build Delphi backends with DMVCFramework, that is where the details live. A subscription also opens up the rest of the premium material: deep-dive articles like this one, videos, the in-depth books, and a discount on the next edition of the official DMVCFramework guide. It is also what keeps the framework’s development going.

👉 Composite Primary Keys in DMVCFramework ActiveRecord: full article on Patreon