MySQL 8 row aliases

[jacobwgillespie]

Hey! 👋

With MySQL 8.0.19+, it's possible to alias the row being inserted and then reference that alias in the values for on duplicate key update [ref]:

insert into table (a, b) values (1, 2) as alias
  on duplicate key update b = alias.b;

This is the replacement for the previous values() function, which is now considered deprecated.

Is it possible to produce this query today with Kysely? My guess is no, but it's also very possible that I'm missing how to inject additional clauses into the query. I assume the alias.b would be achievable with sql`alias.b`, but I'm not sure that I'd be able to inject as alias today.

[koskimas]

I don't think this is possible at the moment. It's definitely not possible in a type-safe way. We have no implementation for this.

I think you're better off using values for now.

You can create a helper function like this:

function values<T>(expr: Expression<T>) {
  return sql<T>`VALUES(${expr})`
}

and then use it like this

db.insertInto('users')
  .values([ row1, row1, ... ])
  .onDuplicateKeyUpdate(({ ref }) => ({
    listid: values(ref('listid')),
    email: values(ref('email')),
    age: values(ref('age')),
    name: values(ref('name')),
  }))

that's fully type-safe. You just need that extra ref call there.

[jacobwgillespie]

Cool, works for me, from my understanding, values() is going to be removed from MySQL at some point, but hasn't been yet 👍

[koskimas]

I think they'll have to keep values around for years and years since so much MySQL code is using it. It's possible that they'll never really remove it.

[Qrokqt]

I'm trying to do this with a JSONColumnType<Array<string>> column and I'm getting an error which is saying the types are incompatible.

interface UsersTable {
  user_id: string;
  content: JSONColumnType<Array<string>>;
}

db.insertInto('users')
  .values([
    {
      user_id: '1',
      content: JSON.stringify(['1','2','3']),
    },
    {
      user_id: '2',
      content: JSON.stringify(['1']),
    },
  ]).onDuplicateKeyUpdate(({ ref }) => ({
    user_id: values(ref('user_id')),
    content: values(ref('content')),
  }));

Argument of type '({ ref }: ExpressionBuilder<Database, "users">) => { user_id: RawBuilder<string>; content: RawBuilder<...>; }' is not assignable to parameter of type 'UpdateObjectExpression<Database, "users", "users">'.
  Type '({ ref }: ExpressionBuilder<Database, "users">) => { user_id: RawBuilder<string>; content: RawBuilder<...>; }' is not assignable to type 'UpdateObjectFactory<Database, "users", "users">'.
    Call signature return types '{ user_id: RawBuilder<string>; content: RawBuilder<string[]>; }' and 'UpdateObject<Database, "users", "users">' are incompatible.
      The types of 'content' are incompatible between these types.
        Type 'RawBuilder<string[]>' is not assignable to type 'ValueExpression<Database, "users", string> | undefined'.
          Property 'isSelectQueryBuilder' is missing in type 'RawBuilder<string[]>' but required in type 'SelectQueryBuilderExpression<Record<string, string>>'.

[jgray33]

Hiya,

I'm having this issue and have come across this ticket whilst looking for a solution, has anyone got a way of referencing the alias onDupliateKeyUpdate?

I am getting errors now due to the deprecation of values, and the only way I can see a around this is to inject the raw sql which isn't ideal

Any help appreciated