Seeking Best Practices for Using Drizzle ORM with D1, Especially for Migrations
Hi everyone,
I’m currently working on a project using Drizzle ORM with D1, and I’ve encountered some challenges around handling migrations. I’d really appreciate any insights or best practices from those who have navigated this before. Here are some of the specific questions and areas where I’m looking for guidance:
1. Creating Migrations:
- What are the best practices for generating migration files?
- Should I be using the Drizzle commands like DrizzleKit generate for this, or should I stick to using Wrangler commands?
2. Applying Migrations Locally and in Production:
- When applying migrations, is it better to use the Wrangler command or the DrizzleKit migrate command?
- I’ve been using DrizzleKit generate to create migration files, then applying them with Wrangler, which has worked both locally and in production. However, I recently encountered a situation where a migration applied successfully locally but failed when I tried to apply it in production.
- 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 database. This left my local database in an odd state, which I resolved by recreating it, but I’m hoping to avoid this in the future. What’s the recommended approach for dropping or reverting migrations properly?
3. Integrating Migrations into CI/CD Pipeline:
- How can I integrate the migration process into my CI/CD pipeline using GitHub Actions?
- I attempted to automate applying migrations in production through GitHub Actions but ran into some difficulties. Has anyone successfully set this up, and if so, could you share your approach?
Overall, I think there’s a need for a comprehensive guide on best practices for using Drizzle ORM with D1, particularly around handling migrations. If anyone has advice or experience to share, I’d be incredibly grateful!
Thanks in advance!
4 Replies
Here is more info about the migration that runs smooth on my local db and fails on D1.
Console Logs:
├───────────────────────────────────┤
│ 0002_third_carmella_unuscione.sql │
└───────────────────────────────────┘
✔ About to apply 1 migration(s)
Your database may not be available to serve requests during the migration, continue? … yes
🌀 Executing on remote database prod-talentsourcer-ai-v2 (86f547dc-d0b2-456b-a369-604c409423fc):
🌀 To execute on your local development database, remove the --remote flag from your wrangler command.
┌───────────────────────────────────┬────────┐
│ name │ status │
├───────────────────────────────────┼────────┤
│ 0002_third_carmella_unuscione.sql │ ❌ │
└───────────────────────────────────┴────────┘
❌ Migration 0002_third_carmella_unuscione.sql failed with the following errors:
✘ [ERROR] A prepared SQL statement must contain only one statement. [code: 7500]
Happy to share full logs in DM.
Here is the migration file. The comment on top is weird to me because I have generated columns with the same settings before.
/*
SQLite does not support "Set default to column" out of the box, we do not generate automatic migration for that, so it has to be done manually
Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
https://www.sqlite.org/lang_altertable.html
https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3
Due to that we don't generate migration automatically and it has to be done manually
*/--> statement-breakpoint
ALTER TABLE
candidate_checks ADD candidate_category text DEFAULT 'NO_FIT';--> statement-breakpoint
ALTER TABLE candidate_checks ADD is_favorite integer DEFAULT false NOT NULL;SQLite: ALTER TABLE Statement
This SQLite tutorial explains how to use the SQLite ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with syntax and examples).
Stack Overflow
Modify a Column's Type in sqlite3
I'm pretty new to SQLite 3 and just now I had to add a column to an existing table I had. I went about doing that by doing: ALTER TABLE thetable ADD COLUMN category;.
Of course, I forgot to specif...
Ok, I just prayed, removed the comment and ran the thing against production. It worked. So it seems the comment in the beginning of the migration file is a problem for wrangler when running it with the --remote flag.
Just to make sure I understand the fix, you removed the comments and it worked remotely? Also, with the comments it was working locally?