Nestjs DrizzleDrizzle 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
export const projects = pgTable('projects', {
id: serial('id').primaryKey(),
name: varchar('name').notNull(),
version: integer('version').notNull().default(1),
});Set versionColumn for the entity
DrizzleCrudModule.forFeature([
{ service: ProjectsService, table: projects, config: { versionColumn: 'version' } },
]);versionColumnOptionalstring
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:
| Write | Version |
|---|---|
create, massCreate | set to 1 |
update, massUpdate | plus one |
softDelete, massSoftDelete | plus one |
restore, massRestore | plus one |
upsert, massUpsert | 1 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:
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 }); // 409or as options.expectedVersion, which also works for the methods without a body:
await projects.softDelete(1, { expectedVersion: 4 });
await projects.restore(1, { expectedVersion: 5 });
await projects.delete(1, { expectedVersion: 6 });expectedVersionOptionalnumber
expectedVersionsOptionalRecord<string, number>
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:
{
"message": "projects with id 1 was changed by someone else; read it again and retry",
"error": "Conflict",
"statusCode": 409
}The check runs twice. The existence check compares the version before anything is written, and the UPDATE or DELETE itself carries AND version = <expected>, so a write that lands between the two is also caught.
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:
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:
{
"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
A 409 Conflict from StaleVersionException. In a mass write the call is a 400 BulkOperationException with a 409 entry for each stale id.
No. The version key is removed from create and update bodies, and the package sets it in SQL: 1 on insert, plus one on every later write.
No. Without a version in the body or expectedVersion in the options, the write is not checked, but the version still goes up.
No. It compares versions in the WHERE clause and holds no lock. For a lock across a read and a write, use lock: 'update' inside a transaction.
Next steps
Row locks with SELECT FOR UPDATE
SELECT ... FOR UPDATE when a version check is not enough.
Bulk operations
expectedVersions on mass writes and the error body they return.
Errors
StaleVersionException and every other exception, with its HTTP status.
Nested create with Drizzle relations
Drizzle nested create in NestJS. Write a row and its related rows in one transaction, return writes with relations, and attach or sync links.
Drizzle transactions in NestJS
Run Drizzle transactions in NestJS across several service calls. Open one with executeSqlTransaction or db.transaction and pass the handle to each call.