Add Drizzle ORM to Remix with SQLite and Fly.io

Drizzle Not to be confused with Drizzle the discontinued MySQL database management system is a typescript ORM for interacting with relational databases. It gives you type-safe reads and writes, plus automatic migration handling.

In this guide we'll add Drizzle to a Remix app and set it up to use SQLite.

In addition to Drizzle, we will also need to install Drizzle Kit, which is a CLI migration tool, and any SQLite driver, such as better-sqlite3.

bash

npm i drizzle-orm better-sqlite3
npm i --save-dev drizzle-kit @types/better-sqlite3

Add drizzle to your project

Drizzle is pretty lightweight and easy to integrate into an existing project. Using the SQLite drive you installed, you'll create a new database instance and pass it to Drizzle.

Create a new file app/drizzle/config.server.ts and add the following:

import { drizzle } from "drizzle-orm/better-sqlite3"
import { migrate } from "drizzle-orm/better-sqlite3/migrator"
import Database from "better-sqlite3"
if (!process.env.DATABASE_PATH) {
  throw new Error(
    "Missing environment variable: DATABASE_PATH",
  )
}
export const db = drizzle(
  new Database(process.env.DATABASE_PATH),
)
// Automatically run migrations on startup
void migrate(db, {
  migrationsFolder: "app/drizzle/migrations",
})

There are two main things happening here:

We're exporting a db instance of Drizzle, which will be the primary way we use this across our app
When this file loads (on server startup), we're running any pending migrations.

Environment variables

You'll want to set the DATABASE_PATH=./data.db environment variable to the path where you want your SQLite database to live. A .env file is a good place to keep it during development.

This is a good time to set the same environment variable on your host too. If you're using fly, either use fly secrets set or, since it's not sensitive, just add it your fly.toml

toml

[env]
  DATABASE_PATH="/data/sqlite.db"

Write your schema

The schema is where you define your tables and columns.

Create a new file app/drizzle/schema.server.ts and write a basic schema to get started. You can read more about the schema syntax in the Drizzle docs

import { sql } from "drizzle-orm"
import {
  sqliteTable,
  text,
  integer,
} from "drizzle-orm/sqlite-core"
export const items = sqliteTable("items", {
  id: integer("id").primaryKey(),
  title: text("title").notNull(),
  description: text("description"),
  createdAt: text("createdAt")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text("updatedAt")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
})

To power typescript's type inference and autocomplete, we need to generate types based on our schema. That's what the drizzle-kit CLI tool was for.

Go to your package.json, add a db:migrations script to run the migrations:

json

{
  "scripts": {
    "db:migrations": "drizzle-kit generate:sqlite --out ./app/drizzle/migrations --schema ./app/drizzle/schema.server.ts"
  }
}

Run npm run db:migrations to and you should see a migrations directory in your app/drizzle folder. This will be committed to your repo.

Read and write documents

Drizzle should be fully set up now. Let's try it out!

In one of your routes

add an action that inserts a new item into the database
add a loader that reads all the items from the database and returns them as JSON
add a form that submits to the action

tsx

import { db } from "#app/drizzle/config.server.ts"
import { items } from "#app/drizzle/schema.server.ts"
import { json, Form } from "@remix-run/react"
export async function action({
  request,
}: ActionFunctionArgs) {
  db.insert(items).values({ title: "Item title" }).run()
  return {
    success: true,
  }
}
export async function loader({
  request,
}: LoaderFunctionArgs) {
  // use drizzle to get the data
  const data = db.select().from(items).all()
  return json({
    data,
  })
}
export default function Index() {
  const { items } = useLoaderData<typeof loader>()
  return (
    <div>
      <h1> Items </h1>
      <ul>
        {items.map((item) => (
          <li key={item.id}>{item.title}</li>
        ))}
      </ul>
      <Form method="POST">
        <input type="submit" value="Submit" />
      </Form>
    </div>
  )
}

Every time you hit the submit button, you should see a new item pop up on the list! You can shut down the server and restart it, and the items will still be there, safe in your database.

Include the migrations in your build

Depending on how you've set up your production environment, this step might not be necessary. But if you deploy your app and you get an error that it can't read migrations/meta/_journal.json then there's a good chance the migrations just didn't make it into your build.

Docker uses the ADD command to copy files into the image. If you have a single staged build (i.e. you're not using a multi-stage build), then you can just add the migrations to your build:

FROM base
ADD ./app/drizzle/migrations /myapp/app/drizzle/migrations

If you're doing a multi-staged build, you probably have a build layer where you copy all of your source code into the image (using ADD . .). The migrations will be copied too when that happens, but you'll need to copy them again into the final image.

With that structure, the final COPY line is the one you'll want to bring into your own Dockerfile.

FROM base as build
ADD . .
FROM base as run
COPY --from=build /myapp/app/drizzle/migrations /myapp/app/drizzle/migrations

Try deploying again and you should be good to go!