Connect to OceanBase Database by using Sequelize

Sequelize is a Promise-based Node.js ORM tool that supports multiple databases, such as PostgreSQL, MySQL, SQLite, and SQL Server. This topic describes how to connect to OceanBase Database in MySQL mode by using Sequelize.

Prerequisites

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

Procedure

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

Step 1: Check the Node.js and npm versions

Open the terminal and run the following command 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 you use TypeScript
   npm install -D typescript @types/node @types/sequelize
   
   # Initialize the TypeScript configuration
   npx tsc --init
   

Step 3: Obtain the connection information of OceanBase Database

Contact the OceanBase Database deployment personnel or administrator 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 for connecting to OceanBase Database.
• $database_name: the name of the database to be accessed.
• $user_name: the tenant connection account.
• $password: the account password.

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 the 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 the 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 the 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

The following example shows how to use Sequelize to perform 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 synchronization successful');
  } catch (error) {
    console.error('Database synchronization failed:', error);
  }
}

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

  // Query
  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 using a transaction
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 official documentation
• OceanBase Database documentation
• Node.js documentation