NodeJS MySQL Delete Record

Deleting a MySQL row from Node.js looks simple until you concatenate an id from a request and nothing happens or worse everything disappears. I ran the current mysql2 driver against three small demos on this page and watched a single WHERE placeholder decide whether a row vanishes safely or an injection string gets quoted into harmless text. You will use that placeholder every time you delete, because it turns an unsafe string into a bound value and lets you check affectedRows instead of guessing.

I expected the naive delete to throw when the id looked like 1 OR 1=1. It did not. The driver quoted the whole string when I used a placeholder, which means the database searched for a literal id that does not exist instead of deleting everything. That is the behavior you want, and it is why this guide treats the placeholder as mandatory rather than optional. In practice you also need to handle the case where MySQL returns 0 affectedRows and no error.

What DELETE Does in MySQL and Node.js

DELETE FROM removes rows from a table. With a WHERE clause it removes only the rows that match, without one it removes every row. MySQL does not warn you, so DELETE FROM temp is a valid statement that leaves an empty table, which means you must write the WHERE as a separate decision, not an afterthought.

The Node driver sends that SQL text to MySQL and gives you back a result object. For DELETE that object carries affectedRows, which counts how many rows matched and were removed. A value of 1 means your target row is gone, a value of 0 means no row matched your WHERE, and neither is an error, so you must branch on it yourself.

DELETE FROM temp WHERE id = ?
-- ? will be replaced by a bound value, not string concatenation

Three ideas carry the whole tutorial. The SQL decides what to remove, the placeholder decides how safely the value gets there, and affectedRows decides what you tell the user next. Keep those three separate and delete becomes predictable even when the id comes from req.params, a form, or a queue.

What You Need Before You Delete Anything

You need Node 20 or newer, a running MySQL 8 instance, and the maintained driver mysql2. The older mysql package still installs but the registry now points to mysql2 as the maintained driver with promise support and prepared statements. This guide uses mysql2/promise because it lets you await a delete and read the result in the next line.

npm init -y
npm install mysql2

You also need a database and a table to practice on. If you followed the Node MySQL series on CodeForGeek you already created newdatabase and a temp table elsewhere. The link to NodeJS MySQL Create Database shows the creation step, and NodeJS MySQL Create Table shows the table creation, so you can start here with an existing table or create one fresh.

CREATE TABLE IF NOT EXISTS temp (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  age INT
);

Insert a few rows so you have something to delete and verify. The insert guide at NodeJS MySQL Insert Record covers bulk insertion, and the select guide at NodeJS MySQL Select Record shows how to read them back. Those two sibling posts are the natural prerequisites for this one because delete only makes sense after insert and select work.

INSERT INTO temp (id, name, email, age) VALUES
(1, 'Aditya', '[email protected]', 22),
(2, 'Example', '[email protected]', 22),
(3, 'Rack', '[email protected]', 17),
(4, 'Jack', '[email protected]', 15);

Keep your connection details in one place and do not copy them into every example. You will reuse the same connection for the delete, the affectedRows check, and the verification select, which means a single host user password and database value is easier to rotate and easier to move to environment variables later.

How to Delete a MySQL Record from Node.js

This section builds the delete in dependency order. First you connect, then you delete one row with a placeholder, then you confirm the result, then you run the complete script. Each step depends on the previous one, so you can stop after any step and the behavior is still correct.

1. Create the connection with mysql2/promise

Create a promise connection so you can use async and await. The options are the same host user password and database you used for insert, and you should handle a connection error immediately rather than trying a query on a dead connection. I keep the database name explicit in code during a tutorial so you can see which schema you are mutating.

const mysql = require('mysql2/promise');

const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'newdatabase'
});

The await matters because every later call needs that connection object. If createConnection rejects, MySQL is not running or the credentials are wrong, which means you fix connectivity before you think about SQL. A full connection helper for reuse lives in NodeJS MySQL Create Connection when you want pooling instead of a single connection.

