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 -yInstall 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 --initUpdate
tsconfig.jsonto 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/subscriberCreate 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, anddatabasewith your OceanBase Database connection information - If you use SSL connections, add the
ssl: { rejectUnauthorized: false }configuration - Set
synchronizetotrueduring development, but use migrations in production to manage database schema changes - We recommend using a
.envfile 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 tscRun 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.