Drizzle Team•6mo ago

Alias values rows in insert with mysql

Hello 👋 The use of "VALUES()" is deprecated in "ON DUPLICATE KEY" statement when inserting multiple values. (source: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html#:~:text=Note,of%20this%20section.) Now you have to alias values like that

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

Do you know if it is possible to do that with Drizzle ? Have a nice day ☀️

3 Replies

rphlmr ⚡•6mo ago

👋 Hi, Thanks for reporting this. I guess it still works but could break in the future? Would you want to open an issue at https://github.com/drizzle-team/drizzle-orm/issues ? Even if it is deprecated, but it works, you can continue to use Drizzle. Once it is updated, you should have nothing to update as it will be done by Drizzle.

GitHub

Issues · drizzle-team/drizzle-orm

Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅 - Issues · drizzle-team/drizzle-orm

OupslaOP•6mo ago

Yes at the moment, it is not even fully compatible with Drizzle because you have to write custom sql to pick values Eg.

await drizzleClient
      .insert(foo)
      .values([...])
      .onDuplicateKeyUpdate({
        set: {
          bar: sql`VALUES(${foo.bar})`
        },
      });

await drizzleClient
      .insert(foo)
      .values([...])
      .onDuplicateKeyUpdate({
        set: {
          bar: sql`VALUES(${foo.bar})`
        },
      });

And yes this syntax could break in the futur. But I can open an issue to allow aliasing of rows 👍

OupslaOP•6mo ago

Here is the issue : https://github.com/drizzle-team/drizzle-orm/issues/2676

GitHub

[FEATURE]: alias of rows to insert with ON DUPLICATE KEY · Issue #2...

Describe what you want From the MySQL documentation : Beginning with MySQL 8.0.19, it is possible to use an alias for the row, with, optionally, one or more of its columns to be inserted, following...

Gaming

Programming

Alias values rows in insert with mysql