Node-oracledb: Is it possible to insert multiple records at once?

[grug]

Hey there,

If I have an array that looks like this:

[[1,2,3],[4,5,6],[7,8,9]]

And I had a table that has 3 integer columns (let's call the table 'TEST' and the columns 'foo', 'bar' and 'baz'), am I able to insert all this data in one go, or do I need to do a single insert query for each record to insert into the table?

Thanks,
Dave

[sagiegurari]

maybe this will help you,
https://github.com/sagiegurari/simple-oracledb#usage-batchInsert
it will simplify the invocation to a single one, but behind the scenes it is multiple inserts running in parallel (well... on same db connection so nothing is really parallel).

[grug]

Hey @sagiegurari,

It sort of looks like what I'm after, but the documentation is a bit sketchy. Is there any chance you can provide a slightly more basic example than what the documentation is trying to explain?

Thanks,
Dave

[sagiegurari]

sure,
basically you need to extend the oracledb lib (once) like so:

var oracledb = require('oracledb');
var SimpleOracleDB = require('simple-oracledb');
SimpleOracleDB.extend(oracledb);

than when you want to do a multiple insert with same sql but different bind vars, just do the following:

connection.batchInsert('some sql here....', [ //bind vars is array of objects or arrays
  { //first row values
    var1: 'row 1 value 1',
    var2: 'row 1 value 2'
},{ //second row values
    var1: 'row 2 value 1',
    var2: 'row 2 value 2'
}],
}, function onResults(error, output) {
  //continue flow...
});

[sagiegurari]

or take a look at the tests, for example:
https://github.com/sagiegurari/simple-oracledb/blob/47329b0b8955b29d2349c61cdfe3926f39fe06ee/test/spec/connection-spec.js#L1691-L1717

[grug]

Hey @sagiegurari

Still not quite following the implementation. What if I have more than two rows? Do I need to defined more "row values"? If so, how would this cope with an arbitrary number of rows needing to be inserted?

Thanks,
Dave

[sagiegurari]

I"ll take the example you gave, hope it will be more clear.
but basically instead of putting bind vars, you put array of bind vars (bind vars originally is either object or array, so array of bind vars is array of either objects or arrays).
each item in the bind vars array defines the bind vars of a new row.
so you will have a new row for each item in the bind vars array.

Based on the example you gave, i wrote this example that has a bind vars array where each item (defines a row) is an array of values for each column of that row.

connection.batchInsert('INSERT INTO TEST (foo, bar, baz) VALUES (:0, :1, :2)',
  [[1,2,3],[4,5,6],[7,8,9]], 
  function onResults(error, output) {
  //continue flow...
});

[grug]

Hey @sagiegurari,

Thanks for the clarification - that looks good :)

Just one more question - is onResults guaranteed to be called only when the insert statements have completed?

[sagiegurari]

yes. if you also provide options where the autoCommit=true than only if all insert commands were successful, than there is a commit for all rows at once.
the callback is after all rows have been inserted and the result is an array of results (1 result per 1 insert).

[grug]

Perfect. I'll give it a look.

Thanks,
Dave

[cjbj]

@grug also look at https://github.com/oracle/node-oracledb/pull/309 which lets you transfer multiple values to the DB.

OCI array-inserts on the client side have not been implemented.

[grug]

@cjbj Thanks!

[amit-mn]

hey @sagiegurari ,

The connection.batchInsert taking too much time if I have an array with more than 12,000 items. So batch/bulk insert process with batchInsert is not quite good in my case.
Is there any way for bulk insert which support up to 50,000 rows at a time in oracledb?

Thanks,
Amit

[dmcghan]

@amit-mn batchInsert doesn't do bulk operations, it works row by row. To cut the round trips down you'll need to write a little more code and leverage bulk binds.

Given these objects:

create table t (
  id    number not null primary key,
  prop1 number not null,
  prop2 varchar2(50) not null
)
/

create sequence t_seq;

The following should work:

const oracledb = require('oracledb');
const config = require('./dbConfig.js');

async function insertObjects(objs) {
  const start = Date.now();
  let conn;

  try {
    conn = await oracledb.getConnection(config);

    const prop1s = [];
    const prop2s = [];

    // Split the objects up into separate arrays because the driver
    // currently only supports scalar array bindings.
    for (let idx = 0; idx < objs.length; idx += 1) {
      prop1s.push(objs[idx].prop1);
      prop2s.push(objs[idx].prop2);
    }

    const result = await conn.execute(
      ` declare
          type number_aat is table of number
            index by pls_integer;
          type varchar2_aat is table of varchar2(50)
            index by pls_integer;

          l_prop1s number_aat := :prop1s;
          l_prop2s varchar2_aat := :prop2s;
        begin
          forall x in l_prop1s.first .. l_prop1s.last
            insert into t (id, prop1, prop2) values (t_seq.nextval, l_prop1s(x), l_prop2s(x));
        end;`,
      {
        prop1s: {
          type: oracledb.NUMBER,
          dir: oracledb.BIND_IN,
          val: prop1s
        }, 
        prop2s: {
          type: oracledb.STRING,
          dir: oracledb.BIND_IN,
          val: prop2s
        }
      },
      {
        autoCommit: true
      }
    );

    console.log('Success. Inserted ' + objs.length + ' rows in ' + (Date.now() - start) + ' ms.');
  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

function getObjects(count) {
  var objs = [];

  for (let idx = 0; idx < count; idx += 1) {
    objs[idx] = {
      prop1: idx,
      prop2: "Thing number " + idx
    };
  }

  return objs;
}

const objs = getObjects(500);

insertObjects(objs);

We hope to simplify this in the future with associative array binds.

[cjbj]

Batch DML is the way (for us) to go. This would avoid the overhead of PL/SQL. But it is not implemented yet.

[cjbj]

Node-oracledb 2.2 introduces connection.executeMany() (also see Batch Statement Execution) making batch data insert more efficient.