DT
Drizzle Team•9mo ago
Ilan Yehezkely

Is this the correct way to use With clause (CTEs) to delete a row using only one transaction?

export const deleteMainAccountUser = async (publicUserId: string, publicMainAccountId: string) => { const userId = neonClient .$with('user_id') .as(neonClient.select({ id: user.id }).from(user).where(eq(user.publicUserId, publicUserId))) const mainAccountId = neonClient .$with('account_id') .as( neonClient .select({ id: mainAccount.id }) .from(mainAccount) .where(eq(mainAccount.publicId, publicMainAccountId)) ) return neonClient .with(userId, mainAccountId) .delete(mainAccountUser) .where( and(eq(mainAccountUser.userId, userId), eq(mainAccountUser.mainAccountId, mainAccountId)) ) } I first need to get both the userId and mainAccountId before removing the row in mainAccountUser, is this the best way to do it? Do I have to use sql in order to fully use the CTEs flow?
8 Replies
Sillvva
Sillvva•9mo ago
Using WITH/AS does not return a value (like userId). It returns a query. Your code should look more like this: 
export const deleteMainAccountUser = async (publicUserId: string, publicMainAccountId: string) => {
  const userId = neonClient
    .$with('user_id')
    .as(
      neonClient.select({ id: user.id })
      .from(user)
      .where(eq(user.publicUserId, publicUserId))
    )

  const mainAccountId = neonClient
    .$with('account_id')
    .as(
      neonClient
        .select({ id: mainAccount.id })
        .from(mainAccount)
        .where(eq(mainAccount.publicId, publicMainAccountId))
    )

  return neonClient
    .with(userId, mainAccountId)
    .delete(mainAccountUser)
    .where(
      and(
        eq(mainAccountUser.userId, sql`(SELECT * FROM ${userId})`), 
        eq(mainAccountUser.mainAccountId, sql`(SELECT * FROM ${mainAccountId})`)
      )
    )
}
export const deleteMainAccountUser = async (publicUserId: string, publicMainAccountId: string) => {
  const userId = neonClient
    .$with('user_id')
    .as(
      neonClient.select({ id: user.id })
      .from(user)
      .where(eq(user.publicUserId, publicUserId))
    )

  const mainAccountId = neonClient
    .$with('account_id')
    .as(
      neonClient
        .select({ id: mainAccount.id })
        .from(mainAccount)
        .where(eq(mainAccount.publicId, publicMainAccountId))
    )

  return neonClient
    .with(userId, mainAccountId)
    .delete(mainAccountUser)
    .where(
      and(
        eq(mainAccountUser.userId, sql`(SELECT * FROM ${userId})`), 
        eq(mainAccountUser.mainAccountId, sql`(SELECT * FROM ${mainAccountId})`)
      )
    )
}
The resulting query would look like this: 
WITH "user_id" AS (SELECT "id" FROM "user" WHERE "public_user_id" = $1),
"account_id" AS (SELECT "id" FROM "main_account" WHERE "public_id" = $2)
DELETE FROM "main_account_user"
WHERE "user_id" = (SELECT * FROM "user_id")
AND "main_account_id" = (SELECT * FROM "account_id")
WITH "user_id" AS (SELECT "id" FROM "user" WHERE "public_user_id" = $1),
"account_id" AS (SELECT "id" FROM "main_account" WHERE "public_id" = $2)
DELETE FROM "main_account_user"
WHERE "user_id" = (SELECT * FROM "user_id")
AND "main_account_id" = (SELECT * FROM "account_id")
Here's an example from the docs: https://orm.drizzle.team/docs/delete#with-delete-clause
Drizzle ORM - Delete
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Ilan Yehezkely
Ilan YehezkelyOP•9mo ago
Oh, it returns a query, so I'm actually running multiple queries from a single query, that makes sense 🙂 I wish there were more examples like this for these options in the docs especially when it's a more advanced option. Thank you very much for the help Why do you need to run "SELECT * FROM" instead of just running the query: sql(${userId})?
Sillvva
Sillvva•9mo ago
Because you're using WITH/AS. Here's an example with my db. In the WHERE clause, you're referencing the WITH id as a sort of pseudo table name. 
WITH "character_id" AS (SELECT "id" from "character" WHERE "id" = 'cl7gfkggq002009mluw41peqd')
SELECT *
FROM "log"
WHERE "log"."characterId" = (SELECT * FROM "character_id")
WITH "character_id" AS (SELECT "id" from "character" WHERE "id" = 'cl7gfkggq002009mluw41peqd')
SELECT *
FROM "log"
WHERE "log"."characterId" = (SELECT * FROM "character_id")
vs 
SELECT *
FROM "log"
WHERE "log"."characterId" = (SELECT "id" from "character" WHERE "id" = 'cl7gfkggq002009mluw41peqd')
SELECT *
FROM "log"
WHERE "log"."characterId" = (SELECT "id" from "character" WHERE "id" = 'cl7gfkggq002009mluw41peqd')
I believe the alternative approach would look like this: 
export const deleteMainAccountUser = async (publicUserId: string, publicMainAccountId: string) => {
  const userId = neonClient
    .select({ id: user.id })
    .from(user)
    .where(eq(user.publicUserId, publicUserId))

  const mainAccountId = neonClient
    .select({ id: mainAccount.id })
    .from(mainAccount)
    .where(eq(mainAccount.publicId, publicMainAccountId))

  return neonClient
    .delete(mainAccountUser)
    .where(
      and(
        eq(mainAccountUser.userId, sql`${userId}`), 
        eq(mainAccountUser.mainAccountId, sql`${mainAccountId}`)
      )
    )
}
export const deleteMainAccountUser = async (publicUserId: string, publicMainAccountId: string) => {
  const userId = neonClient
    .select({ id: user.id })
    .from(user)
    .where(eq(user.publicUserId, publicUserId))

  const mainAccountId = neonClient
    .select({ id: mainAccount.id })
    .from(mainAccount)
    .where(eq(mainAccount.publicId, publicMainAccountId))

  return neonClient
    .delete(mainAccountUser)
    .where(
      and(
        eq(mainAccountUser.userId, sql`${userId}`), 
        eq(mainAccountUser.mainAccountId, sql`${mainAccountId}`)
      )
    )
}
Ilan Yehezkely
Ilan YehezkelyOP•9mo ago
Oh, the alternative looks much more friendly, it should work the same? You didn't use the .with() on the return query, why?
Sillvva
Sillvva•9mo ago
You don't need it when not using WITH/AS. You're encoding the entire query in the WHERE, rather than the WITH id.
Ilan Yehezkely
Ilan YehezkelyOP•9mo ago
And it has the same performance as with the first option (with/as)? How can I test how many transactions are running per query?
Sillvva
Sillvva•9mo ago
As far as I know they have the same performance. The advantage of WITH/AS is not having to repeat a subquery. If you have to repeat a subquery, then there may be a performance benefit, but the query optimizer is usually pretty good about it. For example, this contrived example: 
select curr from (
  select curr from tableone t1
  left join tabletwo t2 on (t1.empid = t2.empid)
) temp_table
where curr >= 0
union all
select -1 * curr from (
  select curr from tableone t1
  left join tabletwo t2 on (t1.empid = t2.empid)
) temp_table
where curr < 0
select curr from (
  select curr from tableone t1
  left join tabletwo t2 on (t1.empid = t2.empid)
) temp_table
where curr >= 0
union all
select -1 * curr from (
  select curr from tableone t1
  left join tabletwo t2 on (t1.empid = t2.empid)
) temp_table
where curr < 0
could be refactored to: 
with temp_table as (
  select curr from tableone t1
  left join tabletwo t2 on (t1.empid = t2.empid)
)
select curr from temp_table
where curr >= 0
union all
select -1 * curr from temp_table
where curr < 0
with temp_table as (
  select curr from tableone t1
  left join tabletwo t2 on (t1.empid = t2.empid)
)
select curr from temp_table
where curr >= 0
union all
select -1 * curr from temp_table
where curr < 0
Ilan Yehezkely
Ilan YehezkelyOP•9mo ago
ok I see now, thank you for the explanations and examples 👏
Want results from more Discord servers?
Add your server