Use Turso with Drizzle ORM And Bun in Next.js

4 min readJun 11, 2024

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, OSAlts.

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

Signup or login to Turso

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 osalts:

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

And show the information about the osalts databasewith following command:

turso db show osalts
# Name: oslats
# URL: libsql://oslats-*****.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 osalts
# Connected to osalts at libsql://osalts-****.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
# │ └── osalts.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 osalts
#libsql://osalts-*****.turso.io
turso db tokens create osalts
#eyj******Cw

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

TURSO_DATABASE_URL='libsql://oslats-*****.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 local Turso

For connecting the Turso database locally, 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 an `os` table in `osalts` database

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

Generate the Migration

generate migrations 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/osalts/drizzle.config.ts'
# 1 tables
# os 2 columns 1 indexes 0 fks

This will generate a migration SQL file:

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

Push the Schema to Turso Database

`drizzle-kit push` lets you push the schema changes directly to the `osalts` 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/osalts/drizzle.config.ts'
# [✓] Pulling schema from database…[✓] Changes applied

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

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

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