drizzle-team / drizzle-team/drizzle-orm
[FEATURE]: Support PostgreSQL 19 `INSERT ... ON CONFLICT DO SELECT` (`onConflictDoSelect`)
- 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
PostgreSQL 19 adds a third `ON CONFLICT` action: `DO SELECT` (optionally with `FOR UPDATE | SHARE | ...`). It gives atomic get-or-create semantics: insert the row, or, on conflict, return the existing row through `RETURNING` without writing a dead tuple.
```sql
INSERT INTO agent_thread ("orgId", "userId", "scopeType", "scopeId")
VALUES ($1, $2, $3, $4)
ON CONFLICT ON CONSTRAINT agent_thread_owner_uq DO SELECT
RETURNING *;
```
Release notes: https://www.postgresql.org/docs/19/release-19.html (`INSERT ... ON CONFLICT DO SELECT`). Available since 19 beta 1; GA expected autumn 2026.
**Why**
Today a get-or-create in drizzle is either
- `insert().onConflictDoNothing().returning()` followed by a second `select` when `returning()` comes back empty (two or three round-trips, plus a race window), or
- `insert().onConflictDoUpdate({ set: { col: sql\`excluded.col\` } }).returning()` as a no-op update, which writes a dead tuple on every conflict.
`DO SELECT` removes both workarounds in one statement.
**Proposed API (pg-core only)**
Mirror the existing `onConflictDoNothing` / `onConflictDoUpdate` shape:
```ts
await db
.insert(agentThreads)
.values(row)
.onConflictDoSelect({
target: [agentThreads.orgId, agentThreads.userId, agentThreads.scopeType, agentThreads.scopeId],
// optional
targetWhere?: SQL,
lock?: 'update' | 'no key update' | 'share' | 'key share',
})
.returning();
```
- `target` optional, same as `onConflictDoNothing` (Postgres allows `ON CONFLICT DO SELECT` without a conflict target).
- `lock` renders `DO SELECT FOR UPDATE` etc.
- Mutually exclusive with `onConflictDoNothing` / `onConflictDoUpdate` via the same `PgInsertWithout<..., 'onConflictDoNothing' | 'onConflictDoUpdate' | 'onConflictDoSelect'>` exclusion.
- Since `DO SELECT` is only useful with `RETURNING`, the types could require `.returning()` afterwards, but a runtime no-op is also fine (Postgres itself allows it).
**Environment**
- `drizzle-orm` 0.45.2, `drizzle-kit` 0.31.10
- driver: `drizzle-orm/node-postgres` with `pg` 8.23
- PostgreSQL 19beta3 (`postgres:19beta3-alpine`)
- Node 24, TypeScript strict
Not applicable to MySQL/SQLite, so `PgInsertBase` only.
Happy to open a PR if the API shape is approved.
Contributor guide
Research direction
Start in the pg-core insert API by comparing the existing onConflictDoNothing and onConflictDoUpdate entry points and the PgInsertBase type exclusions. Verify the proposed SQL and lock options against PostgreSQL 19beta3. Done means PgInsertBase exposes onConflictDoSelect with the stated target and lock behavior, while remaining unavailable to the MySQL and SQLite APIs.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, typescript
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100