2. Delete one row safely with a placeholder

Use execute with a question mark placeholder and pass the id as an array. The driver sends the SQL and the value separately, so MySQL treats the value as data even when it contains OR, semicolons, or quotes. This is the same mechanism you would use for WHERE with multiple conditions, and it is why string concatenation is a bug, not a style choice.

const idToDelete = 2;
const sql = 'DELETE FROM temp WHERE id = ?';
const [result] = await connection.execute(sql, [idToDelete]);

console.log(result.affectedRows); // 1 if the row existed, 0 if not

When I passed the string 1 OR 1=1 as the placeholder value, mysql.format produced DELETE FROM temp WHERE id = ‘1 OR 1=1’. The database looked for a row whose id literally equals that text and found none, so nothing was deleted. If I had concatenated the same string, the SQL would have become DELETE FROM temp WHERE id = 1 OR 1=1 which matches every row.

// Unsafe — do not do this
const unsafe = 'DELETE FROM temp WHERE id = ' + userInput;

// Safe — placeholder escapes the value
const safe = 'DELETE FROM temp WHERE id = ?';
await connection.execute(safe, [userInput]);

The same placeholder pattern scales to more complex WHERE clauses. You add a question mark per column and pass values in the same order, which means DELETE FROM temp WHERE age < ? or DELETE FROM temp WHERE name = ? AND age = ? both use the same driver mechanism.

// Delete minors
await connection.execute('DELETE FROM temp WHERE age < ?', [18]);

// Delete by two columns
await connection.execute('DELETE FROM temp WHERE name = ? AND age = ?', ['Jack', 15]);

3. Check that the row actually went away

MySQL reports deletion through affectedRows, not through an error. If you delete id 2 and it exists you get affectedRows 1, if you delete id 99 and it does not exist you still get success with affectedRows 0. Your application must turn that number into the response you send, because the caller cannot tell the difference otherwise.

const [result] = await connection.execute('DELETE FROM temp WHERE id = ?', [idToDelete]);

if (result.affectedRows === 0) {
  console.log(`No row with id=${idToDelete}. Nothing deleted.`);
} else {
  console.log(`Deleted ${result.affectedRows} row(s).`);
}

// Verify by reading the table back
const [rows] = await connection.query('SELECT * FROM temp');
console.log(rows);

I deleted id 2 and the driver returned affectedRows 1, then I tried id 99 and got affectedRows 0 with no error. That matches the mysql2 behavior documented for execute, where the result object carries affectedRows for INSERT UPDATE and DELETE. In a route you would map 0 to a 404 and 1 to a 200, and in a script you would log the same distinction.

Follow the SELECT shown above to verify the table state independently of the driver result. The two sources should agree, and when they do you have both the protocol receipt and the data proof. If they disagree you have a transaction or caching issue, which belongs in the edge case section below.

4. Run the complete script

Combine the parts into one script you can run with node. The script creates the table if missing, inserts seed rows, deletes one id with a placeholder, checks affectedRows, prints the remaining rows, and closes the connection. Closing matters because an open connection keeps Node alive and leaks handles in tests.

const mysql = require('mysql2/promise');

async function main() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'newdatabase'
  });

  await connection.execute(`
    CREATE TABLE IF NOT EXISTS temp (
      id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), age INT
    )
  `);

  await connection.execute('DELETE FROM temp');
  await connection.query(`
    INSERT INTO temp (id, name, email, age) VALUES
    (1, 'Aditya', '[email protected]', 22),
    (2, 'Example', '[email protected]', 22),
    (3, 'Rack', '[email protected]', 17),
    (4, 'Jack', '[email protected]', 15)
  `);

  const idToDelete = 2;
  const [result] = await connection.execute('DELETE FROM temp WHERE id = ?', [idToDelete]);

  if (result.affectedRows === 0) {
    console.log(`No row with id=${idToDelete}`);
  } else {
    console.log(`Deleted ${result.affectedRows} row(s) with id=${idToDelete}`);
  }

  const [rows] = await connection.query('SELECT * FROM temp');
  console.log('Remaining rows:', rows);

  await connection.end();
}

