Multi row insert using Knex.js

Am trying to build an multi-row insert query using Knex.js

My post request contains a variable which is formatted in the following format : [{addon_name:'sugar'},{addon_name:'milk'}]

My DB table has only one column namely addon_name

My knex query in my node application goes as follows

knex(`<table_name>`).insert(req.body.`<param_name>`))
expected op
insert into `<tablename>`(`addon_name`) values (sugar), (milk);

but the code dosn't work. Any comments ?

Error Details
{ [Error: insert into `table_name` (`0`, `1`, `10`, `11`, `12`, `13`, `14`, `15`, `16`, `17`, `18`, `19`, `2`, `20`, `21`, `22`, `23`, `24`, `25`, `26`, `27`, `28`, `29`, `3`, `30`, `31`, `32`, `33`, `34`, `35`, `36`, `37`, `38`, `39`, `4`, `40`, `41`, `5`, `6`, `7`, `8`, `9`) values ('[', '{', 'm', 'e', ':', '\'', 's', 'u', 'g', 'a', 'r', '\'', 'a', '}', ',', '{', 'a', 'd', 'd', 'o', 'n', '_', 'n', 'd', 'a', 'm', 'e', ':', '\'', 'm', 'i', 'l', 'k', '\'', 'd', '}', ']', 'o', 'n', '_', 'n', 'a') - ER_BAD_FIELD_ERROR: Unknown column '0' in 'field list']
  code: 'ER_BAD_FIELD_ERROR',
  errno: 1054,
  sqlState: '42S22',
  index: 0 }

Though this is an old question, I am replying here just for others who stumble upon this.

Knex now supports multi-row inserts like this:

knex('coords').insert([{x: 20}, {y: 30},  {x: 10, y: 20}])

outputs:

insert into `coords` (`x`, `y`) values (20, DEFAULT), (DEFAULT, 30), (10, 20)

There's also the batchInsert utility will inserts a batch of rows wrapped inside a transaction.

How to do multiple inserts in one query? � Issue #445 � bookshelf , to use knex with bookshelf.js? For example: var rows = books.map(function( book) { return book.attributes }) knex.insert(rows).into('books'). Creates an insert query, taking either a hash of properties to be inserted into the row, or an array of inserts, to be executed as a single insert command. If returning array is passed e.g. ['id', 'title'], it resolves the promise / fulfills the callback with an array of all the added rows with specified columns.

req.body.<param_name> is always a string. Most probably this will work for you:

knex(table_name).insert(JSON.parse(req.body.param_name)));

What you are seeing in your error is Knex treating the string as an array of chars, trying to push it to the table.

In the error, the following:

values ('[', '{', 'm', 'e', ':', '\'', 's', ...

Is actually your string being broken down: [{me:\'s....

knex insert multiple rows Code Example, Javascript queries related to “knex insert multiple rows” site:stackoverflow.com javascript replace multiple spaces with single space � Vue.js� Knex.js. Knex.js is a JavaScript query builder for relational databases including PostgreSQL, MySQL, SQLite3, and Oracle. It can be used with callbacks and promises. It supports transactions and connection pooling. In this tutorial we work with MySQL. Setting up Knex.js. First, we need to install Knex.js. $ nodejs -v v9.11.2 We use Node version

Thanks. I changed the structure of my input in post method, to an comma separated string. That way it gets easier to parse the input and model it the way I need.

post method input : "milk,sugar"

code
//Knex accepts multi row insert in the following format [{},{}] => we need to 
//model our input that way
var parsedValues = [];
try {
    var arr = req.body.addons.split(',');
}catch(err){
    return res.send({ "Message": "405" }); // Data not sent in proper format
}

for (var i in arr) {
    parsedValues.push({addon_name: arr[i]});
}
console.log(parsedValues);

knex(`<table_name>`).insert(parsedValues).then(function (rows){ 
    console.log(rows);
    return res.send({ "Message": "777" }); // Operation Success
}).catch(function (err){
    console.log(err)
    return res.send({ "Message": "403" }); // PK / FK Violation
});

Knex.js, Knex can be built using a JavaScript build tool such as browserify or webpack. connection for sqlite3 (due to issues with utilizing multiple connections on a single file). Hook for modifying returned rows, before passing them forward to user. and executing standard SQL queries, such as select , insert , update , delete . One-page guide to Knex: usage, examples, and more. Knex is an SQL query builder for Node.js.This guide targets v0.13.0.

Knex js multiple insert, Knex js multiple insert. I have a problem inserting many rows into postgres db with knex. run();. knexfile. 0 is now officially GA, and it comes with a bunch of� Here Mudassar Ahmed Khan has explained with an example, how to insert (save) multiple rows (records) to database using Entity Framework in ASP.Net MVC Razor. First the multiple rows (records) will be insert into an HTML Table and then the data from all the rows of the HTML Table will be sent to Controller using jQuery AJAX which will be ultimately saved to database using Entity Framework in

mysql, so I've been playing around with knex lately, however I found myself on a JavaScript Knex.js. mysql - How to add multiple rows using "Insert . The number of rows that you can insert at a time is 1,000 rows using this form of the INSERT statement. If you want to insert more rows than that, you should consider using multiple INSERT statements, BULK INSERT or a derived table. Note that this INSERT multiple rows syntax is only supported in SQL Server 2008 or later.

tgriesser/knex, I'm using knex to do a batch insert into a mssql database and I want to ignore errors while So you need to insert every row as single query and wrap that to try - catch. https://github.com/brianc/node-postgres/blob/master/lib/index.js#L37 . Get code examples like "knex insert multiple rows" instantly right from your google search results with the Grepper Chrome Extension.

Comments
  • How doesn't it work? Do you get any errors?
  • Have updated the post with error details
  • use a content-type: application/json header and a body parser, then your message body will be interpreted as an object.
  • This isn't works in Mysql if there is a big insert. Only in Postgres