Node.js MySQL Insert Record

I pointed a Node script at a MySQL table and the connection succeeded. The INSERT failed on a name containing an apostrophe.

Pasting values into the SQL string holds until real data arrives, then a name like O’Brien ends the run with a syntax error. Placeholders are the fix, and the driver escapes every value for you.

The insert statement in plain SQL

MySQL adds rows with INSERT INTO, and the column list tells the server exactly where each value belongs. Node only delivers that statement, so the SQL has to be right before any JavaScript enters the picture.

INSERT INTO customers (name, address) VALUES ('Company Inc', 'Highway 37');

The statement names the table, names the columns, and supplies one value per column. When the column list is skipped, MySQL expects a value for every column in table order, which breaks the moment the schema changes.

Keep the column list in every statement below. It costs a few words and removes a whole class of silent misalignment.

What you need before the first insert

I ran everything here on Node v26.7.0 with mysql 2.18.1 against MariaDB 10.11.14, installed with a plain npm install mysql and no version pin. You need a reachable server, a database, and one table with an AUTO_INCREMENT primary key.

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  address VARCHAR(255)
);

The id column is the reason later sections can talk about insertId. Without AUTO_INCREMENT there is no generated id to read back, and one FAQ below covers exactly that case.

Connection handling lives in one small file that every script below reuses. If the connection itself is not working yet, my guide to creating a MySQL connection in Node.js walks through it step by step. Before running anything, confirm each item below.

• Node 20 or later with npm available
• mysql package installed via npm install mysql
• A reachable MySQL or MariaDB server with a database you can write to
• The customers table created with the statement above

var mysql = require('mysql');
var con = mysql.createConnection({
  host: '127.0.0.1',
  user: 'your-username',
  password: 'your-password',
  database: 'mydb'
});
con.connect(function (err) {
  if (err) throw err;
  console.log('Connected.');
});

Replace the four connection fields with your own credentials and keep them out of committed code, since the host here is 127.0.0.1 only because that is what my test server listened on.

Insert one row with placeholders

A single insert is one statement with one value per column, and each value travels as a placeholder so the driver escapes it. This is the call to learn first, because the bulk form in the next section is the same idea with more rows.

Pair each value with a placeholder

The question mark holds the position of a value, and the array beside the statement supplies the values in order, so never paste values into the SQL string.

var sql = 'INSERT INTO customers (name, address) VALUES (?, ?)';
con.query(sql, ['Company Inc', 'Highway 37'], function (err, result) {
  if (err) throw err;
  console.log('1 record inserted, ID: ' + result.insertId);
});

The driver sends the statement and the values separately, so a quote inside a value is escaped instead of ending the string. That single separation is what survived O’Brien in my test run while the pasted-string version died.

Run it and read the confirmation

I saved this as insert_single.js and ran it with node insert_single.js against a freshly truncated table. The callback fired with no error and printed the confirmation line below.

The printed id is the AUTO_INCREMENT value MySQL assigned to the new row. result.insertId carries it because the table generates ids, and the result-object section below decodes the rest of that object.

Insert many rows in one query

Five separate INSERT calls mean five round trips, while one statement with grouped values lands every row at once. The bulk form looks odd because the rows sit inside an extra array, and the next step explains why.

Nest the rows inside one outer array

VALUES takes a single placeholder standing for the whole table of rows. Rows must sit inside one outer array, with each row as its own inner array for the driver to expand.

var sql = 'INSERT INTO customers (name, address) VALUES ?';
var values = [
  ['John', 'Highway 71'],
  ['Peter', 'Lowstreet 4'],
  ['Amy', 'Apple st 652'],
  ['Hannah', 'Mountain 21'],
  ['Michael', 'Valley 345']
];
con.query(sql, [values], function (err, result) {
  if (err) throw err;
  console.log('Number of records inserted: ' + result.affectedRows);
});

The outer array wraps the full set because the driver expands exactly one placeholder into the full row list. I ran this as insert_multiple.js right after the single insert, and all five rows landed in one query.

Run it and read affectedRows

The callback reports through affectedRows rather than insertId, because the interesting number for a batch is how many rows landed. My run printed the line shown below with exit code zero.

Five rows, one round trip, one number to check. When the count matches the rows you sent, the batch is complete. The ids MySQL assigned in my run are shown below.

Read the result object without guessing

