TypeORM is an ORM framework for TypeScript and JavaScript that supports multiple database systems. This guide will walk you through connecting to OceanBase Database's MySQL-compatible mode by using TypeORM.
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.
- You have a TypeScript environment (recommended).
Procedure
- Check the versions of Node.js and npm.
- Install the required dependencies.
- Configure the project.
- Try out some data operations.
- Run the example.
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 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 are using 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 details. - If you are using an SSL connection, add the
ssl: { rejectUnauthorized: false }configuration. - During development, you can set
synchronizetotrue, but for production environments, it is recommended to use migrations to manage database schema changes. - We recommend storing sensitive information in a
.envfile using environment variables.
Step 4: Try out some data operations
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;
}
Create the database connection and perform operations
Create src/index.ts:
import 'reflect-metadata';
import { createConnection } from 'typeorm';
import { User } from './entity/User';
async function main() {
// 1. Create database connection
const connection = await createConnection();
console.log('Database connection established');
// 2. Create a new user
const user = new User();
user.firstName = "John";
user.lastName = "Smith";
user.email = "john.smith@example.com";
user.age = 25;
await connection.manager.save(user);
console.log('User saved, User ID: ', user.id);
// 3. Retrieve all users
const users = await connection.manager.find(User);
console.log('All users: ', users);
// 4. Find a user by condition
const specificUser = await connection.manager.findOne(User, {
where: {
firstName: "John",
lastName: "Smith"
}
});
console.log('Conditional query: ', specificUser);
// 5. Advanced queries with QueryBuilder
const userRepository = connection.getRepository(User);
const queryBuilderUsers = await userRepository
.createQueryBuilder("user")
.where("user.age > :age", { age: 18 })
.orderBy("user.age", "DESC")
.getMany();
console.log('Advanced query: ', queryBuilderUsers);
// 6. Update a record
await connection
.createQueryBuilder()
.update(User)
.set({ firstName: "Jane", lastName: "Doe" })
.where("id = :id", { id: 1 })
.execute();
const updateUsers = await connection.manager.find(User);
console.log('Updated records: ', updateUsers);
// 7. Delete a record
await connection
.createQueryBuilder()
.delete()
.from(User)
.where("id = :id", { id: 1 })
.execute();
const deleteUsers = await connection.manager.find(User);
console.log('Remaining records after deletion: ', deleteUsers);
}
main().catch(error => {
console.error('Error occurred:', error);
process.exit(1);
});
Step 5: Run the example
Make sure your database is started and accessible.
Compile the TypeScript code:
npx tscRun the example:
node src/index.js
Note
In real-world projects, make sure to:
- Encrypt passwords (for example, using bcrypt)
- Handle potential errors appropriately
- Manage database connection information with environment variables
- Add proper input validation