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 };Code language: JavaScript (javascript)

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';Code language: JavaScript (javascript)

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);Code language: JavaScript (javascript)

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`);Code language: JavaScript (javascript)

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!`);
}Code language: JavaScript (javascript)

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

console.error(err);Code language: JavaScript (javascript)

Step 3. Export the deleteAuthorById() function:

export { deleteAuthorById };Code language: JavaScript (javascript)

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

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

deleteAuthorById(5);Code language: JavaScript (javascript)

How it works.

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

import { deleteAuthorById } from './delete.js';Code language: JavaScript (javascript)

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

deleteAuthorById(5);Code language: JavaScript (javascript)

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

npm startCode language: JavaScript (javascript)

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

1 row(s) deleted!Code language: JavaScript (javascript)

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);
}Code language: JavaScript (javascript)

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.