Integrating TiDB Cloud Serverless Driver with Prisma ORM

We’re excited to announce the release of the TiDB Cloud Prisma Adapter, a significant advancement following our recent launch of the TiDB Cloud Serverless Driver. The serverless driver is a JavaScript Driver tailored for TiDB Serverless, our fully managed MySQL-compatible DBaaS optimized for auto-scaling workloads. By leveraging the efficiency of HTTPS connections over traditional long-lived TCP connections, the TiDB Cloud Prisma Adapter simplifies the connectivity process to TiDB Serverless from Prisma ORM and significantly enhances performance.

What is Prisma ORM?

Prisma is an open-source next-generation ORM (Object-Relational Mapping) that helps developers interact with their database intuitively, efficiently, and safely. It supports databases such as MySQL, PostgreSQL, SQLite, and SQL Server.

There are two critical parts of Prisma:

• Prisma Client: An auto-generated and type-safe query builder for Node.js & TypeScript.
• Prisma Query Engine: A translation engine written in Rust.

The Prisma Client can be generated from the Prisma schema, a declarative language that defines database schema. You can import it into your code and send queries to your database.

The Prisma Query Engine accepts the query from the Prisma Client and translates it into raw SQL statements. Then, it will execute SQL statements with the corresponding database drivers.

Figure 1. Prisma Workflow

Why TiDB Cloud Prisma Adapter?

Recently, Prisma introduced the driver adapters preview feature, an innovative mechanism to connect Prisma with serverless drivers. The TiDB Cloud Prisma adapter is a bridge between the Prisma Client and TiDB Cloud serverless driver. It connects the Prisma Client with the TiDB Cloud serverless driver, adeptly handling SQL statements generated by the Prisma query engine. These statements are executed via the TiDB Cloud serverless driver, which communicates with TiDB Serverless over HTTPS. 

Figure 2. TiDB Cloud Prisma Adapter Workflow

This is especially useful in serverless functions where connections are established per each function invocation. Establishing the HTTPS connection optimizes performance and alleviates stress on the database. More importantly, this marks a pivotal step in enabling Prisma Client to function seamlessly in edge computing environments, such as Vercel Edge Function and Cloudflare Workers. 

How to Use the Adapter？

Before you start, make sure you have:

• A TiDB Serverless cluster. Register now for free if you don’t have an account yet.
• Node.js 18 or later.

Install the Adapter

Install the @tidbcloud/prisma-adapter driver adapter, the @tidbcloud/serverless serverless driver, and the Prisma CLI: 

npm install @tidbcloud/prisma-adapter
npm install @tidbcloud/serverless
npm install prisma --save-dev

Configure the Environment

Then set the environment to your .env file. You can get connection information on the TiDB Cloud console.

// .env
DATABASE_URL="mysql://username:password@host:4000/database?sslaccept=strict"

NOTE

The adapter only supports Prisma Client. Prisma migration and introspection still go through the traditional TCP way. If you only need Prisma Client, you can set the DATABASE_URL as the mysql://username:password@host/database format, in which port and SSL parameters are not needed.

Define the Schema

First, create a file named schema.prisma to define your schema. You need to reference the environment variable in the schema.prisma file. Make sure you also include driverAdapters in the Preview Feature field. Then, define the schema according to your business. Here we use the user as an example:

// schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["driverAdapters"]
}

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
} 

// define model according to your database table
model user {
  id    Int     @id @default(autoincrement())
  email String? @unique(map: "uniq_email") @db.VarChar(255)
  name  String? @db.VarChar(255)
}

After defining your schema, you need to:

1. Run prisma db push to sync the schema if your TiDB Serverless cluster does not have the corresponding table. This step will go through the traditional TCP way rather than the adapter.
2. Run prisma generate to generate the Prisma Client.

Use the Adapter

Here is an example of using the adapter to query from the TiDB Serverless cluster:

// hello-world.js
import { connect } from '@tidbcloud/serverless';
import { PrismaTiDBCloud } from '@tidbcloud/prisma-adapter';
import { PrismaClient } from '@prisma/client';
import dotenv from 'dotenv';

// setup
dotenv.config();
const connectionString = `${process.env.DATABASE_URL}`;

// init prisma client
const connection = connect({ url: connectionString });
const adapter = new PrismaTiDBCloud(connection);
const prisma = new PrismaClient({ adapter });

// insert
const user = await prisma.user.create({
  data: {
    email: 'test@prisma.io',
    name: 'test',
  },
})
console.log(user)

// query
console.log(await prisma.user.findMany())

// delete
await prisma.user.delete({
   where: {
      id: user.id,
   },
})

// txn
const createUser1 = prisma.user.create({
  data: {
    email: 'yuhang.shi@pingcap.com',
    name: 'Shi Yuhang',
  },
})

const createUser2 = prisma.user.create({
  data: {
    email: 'yuhang.shi@pingcap.com',
    name: 'Shi Yuhang2',
  },
})

const createUser3 = prisma.user.create({
  data: {
    email: 'yuhang2.shi@pingcap.com',
    name: 'Shi Yuhang2',
  },
})
try {
  await prisma.$transaction([createUser1, createUser2]) // Operations fail together
} catch (e) {
  console.log(e)
  await prisma.$transaction([createUser1, createUser3]) // Operations success together
}

console.log(await prisma.user.findMany())

Conclusion

The integration of TiDB Cloud Serverless Driver with Prisma ORM provides a valuable addition to the world of web development and database management. This powerful combination offers developers the efficiency and scalability of a serverless database with the user-friendly experience of Prisma’s ORM, particularly in edge computing scenarios.

Experience the power of this integration by spinning up a free TiDB Serveress cluster now. If you have any feedback about the adapter, please comment on our GitHub issue.