Node.js SQL Server: Insert

Summary: in this tutorial, you will learn how to insert data into a table in SQL Server from a Node.js program.

This tutorial begins where the Connecting to SQL Server from Node.js tutorial left off.

Inserting a new row into the table

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

Step 2. Add the following code to the insert.js to insert a new author into the Authors table:

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

const insertAuthor = async (firstName, lastName, birthDate) => {
  try {
    // Connect to the database
    await sql.connect(config);

    // Execute the insert statement
    await sql.query`INSERT INTO Authors (FirstName, LastName, BirthDate) 
                    VALUES (${firstName}, ${lastName}, ${birthDate})`;

    console.log(`The author ${firstName} ${lastName} was inserted!`);
  } catch (err) {
    console.error('Error inserting new author:', err);
  } finally {
    // Close the database connection
    await sql.close();
  }
};

insertAuthor('Agatha', 'Christie', '1890-09-15');Code language: JavaScript (javascript)

How it works.

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

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

Please note that the config object contains the database configuration defined in the connecting to the SQL Server from the Node.js program tutorial.

Second, define a function insertAuthor that has three parameters firstName, lastName, and birthDate:

const insertAuthor = async (firstName, lastName, birthDate) => {Code language: JavaScript (javascript)

Third, connect to the SQL Server by calling the connect method of the sql object with the config object:

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

Fourth, execute the INSERT statement:

 await sql.query`INSERT INTO Authors (FirstName, LastName, BirthDate) 
                 VALUES (${firstName}, ${lastName}, ${birthDate})`;Code language: JavaScript (javascript)

Notice that the mssql package leverages the tagged template literals to construct SQL statements in a way that automatically handles SQL injection prevention and parameterized query.

Fifth, show a message on the console to indicate that the author has been inserted successfully:

console.log(`The author ${firstName} ${lastName} was inserted!`);Code language: JavaScript (javascript)

Sixth, if an error occurs during the insertion process, display the error message in the catch block:

console.error('Error inserting new author:', err);Code language: JavaScript (javascript)

Seventh, close the database connection in the finally block by calling the close() method of the sql object:

await sql.close();Code language: JavaScript (javascript)

Finally, call the insertAuthor function to insert the author with the first name Agatha, last name Christie, and birth date 1890-09-15 into the Authors table:

insertAuthor('Agatha', 'Christie', '1890-09-15');Code language: JavaScript (javascript)

Step 3. Execute the following npm command to run the insert.js file:

npm --env-file=.env insert.js

Verifying the insert

Step 1. Open SQL Server Management Studio and connect the BookStore database.

Step 2. Query data from the Authors table:

SELECT * FROM Authors;Code language: SQL (Structured Query Language) (sql)

Output:

AuthorID | FirstName | LastName | BirthDate
---------+-----------+----------+------------
1        | Agatha    | Christie | 1890-09-15
(1 row)

Insert multiple rows into a table

Step 1. Create a new file named insert-many.js in the project directory.

Step 2. Add the following code to the insert-many.js to insert multiple authors from an array into the Authors table:

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

async function insertAuthors(authors) {
  try {
    // Connect to the database
    await sql.connect(config);

    // Construct the insert query
    let query = 'INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES ';

    // Build the values part of the query
    const values = authors
      .map(
        ({ firstName, lastName, birthDate }) =>
          `('${firstName}', '${lastName}', '${birthDate}')`
      )
      .join(', ');

    // Complete the query
    query += values;

    // Execute the query
    await sql.query(query);

    console.log(`${authors.length} authors were inserted successfully!`);
  } catch (err) {
    console.error('Error inserting authors:', err);
  } finally {
    // Close the database connection
    await sql.close();
  }
}

const authors = [
  { firstName: 'George', lastName: 'Orwell', birthDate: '1903-06-25' },
  { firstName: 'Jane', lastName: 'Austen', birthDate: '1775-12-16' },
  { firstName: 'Leo', lastName: 'Tolstoy', birthDate: '1828-09-09' },
];

// Call the function to insert the authors
insertAuthors(authors);
Code language: JavaScript (javascript)

How it works (focusing on the new part)

First, construct an insert statement that inserts multiple rows into the Authors table:

// Construct the insert query
let query = 'INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES ';

// Build the values part of the query
const values = authors
  .map(
    ({ firstName, lastName, birthDate }) =>
      `('${firstName}', '${lastName}', '${birthDate}')`
  )
  .join(', ');

// Complete the query
query += values;
Code language: JavaScript (javascript)

Second, execute the insert statement using the query() method of the sql object:

await sql.query(query);Code language: JavaScript (javascript)

Third, initialize an array of author objects for insertion:

const authors = [
  { firstName: 'George', lastName: 'Orwell', birthDate: '1903-06-25' },
  { firstName: 'Jane', lastName: 'Austen', birthDate: '1775-12-16' },
  { firstName: 'Leo', lastName: 'Tolstoy', birthDate: '1828-09-09' },
];Code language: JavaScript (javascript)

In practice, you may read the data from a CSV file or get the data from an API call.

Finally, call the insertAuthors() function to insert the author objects from the authors array:

insertAuthors(authors);Code language: JavaScript (javascript)

Step 3. Run the following command in your terminal to execute the index-many.js file:

node --env-file=.env insert-many.js

If you see the following message, you have successfully inserted three authors from the array into the Authors table.

Download the project source code

Summary

Construct an INSERT statement using the tagged template literals to insert a new row into a table.

Was this tutorial helpful?