Drizzle Team•4d ago

Infer insert model for select in "Insert into ... select"

Thanks for adding support for "Insert into … select". This is going to make FKs to internal IDs a lot less painful. I have plenty of use cases where all service interfaces work off external IDs (id) but to insert/update they will need to be mapped to internal IDs (iid). Here's a typical example where customer's external ID is resovled to its internal ID so it can be used in the relevant column of transaction table.

db.insert(transactionTable).select(
  db.select({
    id: "11",
    type: "STUFF",
    customerId: customerTable.iid
  })
  .from(customerTable)
  .where(eq(customerTable.id, customerId))
)

db.insert(transactionTable).select(
  db.select({
    id: "11",
    type: "STUFF",
    customerId: customerTable.iid
  })
  .from(customerTable)
  .where(eq(customerTable.id, customerId))
)

But I think I need to somehow type the db.select({}) so it knows I'm constructing a transactionTable insert model, i.e. it knows what the column types for id and type above. I did try db.select<typeof transactionTable.$inferInsert>({}) but I don't think that's compatible with SelectFields generic arg on select(). Any pointers on how to do this? Hopefully the answer also applies to "update ... from" use cases as well. My current workaround is to just use raw SQL on each field which is not great:

  id: sql<string>`'11'`.as(transactionTable.id.name),
  type: sql<string>`'STUFF'`.as(transactionTable.type.name),
  ...

  id: sql<string>`'11'`.as(transactionTable.id.name),
  type: sql<string>`'STUFF'`.as(transactionTable.type.name),
  ...

2 Replies

Angelelz•4d ago

I think your current workaround is the way to go. The sql operator will write the correct statement

sohrabOP•4d ago

The problem is I lose type safety. For exmaple I can switch one of the above to sql<number> and the compiler wouldn't complain. Things get worst with typed json/jsonb fields as well

Gaming

Programming

Infer insert model for select in "Insert into ... select"