Knex.jsでMySQLのUPSERTを実行する方法

Knex.jsで、MySQLのUPSERTを実行する方法を紹介します。

UPSERTとは

UPSERTとは、RDBにおいてキーが重複するレコードがあればUPDATEし、なければINSERTする処理のことです。
といっても、UPDATE文やINSERT文のようにUPSERT文があるわけではないので、データベース製品によってその構文は異なります。

MySQLにおけるUPSERT

MySQLでUPSERTを行なうには、INSERT ... ON DUPLICATE KEY UPDATE構文を用います。¹

INSERT INTO `users` (`id`, `name`, `status`)
  VALUES (1, 'Alice', 'I\'m happy')
  ON DUPLICATE KEY UPDATE `id` = 1, `name` = 'Alice', `status` = 'I\'m happy';

UPSERT用関数を作成

Knex.js自体にはUPSERT用のAPIはないので、UPSERTを実行するための関数をつくります。

import Knex from 'knex'

interface Params {
  db: Knex,
  table: string,
  record: Record<string, any>
}

/**
 * Execute UPSERT (INSERT ... ON DUPLICATE KEY UPDATE) for MySQL
 * @see https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
 */
export function upsert ({
  db,
  table,
  record
}: Params): Knex.Raw<any[]> {
  const insert = db.insert(record).into(table)
  const values = Object.entries(record)
    .map(([column, value]) => db.raw('?? = ?', [column, value]))
    .join(', ')
  return db.raw(`${insert} ON DUPLICATE KEY UPDATE ${values}`)
}

この関数は、次のように使用します。

await upsert({
  db: knex,
  table: 'users',
  record: {
    id: 1,
    name: 'Alice',
    status: "I'm happy"
  }
})

戻り値の型はKnex.Raw<any[]>なので、生成されたSQL文字列を取得することもできます。

const query = upsert({
  db: knex,
  table: 'users',
  record: {
    id: 1,
    name: 'Alice',
    status: "I'm happy"
  }
})
console.log(query.toString())
await query

上記の出力を整形（可読性のために改行を追加）すると、次のようになります。

insert into `users` (`id`, `name`, `status`)
  values (1, 'Alice', 'I\'m happy')
  ON DUPLICATE KEY UPDATE `id` = 1, `name` = 'Alice', `status` = 'I\'m happy'

参考リンク

• MySQL :: MySQL 8.0 Reference Manual :: 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement
• UPSERT with Knexjs (and MySQL) - artzecode - Medium
• Upsert with Knexjs and MySQL

1. MySQL :: MySQL 8.0 Reference Manual :: 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement ↩