Connect to OceanBase Database in MySQL mode by using TypeORM|OceanBase Database|docs|OceanBase Documentations

Applicability

TypeORM is applicable to OceanBase Database in MySQL mode.

TypeORM is an ORM framework that supports TypeScript and JavaScript. It supports multiple database systems. This topic describes how to connect to OceanBase Database in MySQL mode by using TypeORM.

Prerequisites

Node.js 14.0.0 or later is installed.
The npm or yarn package manager is installed.
OceanBase Database is deployed and a MySQL tenant is created.
A TypeScript environment is available (recommended).

Procedure

Check the Node.js and npm versions.
Install the required dependencies.
Configure the project.
Data operation example.
Run the example.

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

In the terminal, run the following command to check the versions of Node.js and npm:

node -v
npm -v

Step 2: Install the required dependencies

Create a project directory and initialize it:

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

Install TypeORM and the MySQL2 driver:

npm install typeorm mysql2 reflect-metadata

# If you use TypeScript
npm install --save-dev typescript @types/node

# Initialize TypeScript configuration
npx tsc --init

Update tsconfig.json to support decorators:

{
  "compilerOptions": {
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true,
    "target": "es6",
    "module": "commonjs",
    "outDir": "./dist",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true
  },
  "include": ["src/**/*.ts"],
  "exclude": ["node_modules"]
}

Step 3: Configure the project

Create the project directory structure:

mkdir -p src/entity src/migration src/subscriber

Create the TypeORM configuration file ormconfig.json:

{
  "type": "mysql",
  "host": "oceanbase-host",
  "port": 2881,
  "username": "your_username",
  "password": "your_password",
  "database": "your_database",
  "synchronize": true,
  "logging": false,
  "entities": ["src/entity/**/*.ts"],
  "migrations": ["src/migration/**/*.ts"],
  "subscribers": ["src/subscriber/**/*.ts"],
  "cli": {
    "entitiesDir": "src/entity",
    "migrationsDir": "src/migration",
    "subscribersDir": "src/subscriber"
  }
}

Notice

Replace host, port, username, password, and database with your OceanBase Database connection information
If you use SSL connections, add the ssl: { rejectUnauthorized: false } configuration
Set synchronize to true during development, but use migrations in production to manage database schema changes
We recommend using a .env file and environment variables to store sensitive information

Step 4: Data operation examples

1. Define an entity

Create the src/entity/User.ts file:

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm";

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id?: number;

  @Column()
  firstName: string = "";

  @Column()
  lastName: string = "";

  @Column({ unique: true })
  email: string = "";

  @Column()
  age: number = 0;
}

2. Create a database connection and perform operations

Create the src/index.ts file:

import 'reflect-metadata';
import { createConnection } from 'typeorm';
import { User } from './entity/User';

async function main() {
  // 1. Create a database connection
  const connection = await createConnection();
  console.log('Database connection established');

  // 2. Create a new user
  const user = new User();
  user.firstName = "Zhang";
  user.lastName = "San";
  user.email = "zhangsan@example.com";
  user.age = 25;

  await connection.manager.save(user);
  console.log('User saved, User ID: ', user.id);

  // 3. Query all users
  const users = await connection.manager.find(User);
  console.log('All users: ', users);

  // 4. Query by conditions
  const specificUser = await connection.manager.findOne(User, {
    where: {
      firstName: "Zhang",
      lastName: "San"
    }
  });
  console.log('Query by conditions: ', specificUser);

  // 5. Use QueryBuilder for complex queries
  const userRepository = connection.getRepository(User);
  const queryBuilderUsers = await userRepository
    .createQueryBuilder("user")
    .where("user.age > :age", { age: 18 })
    .orderBy("user.age", "DESC")
    .getMany();
  console.log('Complex query: ', queryBuilderUsers);

  // 6. Update records
  await connection
    .createQueryBuilder()
    .update(User)
    .set({ firstName: "Li", lastName: "Si" })
    .where("id = :id", { id: 1 })
    .execute();
  const updateUsers = await connection.manager.find(User);
  console.log('Updated records: ', updateUsers);

  // 7. Delete records
  await connection
    .createQueryBuilder()
    .delete()
    .from(User)
    .where("id = :id", { id: 1 })
    .execute();
  const deleteUsers = await connection.manager.find(User);
  console.log('Deleted records: ', deleteUsers);
}

main().catch(error => {
  console.error('Error occurred:', error);
  process.exit(1);
});

Step 5: Run the sample

Make sure that the database is running and accessible.
Compile the TypeScript code:
```
npx tsc
```
Run the sample:
```
node src/index.js
```

Note

In a real project, make sure that:

Passwords must be encrypted (for example, using bcrypt).
Errors that may occur are handled.
Database connection information is managed by using environment variables.
Appropriate input validation is added.