Node.js SQL Server: Update

Summary: in this tutorial, you will learn how to update data in SQL Server from Node.js applications.

This tutorial begins where the Performing a Bulk Insert from Node.js tutorial left off.

We’ll show you how to update the author’s first name in the Authors table.

Updating data in a table

Step 1. Create a new file called update.js in the project directory.

Step 2. Define a new function updateAuthor in the update.js file to update the author’s first name:

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

export const updateAuthor = async (id, firstName) => {
  try {
    // Connect to the database
    await sql.connect(config);

    // Update the First Name
    const result =
      await sql.query`UPDATE Authors SET FirstName =${firstName} WHERE AuthorID=${id}`;

    console.dir(result);
  } catch (err) {
    console.error(err);
  }
};Code language: JavaScript (javascript)

How it works.

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

import sql from 'mssql';
import { config } from './config.js';Code language: JavaScript (javascript)

Note that the config object contains all the database parameters including server, database name, username, and password.

Second, define an updateAuthor function that updates the first name of an author specified by an id:

export const updateAuthor = async (id, firstName) => {Code language: JavaScript (javascript)

Third, connect to the SQL Server using the connect() method of the sql object:

await sql.connect(config);Code language: JavaScript (javascript)

Fourth, execute an UPDATE statement to change the name of the author specified by the author id to the new one:

const result =
      await sql.query`UPDATE Authors SET FirstName =${firstName} WHERE AuthorID=${id}`;Code language: JavaScript (javascript)

Fifth, display the result if the query is executed successfully:

console.dir(result);Code language: JavaScript (javascript)

The result object contains the number of rows updated. To access it, you use the rowsAffected property:

result.rowsAffectedCode language: JavaScript (javascript)

It is an array of numbers indicating the row affected by statements. To access the rows affected by the first statement, you can access the first element of the result.rowsAffected array:

if (result.rowsAffected.length >= 1) {
  console.log(`Row(s) affected: ${result.rowsAffected[0]}`);
}Code language: JavaScript (javascript)

Step 3. Import the updateAuthor from the update.js module and call it to update the first name of the author with id 14 to 'Joanne Kathleen':

import { updateAuthor } from './update.js';

await updateAuthor(14, 'Joanne Kathleen');Code language: JavaScript (javascript)

Step 4. Open the terminal and run the following command to run the index.js file:

npm startCode language: JavaScript (javascript)

If you see the following output, you have successfully updated the row:

{
  recordsets: [],
  recordset: undefined,
  output: {},
  rowsAffected: [ 1 ]
}Code language: JavaScript (javascript)

The output indicates that the rowsAffected contains the number 1, meaning that one row has been updated successfully.

Here’s the detailed information on the properties of the result object:

• recordsets contains an array of recordsets.
• recordset stores the first recordset from an array of recordsets.
• rowsAffected is an array of numbers representing several affected rows by each statement.
• output is key/value collection of output parameters’ values.

Updating data using parameters

The following redefines the updateAuthor that uses parameters to update the first name of an author specified by an ID:

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

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

    // Update the First Name
    const result = await pool
      .request()
      .input('id', sql.Int, id)
      .input('firstName', sql.VarChar, firstName)
      .query(`UPDATE Authors SET FirstName =@firstName WHERE AuthorID=@id`);

    if (result.rowsAffected.length >= 1) {
      console.log(`${result.rowsAffected.length} row(s) updated!`);
    }
  } catch (err) {
    console.error(err);
  }
};Code language: JavaScript (javascript)

How it works.

First, open a connection to the SQL Server:

const pool = await sql.connect(config);Code language: JavaScript (javascript)

The connect() method returns a ConnectionPool object.

Second, execute an UPDATE statement by executing the query method and binding parameters using the input() method:

const result = await pool
  .request()
  .input('id', sql.Int, id)
  .input('firstName', sql.VarChar, firstName)
  .query(`UPDATE Authors SET FirstName =@firstName WHERE AuthorID=@id`);Code language: JavaScript (javascript)

In this statement, the @firstName and @id are parameter holders. They will be replaced by the value of the parameters id and firstName provided to input() methods respectively.

The query() method returns a result object that contains the same information as the sql.query tagged template literals.

Third, display a number of updated rows:

if (result.rowsAffected.length >= 1) {
  console.log(`${result.rowsAffected.length} row(s) updated!`);
}Code language: JavaScript (javascript)

Download the project source code

Download the project source code

Summary

• Use sql.query tag template literals or query() method to execute an UPDATE statement to update a row in a table from Node.js.