Composite primary keys

Sep 5, 2026

Roger Padilla

Two @Id decorators on one entity make a composite primary key. That is the easy part.

The main question was where the second key lives. That one choice decides whether single-key code can quietly address a composite row, so I read how the others store theirs first: Drizzle 0.45.3, MikroORM 7.2.0, Prisma 8.0.0-rc.8, Sequelize 7.0.0-alpha.48 and TypeORM 1.1.1, against UQL’s own 0.44.

How others store it?

Library	Where the key lives	Singular key reachable
Drizzle	column.primary for one, PrimaryKey { columns } for several	yes, but no id API at all
MikroORM	primaryKeys[] plus compositePK, simplePK	yes, primaryKeys[0]
Prisma	primaryKey: { columns: readonly string[] }	no
Sequelize	primaryKeysAttributeNames plus primaryKeyAttribute	yes, deprecated
TypeORM	primaryColumns[] plus hasMultiplePrimaryKeys	yes, primaryColumns[0]
UQL	ids: readonly IdKey<E>[]	no

In Prisma and UQL a key is always a list, however many columns are in it; Prisma writes primaryKey: { columns } even for a lone id. Drizzle splits it by arity instead. A .primaryKey() column is a boolean on that column, and only a table-level primaryKey({ columns }) builds a list, so drizzle-kit’s snapshot carries column.primaryKey and compositePrimaryKeys side by side. It gets away with that because it has no findById at all: a key there is a schema fact rather than a way to address a row.

MikroORM, Sequelize and TypeORM keep the list and a singular accessor beside it. MikroORM’s isPrimaryKey takes a bare string or number unless the caller passes allowComposite. Sequelize is the frankest: its primaryKeyAttribute is @deprecated because it “doesn’t work for composed primary keys”, and it still returns the first one. TypeORM’s getEntityIdMixedMap hands back a bare value for one key column and a map for several.

Those guards are checked in most places, so most of this holds. It does mean correctness rides on remembering a flag, and Sequelize’s own upsert forgets: its hasPrimary reads both deprecated singular accessors, so on a two-column key it only ever asks about the first.

No singular key at all

The key is a list and nothing else. No meta.id to reach for. About a dozen paths only handle one column, and now each one has to say so:

'Enrolment' has a composite primary key (studentId, courseId), which saving a row does not support yet.

Writing those messages is what made the gaps visible.

A bug in single-key code

Widening findOneById’s id to accept { studentId, courseId } also made {} compile. {} reaches deleteMany as no filter at all, so deleteOneById(User, {}) was DELETE FROM "User" on a plain single-key entity. The guard that checks an id is complete had been skipping single-key entities for years.

Inserts work, saves don’t

I refused inserts first, for the wrong reason. A composite insert needs nothing from the database; you write every column of the key yourself. It only can’t name the row afterwards, and that is insertOne’s return type rather than the key: TypeORM hands back an id map from every insert. Widening ours gave 139 compile errors in our own repo, all of them single-key code doing const id = await insertOne(User, u). So inserts work and report undefined.

saveMany looks like the same fix but isn’t. Save reads an id as proof the row exists, and a composite carries one on an insert too, so a new row looks like an update and silently updates nothing. Nobody else guesses: TypeORM reads the row, MikroORM remembers loading it, Prisma and Drizzle make you name the conflict columns. That last one is upsertMany, where composites already work, so saveMany refuses and points there.

The migration missed the key

An entity gaining a second @Id should gain a column and a two-column PRIMARY KEY. It gained the column.

existing table PK: [ "userId" ]
entity now wants : [ "userId", "groupId" ]
migration SQL    : [ 'ALTER TABLE "Member" ADD COLUMN "groupId" BIGINT;' ]

No crash, just a database quietly disagreeing with the code about which rows are distinct. Three things describe a table here: the AST that writes CREATE TABLE, what introspection reads back, and the diff that drives ALTER TABLE. The first two model a key as a list of columns. The third had no field for one, and every patch on top of it was working around that.

One test, four bugs

The fix meant merging two schema comparisons that had drifted apart, one for migrations and one for drift detection. So I wrote the test first: create a schema from its own entities, sync again, assert zero statements. Anything reported is a phantom.

It failed on the first run, on MariaDB, on unrelated code: every nullable column, on every sync, forever. MariaDB reports “no default” as null, an entity that declares none carries undefined, and one line compared them before the normaliser. Unit tests had covered that function for a long time, but you need a real MariaDB to produce the null.

The merge surfaced three more, all hidden behind one line that looked like prudence:

if (current.isPrimaryKey && desired.isPrimaryKey) {
  return false; // never alter a key column
}

Remove it and a freshly created table wants MODIFY COLUMN id BIGINT, because BIGINT UNSIGNED AUTO_INCREMENT reads back as BIGINT(20) UNSIGNED. SQLite threw outright, since it can’t alter a column at all. And its PRAGMA table_info reports notnull: 0 for the INTEGER PRIMARY KEY that is the table’s own rowid.

Two narrow exclusions replaced the blanket one: a generated type isn’t comparable, and a key column is NOT NULL whatever the catalogue says. Everything else about a key column is compared now, including the default and uniqueness the old rule had been hiding.

Shipped in 0.42.1.

UQL is a JSON-native TypeScript ORM for Node.js, Bun and Deno. Supports PostgreSQL, PGlite, MySQL, MariaDB, SQLite, CockroachDB, Turso, Neon, Cloudflare D1 and MongoDB. Queries are plain JSON, typed to the leaf.

Tags:

• orm
• typescript
• sql

In search of the type-safest ORM