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);
  }
};

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';

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) => {

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

await sql.connect(config);

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}`;

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

console.dir(result);

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

result.rowsAffected

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]}`);
}

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');

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

npm start

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

{
  recordsets: [],
  recordset: undefined,
  output: {},
  rowsAffected: [ 1 ]
}

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);
  }
};

How it works.

First, open a connection to the SQL Server:

const pool = await sql.connect(config);

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`);

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!`);
}

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.

Was this tutorial helpful?