SQL Server CRUD Operation with NodeJS

Back to: Mastering Node.js and SQL Server

SQL Server CRUD Operations with Node.js – Comprehensive Guide with Examples

Introduction:

In this detailed lesson, we will explore how to perform CRUD operations (Create, Read, Update, Delete) in a SQL Server database using Node.js. We’ll cover each operation with practical examples to help you master these fundamental database operations in a real-world context.

Prerequisites:

Node.js installed on your machine.
SQL Server installed and configured.
npm packages: mssql for SQL Server connectivity.

Step 1: Setting Up Your Node.js

Project: Begin by creating a new
Node.js project and installing the necessary dependencies. Open your terminal and navigate to your project directory.

mkdir node-crud-sql-server
cd node-crud-sql-server
npm init -y
npm install mssql

Step 2: Connecting to SQL Server:

Establish a connection to your SQL Server instance using the mssql package.

const sql = require('mssql');

const config = {
  user: 'your_username',
  password: 'your_password',
  server: 'your_server_name',
  database: 'your_database_name',
};

async function connectToSqlServer() {
  try {
    await sql.connect(config);
    console.log('Connected to SQL Server');
  } catch (error) {
    console.error('Error connecting to SQL Server:', error);
  }
}

connectToSqlServer();

Step 3: CRUD Operations Examples:

3.1. Create (INSERT) Operation:

async function createRecord() {
  try {
    const request = new sql.Request();
    const insertQuery = "INSERT INTO YourTable (Column1, Column2) VALUES ('Value1', 'Value2')";
    const result = await request.query(insertQuery);
    console.log('Record inserted successfully.');
  } catch (error) {
    console.error('Error creating record:', error);
  }
}

3.2. Read (SELECT) Operation:

async function readRecords() {
  try {
    const request = new sql.Request();
    const selectQuery = 'SELECT * FROM YourTable';
    const result = await request.query(selectQuery);
    console.log('Records retrieved:', result.recordset);
  } catch (error) {
    console.error('Error reading records:', error);
  }
}

3.3. Update (UPDATE) Operation:

async function updateRecord() {
  try {
    const request = new sql.Request();
    const updateQuery = "UPDATE YourTable SET Column1 = 'NewValue' WHERE SomeCondition";
    const result = await request.query(updateQuery);
    console.log('Record updated successfully.');
  } catch (error) {
    console.error('Error updating record:', error);
  }
}

3.4. Delete (DELETE) Operation:

async function deleteRecord() {
  try {
    const request = a new sql.Request();
    const deleteQuery = "DELETE FROM YourTable WHERE SomeCondition";
    const result = await request.query(deleteQuery);
    console.log('Record deleted successfully.');
  } catch (error) {
    console.error('Error deleting record:', error);
  }
}

Conclusion:

You have learned how to perform CRUD operations in a SQL Server database using Node.js. These examples cover creating, reading, updating, and deleting records. Remember to replace placeholders like ‘YourTable’, ‘Column1’, ‘Value1’, ‘SomeCondition’, and the connection details with your specific database information.

Stay Connected