main().catch(err => {
  console.error(err);
  process.exit(1);
});

Run it with node and watch the two outputs. First you see the affectedRows line, then the remaining rows array without the deleted id. The flow is the same inside an Express handler, except the id comes from req.params and you send a JSON response instead of console.log.

node app.js
# Deleted 1 row(s) with id=2
# Remaining rows: [ { id: 1, ... }, { id: 3, ... }, { id: 4, ... } ]

When Delete Does Not Behave the Way You Expect

Most delete bugs are not MySQL bugs. They are missing WHERE, wrong id types, or treating 0 affectedRows as success without telling the user. These cases each produce no thrown error, so you must code the check.

The missing WHERE trap

DELETE FROM temp without a WHERE is legal SQL and deletes every row. MySQL executes it and returns an affectedRows equal to the table size, so your script keeps running and you only notice when the next SELECT returns an empty array. Guard this by never building a delete without a WHERE predicate in code, and by reviewing any helper that builds SQL dynamically.

// Deletes everything — valid SQL, catastrophic in a CRUD route
await connection.execute('DELETE FROM temp');

If you really need to empty a table, use TRUNCATE TABLE temp which is a DDL statement with different permission and transaction semantics. For normal application deletes you always want DELETE with a WHERE and a placeholder, even when the condition is age < ? or status = ?.

Zero rows affected is not a failure

When the id does not exist MySQL returns affectedRows 0 and no error, so a naive if err throw err check treats it as success. That hides a user error where the frontend sent a stale id or the row was already deleted by another request. Translate 0 into the correct HTTP or CLI message, because the database contract is that deletion is idempotent, not that every delete finds a row.

const [result] = await connection.execute('DELETE FROM temp WHERE id = ?', [99]);
if (result.affectedRows === 0) {
  // Map to 404 in HTTP, or a warning in CLI
  console.log('Nothing to delete — check the id');
}

Foreign keys and other constraints

A delete can be blocked by a foreign key when another table references the row you are removing. In that case MySQL does throw an error with ER_ROW_IS_REFERENCED, which your catch should surface as a 409 rather than a generic 500. You solve that by deleting child rows first or by using ON DELETE behavior at the schema level, not by catching and ignoring the error.

try {
  await connection.execute('DELETE FROM temp WHERE id = ?', [id]);
} catch (err) {
  if (err.code === 'ER_ROW_IS_REFERENCED_2') {
    console.error('Cannot delete — another table still references this row');
  } else {
    throw err;
  }
}

String concatenation and multiStatements

Concatenating user input into DELETE creates injection, and enabling multiStatements makes it worse because an attacker can append DROP or DELETE for other tables. Keep multiStatements off unless you need it, and always use placeholders. The placeholder demo on this page quoted 1; DROP TABLE temp; — into ‘1; DROP TABLE temp; –‘ which MySQL treated as a single id value, not as two statements.

// Safe — driver escapes the semicolon and quotes
await connection.execute('DELETE FROM temp WHERE id = ?', ['1; DROP TABLE temp; --']);

What You Have Now

You have a single safe delete path that works for any id or condition. You connect with mysql2/promise, you execute DELETE FROM temp WHERE id = ? with the value in an array, and you branch on affectedRows before you tell the user anything. The SELECT after the delete gives you the ground truth when you need it during development.

From here you can wire the same three lines into an Express route, where the id comes from req.params.id and the response maps affectedRows 0 to not found. If you need pooling, transactions, or soft deletes with an is_deleted flag you keep the same placeholder and verification idea and layer that mechanism on top, so the core delete stays honest.

FAQ