Drizzle with Turso

This tutorial provides a step-by-step walkthrough on how to integrate Drizzle ORM with Turso database.

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.

The first step involves signing up for Turso and setting up a new database. Turso’s official docs are excellent resources for newcomers. Begin by installing the Turso CLI tool, which is essential for managing Turso services directly from your terminal. Once the CLI is installed, you can sign up for Turso.

Signup to Turso

turso auth signup

Create new database

turso db create drizzle-turso-db

To see information about the database

turso db show drizzle-turso-db

Get an authentication token

turso db tokens create drizzle-turso-db

Update .env

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

TURSO_CONNECTION_URL=
TURSO_AUTH_TOKEN=

With the database set up, the next phase involves integrating Drizzle ORM into your project. This begins by installing necessary dependencies:

npm
yarn
pnpm
bun
npm i drizzle-orm @libsql/client
npm i -D drizzle-kit

To connect Drizzle ORM with your Turso database, you’ll need to create a client using the @libsql/client package, configuring it with the connection URL and authentication token from your .env file. This client is then passed to Drizzle to establish the connection.

src/db/db.ts
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);

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

drizzle.config.ts
import 'dotenv/config';
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema.ts',
  out: './migrations',
  driver: 'turso',
  dbCredentials: {
    url: process.env.TURSO_CONNECTION_URL!,
    authToken: process.env.TURSO_AUTH_TOKEN!,
  },
} satisfies Config;

This configuration file specifies the database schema location, path for generating migrations, and database connection through the connection string.

Finally, you’ll define the schemas for your database tables. In this guide, two schemas are created: users and posts.

src/db/schema.ts
import { sql } from 'drizzle-orm';
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

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

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  userId: integer('user_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  createdAt: text('created_at')
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
});

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

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

Applying changes to the database

To apply changes to the database, we will generate migrations and then run them using the migrate function.

Command for generating migrations:

npx drizzle-kit generate:sqlite

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,
  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,
  `email` text NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);

To run migrations, use the migrate function. This function takes your database connection and the path to your migrations directory as arguments. In the provided example, we call it in index.ts, applying all pending migrations to the database.

import 'dotenv/config';
import { resolve } from 'node:path';
import { db } from './db/db';
import { migrate } from 'drizzle-orm/libsql/migrator';

(async () => {
  await migrate(db, { migrationsFolder: resolve(__dirname, '../migrations') });
})();

Basic file structure

The basic file structure outlines how the project is organized, with the src directory containing your database and schema definitions (db.ts and schema.ts) and the entry point to your application (index.ts). The **migrations** directory holds your migrations and their metadata.

πŸ“¦ <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

As an alternative to generating migration files, you can apply your schema changes directly to the database without generating any migrations files using drizzle-kit push:sqlite command:

npx drizzle-kit push:sqlite
πŸ’‘
It 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.

CRUD

Now, we’re ready to write queries using Drizzle ORM. This section covers the practical aspects of interacting with your database using Drizzle ORM. It includes creating functions for basic CRUD (Create, Read, Update, Delete) operations for users and their posts.

For instance, we will create user.ts and post.ts files in src/functions folder for our queries.

Inserting data

The functions insertUser and insertPost are straightforward examples of how to insert data into the users and posts tables.

src/functions/user.ts
import { db } from '../db/db';
import { InsertUser, users } from '../db/schema';

export async function insertUser(data: InsertUser): Promise<void> {
  await db.insert(users).values(data);
}
src/functions/post.ts
import { db } from '../db/db';
import { InsertPost, posts } from '../db/schema';

export async function insertPost(data: InsertPost): Promise<void> {
  await db.insert(posts).values(data);
}

Retrieving data

The function getUserWithPosts is straightforward example of how to retrieve data with relation:

src/functions/user.ts
import { eq } from 'drizzle-orm';
import { db } from '../db/db';
import { SelectPost, SelectUser, users, posts } from '../db/schema';

export async function getUserWithPosts(
  id: SelectUser['id'],
): Promise<Array<{ users: SelectUser; posts: SelectPost | null }>> {
  const user = await db
    .select()
    .from(users)
    .where(eq(users.id, id))
    .leftJoin(posts, eq(posts.userId, users.id));

  return user;
}

Alternatively, you can use relational queries.

Updating data

The patchPost function illustrates how to update existing records. It highlights the use of the returning() function to fetch the updated records after the operation.

src/functions/post.ts
import { eq } from 'drizzle-orm';
import { db } from '../db/db';
import { posts, SelectPost } from '../db/schema';

export async function patchPost(
  id: SelectPost['id'],
  data: Partial<Omit<SelectPost, 'id'>>,
): Promise<SelectPost[]> {
  const updatedPost = await db.update(posts).set(data).where(eq(posts.id, id)).returning();

  return updatedPost;
}

Deleting data

Finally, the deleteUser function shows how to remove a user from the database. Due to the relational integrity constraints defined in your schema (e.g., cascade delete), deleting a user will also remove all their associated posts.

src/functions/user.ts
import { eq } from 'drizzle-orm';
import { db } from '../db/db';
import { SelectUser, users } from '../db/schema';

export async function deleteUser(id: SelectUser['id']): Promise<void> {
  await db.delete(users).where(eq(users.id, id));
}

That’s it! By implementing these functions, you can perform a full spectrum of CRUD operations in your application, effectively managing your database records with Drizzle ORM.

Conclusion

In this guide, we walked through the steps of building a compact application, using Drizzle and Turso to set up a flexible and powerful database environment.

Turso offers a variety of advanced database functionalities, such as replication, among others. For further insights into how Turso can support your database needs, it’s recommended to consult their documentation.