Add Drizzle+PostgreSQL to Remix Vite with Cloudflare

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.

Drizzle works with many different databases, and can be configured to work with PostgreSQL.

In this guide, we'll implement this example of Drizzle and PostgreSQL to a Remix Vite app running on Cloudflare.

Start with the remix cloudflare vite template:

bash

npx create-remix@latest --template remix-run/remix/templates/cloudflare

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

bash

npm i drizzle-orm postgres
npm i --save-dev drizzle-kit

You may also want to check out my guide on deploying to Cloudflare Pages if you have any issues along the way.

Write your schema

The schema is where you define your tables and columns. Drizzle uses typescript to infer types from your schema, so you get type-safe reads and writes.

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 { pgTable, text, serial } from "drizzle-orm/pg-core"
export const resources = pgTable("resources", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  href: text("href").notNull(),
})

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": {
    "drizzle:update": "drizzle-kit generate:pg --out ./app/drizzle/migrations --schema ./app/drizzle/schema.server.ts"
  }
}

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

Set up a Postgres database

Create a Postgres database with your provider of choice and set the DATABASE_URL environment variable in a .dev.vars file. Cloudflare uses this instead of a .env file. For example,

bash

DATABASE_URL="postgresql://postgres:postgres@localhost:5432"

We need to tell typescript that this database exists, so run the following command to generate an interface in worker.configuration.d.ts

npx wrangler types

which will look something like this:

interface Env {
  DATABASE_URL: string
}

The remix-cloudflare template currently comes with a load-context.ts file that has an empty Env interface. You'll need to delete this to get the generated one to work.

diff

- interface Env {}

You may need to restart the Typescript server to pick up the changes. In VS Code, open the command pallette with CMD Shift P and run Typescript: Restart TS Server.

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

You can access the connection string from context.cloudflare.env.DATABASE_URL in your action and loader functions.

import postgres from "postgres"
import { drizzle } from "drizzle-orm/postgres-js"
export async function loader({ context }: LoaderFunctionArgs) {
  const db = drizzle(postgres(context.cloudflare.env.DATABASE_URL))
  …
}

Here is a full route file you can copy and paste into your Remix project at app/routes/_index.tsx

tsx

// http://localhost:5173/
import {
  json,
  type MetaFunction,
} from "@remix-run/cloudflare"
import type {
  LoaderFunctionArgs,
  ActionFunctionArgs,
} from "@remix-run/node"
import { Form, useLoaderData } from "@remix-run/react"
import { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"
import { resources } from "#app/drizzle/schema.server"
export async function action({
  request,
  context,
}: ActionFunctionArgs) {
  const formData = await request.formData()
  const title = formData.get("title") as string
  const href = formData.get("href") as string
  const db = drizzle(
    postgres(context.cloudflare.env.DATABASE_URL),
  )
  await db
    .insert(resources)
    .values({ title, href })
    .execute()
  return json(
    { message: "Resource added" },
    { status: 201 },
  )
}
export async function loader({
  context,
}: LoaderFunctionArgs) {
  const db = drizzle(
    postgres(context.cloudflare.env.DATABASE_URL),
  )
  const resourceList = await db
    .select({
      id: resources.id,
      title: resources.title,
      href: resources.href,
    })
    .from(resources)
    .orderBy(resources.id)
  return json({
    resourceList,
  })
}
export default function Index() {
  const { resourceList } = useLoaderData<typeof loader>()
  return (
    <div>
      <h1>
        Welcome to Remix (with Cloudflare, Drizzle, and
        Postgres)
      </h1>
      <ul>
        {resourceList.map((resource) => (
          <li key={resource.id}>
            <a
              target="_blank"
              href={resource.href}
              rel="noreferrer"
            >
              {resource.title}
            </a>
          </li>
        ))}
      </ul>
      <Form method="POST">
        <div>
          <label>
            Title:{" "}
            <input type="text" name="title" required />
          </label>
        </div>
        <div>
          <label>
            URL: <input type="url" name="href" required />
          </label>
        </div>
        <button type="submit">Add Resource</button>
      </Form>
    </div>
  )
}