2 replies

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

id

) but to insert/update they will need to be mapped to internal IDs (

iid

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

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({})

db.select({})

so it knows I'm constructing a

transactionTable

transactionTable

insert model, i.e. it knows what the column types for

id

id

and

type

type

above.

I did try

db.select<typeof transactionTable.$inferInsert>({})

db.select<typeof transactionTable.$inferInsert>({})

but I don't think that's compatible with

SelectFields

SelectFields

generic arg on

select()

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

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

id

) but to insert/update they will need to be mapped to internal IDs (

iid

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

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({})

db.select({})

so it knows I'm constructing a

transactionTable

transactionTable

insert model, i.e. it knows what the column types for

id

id

and

type

type

above.

I did try

db.select<typeof transactionTable.$inferInsert>({})

db.select<typeof transactionTable.$inferInsert>({})

but I don't think that's compatible with

SelectFields

SelectFields

generic arg on

select()

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

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

Similar Threads

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

Similar Threads

Similar Threads

Similar Threads