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

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

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

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

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;

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

How it works.

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

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

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

const author = await findAuthorById(1);

Third, display the author:

console.log(author);

Step 4. Run the index.js file:

npm start

Output:

{
  AuthorID: 1,
  FirstName: 'George',
  LastName: 'Orwell',
  BirthDate: 1903-06-25T00:00:00.000Z
}

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

How it works.

First, connect to the SQL Server:

const pool = await sql.connect(config);

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

Third, return the record set:

return result.recordset;

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

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

npm start

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
  }
]

Node.js SQL Server: Select

Selecting a single row #

Selecting multiple rows #

Download the project source code #

Summary #