ozzy Posts - Answer Overflow

ozzy

•Created by ozzy on 6/12/2024 in #help

Does Drizzle support using INSERT statements in a WITH clause?

I've attempted to write the following postgres query with Drizzle multiple ways, but ultimately haven't found a suitable replacement for the templated SQL:

const query = await db.execute(sql`
  WITH "new_thread" AS (
    INSERT INTO
      thread (title, body, author_id, topic_id)
    VALUES (
        ${newThread.title}, 
        ${newThread.body}, 
        ${newThread.authorID}, 
        ${newThread.topicID}
    )
    RETURNING author_id
  )
  SELECT 
      id, username 
  FROM 
      new_thread
  LEFT JOIN 
      auth_user
      ON auth_user.id = new_thread.author_id
  ;
`)

const query = await db.execute(sql`
  WITH "new_thread" AS (
    INSERT INTO
      thread (title, body, author_id, topic_id)
    VALUES (
        ${newThread.title}, 
        ${newThread.body}, 
        ${newThread.authorID}, 
        ${newThread.topicID}
    )
    RETURNING author_id
  )
  SELECT 
      id, username 
  FROM 
      new_thread
  LEFT JOIN 
      auth_user
      ON auth_user.id = new_thread.author_id
  ;
`)

This is a bit of a bummer, since I really like the type inference of the ORM, and I'm not sure how to get the return type of this without explicitly casting. I attempted something like this with Drizzle ORM:

const sq = db.$with('sq').as(db.insert(thread).values(newThread).returning({ author_id: thread.id })

const sq = db.$with('sq').as(db.insert(thread).values(newThread).returning({ author_id: thread.id })

but got an intense amount of typescript errors, and couldn't run the query. Obviously I could write this as multiple queries, and I still might. I'm just still learning and trying to push my understanding of SQL and Drizzle, so I'd like to understand how powerful a single query can be. Am I missing something here? Is there a Drizzle-y way to write this in a single query, or am I better off sticking with the templated SQL?

1 replies

Gaming

Programming