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

Oracle Database is a powerful relational database management system used by enterprises for managing large-scale data. In this tutorial, we will explore how to perform CRUD (Create, Read, Update, Delete) operations with Oracle Database using JavaScript and Node.js. By the end of this article, you’ll have a solid understanding of how to interact with an Oracle Database from your JavaScript applications.

Prerequisites

1. Node.js installed on your machine.
2. Access to an Oracle Database instance with appropriate privileges.
3. Basic knowledge of JavaScript and Node.js.

Step 1: Install Required Packages

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

npm install oracledb

Step 2: Set Up the Database Connection

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

const oracledb = require("oracledb")

async function connectToDatabase() {
  try {
    await oracledb.initOracleClient({ libDir: "path-to-oracle-client-lib" }) // Replace with the path to your Oracle client library
    const connection = await oracledb.getConnection({
      user: "your-username",
      password: "your-password",
      connectString: "your-connect-string" // Replace with your Oracle Database connection string
    })
    console.log("Connected to Oracle Database")
    return connection
  } catch (err) {
    console.error("Error connecting to Oracle Database:", err)
  }
}

module.exports = connectToDatabase

Replace 'path-to-oracle-client-lib', 'your-username', 'your-password', and 'your-connect-string' with the appropriate values for your Oracle Database setup.

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

Now, let’s create a function to insert data into an Oracle Database table. Create a JavaScript file (e.g., create.js) and add the following code:

const connectToDatabase = require("./db")

async function createRecord(name, email) {
  const connection = await connectToDatabase()

  try {
    const result = await connection.execute(
      `INSERT INTO users (name, email) VALUES (:name, :email)`,
      [name, email]
    )
    console.log(`New record added with ID: ${result.lastRowid}`)
  } catch (err) {
    console.error("Error creating record:", err)
  } finally {
    connection.close()
  }
}

createRecord("John Doe", "[email protected]")

This code defines a function createRecord that inserts a new user into a ‘users’ table.

Step 4: Read Data (Read - R)

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

const connectToDatabase = require("./db")

async function readRecords() {
  const connection = await connectToDatabase()

  try {
    const result = await connection.execute(`SELECT * FROM users`)
    const rows = result.rows
    rows.forEach((row) => {
      console.log(`ID: ${row[0]}, Name: ${row[1]}, Email: ${row[2]}`)
    })
  } catch (err) {
    console.error("Error reading records:", err)
  } finally {
    connection.close()
  }
}

readRecords()

This code defines a function readRecords that retrieves all user records from the ‘users’ table and prints them to the console.

Step 5: Update Data (Update - U)

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

const connectToDatabase = require("./db")

async function updateRecord(id, newName) {
  const connection = await connectToDatabase()

  try {
    const result = await connection.execute(
      `UPDATE users SET name = :newName WHERE id = :id`,
      [newName, id]
    )
    console.log(`Record updated: ${result.rowsAffected} rows affected`)
  } catch (err) {
    console.error("Error updating record:", err)
  } finally {
    connection.close()
  }
}

updateRecord(1, "Jane Doe")

This code defines a function updateRecord that updates a user’s name based on their ID.

Step 6: Delete Data (Delete - D)

To delete records, create a JavaScript file (e.g., delete.js) with the following code:

const connectToDatabase = require("./db")

async function deleteRecord(id) {
  const connection = await connectToDatabase()

  try {
    const result = await connection.execute(
      `DELETE FROM users WHERE id = :id`,
      [id]
    )
    console.log(`Record deleted: ${result.rowsAffected} rows affected`)
  } catch (err) {
    console.error("Error deleting record:", err)
  } finally {
    connection.close()
  }
}

deleteRecord(1)

This code defines a function deleteRecord that deletes a user record based on their ID.

Conclusion

In this article, we’ve covered the fundamental CRUD operations using Oracle Database with JavaScript and Node.js. You’ve learned how to establish a database connection, create, read, update, and delete records. This knowledge will empower you to build more complex applications that interact with Oracle Databases seamlessly. Remember to handle errors gracefully and adapt these examples to suit your specific project requirements.