Drizzle with Turso

This tutorial demonstrates how to use Drizzle ORM with Turso.

This guide assumes familiarity with:

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

npm

yarn

pnpm

bun

npm i drizzle-orm
npm i -D drizzle-kit

yarn add drizzle-orm
yarn add -D drizzle-kit

pnpm add drizzle-orm
pnpm add -D drizzle-kit

bun add drizzle-orm
bun add -D drizzle-kit

You should have installed dotenv package for managing environment variables. Read more about this package here.
You should have installed Turso CLI. Check documentation for more information.
You should have installed @libsql/client package. Read more about this package here.

Turso is a SQLite-compatible database built on libSQL, the Open Contribution fork of SQLite. It enables scaling to hundreds of thousands of databases per organization and supports replication to any location, including your own servers, for microsecond-latency access. You can read more about Turso’s concepts here.

Drizzle ORM natively supports libSQL driver, we embrace SQL dialects and dialect specific drivers and syntax and mirror most popular SQLite-like all, get, values and run query methods syntax.

Check official documentation to setup Turso database.

Setup Turso and Drizzle ORM

Signup:

turso auth signup

turso auth login

Create new database

Create new database by running the turso db create <DATABASE_NAME> command:

turso db create drizzle-turso-db

To see information about the database, run the following command:

turso db show drizzle-turso-db

Create an authentication token

To create an authentication token for your database, run the following command:

turso db tokens create drizzle-turso-db

Learn more about this command and its options in the documentation.

Update environment variables

Update your .env file with connection url and authentication token.

TURSO_CONNECTION_URL=
TURSO_AUTH_TOKEN=

Connect Drizzle ORM to your database

import 'dotenv/config';
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

import { sql } from 'drizzle-orm';
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const usersTable = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  age: integer('age').notNull(),
  email: text('email').unique().notNull(),
});

export const postsTable = sqliteTable('posts', {
  id: integer('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  userId: integer('user_id')
    .notNull()
    .references(() => usersTable.id, { onDelete: 'cascade' }),
  createdAt: text('created_at')
    .default(sql`(CURRENT_TIMESTAMP)`)
    .notNull(),
  updateAt: integer('updated_at', { mode: 'timestamp' }).$onUpdate(() => new Date()),
});

export type InsertUser = typeof usersTable.$inferInsert;
export type SelectUser = typeof usersTable.$inferSelect;

export type InsertPost = typeof postsTable.$inferInsert;
export type SelectPost = typeof postsTable.$inferSelect;

Setup Drizzle config file

Drizzle config - a configuration file that is used by Drizzle Kit and contains all the information about your database connection, migration folder and schema files.

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

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './migrations',
  dialect: 'sqlite',
  driver: 'turso',
  dbCredentials: {
    url: process.env.TURSO_CONNECTION_URL!,
    authToken: process.env.TURSO_AUTH_TOKEN!,
  },
});

Applying changes to the database

You can generate migrations using drizzle-kit generate command and then run them using the drizzle-kit migrate command.

Generate migrations:

npx drizzle-kit generate

These migrations are stored in the migrations directory, as specified in your drizzle.config.ts. This directory will contain the SQL files necessary to update your database schema and a meta folder for storing snapshots of the schema at different migration stages.

Example of a generated migration:

CREATE TABLE `posts` (
  `id` integer PRIMARY KEY NOT NULL,
  `title` text NOT NULL,
  `content` text NOT NULL,
  `user_id` integer NOT NULL,
  `created_at` text DEFAULT (CURRENT_TIMESTAMP) NOT NULL,
  `updated_at` integer,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `users` (
  `id` integer PRIMARY KEY NOT NULL,
  `name` text NOT NULL,
  `age` integer NOT NULL,
  `email` text NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);

Run migrations:

npx drizzle-kit migrate

Learn more about migration process. Alternatively, you can push changes directly to the database using push command.

💡

Push command is good for situations where you need to quickly test new schema designs or changes in a local development environment, allowing for fast iterations without the overhead of managing migration files.

Basic file structure

This is the basic file structure of the project. In the src/db directory, we have database-related files including connection in db.ts and schema definitions in schema.ts.

📦 <project root>
 ├ 📂 src
 │   ├ 📂 db
 │   │  ├ 📜 db.ts
 │   │  └ 📜 schema.ts
 │   └ 📜 index.ts
 ├ 📂 migrations
 │  ├ 📂 meta
 │  │  ├ 📜 _journal.json
 │  │  └ 📜 0000_snapshot.json
 │  └ 📜 0000_watery_spencer_smythe.sql
 ├ 📜 drizzle.config.ts
 ├ 📜 package.json
 └ 📜 tsconfig.json

Query examples

For instance, we create src/db/queries.ts file with the following queries.

Insert data

Read more about insert query in the documentation.

import { db } from './db';
import { InsertUser, usersTable } from './schema';

export async function createUser(data: InsertUser) {
  await db.insert(usersTable).values(data);
}

Select data

Read more about select query in the documentation.

import { asc, count, eq, getTableColumns, gt, sql } from 'drizzle-orm';
import { db } from './db';
import { SelectUser, postsTable, usersTable } from './schema';

export async function getUserById(id: SelectUser['id']): Promise<
  Array<{
    id: number;
    name: string;
    age: number;
    email: string;
  }>
> {
  return db.select().from(usersTable).where(eq(usersTable.id, id));
}

export async function getUsersWithPostsCount(
  page = 1,
  pageSize = 5,
): Promise<
  Array<{
    postsCount: number;
    id: number;
    name: string;
    age: number;
    email: string;
  }>
> {
  return db
    .select({
      ...getTableColumns(usersTable),
      postsCount: count(postsTable.id),
    })
    .from(usersTable)
    .leftJoin(postsTable, eq(usersTable.id, postsTable.userId))
    .groupBy(usersTable.id)
    .orderBy(asc(usersTable.id))
    .limit(pageSize)
    .offset((page - 1) * pageSize);
}

export async function getPostsForLast24Hours(
  page = 1,
  pageSize = 5,
): Promise<
  Array<{
    id: number;
    title: string;
  }>
> {
  return db
    .select({
      id: postsTable.id,
      title: postsTable.title,
    })
    .from(postsTable)
    .where(gt(postsTable.createdAt, sql`(datetime('now','-24 hour'))`))
    .orderBy(asc(postsTable.title), asc(postsTable.id))
    .limit(pageSize)
    .offset((page - 1) * pageSize);
}

Alternatively, you can use relational query syntax.

Update data

Read more about update query in the documentation.

// imports

export async function updatePost(id: SelectPost['id'], data: Partial<Omit<SelectPost, 'id'>>) {
  await db.update(postsTable).set(data).where(eq(postsTable.id, id));
}

Delete data

Read more about delete query in the documentation.

// imports

export async function deleteUser(id: SelectUser['id']) {
  await db.delete(usersTable).where(eq(usersTable.id, id));
}

Benchmarks

Extension

Studio

Become a Gold Sponsor