Turso

Existing database

Meet Drizzle

Get Started with Drizzle and Turso in existing project

This guide assumes familiarity with:

dotenv - package for managing environment variables - read here
tsx - package for running TypeScript files - read here
turso - SQLite for Production - read here
libsql - a fork of SQLite optimized for low query latency, making it suitable for global applications - read here

Basic file structure

This is the basic file structure of the project. In the src/db directory, we have table definition in schema.ts. In drizzle folder there are sql migration file and snapshots.

📦 <project root>
 ├ 📂 drizzle
 ├ 📂 src
 │   ├ 📂 db
 │   │  └ 📜 schema.ts
 │   └ 📜 index.ts
 ├ 📜 .env
 ├ 📜 drizzle.config.ts
 ├ 📜 package.json
 └ 📜 tsconfig.json

Step 1 - Install required packages

npm

yarn

pnpm

bun

npm i drizzle-orm @libsql/client dotenv
npm i -D drizzle-kit tsx

yarn add drizzle-orm @libsql/client dotenv
yarn add -D drizzle-kit tsx

pnpm add drizzle-orm @libsql/client dotenv
pnpm add -D drizzle-kit tsx

bun add drizzle-orm @libsql/client dotenv
bun add -D drizzle-kit tsx

Step 2 - Setup connection variables

Create a .env file in the root of your project and add you Turso database url and auth token:

TURSO_DATABASE_URL=
TURSO_AUTH_TOKEN=

important

If you don’t know your TURSO_DATABASE_URL and TURSO_AUTH_TOKEN values, you can refer to the LibSQL Driver SDK tutorial. Check it out here, then return with all the values generated and added to the .env file

Step 3 - 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({
  out: './drizzle',
  schema: './src/db/schema.ts',
  dialect: 'turso',
  dbCredentials: {
    url: process.env.TURSO_DATABASE_URL,
    authToken: process.env.TURSO_AUTH_TOKEN,
  },
});

Step 4 - Introspect your database

Drizzle Kit provides a CLI command to introspect your database and generate a schema file with migrations. The schema file contains all the information about your database tables, columns, relations, and indices.

For example, you have such table in your database:

CREATE TABLE `users_table` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`name` text NOT NULL,
	`age` integer NOT NULL,
	`email` text NOT NULL UNIQUE
);

Pull your database schema:

npx drizzle-kit pull

The result of introspection will be a schema.ts file, meta folder with snapshots of your database schema, sql file with the migration and relations.ts file for relational queries.

Here is an example of the generated schema.ts file:

// table schema generated by introspection
import {
  sqliteTable,
  uniqueIndex,
  integer,
  text,
} from "drizzle-orm/sqlite-core";

export const usersTable = sqliteTable(
  "users_table",
  {
    id: integer().primaryKey({ autoIncrement: true }).notNull(),
    name: text().notNull(),
    age: integer().notNull(),
    email: text().notNull(),
  },
  (table) => [
    uniqueIndex("users_table_email_unique").on(table.email)
  ]
);

Learn more about introspection in the documentation.

Step 5 - Transfer code to your actual schema file

We recommend transferring the generated code from drizzle/schema.ts and drizzle/relations.ts to the actual schema file. In this guide we transferred code to src/db/schema.ts. Generated files for schema and relations can be deleted. This way you can manage your schema in a more structured way.

 ├ 📂 drizzle
 │ ├ 📂 meta
 │ ├ 📜 migration.sql
 │ ├ 📜 relations.ts ────────┐
 │ └ 📜 schema.ts ───────────┤
 ├ 📂 src                    │ 
 │ ├ 📂 db                   │
 │ │ ├ 📜 relations.ts <─────┤
 │ │ └ 📜 schema.ts <────────┘
 │ └ 📜 index.ts         
 └ …

Step 6 - Connect Drizzle ORM to the database

Drizzle has native support for all @libsql/client driver variations:


`@libsql/client`	defaults to `node` import, automatically changes to `web` if `target` or `platform` is set for bundler, e.g. `esbuild --platform=browser`
`@libsql/client/node`	`node` compatible module, supports `:memory:`, `file`, `wss`, `http` and `turso` conneciton protocols
`@libsql/client/web`	module for fullstack web frameworks like `next`, `nuxt`, `astro`, etc.
`@libsql/client/http`	module for `http` and `https` connection protocols
`@libsql/client/ws`	module for `ws` and `wss` conneciton protocols
`@libsql/client/sqlite3`	module for `:memory:` and `file` conneciton protocols
`@libsql/client-wasm`	Separate experimental package for WASM

default

node

web

http

web sockets

wasm

import { drizzle } from 'drizzle-orm/libsql';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

import { drizzle } from 'drizzle-orm/libsql/node';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

import { drizzle } from 'drizzle-orm/libsql/web';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

