NodeJS – Insert Multiple Records Into MySQL

Preamble (Insert Multiple Records)

So lets assume you have a large array of data, and you’d like to insert all of this into a database. Doing a for loop and making a new connection for every single piece of data is bad practice and very inefficient. We’re going to look at a couple of ways we can achieve this very easily and a few ways we can make the process more robust.

Connecting To The Database

So to begin with, lets create a new mysql connection. First, lets make sure we have the necessary node module packages.

Next lets go ahead and create a new connection. I’ll be using ES6 for the code examples.

import mysql from 'mysql';
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'username',
    password: 'super_secure_password',
    database: 'database_name'
});
connection.connect((error) => {
    if (error) {
        console.error(error);
    } else {
        console.log('connection successful');
    }
});

Note: you need to input your own host, user, password and database values.

Now we have a standard connection, lets just take a look at an example INSERT statement

connection.connect((error) => {
    if (error) {
        console.error(error);
    } else {
        let query = 'INSERT INTO user (name, age, occupation) VALUES (?, ?, ?);';
		let params = ['fred', 34, 'car sales'];
		connection.query(query, params, (error, result) => {
			// inserted row -> 1
		});
    }
});

Just stepping through the code quickly;

1. We make a new connection (line 1)
2. Write our query string with parameter binding (line 5)
3. We put our vales into an array (line 6)
4. We pass the query string, parameters and callback to the connection (line 7)
5. Line 8, we can now check for errors and validate that we did actually insert a new record

Using Parameter Binding

So as you might have already noticed above, we’ve already started using parameter binding native to the NodeJS package. However, what happens if we have 5 users to insert. Doing what we did above, 5 times over would be a very inefficient method. We don’t want to create 5 new connections and dispose of each one.

So lets use native multiple insert functionality.

connection.connect((error) => {
    if (error) {
        console.error(error);
    } else {
        let query = 'INSERT INTO user (name, age, occupation) VALUES ?;';
		let params = [
			['fred', 34, 'car sales'],
			['john', 21, 'gardner'],
			['peter', 17, 'postman'],
			['rob', 55, 'engineer']
		];
		connection.query(query, [params], (error, result) => {
			// inserted rows -> 4
		});
    }
});

So there’s a few things to pay special attention to here.

1. Notice we don’t have the parentheses around the parameter binding after the ‘VALUES’ sql keyword.
2. Also notice that we actually have a three layer nested array.

Manual SQL Query Construction

So what if all of your data isn’t consistent and you need to make minor changes to the query as you pass through the data. Well it’s possible to manually use the mysql’s escape method. I’ll go into why we need to do this in another post, but suffice it to say, you must!

connection.connect((error) => {
    if (error) {
        console.error(error);
    } else {
		let data = [
			['fred''],
			['john'],
			['peter'],
			['rob']
		];
		let query = '';
		for(let user of data) {
			if(!user[0] !== 'fred') {
				query += 'INSERT INTO user (name) VALUES (' + mysql.escape(user[0]) + ');';
			}
		}
		connection.query(query, (error, result) => {
			// inserted rows -> 3
		});
    }
});

Fairly simple then, just loop over your data (array or object) and construct your query string one at a time. As you can see, we only created a new insert query if the first index of the data was not ‘fred’. You can make this as complex as you like to produce ever more dynamic queries.

PLEASE ensure you make sure to escape ALL parameters, otherwise you may be leaving yourself wide open for SQL Injection, which is bad!

Maximum Packet Size

So I didn’t actually encounter many errors when implementing this in a live system, so I don’t have a plethora of advice on what goes wrong and why; except this.

ER_NET_PACKET_TOO_LARGE

I was trying to bulk insert around 180k records from a CSV file, and I kept getting an error. When I traced the error back to MySQL output in the console, I could see that my MySQL server was running with a fairly small ‘maximum_allowed_packet’.

In order to check what packet size you have set currently, just run this command, and the one underneath it to update the variable.

show variables like 'max_allowed_packet';
set global max_allowed_packet=33554432;