Every insert callback receives a result object, and two of its fields answer nearly every follow-up question. The rest is server bookkeeping you can log once and then ignore.

affectedRows counts, insertId identifies

affectedRows tells you how many rows the statement added, which is the field to assert in scripts. insertId holds the AUTO_INCREMENT id of the first new row, which is the field to store when later queries must reference the row you created.

The full executed dump

I inserted one row and printed four fields as JSON to keep the dump readable. The run returned affectedRows 1, insertId 7, warningCount 0, and an empty message, which is a clean single-row write. Each field earns its place as follows.

{
  "affectedRows": 1,
  "insertId": 7,
  "warningCount": 0,
  "message": ""
}

The insertId is 7 rather than 1 because earlier test rows in my demo database had already consumed the lower ids. AUTO_INCREMENT never reuses values after deletes or truncates in normal operation, so skipped ids like these are expected rather than a sign of loss.

Confirm the rows landed

A printed confirmation says the driver is happy, while a SELECT says the data is stored. I close every insert with the read below, and you should too.

Run the SELECT

The verification call is the same con.query shape with a plain SELECT and no placeholders. My select-records tutorial breaks that call down in full if the row objects look unfamiliar.

con.query('SELECT * FROM customers', function (err, result) {
  if (err) throw err;
  console.log(result);
});

Against my demo table this returned six rows: the single Company Inc insert followed by the five bulk rows. Each RowDataPacket carries the generated id alongside the name and address, so the ids trace the full story of the session.

RowDataPacket { id: 1, name: 'Company Inc', address: 'Highway 37' }
RowDataPacket { id: 2, name: 'John', address: 'Highway 71' }
RowDataPacket { id: 3, name: 'Peter', address: 'Lowstreet 4' }
RowDataPacket { id: 4, name: 'Amy', address: 'Apple st 652' }
RowDataPacket { id: 5, name: 'Hannah', address: 'Mountain 21' }
RowDataPacket { id: 6, name: 'Michael', address: 'Valley 345' }

Six rows sent, six rows stored. That match is the whole verification.

When the insert breaks

Three failures cover nearly every broken insert I have seen in this corner of Node. Each one has a distinct signature and a distinct fix, so match the symptom before changing code. The table gives the quick map, and the steps below it work each case.

A quote in the value ends a pasted string

A name like O’Brien closes the quoted string early when values are pasted into the statement, and MySQL answers with ER_PARSE_ERROR. Placeholders remove the failure because the driver escapes the value before it reaches the server.

console.log(mysql.escape("O'Brien"));
// 'O\'Brien'

I printed that escape output in my run to watch the mechanism directly. The backslash before the quote is the driver doing its job, and the insert of that name then landed with a fresh id and no error.

Column count and value count disagree

Supplying three values for two named columns fails before anything is written. Count the placeholders against the column list first, because that mismatch is the most common bulk-insert typo.

Connection errors surface at connect time

ECONNREFUSED means the server is unreachable at the given host and port, while ER_ACCESS_DENIED_ERROR means the credentials were rejected. Both throw from the connect callback, so a failing insert that never reaches its query almost always starts there.

What you have now

Single rows go through one placeholder per value, batches go through VALUES with a nested array, and every write ends with affectedRows plus a SELECT. That is the complete working set for inserts from Node. The recap below maps each task to its call and its proof.

The natural next move is deleting a row, which reverses this tutorial through the same con.query shape. My delete-record tutorial picks up exactly there.

FAQ

Four questions come up every time inserts are taught, and each one has a short answer.

Do I always need placeholders for INSERT values?

Yes for anything that is not a fixed literal you typed yourself, because one placeholder hands the driver three jobs at once.

• Quotes inside values get escaped instead of ending the string
• Injected SQL in user input stays inert data
• Numbers, dates, and nulls convert to the right column types

Why does the bulk insert wrap rows in an extra array?

The single placeholder in VALUES stands for the entire row set, so the driver needs one outer array holding every row. Forgetting that wrapper is the most common bulk-insert error.

Why is insertId zero on my table?

insertId reflects the AUTO_INCREMENT counter, so a table without one reports zero by design. Rely on affectedRows there instead.

Should I use mysql or mysql2 for a new project?

This tutorial uses mysql because that is what the original page taught and every sample above ran against it. mysql2 offers a promise interface and prepared statements, so reach for it when you start new work and keep mysql where it already runs.