How to insert if not existing and update if existing (upsert)?

Question

I am currently having a confusion with:

var post = {ip: host, status: info};
var sql = con.query('INSERT OR IGNORE INTO ipaddress SET ?', post, 
function(err, result){});

I have already set the IP address to primary key.

Which database is this? It appears to be Oracle, Postgres, or sqlite from a quick search. — Alex Taylor, Aug 09 '18 at 07:28
The operation you're referring to is called `UPSERT`. Knowing what to google is a key :P - Here is a nice article on multiple ways to do what you intend to do - https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/ — AdityaParab, Aug 09 '18 at 08:00

score 1 · Answer 1 · answered Aug 09 '18 at 08:34

Sequelize

Since you're using node.js, how about using Sequelize?

Sequelize is a promise-based ORM for Node.js v4 and up. It supports the dialects PostgreSQL, MySQL, SQLite and MSSQL and features solid transaction support, relations, read replication and more.

It doesn't matter if you don't know SQL well because you can use its built-in functions. For example, if you want to perform an upsert:

ipaddress.upsert({
    ip: host,
    status: info,
});

Pure MySQL

If you still want to use MySQL, use ON DUPLICATE KEY UPDATE to perform an upsert.

var sql = `INSERT INTO post (ip, status) VALUES (${host}, ${info}) ON DUPLICATE KEY UPDATE ip=${host}, status=${info}`;

But you need to take care some potential issues like SQL injection.

So what is safer to use sequelize? or pure mysql? – Christian Angelo M Sulit Aug 09 '18 at 10:27 — Christian Angelo M Sulit, Aug 09 '18 at 10:27

How to insert if not existing and update if existing (upsert)?

1 Answers1

Sequelize

Pure MySQL

Linked