sohrab Posts - Answer Overflow

sohrab

•Created by sohrab on 11/18/2024 in #help

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),
  ...

3 replies

Gaming

Programming