Topics

Drizzle Team•15mo ago

Guilherme Rosado

[HELP]: I'm trying to re-create a CTE-insert in drizzle, having some difficulties

with cart_products as (
  select * from cart_product where cart_id = ${cartId}
)
insert into order_product (order_id, cart_product_id)
select ${orderId}, id from cart_products
returning 
   id,
   order_id as "orderId",
   cart_product_id as "cartProductId";

with cart_products as (
  select * from cart_product where cart_id = ${cartId}
)
insert into order_product (order_id, cart_product_id)
select ${orderId}, id from cart_products
returning 
   id,
   order_id as "orderId",
   cart_product_id as "cartProductId";

How does the query above translate to drizzle? I think more examples that translates to the above would be awesome in the docs, I've been fighting a bit with the API. I know from the start how to do it on sql, but sometimes it gets really hard translating to drizzle. I feel this could be solved with more examples, maybe?

37 Replies

rphlmr ⚡•15mo ago

We spoke about that few days ago here:https://discord.com/channels/1043890932593987624/1145679831841919006 (scroll to the end) Hope it helps

Guilherme RosadoOP•15mo ago

Well, looking at that thread it doesn't show much. I'm also just using sql template directive with the CTE Maybe there could be a solution using the core api Also a very, very wierd behavior

      const query = sql<OrderProduct[]>`
        with cart_products as (
          select * from cart_product where cart_id = ${cartId}
        )
        insert into order_product (order_id, cart_product_id)
        select ${orderId}, id from cart_products
        returning 
          id,
          order_id as "orderId",
          cart_product_id as "cartProductId";
      `;

      // Don't know why, but type-inferece is not working here, need to use
      // "as" to cast the type
      const data = (await this.db.db.execute(query)) as OrderProduct[];

      const query = sql<OrderProduct[]>`
        with cart_products as (
          select * from cart_product where cart_id = ${cartId}
        )
        insert into order_product (order_id, cart_product_id)
        select ${orderId}, id from cart_products
        returning 
          id,
          order_id as "orderId",
          cart_product_id as "cartProductId";
      `;

      // Don't know why, but type-inferece is not working here, need to use
      // "as" to cast the type
      const data = (await this.db.db.execute(query)) as OrderProduct[];

I can't get inference from the sql<OrderProduct> It infers data as unknown the execute method should pick up the type from the sql<T> Do you have anything in mind why this would happen? Am I doing something wrong? 😮

rphlmr ⚡•15mo ago

I can reproduce. Not sure if it is expected 🧐

Guilherme RosadoOP•15mo ago

😢 @Andrew Sherman Is this a bug?

Angelelz•15mo ago

It seems this query could be written like this:

const cartProducts = db.$with('cart_products').as(db.select().from(cartProduct).where(eq(cartProduct.cartId, cartId)));

const insrt = await db.insert(orderProduct).values({ orderId, cartProductId});

const selected = await db.with(cartProducts).select({ orderId: orderProduct.orderId }).from(cartProducts).returning(...);

const cartProducts = db.$with('cart_products').as(db.select().from(cartProduct).where(eq(cartProduct.cartId, cartId)));

const insrt = await db.insert(orderProduct).values({ orderId, cartProductId});

const selected = await db.with(cartProducts).select({ orderId: orderProduct.orderId }).from(cartProducts).returning(...);

I'm assuming a lot of table a column names here. So you might need to edit it btw, you might want to do this in a transaction

rphlmr ⚡•15mo ago

I thought the same but not sure if running them separately (not in the same execute) will produce the same result

Angelelz•15mo ago

db.$with, only sets up the with for drizzle, it doesn't send anything to the DB. I forgot to await the db call. Will edit

Guilherme RosadoOP•15mo ago

You're doing two queries here Isn't real 1-1 to the query I stated above I think this might be a limitation of drizzle? I mean, if I understood correctly.

Angelelz•15mo ago

Then I don't think I understood your query. Can you write it in plain SQL? cause I believe you might have just put the what you would put in the sql operator The values from the insert are coming from the select?

Guilherme RosadoOP•15mo ago

Yep!

