Batch Insert Only Inserts First Row

[g-ambifi]

Hello, thanks for writing this great library!

One issue I am having is running batch inserts. The documentation states that you can pass in an array to insert multiple rows, however, when I try that it only inserts the first row.

let insert = await data.query( "INSERT INTO users(username, firstName, lastName) VALUES(:username,:firstName,:lastName)", [ { "username": "Username test 1", "firstName": "first name 0.6054065569727269", "lastName": "last name 0.5419870975135661" }, { "username": "Username test 2", "firstName": "first name 0.6424165900774252", "lastName": "last name 0.8571923498518657" }, { "username": "Username test 3", "firstName": "first name 0.13976832378006354", "lastName": "last name 0.8039286127593184" } ] );

I did some digging through the source code, batch processing is enabled if the processed param's first child is an array:

const isBatch = processedParams.length > 0 && Array.isArray(processedParams[0]) ? true : false

Passing in parameters as an array of arrays triggers the batch insert:

let insert = await data.query({ sql: "INSERT INTO users(username, firstName, lastName) VALUES(:username,:firstName,:lastName)", parameters: [ [ { "username": "Username test 1", "firstName": "first name 0.6054065569727269", "lastName": "last name 0.5419870975135661" } ], [ { "username": "Username test 2", "firstName": "first name 0.6424165900774252", "lastName": "last name 0.8571923498518657" } ], [ { "username": "Username test 3", "firstName": "first name 0.13976832378006354", "lastName": "last name 0.8039286127593184" } ] ] });

It would be great if one could pass in a one-dimensional array instead of a 2D one to insert multiple rows.

Thanks!

[jeremydaly]

Hi @g-ambifi,

The library automatically merges items in each array, which is by design so that you can use a combination of native Data API params and the library's object syntax. In order for the library to know the difference between single and bulk inserts, you need to use nested arrays.

Thanks for the feedback.

Jeremy

[g-ambifi]

Got it, thanks!

[davegariepy]

Hi, thanks for creating this.

I was only able to get my batch insert to work when passing an object as the first parameter to the query like this:

await data.query({
    sql: `INSERT INTO myTable (userId, date) VALUES(:userId, :date)`,
    parameters: [
      [{ userId: "user-1", date: "2019-08-01" }],
      [{ userId: "user-2", date: "2019-08-02" }]
    ]
  });

Passing the parameters as an array of objects or as nested arrays with objects did not work for me. Both of these attempts resulted in only the first item inserting into the table.

The Simple Examples list includes a batch example with only an array of objects, but seems like it might be a typo since you confirmed that the batch needs to be nested arrays?

Passing a simple array of objects to batch execution would be cool

[cdelgadob]

This made my day! Thanks for sharing @davegariepy , I was going to give up using this lib, but with this solution it seems to work. @jeremydaly , this is a great lib which will be used much more, but only if it works according to the docs. Thanks to you as well :)

[johnelkins]

Thank you @davegariepy! Same sentiment as @cdelgadob ... was just about to call it quits, but now I am super stoked on this library.... and of course big thanks to @jeremydaly for writing it. The default api is super clunky.

[jeremydaly]

FYI, all of you were NOT crazy! There was some logic that flattened arrays when parameters were passed as the second argument. E.g. this form:

let resultParams = await data.query(
  `SELECT * FROM myTable WHERE id = :id`,
  [{ id: 2 }]
)

I'm not sure what the original purpose of that was (thinking it was to do with auto-merging of parameter objects), but it no longer appears to be needed. So even if you were using nested arrays [[ ]], it would get flattened, which moved other items to separate arguments, therefore ignoring them. The object syntax (i.e. specifying sql and parameters) did not have this problem.

Anyway, this has been resolved in v1.0, so please take a look and see if it has introduced any additional problems.

Thanks, Jeremy