Connect to OceanBase Database by using Prisma

Prisma is a modern database toolkit that provides type-safe access to your data. This guide walks you through connecting to OceanBase's MySQL-compatible mode by using Prisma.

Prerequisites

• You have installed Node.js 14.0.0 or later.
• You have installed the npm or Yarn package manager.
• You have installed deployed OceanBase Database and created a MySQL-compatible tenant.

Procedure

1. Check the versions of Node.js and npm.
2. Install the required dependencies.
3. Obtain the connection information of OceanBase Database.
4. Create and configure the project.
5. 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

1. Create a project directory and initialize it:

   mkdir prisma-oceanbase-demo
   cd prisma-oceanbase-demo
   npm init -y
   npm install prisma @prisma/client
   

2. Initialize Prisma:

   npx prisma init --datasource-provider mysql
   

   After initialization, you will see the following output:

   ✔ Your Prisma schema was created at prisma/schema.prisma
     You can now open it in your favorite editor.
   
   Next steps:
   1. Set the DATABASE_URL in the .env file to point to your existing database.
   2. Run prisma db pull to turn your database schema into a Prisma schema.
   3. Run prisma generate to generate the Prisma Client.
   4. Start querying your database with Prisma Client.
   
   More information in our documentation:
   https://pris.ly/d/getting-started
   

3. Configure the environment variables:

   After initialization, Prisma will automatically create the .env file and the prisma/schema.prisma file. Edit the .env file and set the connection information for OceanBase Database:

   # .env
   DATABASE_URL="mysql://username:password@host:port/database?schema=public"
   

   Notice

   • Replace username, password, host, port, and database with your OceanBase Database connection information.
   • Make sure that the schema parameter matches the name of your database schema.
   • If you use an SSL connection, add the ?sslmode=require parameter.

4. Pull the database schema (if your database already contains tables):

   npx prisma db pull
   

   This command pulls the table structure from the existing database and updates the prisma/schema.prisma file.

5. Generate the Prisma client:

   npx prisma generate
   

   This command reads the prisma/schema.prisma file and generates the corresponding TypeScript type definitions and Prisma client code.

6. Create a database migration (for new projects only):

   npx prisma migrate dev --name init
   

   For a new project, this command will:

   • Creates a migration file
   • Applies the migration to the database
   • Regenerates the Prisma client

   Notice

   If you have already used db pull to pull the schema from an existing database, you can skip this step.

Step 3: Try out some data operations

This section shows how to use Prisma to insert and query data in the users table.

1. Initialize the Prisma client

First, create an index.js file in your project's root directory and add the following code:

const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()

async function main() {
  // 1. Create a new user
  const newUser = await prisma.users.create({
    data: {
      username: 'ambermoe',
      email: 'amber@example.com',
      password_hash: 'hashed_password_here',  // Use bcrypt for password hashing in production
    },
  })
  console.log('User created successfully:', newUser)

  // 2. Query user
  const user = await prisma.users.findUnique({
    where: {
      email: 'amber@example.com',
    },
  })
  console.log('Retrieved user:', user)
}

main()
  .catch((e) => {
    console.error('Error:', e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

2. Run the example

Make sure you have completed the following steps:

1. The users table model is correctly defined in prisma/schema.prisma

2. For new projects, apply the database migration:

   npx prisma migrate dev --name init
   

   If you pulled the schema from an existing database, you can skip this step.

3. Generate the Prisma client:

   npx prisma generate
   

4. Run the example code:

   node index.js
   

After running the code, you will see the information of the newly created user and the queried user in the console.

User created successfully: {
  id: 2,
  username: 'ambermoe',
  email: 'amber@example.com',
  password_hash: 'hashed_password_here',
  created_at: 2025-06-04T03:40:43.000Z,
  updated_at: 2025-06-04T03:40:43.000Z
}
Retrieved user: {
  id: 2,
  username: 'ambermoe',
  email: 'amber@example.com',
  password_hash: 'hashed_password_here',
  created_at: 2025-06-04T03:40:43.000Z,
  updated_at: 2025-06-04T03:40:43.000Z
}

References

• Prisma Documentation
• OceanBase Documentation