TiDB Cloud Serverless Driver Prisma Tutorial

Prisma is an open source next-generation ORM (Object-Relational Mapping) that helps developers interact with their database in an intuitive, efficient, and safe way. TiDB Cloud offers @tidbcloud/prisma-adapter, enabling you to use Prisma Client over HTTPS with TiDB Cloud serverless driver. Compared with the traditional TCP way, @tidbcloud/prisma-adapter brings the following benefits:

Better performance of Prisma Client in serverless environments
Ability to use Prisma Client in edge environments

This tutorial describes how to use @tidbcloud/prisma-adapter in serverless environments and edge environments.

Install

You need to install both @tidbcloud/prisma-adapter and TiDB Cloud serverless driver. You can install them using npm or your preferred package manager.

Taking npm as an example, you can run the following commands for installation:

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

Enable `driverAdapters`

To use the Prisma adapter, you need to enable the driverAdapters feature in the schema.prisma file. For example:

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

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

Initialize Prisma Client

Before using Prisma Client, you need to initialize it with @tidbcloud/prisma-adapter. For example:

import { connect } from '@tidbcloud/serverless';
import { PrismaTiDBCloud } from '@tidbcloud/prisma-adapter';
import { PrismaClient } from '@prisma/client';

// Initialize Prisma Client
const connection = connect({ url: ${DATABASE_URL} });
const adapter = new PrismaTiDBCloud(connection);
const prisma = new PrismaClient({ adapter });

Then, queries from Prisma Client can be sent to the TiDB Cloud serverless driver for processing.

Use the Prisma adapter in Node.js environments

This section provides an example of how to use @tidbcloud/prisma-adapter in Node.js environments.

Before you begin

To complete this tutorial, you need the following:

Node.js >= 18.0.0.
npm or your preferred package manager.
A TiDB Serverless cluster. If you don't have any, you can create a TiDB Serverless cluster.

Step 1. Create a project

Create a project named prisma-example:
```
mkdir prisma-example
cd prisma-example
```
Install the @tidbcloud/prisma-adapter driver adapter, the @tidbcloud/serverless serverless driver, and the Prisma CLI.
The following commands use npm as the package manager. Executing npm install @tidbcloud/serverless will create a node_modules directory and a package.json file in your project directory.
```
npm install @tidbcloud/prisma-adapter
npm install @tidbcloud/serverless
npm install prisma --save-dev
```

In the package.json file, specify the ES module by adding type: "module":

{
  "type": "module",
  "dependencies": {
    "@prisma/client": "^5.5.2",
    "@tidbcloud/prisma-adapter": "^5.5.2",
    "@tidbcloud/serverless": "^0.0.7"
  },
  "devDependencies": {
    "prisma": "^5.5.2"
  }
}

Step 2. Set the environment

On the overview page of your TiDB Serverless cluster, click Connect in the upper-right corner, and then get the connection string for your database from the displayed dialog. The connection string looks like this:
```
mysql://[username]:[password]@[host]:4000/[database]?sslaccept=strict
```
In the root directory of your project, create a file named .env, define an environment variable named DATABASE_URL as follows, and then replace the placeholders [] in this variable with the corresponding parameters in the connection string.
```
DATABASE_URL='mysql://[username]:[password]@[host]:4000/[database]?sslaccept=strict'
```
Note
@tidbcloud/prisma-adapter only supports the use of Prisma Client over HTTPS. For Prisma Migrate and Prisma Introspection, the traditional TCP connection is still used. If you only need to use Prisma Client, you can simplify DATABASE_URL to the mysql://[username]:[password]@[host]/[database] format.
Install dotenv to load the environment variable from the .env file:
```
npm install dotenv
```

Step 3. Define your schema

Create a file named schema.prisma. In this file, include the driverAdapters preview feature and reference the DATABASE_URL environment variable. Here is an example file:

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

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

In the schema.prisma file, define a data model for your database table. In the following example, a data model named user is defined.

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

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

// define a data 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)
}

Synchronize your database with the Prisma schema. You can either manually create the database tables in your TiDB Serverless cluster or use the Prisma CLI to create them automatically as follows:
```
npx prisma db push
```
This command will create the user table in your TiDB Serverless cluster through the traditional TCP connection, rather than through the HTTPS connection using @tidbcloud/prisma-adapter. This is because it uses the same engine as Prisma Migrate. For more information about this command, see Prototype your schema.
Generate Prisma Client:
```
npx prisma generate
```
This command will generate Prisma Client based on the Prisma schema.

Step 4. Execute CRUD operations

Create a file named hello-word.js and add the following code to initialize Prisma Client:

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}`;

// Initialize Prisma Client
const connection = connect({ url: connectionString });
const adapter = new PrismaTiDBCloud(connection);
const prisma = new PrismaClient({ adapter });

Execute some CRUD operations with Prisma Client. For example:

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

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

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

Execute some transaction operations with Prisma Client. For example:

const createUser1 = prisma.user.create({
  data: {
    email: 'test1@pingcap.com',
    name: 'test1',
  },
})
const createUser2 = prisma.user.create({
  data: {
    email: 'test1@pingcap.com',
    name: 'test1',
  },
})
const createUser3 = prisma.user.create({
  data: {
    email: 'test2@pingcap.com',
    name: 'test2',
  },
})

try {
  await prisma.$transaction([createUser1, createUser2]) // Operations fail because the email address is duplicated
} catch (e) {
  console.log(e)
}

try {
  await prisma.$transaction([createUser2, createUser3]) // Operations success because the email address is unique
} catch (e) {
  console.log(e)
}

Use the Prisma adapter in edge environments

You can use @tidbcloud/prisma-adapter v5.11.0 or a later version in edge environments such as Vercel Edge Functions and Cloudflare Workers.