How to Create and Connect Sequelize with Oracle and Node.js

Sequelize ORM supports SQL-related databases such as MySQL, MariaDB, and Oracle. When working on a Node.js app, you want to have the correct Oracle Sequelize database connection and run your server.

In this guide, you will learn the right way to have Sequelize ORM connect to the Oracle database and use Node.js and Express to build your Sequelize Oracle-driven APIs. You will learn:

• How to create an Oracle database connection with Sequelize.
• How to use Sequelize ORM to model Oracle database tables.
• How to use Node.js and Express to create CRUD API with Sequelize and Oracle Node.js Drivers.

Ready? Dive in and learn how to create and connect Sequelize with Oracle and Node.js.

Requirements to Run Sequelize with Oracle and Node.js

This tutorial will assume you have the following:

• Node.js ready on your computer.
• An Oracle database server running your computer

Related: Master Oracle DB Connection with TypeORM and NestJS Example

Installing Node.js Oracle and Sequelize ORM Dependencies

You first need to create a Node.js app using the following command:

# Auto-create Node.js files
npm init -y

Sequelize and Oracle are two different libraries used in Node.js for different purposes. To access them at once, you will need:

• Sequelize ORM package to create models and easily perform CRUD (Create, Read, Update, Delete) operations.
• Oracledb as your Node.js driver for Oracle Database to interact with an Oracle database Sequelize.
• Express framework for creating Node.js web APIs.

To install them, run the following command:

npm install express sequelize oracledb

Creating Oracle Database connection with Sequelize ORM

The first major step is to ensure Oracle and Sequelize can create a Database connection. To connect Sequelize with Oracle and Node.js, you can follow these steps:

• Create a config.js file and define the Oracle connection parameters:

module.exports = {
    HOST: 'localhost',
    USER: "SYSTEM",
    PASSWORD: "oracle_password!!Q11",
    DB: "oracleDB", 
    PORT: 1521,
    SID: 'orcl',
    dialect: "oracle",
    pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000
    }
};

In this example, you must ensure that your_username and your_password are updated accordingly. At this step, ensure you have the database oracleDB ready and created on your Oracle server.

• Initialize Sequelize in your application. Create a db.js and use Sequelize to create an Oracle database connection as follows:

const { Sequelize } = require('sequelize');
// Path to your Sequelize configuration file
const config = require('./config'); 

const sequelize = new Sequelize({
    username: config.USER,
    password: config.PASSWORD,
    dialect: config.dialect,
    dialectOptions: {connectString: `${config.HOST}:${config.PORT}/${config.SID}`},// oracle
    pool: {
      max: config.pool.max,
      min: config.pool.min,
      acquire: config.pool.acquire,
      idle: config.pool.idle
    }
});

module.exports = sequelize;

Now, you should have the connection ready.

Modelling Oracle Database with Sequelize

Sequelize uses models to represent your tables. In the above db.js, a Sequelize instance is needed to define models and manage your Oracle database using Sequelize ORM features.

Now define a model, for example, User, and then use the sequelize.sync() method to synchronize the model with the Oracle database. Therefore, create a model.js file and create a User model as follows:

const { DataTypes } = require('sequelize');
// Import the Sequelize Oracle connection instance
const sequelize = require('./db');

const User = sequelize.define('User', {
  firstName: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  lastName: {
    type: DataTypes.STRING,
  },
});

// Synchronize the model with the database
sequelize.sync({ force: true }).then(() => {
  console.log('User table created!');
});

module.exports = User;

Building CRUD API with Sequelize ORM, Oracle and Node.js

The final step is to allow Sequelize and Oracle to accept different CRUD operations from Express and Node.js. Here, you create HTTP methods with respective Sequelize methods to access Oracle and add, get, update, or delete data.

Create an index.js file and add a method to POST and GET using Sequelize as follows:

const express = require('express');
const User = require('./model');

const app = express();
const PORT = process.env.PORT || 3000;

// Middleware to parse JSON requests
app.use(express.json());

// Route to add a new user
app.post('/users', async (req, res) => {
  try {
    const { firstName, lastName } = req.body;
    const newUser = await User.create({ firstName, lastName });
    res.status(201).json(newUser);
  } catch (error) {
    console.error(error);
    res.status(500).send('Internal Server Error');
  }
});

// Route to get all users
app.get('/users', async (req, res) => {
  try {
    const users = await User.findAll();
    res.status(200).json(users);
  } catch (error) {
    console.error(error);
    res.status(500).send('Internal Server Error');
  }
});

// Start the server
app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`);
});

Running The Node.js Sequelize Oracle Server

The application is ready. Now, you need to run the server and let Sequelize access Oracle DB. Use the following command to run the server:

Before running the server, ensure you have created oracleDB in your Oracle server.

node index.js

This will run the server and create the Table user in your database as follows:

Node.js will open the server on http://localhost:3000/users. Use Postman to add new records as follows:

At the same time, send a GET request to allow Sequelize to fetch the items you add to your Oracle user table:

All these operations should be logged in your terminal if successful:

Conclusion

This guide helped you learn the following:

• How to create an Oracle database connection with Sequelize.
• How to use Sequelize ORM to model Oracle database tables.
• How to use Node.js and Express to create CRUD API with Sequelize and Oracle Node.js Drivers.

Check the code on this GitHub repository.