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.
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
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
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.
# →
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.
db
Folder for Connecting Turso Databasetree -L 3 --gitignore db
# db
# ├── index.ts
# ├── libsql
# │ └── aes.db
# ├── schema.ts
# └── seed.ts
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="*******"
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;
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);
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 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`);
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.