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.rowsAffected
Code 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 start
Code 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 firstrecordset
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 orquery()
method to execute anUPDATE
statement to update a row in a table from Node.js.