Connect to OceanBase Database by using Sequelize

Sequelize is a promise-based ORM for Node.js that supports databases such as Postgres, MySQL, SQLite, and SQL Server. This topic walks you through connecting Sequelize to OceanBase's MySQL-compatible mode.

Prerequisites

• You have installed Node.js 14.0.0 or later.
• You have installed the npm or yarn package manager.
• You have deployed OceanBase Database and created a MySQL-compatible tenant.
• The network connection is normal.

Procedure

1. Check the versions of Node.js and npm.
2. Install the required dependencies.
3. Obtain the connection information of OceanBase Database.
4. Create and configure the project.
5. Run the sample program.

Step 1: Check the versions of Node.js and npm

Open your terminal and run the following commands to check the versions of Node.js and npm:

node -v
npm -v

Step 2: Install the necessary dependencies

1. Create a project directory and initialize it:

   mkdir sequelize-oceanbase-demo
   cd sequelize-oceanbase-demo
   npm init -y
   

2. Install Sequelize and the MySQL2 driver:

   npm install sequelize mysql2
   
   # If using TypeScript
   npm install -D typescript @types/node @types/sequelize
   
   # Initialize TypeScript configuration
   npx tsc --init
   

Step 3: Obtain the connection information of OceanBase Database

Contact the deployment personnel or administrator of OceanBase Database to obtain the corresponding database connection information.

mysql -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the IP address for connecting to OceanBase Database
• $port: the port number for connecting to OceanBase Database
• $database_name: the name of the database to access
• $user_name: the username for connecting to the tenant
• $password: the password for the account

Step 4: Create and configure the project

1. Create the project structure:

   sequelize-oceanbase-demo/
   ├── src/
   │   ├── models/
   │   │   └── index.js
   │   └── index.js
   ├── .env
   └── package.json
   

2. Configure the database connection:

   Create a .env file in the root directory of your project:

   DB_HOST=your_host
   DB_PORT=2881
   DB_USER=your_username
   DB_PASSWORD=your_password
   DB_NAME=your_database
   

3. Create the database connection:

   Create a src/models/index.js file:

   require('dotenv').config();
   const { Sequelize } = require('sequelize');
   
   const sequelize = new Sequelize(
     process.env.DB_NAME,
     process.env.DB_USER,
     process.env.DB_PASSWORD,
     {
       host: process.env.DB_HOST,
       dialect: 'mysql',
       port: process.env.DB_PORT,
       logging: console.log, // Display SQL query logs
       pool: {
         max: 5,
         min: 0,
         acquire: 30000,
         idle: 10000
       }
     }
   );
   
   module.exports = sequelize;
   

4. Test the connection:

   Create a src/index.js file:

   const sequelize = require('./models/index');
   
   async function testConnection() {
     try {
       await sequelize.authenticate();
       console.log('Connection successful');
     } catch (error) {
       console.error('Connection failed:', error);
     }
   }
   
   testConnection();
   

Step 5: Run the sample program

1. Define the model:

   const { DataTypes } = require('sequelize');
   
   const User = sequelize.define('User', {
     username: {
       type: DataTypes.STRING,
       allowNull: false
     },
     email: {
       type: DataTypes.STRING,
       validate: {
         isEmail: true
       }
     }
   }, {
     tableName: 'users'
   });
   

Complete example

Here is a complete example showing how to use Sequelize for CRUD operations:

const { Sequelize, DataTypes } = require('sequelize');

// 1. Create a connection
const sequelize = new Sequelize('database_name', 'username', 'password', {
  host: 'oceanbase-host',
  port: 2881,
  dialect: 'mysql'
});

// 2. Define the model
const User = sequelize.define('User', {
  username: DataTypes.STRING,
  email: DataTypes.STRING
});

// 3. Synchronize the model with the database
async function syncDatabase() {
  try {
    await sequelize.sync();
    console.log('Database synchronized successfully');
  } catch (error) {
    console.error('Failed to synchronize database:', error);
  }
}

// 4. Perform CRUD operations
async function crudOperations() {
  // Create
  const user = await User.create({
    username: 'testuser',
    email: 'test@example.com'
  });

  // Read
  const users = await User.findAll();
  console.log(users);

  // Update
  await User.update(
    { email: 'new@example.com' },
    { where: { id: 1 } }
  );

  // Delete
  await User.destroy({
    where: { id: 1 }
  });
}

// Execute
syncDatabase().then(() => crudOperations());

// Example
async function createWithTransaction() {
  const t = await sequelize.transaction();

  try {
    const user = await User.create({
      username: 'transaction_user',
      email: 'transaction@example.com'
    }, { transaction: t });

    await t.commit();
    return user;
  } catch (error) {
    await t.rollback();
    throw error;
  }
}

syncDatabase().then(() => createWithTransaction());

References

• Sequelize Documentation
• OceanBase Documentation
• Node.js Documentation