Insert expression type safe

Is there a way to make .expression calls while inserting to a table to return an "InsertObject" or an array of column name / value as when using .values method so to have type safety in the query?

Example right now:

type Table = { a: string, b: number }
const query = database.insertInto("table").columns(['a']).expression(qb => qb.selectFrom('other_table').select('a'))
// this works but will crash at runtime, cause you need both a nd b columns

Example wanted:

type Table = { a: string, b: number }
const query1 = database.insertInto("table").columns(['a'] /* type error*/).expression(qb => qb.selectFrom('other_table').select('a'))

const query2 = database.insertInto("table").expression(qb => qb.selectFrom('other_table').select(['a', 'b as bChanged']).mapping({
  a: "a",
  b: "bChanged"
}))
// this or something to link values to columns so that order is not important but it's then decided by the query builder collecting key/values pairs into an array of columns and then array of select in the expression as in:
// insert into table(a, b) select a, b from other_table

Use case: I have a "history" table that I manually insert to when needed due to some business logic and since I need to add columns to it over time (business rules!) and the history table also manages a "archived_at" column I can't just blindly insert into the history table selecting all the columns from the main table respecting order, cause the "archived_at" colum would be somewhere in between other columns when new columns are added. Do you know how to "move" columns? Cause that seems something which is not supported in sql and postgres in my case, so more type safety would be great here

Hey 👋🏻

Did you manage to find a workaround?

I'm thinking about possible edge cases where this wouldn't end up being an InsertResult.. interesting.

Good point! We should make columns error if you don't list all required columns.

Why would you need the mapping function? Why not just give the columns the correct aliases?

Mapping was something to link the expression result to the columns without using the columns method directly. Not needed, really, it was done to give a consistent “ending” data structure for insert expression subquery builders, as when you use OnConflictBuilder and end up using doNothing or DoUpdate etc, whatever you did before

Not really as of now, neither I tried honestly. Wanted to know if this was something kysely could take over or not before messing up with types and helpers

This and also, the returning type should be somehow linked to those columns you listed. That’s why I was proposing doing everything inside expression directly, returning an InsertObject or something similar

And removing columns method altogether (don’t know about other edge cases though)

The mapping method is too far from SQL. It would also require us to add the mapping method to the SelectQueryBuilder and it would only work in this context.

We'd also need to add it to ExpressionBuilder by the way. And in RawBuilder.

yeah I agree, it was just a starting point to see what constraints would be great to have