Drizzle optimistic locking with versions

Drizzle optimistic locking with a version column in NestJS. Set versionColumn, send the version you read, and a stale write is a 409 conflict.

Drizzle optimistic locking with a version column stops a client from overwriting a change it never saw. Set versionColumn on the entity, send back the version you read, and a write against a row that has moved on since then is a 409 StaleVersionException. The package bumps the version in SQL on every write. It takes no row lock, so it suits the read, edit, save flow of a form.

Who this is for

Use a version column when two people can edit the same row and the last save must not silently win. If you need to hold a row while one transaction reads and writes it, use row locks instead.

How do I turn on optimistic locking?

Add an integer column and name it in the entity config.

Add a version column to the table

schema.ts
export const projects = pgTable('projects', {
  id: serial('id').primaryKey(),
  name: varchar('name').notNull(),
  version: integer('version').notNull().default(1),
});

Set versionColumn for the entity

projects.module.ts
DrizzleCrudModule.forFeature([
  { service: ProjectsService, table: projects, config: { versionColumn: 'version' } },
]);
versionColumnOptional

string

The key of the integer version column in your Drizzle table object. Unset, optimistic locking is off. A key the table does not have fails at startup.

Send the version back on every write

Return the version you read with the update, as shown in the next section. A write against a newer version is a 409.

How does the version change?

It starts at 1 on create, whatever the body says, and goes up by one on every write:

WriteVersion
create, massCreateset to 1
update, massUpdateplus one
softDelete, massSoftDeleteplus one
restore, massRestoreplus one
upsert, massUpsert1 on insert, plus one when the row existed

It is computed in SQL (SET version = version + 1), never taken from a body. The version key is removed from a create or update body before anything else happens, so it is never written directly, even when writableColumns lists it.

How do I send the version I read?

Either in the body under the version column's key, which is what a REST client does with the object it fetched:

projects.edit.ts
const project = await projects.find(1);                 // version: 3
await projects.update(1, { name: 'New', version: 3 });  // ok, now version 4
await projects.update(1, { name: 'Late', version: 3 }); // 409

or as options.expectedVersion, which also works for the methods without a body:

projects.edit.ts
await projects.softDelete(1, { expectedVersion: 4 });
await projects.restore(1, { expectedVersion: 5 });
await projects.delete(1, { expectedVersion: 6 });
expectedVersionOptional

number

The version the caller last read. Used by update, softDelete, restore, delete and forceDelete. Wins over a version in the body.
expectedVersionsOptional

Record<string, number>

For mass writes: the expected version per id, keyed by the id as a string. Ids not listed are not checked.

On update, a version that is not an integer is a 400.

What does a stale write return?

A StaleVersionException, which extends Nest's ConflictException, so the HTTP answer is a 409:

409 response
{
  "message": "projects with id 1 was changed by someone else; read it again and retry",
  "error": "Conflict",
  "statusCode": 409
}

The UPDATE or DELETE itself carries AND version = <expected>, so a write that lands between a read and the write is always caught. When that statement changes no row, the service reads the row once to give the right answer: 404 when it is not there (or belongs to another tenant), 409 when it is at another version. When your service runs a before hook, or a before event is wired, the version is also compared before anything is written, so the hook never runs for a stale write.

What if the client sends no version?

Then there is no check, and the write still bumps the version. That keeps internal writes simple. If every client must send it, make the field required in your update DTO.

How does it work with bulk writes?

Pass expectedVersions, keyed by the id as a string:

projects.bulk.ts
await projects.massUpdate([1, 2, 3], { status: 'archived' }, {
  expectedVersions: { '1': 4, '2': 7 }, // id 3 is not checked
});

Any id at another version makes the whole call a 400 BulkOperationException with a 409 entry for that id, and nothing is written:

400 response
{
  "statusCode": 400,
  "error": "Bad Request",
  "message": "Mass update errors. 1 error(s) occurred.",
  "errors": [
    { "id": 2, "status": 409, "message": "projects with id 2 was changed by someone else; read it again and retry" }
  ]
}

massSoftDelete, massRestore, massDelete and massForceDelete take expectedVersions the same way. See Bulk operations for the rest of the error body.

Common questions

Next steps

Last updated View change history

On this page