Drizzle ORM is a lightweight, type-safe Object-Relational Mapping (ORM) designed for TypeScript. This guide walks you through connecting to OceanBase Database's MySQL-compatible mode by using Drizzle ORM.
Prerequisites
- You have installed Node.js 16.0.0 or later.
- You have installed the npm or yarn package manager.
- You have deployed OceanBase Database and created a MySQL-compatible tenant.
Procedure
- Check the versions of Node.js and npm.
- Install the required dependencies.
- Obtain the connection information of OceanBase Database.
- Create and configure the project.
- Run the sample program.
Step 1: Check the versions of Node.js and npm
Open the 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
mkdir drizzle-oceanbase-demo
cd drizzle-oceanbase-demo
npm init -y
npm install drizzle-orm mysql2
npm install -D typescript @types/node ts-node
npx tsc --init
Step 2: Configure the database connection
Create a .env file in the project root directory and fill in the database connection information:
DB_HOST=your_host
DB_PORT=2881
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database
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
Parameters:
$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 be accessed.$user_name: the username for connecting to the tenant.$password: the password for the account.
Step 3: Define the data table model
Create a src/db/schema.ts file:
import { mysqlTable, int, varchar, timestamp, index } from 'drizzle-orm/mysql-core';
export const users = mysqlTable('users', {
id: int('id').primaryKey().autoincrement(),
username: varchar('username', { length: 255 }).notNull(),
email: varchar('email', { length: 255 }).notNull().unique(),
passwordHash: varchar('password_hash', { length: 255 }).notNull(),
createdAt: timestamp('created_at').defaultNow().notNull(),
updatedAt: timestamp('updated_at').defaultNow().onUpdateNow().notNull(),
}, (table) => ({
// Add an index on the email field
emailIdx: index('email_idx').on(table.email),
}));
Create the table in OceanBase Database:
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
);
Step 4: Create a database connection
Create src/db/index.ts:
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as schema from './schema';
import * as dotenv from 'dotenv';
// Load environment variables
dotenv.config();
const poolConnection = mysql.createPool({
host: process.env.DB_HOST,
port: parseInt(process.env.DB_PORT || '2881'),
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
timezone: '+08:00',
// Add these options for better compatibility
multipleStatements: true,
supportBigNumbers: true,
bigNumberStrings: true
});
console.log('Connecting to database at:', {
host: process.env.DB_HOST,
port: process.env.DB_PORT,
user: process.env.DB_USER,
database: process.env.DB_NAME
});
export const db = drizzle(poolConnection, {
schema,
mode: 'default'
});
Step 5: CRUD operations example
Create src/index.ts:
import { db } from './db';
import { users } from './db/schema';
import { eq } from 'drizzle-orm';
async function main() {
try {
// Test the connection first
await db.select().from(users).limit(1);
console.log('Successfully connected to the database');
// Create user
const insertResult = await db.insert(users).values({
username: 'testuser',
email: 'test@example.com',
passwordHash: 'hashed_password',
});
console.log('Insert result:', insertResult);
// Get the inserted user
const [newUser] = await db.select().from(users).where(eq(users.email, 'test@example.com'));
console.log('Created user:', newUser);
// Query user
const [user] = await db.select().from(users).where(eq(users.id, newUser.id));
console.log('Retrieved user:', user);
// Update user
const updateResult = await db.update(users)
.set({ email: 'new.email@example.com' })
.where(eq(users.id, newUser.id));
console.log('Update result:', updateResult);
// Get the updated user
const [updatedUser] = await db.select().from(users).where(eq(users.id, newUser.id));
console.log('Updated user:', updatedUser);
// Delete user
const deleteResult = await db.delete(users)
.where(eq(users.id, newUser.id));
console.log('Delete result:', deleteResult);
// Verify deletion
const [deletedUser] = await db.select().from(users).where(eq(users.id, newUser.id));
console.log('User after deletion:', deletedUser || 'User not found');
} catch (error) {
console.error('Database error:', error);
}
}
main().catch(console.error);
Run the example
npx ts-node src/index.ts