SQL Server CRUD Tutorials in JavaScript/Node.js: A Step-by-Step Guide

SQL Server is a robust relational database management system developed by Microsoft, widely used in enterprise applications. In this tutorial, we will explore how to perform CRUD (Create, Read, Update, Delete) operations with SQL Server using JavaScript and Node.js. By the end of this article, you’ll have a solid understanding of how to interact with a SQL Server database from your JavaScript applications.

Prerequisites

Node.js installed on your machine.
A running SQL Server instance with the appropriate permissions.
Basic knowledge of JavaScript and Node.js.

Step 1: Install Required Packages

Before we start working with SQL Server, let’s make sure we have the necessary packages installed. We’ll use the mssql package for connecting to the database and executing queries.

npm install mssql

Step 2: Set Up the Database Connection

In your Node.js application, you need to establish a connection to the SQL Server database. Create a JavaScript file (e.g., db.js) and add the following code:

const sql = require("mssql")

const config = {
  user: "your-username",
  password: "your-password",
  server: "your-server-name", // Replace with your SQL Server instance name
  database: "your-database-name",
  options: {
    trustServerCertificate: true // Change this to false in production
  }
}

async function connectToDatabase() {
  try {
    const pool = await sql.connect(config)
    console.log("Connected to SQL Server Database")
    return pool
  } catch (err) {
    console.error("Error connecting to SQL Server:", err)
  }
}

module.exports = connectToDatabase

Replace 'your-username', 'your-password', 'your-server-name', and 'your-database-name' with your SQL Server credentials and server information.

Step 3: Create a New Table (Create - C)

Now, let’s create a table to store our data. Create a JavaScript file (e.g., createTable.js) and add the following code:

const connectToDatabase = require("./db")

async function createTable() {
  const pool = await connectToDatabase()

  try {
    await pool.request().query(`
      CREATE TABLE IF NOT EXISTS Users (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Name NVARCHAR(255) NOT NULL,
        Email NVARCHAR(255) NOT NULL
      )
    `)
    console.log("Table created: Users")
  } catch (err) {
    console.error("Error creating table:", err)
  } finally {
    pool.close()
  }
}

createTable()

This code defines a table schema for storing user information.

Step 4: Insert Data (Create - C)

To insert data into the database, create a JavaScript file (e.g., insertData.js) and add the following code:

const connectToDatabase = require("./db")

async function insertData() {
  const pool = await connectToDatabase()

  try {
    const result = await pool
      .request()
      .input("name", sql.NVarChar, "John Doe")
      .input("email", sql.NVarChar, "[email protected]")
      .query("INSERT INTO Users (Name, Email) VALUES (@name, @email)")
    console.log(`New record added with ID: ${result.rowsAffected}`)
  } catch (err) {
    console.error("Error inserting data:", err)
  } finally {
    pool.close()
  }
}

insertData()

This code inserts a new user into the ‘Users’ table and retrieves the number of rows affected.

Step 5: Retrieve Data (Read - R)

To read data from the database, create another JavaScript file (e.g., retrieveData.js) with the following code:

const connectToDatabase = require("./db")

async function retrieveData() {
  const pool = await connectToDatabase()

  try {
    const result = await pool.request().query("SELECT * FROM Users")
    result.recordset.forEach((row) => {
      console.log(`ID: ${row.ID}, Name: ${row.Name}, Email: ${row.Email}`)
    })
  } catch (err) {
    console.error("Error retrieving data:", err)
  } finally {
    pool.close()
  }
}

retrieveData()

This code retrieves all user records from the ‘Users’ table and prints them to the console.

Step 6: Update Data (Update - U)

To update existing data, create a JavaScript file (e.g., updateData.js) with the following code:

const connectToDatabase = require("./db")

async function updateData(id, newName) {
  const pool = await connectToDatabase()

  try {
    const result = await pool
      .request()
      .input("id", sql.Int, id)
      .input("newName", sql.NVarChar, newName)
      .query("UPDATE Users SET Name = @newName WHERE ID = @id")
    console.log(`Record updated: ${result.rowsAffected} rows affected`)
  } catch (err) {
    console.error("Error updating data:", err)
  } finally {
    pool.close()
  }
}

updateData(1, "Jane Doe")

This code updates a user’s name based on their ID.

Step 7: Delete Data (Delete - D)

To delete data from the database, create a JavaScript file (e.g., deleteData.js) with the following code:

const connectToDatabase = require("./db")

async function deleteData(id) {
  const pool = await connectToDatabase()

  try {
    const result = await pool
      .request()
      .input("id", sql.Int, id)
      .query("DELETE FROM Users WHERE ID = @id")
    console.log(`Record deleted: ${result.rowsAffected} rows affected`)
  } catch (err) {
    console.error("Error deleting data:", err)
  } finally {
    pool.close()
  }
}

deleteData(1)

This code deletes a user record based on their ID.

Conclusion

In this article, we’ve covered the fundamental CRUD operations using SQL Server with JavaScript and Node.js. You’ve learned how to create and connect to a database, create tables, insert, retrieve, update, and delete data. SQL Server is a powerful database system used in many enterprise applications, and integrating it with JavaScript and Node.js allows you to build robust database-driven applications. Remember to handle errors gracefully and adapt these examples to suit your specific project requirements.