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

[stabildev]

trafficstars

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 }),
	);

[oliveryasuna]

This would be a game changer.

[oliveryasuna]

Has anyone come up with a workaround?

[benstpierre]

I also need this.

[oliveryasuna]

@michrome Yes. Have you come up with a workaround?

[michrome]

Have you come up with a workaround?

I wanted a find_or_create, so was trying to insert then return the value if it already existed. In my app specifically, I know the record will exist most of the time so I broke it into two queries as below. Not a workaround but suitable (better?) for my use case 😀

I agree with comments above though: Drizzle should add support for INSERT with CTEs as there are many valid use cases for it.

async function getTimezoneId(timezoneName) {
	const existingTimezone = await db
		.select({ id: timezones.id })
		.from(timezones)
		.where(eq(timezones.name, timezoneName))
		.limit(1);
	if (existingTimezone.length !== 1) {
		return await createTimezone(timezoneName); // executes an INSERT and returns the new ID
	}
	return existingTimezone[0].id;
}

[oliveryasuna]

Have you come up with a workaround?

I wanted a find_or_create, so was trying to insert then return the value if it already existed. In my app specifically, I know the record will exist most of the time so I broke it into two queries as below. Not a workaround but suitable (better?) for my use case 😀

I agree with comments above though: Drizzle should add support for INSERT with CTEs as there are many valid use cases for it.

async function getTimezoneId(timezoneName) {
	const existingTimezone = await db
		.select({ id: timezones.id })
		.from(timezones)
		.where(eq(timezones.name, timezoneName))
		.limit(1);
	if (existingTimezone.length !== 1) {
		return await createTimezone(timezoneName); // executes an INSERT and returns the new ID
	}
	return existingTimezone[0].id;
}

Bummer, I was hoping to avoid multiple queries. My company will have to stick with Kysely for data-intensive applications.

[kravetsone]

Great feature! It can improve multiple inserts to many tables

[erikmunson]

really need this in my app, is there a workaround available even if not pretty and/or not fully typesafe that keeps the whole thing in one DB query? i was trying to figure out if i could use toSQL() to build the insert and drop it into a CTE with the sql template helper but wasn't able to get it working with parameters.

[jdgamble555]

I want to add that a nested insert would be useful here. Basically a with clause, but more user friendly.

await db.insert({
  authors: {
    name: 'Jane Austen',
    books: {
      create: [
        { title: 'Pride and Prejudice' },
        { title: 'Sense and Sensibility' },
      ],
    },
  },
});

J

[kravetsone]

I want to add that a nested insert would be useful here. Basically a with clause, but more user friendly.

await db.insert({
  authors: {
    name: 'Jane Austen',
    books: {
      create: [
        { title: 'Pride and Prejudice' },
        { title: 'Sense and Sensibility' },
      ],
    },
  },
});

J

it more about db.query.create

[jdgamble555]

I want to add that a nested insert would be useful here. Basically a with clause, but more user friendly.

await db.insert({
  authors: {
    name: 'Jane Austen',
    books: {
      create: [
        { title: 'Pride and Prejudice' },
        { title: 'Sense and Sensibility' },
      ],
    },
  },
});

J

it more about db.query.create

What if we want to insert the author but update the book? We need a way to differentiate the nested table action.

Note: Going to use insert instead of create for consistency from SQL CRUD (insert, select, update, delete). You could also get into connect or disconnect, but that is a different feature.

await db.insert({
  authors: {
    name: 'Jane Austen',
    books: {
      insert: [
        { title: 'Pride and Prejudice' },
        { title: 'Sense and Sensibility' },
      ],
    },
  },
});

For update, we would need a key, so:

await db.insert({
  authors: {
    name: 'Jane Austen',
    books: {
      update: [
        { id: '0x123', title: 'Pride and Prejudice' },
        { id: '0x234', title: 'Sense and Sensibility' }
      ],
    },
  },
});

J

[kravetsone]

I want to add that a nested insert would be useful here. Basically a with clause, but more user friendly.

await db.insert({
  authors: {
    name: 'Jane Austen',
    books: {
      create: [
        { title: 'Pride and Prejudice' },
        { title: 'Sense and Sensibility' },
      ],
    },
  },
});

J

it more about db.query.create

What if we want to insert the author but update the book? We need a way to differentiate the nested table action.

Note: Going to use insert instead of create for consistency from SQL CRUD (insert, select, update, delete). You could also get into connect or disconnect, but that is a different feature.

await db.insert({
  authors: {
    name: 'Jane Austen',
    books: {
      insert: [
        { title: 'Pride and Prejudice' },
        { title: 'Sense and Sensibility' },
      ],
    },
  },
});

For update, we would need a key, so:

await db.insert({
  authors: {
    name: 'Jane Austen',
    books: {
      update: [
        { id: '0x123', title: 'Pride and Prejudice' },
        { id: '0x234', title: 'Sense and Sensibility' }
      ],
    },
  },
});

J

i talk that it more about relation API

this differ from SQL writing - dont like it in query builder

but proposal is good i need it

[AndriiSherman]

Available in [email protected]

Release Notes Docs

[jdgamble555]

Would this support multiple with clauses?

J

[jhwheeler]

Would this support multiple with clauses?

J

I have the same question. Essentially, with 0.39.0's update, can we now do chained .with statements? For example:

await db
  .with(
    db.$with('newOrder').as(
      db.insert(orders)
        .values({ userId, productId })
        .returning({ id: orders.id })
    )
  )
  .with(
    db.$with('orderItems').as(
      db.insert(orderItems)
        .values(data.items.map(item => ({
          orderId: sql`newOrder.id`,
          productId: item.productId,
          qty: item.qty,
        })))
        .returning()
    )
  )
  .selectFrom('orderItems')
  .selectAll()