[FEATURE]: allow INSERT in CTEs (WITH clauses)

[stabildev]

Describe what you want

Drizzle ORM supports SELECT queries in CTEs (WITH clauses). From the docs:

const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)));
const result = await db.with(sq).select().from(sq);

Currently, Drizzle does not support INSERT queries in CTEs. Example of such a query:

with inserted as (
  insert into orders (user_id, customer_id, order_date, shipping_product, payment_method)
    values (1, 1, now(), 'default', 'cash')
    returning *
)
insert into order_positions (position, order_id, product_id, quantity, price)
  values 
    (1, (select id from inserted), 1, 1, 99.90),
    (2, (select id from inserted), 2, 1, 49.90),
    (3, (select id from inserted), 3, 4, 149.90)
  returning *
;

As you can see, this would be very useful for nested inserts in a single query and should be supported by Drizzle to be a feature complete SQL query builder.

[aaroned]

@AndriiSherman should this also include DELETE as well?

WITH deleted AS (DELETE FROM table WHERE condition IS TRUE RETURNING *) SELECT count(*) FROM deleted;

[stabildev]

IMO it should include INSERT, SELECT, UPDATE and DELETE as Drizzle aims to be full featured SQL-in-TS

[TomDo1234]

This needs to be added, as it is something that appears in complicated apps! And both MySQL and Postgres support such CTE queries.

[olivermaldonado]

+1

In my case, I need to use it with UPDATE. All operations are important for this feature.

Maybe there needs to be a note on the documentation that it only supports SELECT for now.

[valenterry]

Please add this!

[michrome]

Is this why I get the error qb.getSelectedFields is not a function (pg-core/db.js:88:44) with the following code?

const newTimezoneId = db
    .$with("new_timezone_id")
    .as(
        db
            .insert(timezones)
            .values({ name: "UTC" })
            .onConflictDoNothing()
            .returning({ id: timezones.id }),
    );