Node.js SQL Server: Delete

Summary: in this tutorial, you will learn how to delete data from a table in SQL Server from a Node.js application.

This tutorial begins where the Pagination in Node.js tutorial left off.

Deleting author by ID #

Step 1. Create a new file named delete.js within the project directory:

Step 2. Define a new function called deleteAuthorById in the delete.js file:

import sql from 'mssql';
import { config } from './config.js';

const deleteAuthorById = async (id) => {
  try {
    // Connect to the database
    const pool = await sql.connect(config);

    // Execute the query
    const result = await pool
      .request()
      .input('id', sql.Int, id)
      .query(`DELETE  FROM Authors WHERE AuthorID = @id`);

    if (result?.rowsAffected?.length >= 1) {
      console.log(`${result.rowsAffected.length} row(s) deleted!`);
    }
  } catch (err) {
    console.error(err);
  }
};

export { deleteAuthorById };

How it works.

First, import sql object from mssql package and config object from the config.js module:

import sql from 'mssql';
import { config } from './config.js';

The mssql package provides API to interact with the SQL Server. The config is an object that contains database parameters for the connection.

Second, connect to the database using the provided configuration object:

const pool = await sql.connect(config);

The sql.connect() function returns a ConnectionPool object which allows you to execute queries.

Third, execute a DELETE statement:

const result = await pool
  .request()
  .input('id', sql.Int, id)
  .query(`DELETE  FROM Authors WHERE AuthorID = @id`);

In this statement:

pool.request() creates a new Request instance representing a single query you want to execute against the database.
input('id', sql.Int, id) defines the id with the type sql.Int and the value is id.
.query(`DELETE FROM Authors WHERE AuthorID = @id`) executes a DELETE statement with the input id parameter.

Fourth, show the number of rows deleted:

if (result?.rowsAffected?.length >= 1) {
  console.log(`${result.rowsAffected.length} row(s) deleted!`);
}

Fifth, if an error occurs, display the error message in the catch block:

console.error(err);

Step 3. Export the deleteAuthorById() function:

export { deleteAuthorById };

Step 4. Modify the index.js file to use the deleteAuthorById function:

import { deleteAuthorById } from './delete.js';

deleteAuthorById(5);

How it works.

First, import the deleteAuthorById from the delete.js module:

import { deleteAuthorById } from './delete.js';

Second, delete the author with ID 5 by calling the deleteAuthorById function:

deleteAuthorById(5);

Step 5. Open your terminal and run the following command:

npm start

If you see the following message, meaning the program has successfully deleted the author with ID 5:

1 row(s) deleted!

To delete multiple authors, you can iterate over a list of IDs and call the deleteAuthorById() function to delete each individually. For example:

const ids = [7, 8, 12];
for (const id of ids) {
  deleteAuthorById(id);
}

Download the project source code #

Download the project source code

Summary #

Execute a DELETE statement in the Node.js app using the query() method to delete a row from a table.

Was this tutorial helpful?