Use Turso with Drizzle ORM And Bun in Next.js

On April 8th, 2024, the free Hobby database of PlanetScale plan would no longer be available. So I started to find an alternative. After the comparison of other serverless databases in the market, I decided to choose Turso as the database for the next project, Amazing Endemic Species.

Moreover, I used Primsa as an Object-Relational Mapping (ORM) tool applied in my Next.js projects last year. However, I did not like its unique syntax of API design and the level of abstraction. Until I found Drizzle ORM, whose philosophy is "If you know SQL, you know Drizzle ORM", which offers a higher level abstraction from SQL and can be used to read nested relations.

In this article, I will introduce how to use Turso with Drizzle ORM in Next.js.

Prerequisites

Bun v1.1+

Install Drizzle ORM and Drizzle kit

You should have installed Drizzle ORM and Drizzle kit. You can do this by running the following command:

bun add drizzle-orm @libsql/client
bun add -D drizzle-kit

Setup Turso

Install the Turso CLI

Turso CLI will help manage the database, create replicas in other regions, and connect to the database shell. You can install it by following the command:

curl -sSfL https://get.tur.so/install.sh | bash

And check the version of Turso CLI.

turso -v
turso version v0.93.8

turso auth signup
turso auth login
# Already signed in as shenlu89. Use turso auth logout to log out of this account

Create a Database in Turso

I create a database with the name aes:

turso db create aes
# Created database aes at group default in 1.758s.
# Start an interactive SQL shell with:
# turso db shell aes
# To see information about the database, including a connection URL, run:
# turso db show aes
# To get an authentication token for the database, run:
# turso db tokens create aes

And show the information about the aes database with following command:

turso db show aes
# Name: aes
# URL: libsql://aes-*****.turso.io
# ID: ********
# Group: default
# Version: 0.24.7
# Locations: sin, sjc
# Size: 4.1 kB
# Sleeping: No
# Bytes Synced: 0 B
# Is Schema: No
# Database Instances:
# NAME TYPE LOCATION
# sjc primary sjc
# sin replica sin

Connect with the database with following command:

turso db shell aes
# Connected to aes at libsql://aes-****.turso.io
# Welcome to Turso SQL shell!
# Type ".quit" to exit the shell and ".help" to list all available commands.
# →

Setup Turso Database

Turso is a SQLite-compatible database built on libSQL, the Open Contribution fork of SQLite. It is easy to set up a local database for the development process.

Create `db` Folder for Connecting Turso Database

tree -L 3 --gitignore db
# db
# ├── index.ts
# ├── libsql
# │ └── aes.db
# ├── schema.ts
# └── seed.ts

Update environment variables

Create a .env file in the root directory with the connection url and authentication token and get database name and token from the following command:

turso db show --url aes
#libsql://aes-*****.turso.io
turso db tokens create aes
#eyj******Cw

Then Paste the connection url and authentication token in .env as below:

TURSO_DATABASE_URL='libsql://aes-*****.turso.io'
TURSO_AUTH_TOKEN="*******"

Setup Drizzle config file

Create a drizzle.config.ts file in the root of the project and add the following content:

import type { Config } from "drizzle-kit";
export default {
  dialect: "sqlite",
  schema: "./db/schema.ts",
  out: "./db/migrations",
  driver: "turso",
  dbCredentials: {
    url: process.env.TURSO_DATABASE_URL!,
    authToken: process.env.TURSO_AUTH_TOKEN!,
  },
} satisfies Config;

Connect Drizzle ORM to Turso

For connecting Turso database, I write the following code into db/index.ts as below:

import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';
const client = createClient({
  url: process.env.TURSO_CONNECTION_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN!,
});
export const db = drizzle(client);

Create tables in Turso database

The code below is a demonstration of creating a aes table in aes database

import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";
export const aes = sqliteTable("aes", {
  id: integer("id").notNull().primaryKey().unique(),
  createAt: text("createAt")
    .notNull()
    .default(sql`(current_timestamp)`),
});

Generate the Migration

Generate migration based on you Drizzle schema.

bunx drizzle-kit generate
# drizzle-kit: v0.22.4
# drizzle-orm: v0.31.1
# No config path provided, using default 'drizzle.config.ts'
# Reading config file '/home/a/aes/drizzle.config.ts'
# 1 tables
# aes 2 columns 1 indexes 0 fks

This will generate a migration SQL file:

CREATE TABLE `aes` (
`id` integer PRIMARY KEY NOT NULL,
`createAt` text DEFAULT (current_timestamp) NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `aes_id_unique` ON `aes` (`id`);

Push the Schema to Turso Database

drizzle-kit push lets you push the schema changes directly to the aes database

bunx drizzle-kit push
# drizzle-kit: v0.22.4
# drizzle-orm: v0.31.1
# No config path provided, using default path
# Reading config file '/home/a/aes/drizzle.config.ts'
# [✓] Pulling schema from database…[✓] Changes applied

After that, you will see the schema of aes table in Turso database.

turso db shell aes '.schema'
# CREATE TABLE `aes` (
# `id` integer PRIMARY KEY NOT NULL,
# `createAt` text DEFAULT (current_timestamp) NOT NULL
# );
# CREATE UNIQUE INDEX `aes_id_unique` ON `aes` (`id`);

Let's stop here for today and continue adding more later.