Connect to OceanBase Database by using Drizzle ORM

node -v
npm -v

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

DB_HOST=your_host
DB_PORT=2881
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database

mysql -h$host -P$port -u$user_name -p$password -D$database_name

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 for the email field.
  emailIdx: index('email_idx').on(table.email),
}));

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
);

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' 
});

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);

npx ts-node src/index.ts