How do you run Drizzle migrations on D1?

[evrys]

I've got a project set up that uses D1 in production and better-sqlite3 locally for development. Everything works fine in development, and I can access the D1 database in production fine.

However, I'm not sure how to actually get my Drizzle schema into D1. Is it possible to write a "migrate_live" script that connects directly from my local machine to D1 sqlite, or is there some other approach I'm missing?

[ben-xD]

I don't think it's currently possible. The fact no one has responded or faced this issue is people are not using D1. Drizzle doesn't provide a migrate function for D1.

[ben-xD]

As another comment shows, there is a migrate function for D1. https://github.com/drizzle-team/drizzle-orm/blob/6adbd78748c8ecb687caa87b7cd775d86cdc0a2b/drizzle-orm/src/d1/migrator.ts, but the challenge would be there are no migration files (your sql) in the worker runtime.

[jasongitmail]

@ben-xD If you're still sorting out migrations on D1, I shared how I got it working (using all bindings) in this comment: #1388 In case it helps

[ben-xD]

I haven't been using D1 but after seeing your comment I was curious.

I was wondering if it's possible to run migrations when the app/worker starts, rather than manually (perhaps that doesn't make sense for workers). Am I right in thinking the 2 approaches you mention require us to run migrations and deploy workers in sync? Otherwise we might have a worker that doesn't match the D1 schema?

[jasongitmail]

Am I right in thinking the 2 approaches you mention require us to run migrations and deploy workers in sync? Otherwise we might have a worker that doesn't match the D1 schema?

Yeah, and there's still a chance the DB and app won't match up even if you try to migrate and deploy the app at a similar moment. But that's a characteristic of D1, it doesn't support zero-downtime migrations like Xata or some others. Maybe if Cloudflare enables branching on D1, then zero-downtime migrations could be possible.

For zero downtime, you'd have to do more complicated strategies like creating a new col, writing to both cols, syncing old data to the new column, moving reads to the new column, stop writes to the old col, drop the old col, etc. Pain.

[eddie-atkinson]

You can do this with the wrangler d1 migrations apply command. All you need to do is change the config for the migrations_dir in your wrangler.toml file to point at your drizzle directory:

[[d1_databases]]
binding = "DB" # i.e. available in your Worker on env.DB
database_name = "<your db name>"
database_id = "<db-id>"
migrations_dir = "drizzle" # this is the line to change

Then a table will be created with the migration history so that you only run the migrations as needed.

See more details in the cloudflare docs

[jasongitmail]

@eddie-atkinson
Is this still the best approach or have you discovered anything better since?
This means missing out on Drizzle Kit's other feature like check, up, push, etc--right?

I use D1 bindings for interacting with the DB--works great. Now sorting out how best to connect to D1 for migrations (local & production)

[eddie-atkinson]

@jasongitmail

I will have to get back to you on this. My use case for both Drizzle and D1 has led me on a separate side quest contributing to open source. So I don't have an answer right now but once I've popped that off the stack I'll have another look at this

[jasongitmail]

To follow up, I found 2 solutions:

1. Yours. Simplest. Offers migrations list and apply commands.
2. Use drivers for both D1 HTTP and local sqlite (two variations: 1 or 2). But lots to configure.

I went with the first because less to configure and maintain, I prefer to use D1 binding in Drizzle (especially for production), and I don't need Drizzle Studio.

Full instructions for Googlers, for Drizzle with D1 on Cloudflare Pages:

1. Update wrangler.toml to include the path to Drizzle's out directory, which I renamed in Drizzle's config to migrations:

# Local 
d1_databases = [
  { binding = "D1", database_name = "mysite-local", database_id = "n/a", migrations_dir = "src/lib/server/database/migrations" }
]

[env.preview]
d1_databases = [
  { binding = "D1", database_name = "mysite-preview", database_id = "<id>", migrations_dir = "src/lib/server/database/migrations" }
]

[env.production]
d1_databases = [
  { binding = "D1", database_name = "mysite-prod", database_id = "<id>", migrations_dir = "src/lib/server/database/migrations" }
]

2. View unapplied migrations:

wrangler d1 migrations list D1 # `--local` not need, but might be if your wrangler.toml differs.
wrangler d1 migrations list D1 --env preview
wrangler d1 migrations list D1 --env production

3. Apply unapplied migrations:

wrangler d1 migrations apply D1 # `--local` not need, but might be if your wrangler.toml differs.
wrangler d1 migrations apply D1 --env preview
wrangler d1 migrations apply D1 --env production

The apply command creates a d1_migrations table when run the first time.

4. package.json

"list": "wrangler d1 migrations list D1 --local",
"list:preview": "wrangler d1 migrations list D1 --env preview",
"list:production": "wrangler d1 migrations list D1 --env production",
"apply": "wrangler d1 migrations apply D1 --local",
"apply:preview": "wrangler d1 migrations apply D1 --env preview",
"apply:production": "wrangler d1 migrations apply D1 --env production",

[Jdruwe]

Is there also a way for those migrations scripts to be executed automatically when for example deploying my next-on-pages app (which specifies the D1 binding in the wrangler.toml file)?

[ajdiyassin]

Github actions