--- title: "Drizzle with Turso" date: "2024-02-17" svgs: ['', ''] --- import Prerequisites from "@mdx/Prerequisites.astro"; import Npm from '@mdx/Npm.astro'; import Steps from '@mdx/Steps.astro'; import Section from "@mdx/Section.astro"; import Callout from '@mdx/Callout.astro'; This tutorial demonstrates how to use Drizzle ORM with [Turso](https://docs.turso.tech/introduction). - You should have installed Drizzle ORM and [Drizzle kit](/docs/kit-overview). You can do this by running the following command: drizzle-orm -D drizzle-kit - You should have installed `dotenv` package for managing environment variables. Read more about this package [here](https://www.npmjs.com/package/dotenv) dotenv - You should have installed `@libsql/client` package. Read more about this package [here](https://www.npmjs.com/package/@libsql/client). @libsql/client - You should have installed Turso CLI. Check [documentation](https://docs.turso.tech/cli/introduction) for more information [Turso](https://docs.turso.tech/concepts) is a SQLite-compatible database built on [libSQL](https://docs.turso.tech/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](https://docs.turso.tech/concepts). 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](https://docs.turso.tech/quickstart) to setup Turso database. ## Setup Turso and Drizzle ORM #### Signup or login to Turso Signup: ```bash copy turso auth signup ``` Login: ```bash copy turso auth login ``` #### Create new database Create new database by running the `turso db create ` command: ```bash copy turso db create drizzle-turso-db ``` To see information about the database, run the following command: ```bash copy turso db show drizzle-turso-db ``` #### Create an authentication token To create an authentication token for your database, run the following command: ```bash copy turso db tokens create drizzle-turso-db ``` Learn more about this command and its options in the [documentation](https://docs.turso.tech/cli/db/tokens/create). #### Update environment variables Update your `.env` or `.env.local` file with connection url and authentication token. ```text copy TURSO_CONNECTION_URL= TURSO_AUTH_TOKEN= ``` #### Connect Drizzle ORM to your database Create a `index.ts` file in the `src/db` directory and set up your database configuration: ```typescript copy filename="src/db/index.ts" import { config } from 'dotenv'; import { drizzle } from 'drizzle-orm/libsql'; config({ path: '.env' }); // or .env.local export const db = drizzle({ connection: { url: process.env.TURSO_CONNECTION_URL!, authToken: process.env.TURSO_AUTH_TOKEN!, }}); ``` #### Create tables Create a `schema.ts` file in the `src/db` directory and declare your tables: ```typescript copy filename="src/db/schema.ts" 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(), updatedAt: 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](/docs/kit-overview) 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: ```typescript copy filename="drizzle.config.ts" import { config } from 'dotenv'; import { defineConfig } from 'drizzle-kit'; config({ path: '.env' }); export default defineConfig({ schema: './src/db/schema.ts', out: './migrations', dialect: '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: ```bash copy 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: ```sql 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: ```bash copy npx drizzle-kit migrate ``` Alternatively, you can push changes directly to the database using [Drizzle kit push command](/docs/kit-overview#prototyping-with-db-push): ```bash copy npx drizzle-kit push ``` 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 `index.ts` and schema definitions in `schema.ts`. ```plaintext 📦 ├ 📂 src │ ├ 📂 db │ │ ├ 📜 index.ts │ │ └ 📜 schema.ts ├ 📂 migrations │ ├ 📂 meta │ │ ├ 📜 _journal.json │ │ └ 📜 0000_snapshot.json │ └ 📜 0000_watery_spencer_smythe.sql ├ 📜 .env ├ 📜 drizzle.config.ts ├ 📜 package.json └ 📜 tsconfig.json ``` ## Query examples For instance, we create `src/db/queries` folder and separate files for each operation: insert, select, update, delete. #### Insert data Read more about insert query in the [documentation](/docs/insert). ```typescript copy filename="src/db/queries/insert.ts" {4, 8} import { db } from '../index'; import { InsertPost, InsertUser, postsTable, usersTable } from '../schema'; export async function createUser(data: InsertUser) { await db.insert(usersTable).values(data); } export async function createPost(data: InsertPost) { await db.insert(postsTable).values(data); } ``` #### Select data Read more about select query in the [documentation](/docs/select). ```typescript copy filename="src/db/queries/select.ts" {5, 16, 41} import { asc, count, eq, getTableColumns, gt, sql } from 'drizzle-orm'; import { db } from '../index'; 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](/docs/rqb). #### Update data Read more about update query in the [documentation](/docs/update). ```typescript copy filename="src/db/queries/update.ts" {5} import { eq } from 'drizzle-orm'; import { db } from '../index'; import { SelectPost, postsTable } from '../schema'; export async function updatePost(id: SelectPost['id'], data: Partial>) { await db.update(postsTable).set(data).where(eq(postsTable.id, id)); } ``` #### Delete data Read more about delete query in the [documentation](/docs/delete). ```typescript copy filename="src/db/queries/delete.ts" {5} import { eq } from 'drizzle-orm'; import { db } from '../index'; import { SelectUser, usersTable } from '../schema'; export async function deleteUser(id: SelectUser['id']) { await db.delete(usersTable).where(eq(usersTable.id, id)); } ```