TiDB Cloud Serverless Driver Drizzle Tutorial

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind. Starting from drizzle-orm@0.31.2, it supports drizzle-orm/tidb-serverless, enabling you to use Drizzle over HTTPS with TiDB Cloud serverless driver.

This tutorial describes how to use TiDB Cloud serverless driver with Drizzle in Node.js environments and edge environments.

Use Drizzle and TiDB Cloud serverless driver in Node.js environments

This section describes how to use TiDB Cloud serverless driver with Drizzle 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 Cloud Serverless cluster. If you don't have any, you can create a TiDB Cloud Serverless cluster.

Step 1. Create a project

Create a project named drizzle-node-example:

mkdir drizzle-node-example
cd drizzle-node-example

Install the drizzle-orm and @tidbcloud/serverless packages:
```
npm install drizzle-orm @tidbcloud/serverless
```

In the root directory of your project, locate the package.json file, and then specify the ES module by adding "type": "module" to the file:

{
  "type": "module",
  "dependencies": {
    "@tidbcloud/serverless": "^0.1.1",
    "drizzle-orm": "^0.31.2"
  }
}

In the root directory of your project, add a tsconfig.json file to define the TypeScript compiler options. Here is an example file:

{
  "compilerOptions": {
    "module": "ES2022",
    "target": "ES2022",
    "moduleResolution": "node",
    "strict": false,
    "declaration": true,
    "outDir": "dist",
    "removeComments": true,
    "allowJs": true,
    "esModuleInterop": true,
    "resolveJsonModule": true
  }
}

Step 2. Set the environment

In the TiDB Cloud console, navigate to the Clusters page of your project, and then click the name of your target TiDB Cloud Serverless cluster to go to its overview page.
On the overview page, click Connect in the upper-right corner, select Serverless Driver in the Connect With drop-down box, and then click Generate Password to create a random password.
Tip
If you have created a password before, you can either use the original password or click Reset Password to generate a new one.
The connection string looks like this:
```
mysql://[username]:[password]@[host]/[database]
```
Set the environment variable DATABASE_URL in your local environment. For example, in Linux or macOS, you can run the following command:
```
export DATABASE_URL='mysql://[username]:[password]@[host]/[database]'
```

Step 3. Use Drizzle to query data

Create a table in your TiDB Cloud Serverless cluster.
You can use SQL Editor in the TiDB Cloud console to execute SQL statements. Here is an example:
```
CREATE TABLE `test`.`users` (
 `id` BIGINT PRIMARY KEY auto_increment,
 `full_name` TEXT,
 `phone` VARCHAR(256)
);
```

In the root directory of your project, create a file named hello-world.ts and add the following code:

import { connect } from '@tidbcloud/serverless';
import { drizzle } from 'drizzle-orm/tidb-serverless';
import { mysqlTable, serial, text, varchar } from 'drizzle-orm/mysql-core';

// Initialize
const client = connect({ url: process.env.DATABASE_URL });
const db = drizzle(client);

// Define schema
export const users = mysqlTable('users', {
  id: serial("id").primaryKey(),
  fullName: text('full_name'),
  phone: varchar('phone', { length: 256 }),
});
export type User = typeof users.$inferSelect; // return type when queried
export type NewUser = typeof users.$inferInsert; // insert type

// Insert and select data
const user: NewUser = { fullName: 'John Doe', phone: '123-456-7890' };
await db.insert(users).values(user)
const result: User[] = await db.select().from(users);
console.log(result);

Step 4. Run the Typescript code

Install ts-node to transform TypeScript into JavaScript, and then install @types/node to provide TypeScript type definitions for Node.js.
```
npm install -g ts-node
npm i --save-dev @types/node
```
Run the Typescript code with the following command:
```
ts-node --esm hello-world.ts
```

Use Drizzle and TiDB Cloud serverless driver in edge environments

This section takes the Vercel Edge Function as an example.

Before you begin

To complete this tutorial, you need the following:

A Vercel account that provides edge environment.
npm or your preferred package manager.
A TiDB Cloud Serverless cluster. If you don't have any, you can create a TiDB Cloud Serverless cluster.

Step 1. Create a project

Install the Vercel CLI:
```
npm i -g vercel@latest
```

Create a Next.js project called drizzle-example using the following terminal command:

npx create-next-app@latest drizzle-example --ts --no-eslint --tailwind --no-src-dir --app --import-alias "@/*"

Navigate to the drizzle-example directory:
```
cd drizzle-example
```

Install the drizzle-orm and @tidbcloud/serverless packages:

npm install drizzle-orm @tidbcloud/serverless --force

Step 2. Set the environment

In the TiDB Cloud console, navigate to the Clusters page of your project, and then click the name of your target TiDB Cloud Serverless cluster to go to its overview page.
On the overview page, click Connect in the upper-right corner, select Serverless Driver in the Connect With drop-down box, and then click Generate Password to create a random password.
Tip
If you have created a password before, you can either use the original password or click Reset Password to generate a new one.
The connection string looks like this:
```
mysql://[username]:[password]@[host]/[database]
```

Step 3. Create an edge function

Create a table in your TiDB Cloud Serverless cluster.
You can use SQL Editor in the TiDB Cloud console to execute SQL statements. Here is an example:
```
CREATE TABLE `test`.`users` (
 `id` BIGINT PRIMARY KEY auto_increment,
 `full_name` TEXT,
 `phone` VARCHAR(256)
);
```

In the app directory of your project, create a file /api/edge-function-example/route.ts and add the following code:

import { NextResponse } from 'next/server';
import type { NextRequest } from 'next/server';
import { connect } from '@tidbcloud/serverless';
import { drizzle } from 'drizzle-orm/tidb-serverless';
import { mysqlTable, serial, text, varchar } from 'drizzle-orm/mysql-core';
export const runtime = 'edge';

// Initialize
const client = connect({ url: process.env.DATABASE_URL });
const db = drizzle(client);

// Define schema
export const users = mysqlTable('users', {
  id: serial("id").primaryKey(),
  fullName: text('full_name'),
  phone: varchar('phone', { length: 256 }),
});
export type User = typeof users.$inferSelect; // return type when queried
export type NewUser = typeof users.$inferInsert; // insert type

export async function GET(request: NextRequest) {
  // Insert and select data
  const user: NewUser = { fullName: 'John Doe', phone: '123-456-7890' };
  await db.insert(users).values(user)
  const result: User[] = await db.select().from(users);
  return NextResponse.json(result);
}

Test your code locally:

export DATABASE_URL='mysql://[username]:[password]@[host]/[database]'
next dev

Navigate to http://localhost:3000/api/edge-function-example to get the response from your route.

Step 4. Deploy your code to Vercel

Deploy your code to Vercel with the DATABASE_URL environment variable:
```
vercel -e DATABASE_URL='mysql://[username]:[password]@[host]/[database]' --prod
```
After the deployment is complete, you will get the URL of your project.
Navigate to the ${Your-URL}/api/edge-function-example page to get the response from your route.

What's next

Learn more about Drizzle and drizzle-orm/tidb-serverless.
Learn how to integrate TiDB Cloud with Vercel.

TiDB Cloud Serverless Driver Drizzle Tutorial

Use Drizzle and TiDB Cloud serverless driver in Node.js environments

Before you begin

Step 1. Create a project

Step 2. Set the environment

Step 3. Use Drizzle to query data

Step 4. Run the Typescript code

Use Drizzle and TiDB Cloud serverless driver in edge environments

Before you begin

Step 1. Create a project

Step 2. Set the environment

Step 3. Create an edge function

Step 4. Deploy your code to Vercel

What's next

Was this page helpful?