Setup Drizzle ORM with SQLite

This guide shows you how to configure drizzle.config.ts with SQLite with the newest version of Drizzle ORM.

First of all make sure you have the proper dependencies installed.

Setting up the packages

pnpm install drizzle-orm
pnpm install drizzle-kit -D

Then you need to install the SQLite driver. There are two options. Either libSQL which can connect to both local SQLite and turso rremote databases. betterr-sqlite3. is for local SQLite only. I prefer the first one as we will show how to use turso later on.

pnpm install @libsql/client

Setting the config

Now we need to create the main config file which is a file that points towards our client, schemas and so forth. In the root of your project run

touch drizzle.config.ts

This file consists of a defineConfig function that takes an object as an argument. It should always look like this as a minimum. There are morre advanced options, but likely you won't need them, or look up the official docs.

import { defineConfig } from "drizzle-kit";
 
export default defineConfig({});

In here we define ther location of our client (db connection), the schema path, the migration path and the dialect (language of the database).

import { defineConfig } from "drizzle-kit";
 
export default defineConfig({
  out: "./src/server/db/migrations",
  schema: "./src/server/db/schema/index.ts",
  dialect: "sqlite",
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
});

The paths can be any you want, out will be generrated, schema and client must exist. I prefer to have these files. Typically you want to validate enviorment variables with zod, or a custom implementation which you can find in the guide I wrote here here.ß

The enviorment variables

For local SQLite we only need a url. This is defined in your .env file.

DATABASE_URL="file:./dev.db"

It must be prefiex w ith file: as per SQLite convention. ./dev.db is a file in the root of the project. Other common names are ./dev.sqlite. I prefer to store it next to my other database files thus i'd opt for DATABASE_URL=file:./src/server/db/database.db

Creating the schema

I will show you the most basic version. I perrsonally tend to use a way more comprehensive implementation which is way more modular and is heavy on seperration of concerns. Read more about it in the guide.

Then we can create tables. This is a example of a userrs table.

import { sqliteTable as table } from "drizzle-orm/sqlite-core";
import * as t from "drizzle-orm/sqlite-core";
 
export const users = table(
  "users",
  {
    id: t.int().primaryKey({ autoIncrement: true }),
    firstName: t.text("first_name"),
    lastName: t.text("last_name"),
    email: t.text().notNull(),
    role: t.text().$type<"guest" | "user" | "admin">().default("guest"),
  },
  (table) => [t.uniqueIndex("email_idx").on(table.email)]
);

Creating the client

Now we need to create the client that will be used to connect to the database.

touch src/server/db/index.ts

I always make a alias in my tsconfig.json file which will allow us to use the database like this: import { db } from 'db'. I do the same for the schema allowing us to use it like this: import { users } from 'schema'.

{
  "compilerOptions": {
    ...other compiler options,
    "paths": {
      "@/*": ["./src/*"],
      "db": ["./src/server/db/index.ts"],
      "schema": ["./src/server/db/schema/index.ts"]
    }
  }
  }
}

This is all that's needed to populate the database with our schema. You should be able to run the following command to create the database and the tables.

pnpm dlx drizzle-kit generate
pnpm dlx drizzle-kit push
## or npx drizzle-kit generate
## or npx drizzle-kit push

And a database file should have spawned, which if inspected via a sqlite client will show the tables we just created.

Now you can query and mutate the database via server functions or api routes.

Remote turso

To be continued...