Node.js SQL Server Select

Summary: in this tutorial, you will learn how to use query data from the SQL Server in Node.js.

This tutorial begins where the Updating data from the Node.js tutorial left off.

Selecting a single row

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

Step 2. Define a new function that finds the author by id:

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

const findAuthorById = 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(`SELECT * FROM Authors WHERE AuthorID = @id`);

    // return the result
    return result.recordset.length > 0 ? result.recordset[0] : null;
  } catch (err) {
    console.error(err);
  }
};Code language: JavaScript (javascript)

This findAuthorById function does the following:

Connect to the database.
Execute a query to find an author by ID using a parameterized query to prevent SQL injection.
Return the author object if found.

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 allows you 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 query:

const result = await pool
  .request()
  .input('id', sql.Int, id)
  .query(`SELECT * 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(name, type, value) defines input parameters for the query. In this case, it defines the id with the type sql.Int and the value is id.
.query(sqlQuery)executes the SQL query. In this case, it uses the input parameters defined earlier (@id is replaced with the value of the id parameter.

Fourth, return the first record or null if no records were found:

return result.recordset.length > 0 ? result.recordset[0] : null;Code language: JavaScript (javascript)

Step 3. Modify the index.js file to use the findAuthorById function:

import { findAuthorById } from './select.js';

const author = await findAuthorById(1);
console.log(author);Code language: JavaScript (javascript)

How it works.

First, import the function findAuthorById from the select.js module:

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

Second, call the findAuthorById function to find the author with id 1:

const author = await findAuthorById(1);Code language: JavaScript (javascript)

Third, display the author:

console.log(author);Code language: JavaScript (javascript)

Step 4. Run the index.js file:

npm startCode language: JavaScript (javascript)

Output:

{
  AuthorID: 1,
  FirstName: 'George',
  LastName: 'Orwell',
  BirthDate: 1903-06-25T00:00:00.000Z
}Code language: JavaScript (javascript)

Selecting multiple rows

We’ll find the authors by their last names using the LIKE operator.

Step 1. Modify the select.js file by adding the findAuthorsByLastName function:

async function findAuthorsByLastName(term) {
  try {
    // Connect to the database
    const pool = await sql.connect(config);

    // Execute a query
    const result = await pool
      .request()
      .input('term', sql.VarChar, `%${term}%`)
      .query(`SELECT * FROM Authors WHERE LastName LIKE @term`);

    return result.recordset;
  } catch (err) {
    console.error('Error finding authors by last name:', err);
  }
}Code language: JavaScript (javascript)

How it works.

First, connect to the SQL Server:

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

Second, find the authors whose last names contain the input term using the LIKE operator with the % wildcards:

const result = await pool
  .request()
  .input('term', sql.VarChar, `%${term}%`)
  .query(`SELECT * FROM Authors WHERE LastName LIKE @term`);Code language: JavaScript (javascript)

Third, return the record set:

return result.recordset;Code language: JavaScript (javascript)

Step 2. Modify the index.js file to use the findAuthorsByLastName() function:

import { findAuthorsByLastName } from './select.js';

const authors = await findAuthorsByLastName('He');
console.log(authors);Code language: JavaScript (javascript)

Step 3. Run the following command on the terminal to execute the index.js file:

npm startCode language: JavaScript (javascript)

Output:

[
  {
    AuthorID: 11,
    FirstName: 'Ernest',
    LastName: 'Hemingway',
    BirthDate: 1899-07-21T00:00:00.000Z
  },
  {
    AuthorID: 20,
    FirstName: 'Mary',
    LastName: 'Shelley',
    BirthDate: 1797-08-30T00:00:00.000Z
  }
]Code language: JavaScript (javascript)

Download the project source code

Summary

Use the input() method to parameterize the query to prevent SQL injections.
Use the query() method to select one or more rows from the database.

Was this tutorial helpful?