Angelelz•15mo ago

Oh, my bad

Guilherme RosadoOP•15mo ago

The select is just to format the data so that the input can accept it I do it all the time But sometimes this tricks get super hard to implement in drizzle xD Sql is just boom, done. There's lot of mental effort to get this right Maybe I just lack examples

Angelelz•15mo ago

const cartProducts = db.$with('cart_products').as(db.select().from(cartProduct).where(eq(cartProduct.cartId, cartId)));
const insert = await db
  .insert(orderProduct)
  .values(
    db.with(cartProducts)
    .select(
      {
        id: orderProduct.id
        orderId: orderProduct.orderId
      })
    .from(cartProducts)
  )
  .returning(...);

const cartProducts = db.$with('cart_products').as(db.select().from(cartProduct).where(eq(cartProduct.cartId, cartId)));
const insert = await db
  .insert(orderProduct)
  .values(
    db.with(cartProducts)
    .select(
      {
        id: orderProduct.id
        orderId: orderProduct.orderId
      })
    .from(cartProducts)
  )
  .returning(...);

I think this might work, I just don't have anywhere to test it right now The types might complain though

Guilherme RosadoOP•15mo ago

Oh okay So basically you passed the CTE to below the insert Yeah I didn't thought that way Yeah that might work Smart Thanks! Still I wonder if sometimes isn't just easier to write sql Like

const cartProducts = db.$with('cart_products').as(db.select().from(cartProduct).where(eq(cartProduct.cartId, cartId)));
const insert = await db
  .insert(orderProduct)
  .values(
    db.with(cartProducts)
    .select(
      {
        id: orderProduct.id
        orderId: orderProduct.orderId
      })
    .from(cartProducts)
  )
  .returning(...);

const cartProducts = db.$with('cart_products').as(db.select().from(cartProduct).where(eq(cartProduct.cartId, cartId)));
const insert = await db
  .insert(orderProduct)
  .values(
    db.with(cartProducts)
    .select(
      {
        id: orderProduct.id
        orderId: orderProduct.orderId
      })
    .from(cartProducts)
  )
  .returning(...);

vs

with cart_products as (
          select * from cart_product where cart_id = ${cartId}
        )
        insert into order_product (order_id, cart_product_id)
        select ${orderId}, id from cart_products
        returning 
          id,
          order_id as "orderId",
          cart_product_id as "cartProductId";

with cart_products as (
          select * from cart_product where cart_id = ${cartId}
        )
        insert into order_product (order_id, cart_product_id)
        select ${orderId}, id from cart_products
        returning 
          id,
          order_id as "orderId",
          cart_product_id as "cartProductId";

Angelelz•15mo ago

Well, you can always write SQL, but you always end up writing just text, no intelisense or type safety That query should have correct types BTW, I'm hoping that query works, I'm not 100% it does

Guilherme RosadoOP•15mo ago

Yeah the intelisense & typesafety is what is making me stay, but still that API takes a lot of mental effort to adopt 😢 . There's a bit of a learning curve, even if you're actually cool on the sql I think it needs to improve a little, rn it's a bit confusing. At least for me. Thanks so much for the effort @angelelz Will check it out

Angelelz•15mo ago

I honestly think it's quite the opposite. What I've seen is people without SQL experience struggle with the crud API from Drizzle. You can see how you can translate from SQL to Drizzle syntax directly. There are changes we have to do to translate it to JS/TS like the eq operator has to be a function and it's written before the 2 operands, but there is no other way to express that in JS. I this example you can see how the db.$with just saves the cte query in a Drizzle data structure for use in the actual select, very similar to how you would do it in SQL

Guilherme RosadoOP•15mo ago

Yeah but it's a lot more verbose But I get your point 😉 Not hating on the tool, just that sometimes I know exactly what I want in sql, but translating to drizzle is a little hard One example

select
*
from table,
(select ...) as col_1,
(select ...) as col_2,
where col_1 is not null;

select
*
from table,
(select ...) as col_1,
(select ...) as col_2,
where col_1 is not null;

To achieve this in drizzle it's not obvious I need to

