drizzle-team / drizzle-team/drizzle-orm

[FEATURE]:PostgreSQL 18 RETURNING OLD/NEW support

Open
#5,109 1 comment 32 reactions 0 assignees View on GitHub
enhancement
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Feature hasn't been suggested before.

- [x] I have verified this feature I'm about to request hasn't been suggested before.

### Describe the enhancement you want to request

### Feature hasn't been suggested before.
- [x] I have verified this feature I'm about to request hasn't been suggested before.

### Describe the enhancement you want to request

PostgreSQL 18 introduced `RETURNING OLD` and `RETURNING NEW` syntax for `UPDATE` and `DELETE` statements. This allows retrieving both the previous and updated values in a single query, which is extremely useful for audit logging, change tracking, and optimistic locking.

**PostgreSQL 18 syntax:**
```sql
-- Get both old and new values in UPDATE
UPDATE users SET name = 'Jane' WHERE id = 1
RETURNING OLD.name AS old_name, NEW.name AS new_name;

-- Get deleted row values in DELETE
DELETE FROM users WHERE id = 1 RETURNING OLD.*;

-- Mixed usage
UPDATE products SET price = price * 1.1 WHERE category = 'electronics'
RETURNING id, OLD.price AS previous_price, NEW.price AS current_price;
```

**Proposed Drizzle API:**
```typescript
// Option 1: Extend returning() with old/new helpers
import { old, new_ } from 'drizzle-orm/pg-core';

await db.update(users)
.set({ name: 'Jane' })
.where(eq(users.id, 1))
.returning({
id: users.id,
oldName: old(users.name),
newName: new_(users.name),
});

// Option 2: returningOld() / returningNew() methods
await db.update(users)
.set({ name: 'Jane' })
.where(eq(users.id, 1))
.returningNew({ id: users.id, name: users.name })
.returningOld({ name: users.name });

// For DELETE - return deleted row data
await db.delete(users)
.where(eq(users.id, 1))
.returningOld();
```

### Use Cases

1. **Audit Logging** - Capture before/after values without additional SELECT queries
2. **Optimistic Locking** - Verify expected old values during updates
3. **Change Detection** - Track which fields actually changed
4. **Event Sourcing** - Generate change events with complete state transitions

### References

- [PostgreSQL 18 Release Notes - RETURNING OLD/NEW](https://www.postgresql.org/docs/18/release-18.html)
- Related PR: #4934 (PostgreSQL 18 virtual generated columns - shows PG18 support is already in progress)
- Related Issue: #4944 (PostgreSQL 18 NOT NULL constraint bug - already fixed)

### Current Workaround

Using raw SQL via `db.execute()`:
```typescript
const result = await db.execute(sql`
UPDATE ${users} SET name = 'Jane' WHERE id = 1
RETURNING OLD.name AS old_name, NEW.name AS new_name
`);
```

This works but loses type safety, which is one of Drizzle's core strengths.

Contributor guide

Open the contributing guide

Research direction

Start with the PostgreSQL 18 RETURNING OLD/NEW release notes and related PR #4934, then compare the two proposed Drizzle APIs with the existing returning() behavior. Use the supplied UPDATE and DELETE SQL examples and type-safe API examples to define the supported scope; done means typed old/new results without requiring db.execute() raw SQL.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, typescript
Domain
backend-api-design, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.