mysql-upsert

Upsert (insert or update) multiple rows into MySQL

Upsert

Upsert = An operation that inserts rows into a database table if they do not already exist, or updates them if they do.

Install

npm install mysql-upsert --save

Usage

Basic usage is:

  const upsert = require('mysql-upsert')
  upsert(mysqlConnection)(table, data, fields)

• mysqlConnection is a mysql connection that has a query function that returns a Promise.
• table is a the table name to upsert into
• data is an array of objects. Each object is a row to insert
• fields is an array of fields to upsert. (optional, defaults to the keys of the first object in data)

Following examples use async/await syntax but can be used with regular Promise syntax.

  const mysql = require('promise-mysql')
  const upsert = require('mysql-upsert')
 
  const table = 'users'
  const data = [
   { id: 1, name: 'Steve', company: 'Apple' },
   { id: 2, name: 'Bill', company: 'Microsoft' }
  ]
 
  // With single connection
  const connection = await mysql.createConnection({ ...config })
  const { affectedRows } = await upsert(connection)(table, data)
  await connection.end()
 
  // Limit fields
  const { affectedRows } = await upsert(connection)(table, data, ['name', 'company'])
 
  // With pools
  const pool = mysql.createPool({ ...config })
  const { affectedRows } = await upsert(pool)(table, data, ['name', 'company'])  
 
  // With single connection from pool
  const connection = await pool.getConnection()
  const { affectedRows } = await upsert(connection)(table, data)
  await connection.release()

Who made this?

• Torii - https://toriihq.com
• Tal Bereznitskey. Find me on Twitter as @ketacode at https://twitter.com/ketacode