select()
.from(`${table},
(select ...) as col_1,
(select ...) as col_2`)
.where(sql`col_1 is not null`);

select()
.from(`${table},
(select ...) as col_1,
(select ...) as col_2`)
.where(sql`col_1 is not null`);

It's very wierd Or is there another way of doing it?

Angelelz•15mo ago

Uf If you do it like that, the type is going to be wrong

Angelelz•15mo ago

https://orm.drizzle.team/docs/select#select-from-subquery

SQL Select - DrizzleORM

Drizzle ORM | %s

Angelelz•15mo ago

I suggest you do the subqueries as separate variables More readable

Guilherme RosadoOP•15mo ago

The type is not going to be wrong I won't retrieve col_1 or col_2 They are just for ordering and other specific use cases Maybe I should have mentioned that And the subquery here is not an option... I'm just creating columns here, I can decide If I want retrieve that data to the select or not It would be cool for the from accepted an array, that way I could create as much columns as I wanted, like this:

select((col1, col2) => ({
  id: col2.id
  name: col1.name
}))
.from(
  select().from(), // col 1
  select().from()  // col 2
).
where((col1, col2) => {
// do something with col1 and col2
})

// or maybe
const c1 = select().from()
const c2 = select().from()

select({
  id: c2.id
  name: c1.name
})
.from(
  c1,
  c2
).
where(() => {
// do something with c1 and c2
})

select((col1, col2) => ({
  id: col2.id
  name: col1.name
}))
.from(
  select().from(), // col 1
  select().from()  // col 2
).
where((col1, col2) => {
// do something with col1 and col2
})

// or maybe
const c1 = select().from()
const c2 = select().from()

select({
  id: c2.id
  name: c1.name
})
.from(
  c1,
  c2
).
where(() => {
// do something with c1 and c2
})

This would make a lottttt of sense And would make it possible for me These are just my takes for the first time using the library. At the beginning looked awesome, but now there's a bit of an effort... Maybe I'm doing it wrong XD

Angelelz•15mo ago

The "Maybe I'm doing it wrong" is usually a sign the the library we're using is missing some features Drizzle is not yet 1 to 1 fully compatible with SQL It's not v1 yet That's why we have the sql operator for all the stuff that we're missing

Guilherme RosadoOP•15mo ago

True 😆. Well, maybe my rant here may have brought ideas Got it!

Angelelz•15mo ago

Can you open an issue in github for the feature you're referring to?

Guilherme RosadoOP•15mo ago

Sure, I'll do it 💪

Angelelz•15mo ago

Now, let me ask you a question Is the query you're trying to build possible with joins?

Guilherme RosadoOP•15mo ago

Uh yeah I could just join and select inside the join, acting like a table That's smart Hmmmm

Angelelz•15mo ago

Our join api is pretty good, type safe And you can also use the relations API

Guilherme RosadoOP•15mo ago

It's not possible, drizzle only has available 3 types of joins Wait I could do that with UNION Not join Because some columns I just need to calculate shit on the side

Angelelz•15mo ago

Lol, we don't suppor unions yet

Guilherme RosadoOP•15mo ago

E.g ranking etc Then I would filter the rows based on the results of those columns I think I could use joins after all, if those columns had some ID that where I could do (on(table.id, col_from_specific_select.id))

Angelelz•15mo ago

I have an open draft PR to add union to drizzle

Angelelz•15mo ago

https://x.com/Angelelz/status/1701601578942423451?s=20

Angel (@Angelelz)

Union support for @DrizzleORM is coming along nicely.

Twitter

Guilherme RosadoOP•15mo ago

Nice man! Looking good I'll keep digging, I'll try to come-up with some different solutions You may have given some ideas that made me think of different solutions Much thanks

Angelelz•15mo ago

I believe joining tables is faster than selecting from several selects I might be wrong Now I want to do some explain analize lol

Guilherme RosadoOP•15mo ago

I don't think so, the join still needs to compare every result and align with the ID of different tables (or subqueries acting as a table) Not sure tho ahahahahah Great question

Drizzle Team Join

The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!

9.5KMembers

View on Discord

Want results from more Discord servers?

Add your server