TiDB Cloud Serverless Driver Kysely Tutorial

Kysely is a type-safe and autocompletion-friendly TypeScript SQL query builder. TiDB Cloud offers @tidbcloud/kysely, enabling you to use Kysely over HTTPS with TiDB Cloud serverless driver. Compared with the traditional TCP way, @tidbcloud/kysely brings the following benefits:

Better performance in serverless environments.
Ability to use Kysely in edge environments.

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

Use TiDB Cloud Kysely dialect in Node.js environments

This section describes how to use TiDB Cloud serverless driver with Kysely 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 kysely-node-example:

mkdir kysely-node-example
cd kysely-node-example

Install the kysely, @tidbcloud/kysely, and @tidbcloud/serverless packages:
```
npm install kysely @tidbcloud/kysely @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/kysely": "^0.0.4",
    "@tidbcloud/serverless": "^0.0.7",
    "kysely": "^0.26.3",
  }
}

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

On the overview page of your TiDB Cloud 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]/[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 Kysely to query data

Create a table in your TiDB Cloud Serverless cluster and insert some data.

You can use SQL Editor in the TiDB Cloud console to execute SQL statements. Here is an example:

CREATE TABLE `test`.`person`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL DEFAULT NULL,
  `gender` enum('male','female') NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
);

insert into test.person values (1,'pingcap','male')

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

import { Kysely,GeneratedAlways,Selectable } from 'kysely'
import { TiDBServerlessDialect } from '@tidbcloud/kysely'

// Types
interface Database {
  person: PersonTable
}

interface PersonTable {
  id: GeneratedAlways<number>
  name: string
  gender: "male" | "female"
}

// Dialect
const db = new Kysely<Database>({
  dialect: new TiDBServerlessDialect({
    url: process.env.DATABASE_URL
  }),
})

// Simple Querying
type Person = Selectable<PersonTable>
export async function findPeople(criteria: Partial<Person> = {}) {
  let query = db.selectFrom('person')

  if (criteria.name){
    query = query.where('name', '=', criteria.name)
  }

  return await query.selectAll().execute()
}

console.log(await findPeople())

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 TiDB Cloud Kysely dialect in edge environments

This section takes the TiDB Cloud Kysely dialect in 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 kysely-example using the following terminal commands:

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

Install the kysely, @tidbcloud/kysely, and @tidbcloud/serverless packages:
```
npm install kysely @tidbcloud/kysely @tidbcloud/serverless
```

Step 2. Set the environment

On the overview page of your TiDB Cloud 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]/[database]

Step 3. Create an edge function

Create a table in your TiDB Cloud Serverless cluster and insert some data.

You can use SQL Editor in the TiDB Cloud console to execute SQL statements. Here is an example:

CREATE TABLE `test`.`person`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL DEFAULT NULL,
  `gender` enum('male','female') NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
);

insert into test.person values (1,'pingcap','male')

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 { Kysely,GeneratedAlways,Selectable } from 'kysely'
import { TiDBServerlessDialect } from '@tidbcloud/kysely'

export const runtime = 'edge';

// Types
interface Database {
  person: PersonTable
}

interface PersonTable {
  id: GeneratedAlways<number>
  name: string
  gender: "male" | "female" | "other"
}

// Dialect
const db = new Kysely<Database>({
  dialect: new TiDBServerlessDialect({
    url: process.env.DATABASE_URL
  }),
})

// Query
type Person = Selectable<PersonTable>
async function findPeople(criteria: Partial<Person> = {}) {
  let query = db.selectFrom('person')

  if (criteria.name){
    query = query.where('name', '=', criteria.name)
  }

  return await query.selectAll().execute()
}

export async function GET(request: NextRequest) {

  const searchParams = request.nextUrl.searchParams
  const query = searchParams.get('query')

  let response = null;
  if (query) {
    response = await findPeople({name: query})
  } else {
    response = await findPeople()
  }

  return NextResponse.json(response);
}

The preceding code accepts a query parameter query and returns the result of the query. If the query parameter is not provided, it returns all records in the person table.

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 Kysely and @tidbcloud/kysely
Learn how to integrate TiDB Cloud with Vercel

TiDB Cloud Serverless Driver Kysely Tutorial

Use TiDB Cloud Kysely dialect in Node.js environments

Before you begin

Step 1. Create a project

Step 2. Set the environment

Step 3. Use Kysely to query data

Step 4. Run the Typescript code

Use TiDB Cloud Kysely dialect 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?