import { drizzle } from 'drizzle-orm/libsql/http';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

import { drizzle } from 'drizzle-orm/libsql/ws';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

import { drizzle } from 'drizzle-orm/libsql/wasm';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

Create a index.ts file in the src directory and initialize the connection:

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/libsql';

// You can specify any property from the libsql connection options
const db = drizzle({ 
  connection: { 
    url: process.env.TURSO_DATABASE_URL!, 
    authToken: process.env.TURSO_AUTH_TOKEN!
  }
});

If you need to provide your existing driver:

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';

const client = createClient({ 
  url: process.env.TURSO_DATABASE_URL!, 
  authToken: process.env.TURSO_AUTH_TOKEN!
});

const db = drizzle({ client });

Step 7 - Query the database

Let’s update the src/index.ts file with queries to create, read, update, and delete users

import 'dotenv/config';
import { eq } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/libsql';
import { usersTable } from './db/schema';

async function main() {
  const db = drizzle({ 
    connection: { 
        url: process.env.TURSO_DATABASE_URL!, 
        authToken: process.env.TURSO_AUTH_TOKEN!
    }
  });

  const user: typeof usersTable.$inferInsert = {
    name: 'John',
    age: 30,
    email: '[email protected]',
  };

  await db.insert(usersTable).values(user);
  console.log('New user created!')

  const users = await db.select().from(usersTable);
  console.log('Getting all users from the database: ', users)
  /*
  const users: {
    id: number;
    name: string;
    age: number;
    email: string;
  }[]
  */

  await db
    .update(usersTable)
    .set({
      age: 31,
    })
    .where(eq(usersTable.email, user.email));
  console.log('User info updated!')

  await db.delete(usersTable).where(eq(usersTable.email, user.email));
  console.log('User deleted!')
}

main();

Step 8 - Run index.ts file

To run any TypeScript files, you have several options, but let’s stick with one: using tsx

You’ve already installed tsx, so we can run our queries now

Run index.ts script

npm

yarn

pnpm

bun

npx tsx src/index.ts

yarn tsx src/index.ts

pnpm tsx src/index.ts

bun tsx src/index.ts

tips

We suggest using bun to run TypeScript files. With bun, such scripts can be executed without issues or additional settings, regardless of whether your project is configured with CommonJS (CJS), ECMAScript Modules (ESM), or any other module format. To run a script with bun, use the following command:

bun src/index.ts

If you don’t have bun installed, check the Bun installation docs

Step 9 - Update your table schema (optional)

If you want to update your table schema, you can do it in the schema.ts file. For example, let’s add a new column phone to the users_table:

// table schema generated by introspection
import {
  sqliteTable,
  uniqueIndex,
  integer,
  text,
} from "drizzle-orm/sqlite-core";

export const usersTable = sqliteTable(
  "users_table",
  {
    id: integer().primaryKey({ autoIncrement: true }).notNull(),
    name: text().notNull(),
    age: integer().notNull(),
    email: text().notNull(),
    phone: text(),
  },
  (table) => [
    uniqueIndex("users_table_email_unique").on(table.email)
  ]
);

Step 9 - Applying changes to the database (optional)

You can directly apply changes to your database using the drizzle-kit push command. This is a convenient method for quickly testing new schema designs or modifications in a local development environment, allowing for rapid iterations without the need to manage migration files:

npx drizzle-kit push

Read more about the push command in documentation.

Tips

Alternatively, you can generate migrations using the drizzle-kit generate command and then apply them using the drizzle-kit migrate command:

Generate migrations:

npx drizzle-kit generate

Apply migrations:

npx drizzle-kit migrate

Read more about migration process in documentation.

Step 10 - Query the database with a new field (optional)

Let’s update the src/index.ts file with queries to create, read, update, and delete users

import 'dotenv/config';
import { eq } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/libsql';
import { usersTable } from './db/schema';

async function main() {
  const db = drizzle({ 
    connection: { 
        url: process.env.TURSO_DATABASE_URL!, 
        authToken: process.env.TURSO_AUTH_TOKEN!
    }
  });

  const user: typeof usersTable.$inferInsert = {
    name: 'John',
    age: 30,
    email: '[email protected]',
    phone: '123-456-7890',
  };

  await db.insert(usersTable).values(user);
  console.log('New user created!')

  const users = await db.select().from(usersTable);
  console.log('Getting all users from the database: ', users)
  /*
  const users: {
    id: number;
    name: string;
    age: number;
    email: string;
    phone: string | null;
  }[]
  */

  await db
    .update(usersTable)
    .set({
      age: 31,
    })
    .where(eq(usersTable.email, user.email));
  console.log('User info updated!')

  await db.delete(usersTable).where(eq(usersTable.email, user.email));
  console.log('User deleted!')
}

main();