Search code examples
mysqlnode.jsmariadbinsert-update

How to insert array of objects into MariaDB database

I have very simple table in my MariaDB - 3 columns:

  • DT as PK
  • Savings
  • kpd

enter image description here

I want to be able to insert big array of object at once in the table. Here is a piece of the data how looks:

 [  { date: '2021-03-01T05:55:00.000Z', kpdValue: 0, savings: 0 },
    { date: '2021-03-01T06:00:00.000Z', kpdValue: 0, savings: 0 },
    { date: '2021-03-01T06:05:00.000Z', kpdValue: 0, savings: 0 },
    { date: '2021-03-01T06:10:00.000Z', kpdValue: 0, savings: 0 },
    { date: '2021-03-01T06:15:00.000Z', kpdValue: 0, savings: 0 },
......
]

I can receive data with same PK more than once and the idea is when there is PK in new data with same PK in the table - to replace it.

In the example screenshot I have PK DT with value 2021-02-11 15:45:00. I managed to create query which is checking if it exists and if exist replaces it, if not creates it:

INSERT INTO savingswest (DT,Saving,kpd)
 VALUES ('2021-02-11 15:45:00','4.4','1.4') 
 ON DUPLICATE KEY UPDATE Saving='20.5',dt='2021-02-11 15:45:00',kpd='1.9';

How I can do this procedure with the array I receive ?

I am using Node.js.

Solution

  • You can use Knex.js. Specifically the merge method.

    Here's an example:

    knex('tableName')
  .insert([  
    { dt: '2021-03-01T05:55:00.000Z', kpd: 0, savings: 0 },
    { dt: '2021-03-01T06:00:00.000Z', kpd: 0, savings: 0 },
    { dt: '2021-03-01T06:05:00.000Z', kpd: 0, savings: 0 },
    { dt: '2021-03-01T06:10:00.000Z', kpd: 0, savings: 0 },
    { dt: '2021-03-01T06:15:00.000Z', kpd: 0, savings: 0 },
   ])
  .onConflict('dt')
  .merge()

    Output for single record:

    insert into `tableName` (`dt`,`kpd`, `savings`) values ('2021-03-01T05:55:00.000Z', 0, 0) on duplicate key update `kpd` = values(`kpd`), `savings` = values(`savings`)