Help querying D1 with Drizzle

I was able to run migrations with this drizzle.config.ts:

import { defineConfig } from "drizzle-kit"

import { env } from "./env.mjs"

export default defineConfig({
  dialect: "sqlite",
  driver: "d1-http",
  out: "./src/data/migrations",
  schema: "./src/data/schema/*",
  migrations: { table: "migrations" },
  dbCredentials: {
    accountId: env.CLOUDFLARE_ACCOUNT_ID,
    databaseId: env.CLOUDFLARE_DATABASE_ID,
    token: env.CLOUDFLARE_TOKEN,
  },
})

import { defineConfig } from "drizzle-kit"

import { env } from "./env.mjs"

export default defineConfig({
  dialect: "sqlite",
  driver: "d1-http",
  out: "./src/data/migrations",
  schema: "./src/data/schema/*",
  migrations: { table: "migrations" },
  dbCredentials: {
    accountId: env.CLOUDFLARE_ACCOUNT_ID,
    databaseId: env.CLOUDFLARE_DATABASE_ID,
    token: env.CLOUDFLARE_TOKEN,
  },
})

but I'm not sure how to connect with the database to run queries on it, one previous example I've used with RDS was:

const client = new RDSDataClient({})

export const db = drizzle(client)

const client = new RDSDataClient({})

export const db = drizzle(client)

does anyone knows how can I do the same but with D1?

6 Replies

A Dapper Raccoon•6mo ago

I'm new to both D1 and Drizzle, but here's as far as I've managed to wrap my head around the situation: If you're talking about connecting to that D1 instance which you've applied the migrations to remotely (either local dev to D1, or a non-CF deploy to D1), I think you'd need to use the sqlite-proxy driver: - https://orm.drizzle.team/docs/get-started-sqlite#http-proxy - https://github.com/drizzle-team/drizzle-orm/issues/2086#issuecomment-2081272424 If you're developing for Cloudflare Pages, Wrangler doesn't support developing against remote D1 instances (https://developers.cloudflare.com/d1/build-with-d1/local-development/#develop-locally-with-pages) but I'm not clear on if that limitation applies in other contexts. Summarily, if you're developing for Pages and using the d1 driver, your database code will execute against the mock D1 interface provided by Miniflare and backed by local sqlite files when executing locally, and against the actual D1 instance when executing on the CF platform. Kit can properly apply migrations to the local databases if using the d1 driver in the Kit config, however. So I think if you intend to support developing against both local and remote D1 databases, you need to conditionally swap out your ORM drivers/database initialization and Kit config depending on an env var or some such.

cosbgn•6mo ago

Would be cool if there would be a driver which would use binding and fallback to proxy. It would work in both dev and production perfectly with minimal config.

A Dapper Raccoon•6mo ago

Totally agree. Using Drizzle with D1 is kind of a mess if you're not sticking to the basic d1 configuration. On top of swapping d1/d1-http/sqlite-proxy drivers, if you want to use Drizzle Studio against the local Miniflare databases, you additionally presently need to swap the Kit config over to using the better-sqlite3 driver. So we're up to 4 different drivers to use the Drizzle suite with D1 across all environment combinations

Gabriel LucenaOP•6mo ago

is there a technical reason why we don't have an adapter for D1? is this a D1's missing feature?

cosbgn•6mo ago

To be fair, this is more a cloudflare issue rather than drizzle. If cloudflare would provide a simple http endpoins like turso it would work as easily. The problem is that they use binding so it's complicated and everyone has a different way to use bindings locally. I think the current setup is almost perfect, binding for drizzle orm and d1-http for studio/migrations. You get best of both worlds. The only thing missing really is studio & migrations against the local db. For migrations I use wrangler (for both local and prod) and for studio I use a vscode extension which shows me a studio-link table when I click on the local db. So for anyone trying d1, this is what I suggest: 1st: d1-http for drizzle.config.js (migrations & studio)

export default {
    dialect:"sqlite",
    driver:"d1-http",
    schema: "server/db/schema.js",
    out: "server/db/migrations",
    dbCredentials: {
        accountId: process.env.CLOUDFLARE_ACCOUNT_ID,
        databaseId: process.env.CLOUDFLARE_D1_ID,
        token: process.env.CLOUDFLARE_D1_TOKEN,
    }
}

export default {
    dialect:"sqlite",
    driver:"d1-http",
    schema: "server/db/schema.js",
    out: "server/db/migrations",
    dbCredentials: {
        accountId: process.env.CLOUDFLARE_ACCOUNT_ID,
        databaseId: process.env.CLOUDFLARE_D1_ID,
        token: process.env.CLOUDFLARE_D1_TOKEN,
    }
}

2nd: Binding for drizzle orm, you need to figure out how to use bindings locally, most frameworks have a way:

import { drizzle } from 'drizzle-orm/d1';
drizzle(event.context.cloudflare.env.DB, { schema: schema_file })

import { drizzle } from 'drizzle-orm/d1';
drizzle(event.context.cloudflare.env.DB, { schema: schema_file })

To migrate use:

drizzle-kit generate // generate migration files
npx wrangler d1 migrations apply db_name --local

drizzle-kit generate // generate migration files
npx wrangler d1 migrations apply db_name --local

use --local for local and --remote for the production db I guess you could apply migrations with drizzle for the production env, but it won't work for the local one, so you might as well stick with wrangler which will work for both.

manubier•2mo ago

I followed these steps and ran into the following situation: 1. I recently encountered a situation where a migration applied successfully locally but failed when I tried to apply it in production. 2. Additionally, Wrangler doesn’t seem to have a command for dropping migrations. I used Drizzle drop, but since the initial application was through Wrangler, it didn’t properly adjust my local database. This left my local database in an odd state. Anyone has good advice on how to solve this? Also any advice on how to properly seed the local db? I created a script which uses the d1 API to seed prod but not sure how to do it locally.

Gaming

Programming

Help querying D1 with Drizzle