Resolving the `DO $$` Issue in Drizzle ORM with Nile Postgres

When working with Drizzle ORM on a Nile Postgres database, you might encounter an issue where the migration fails due to the use of DO $$ blocks. This is because Nile Postgres does not currently support DO $$ statements. Here's a simple guide to fix this issue and make your migrations work.

What’s the Problem?

Drizzle ORM sometimes generates SQL with DO $$ blocks for operations like adding foreign key constraints. Here’s an example:

DO $$ BEGIN
 ALTER TABLE "account" ADD CONSTRAINT "account_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

When you try to run this SQL on Nile Postgres, it throws an error because DO $$ is not supported.

The Solution

To resolve this issue, you need to remove the DO $$ blocks and keep only the ALTER TABLE statements. This is sufficient to apply the constraints without errors.

Step-by-Step Fix

1. Locate the SQL File
   
   After running drizzle-kit generate, find the generated SQL file for your migrations.

2. Identify DO $$ Blocks
   
   Look for all instances of DO $$ in the file. These blocks usually wrap around ALTER TABLE statements for adding constraints.

3. Modify the SQL
   
   For each block:

   • Remove the DO $$ BEGIN and EXCEPTION ... END $$; lines.
   • Retain only the ALTER TABLE statement.

Before:

   DO $$ BEGIN
    ALTER TABLE "account" ADD CONSTRAINT "account_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;
   EXCEPTION
    WHEN duplicate_object THEN null;
   END $$;

After:

   ALTER TABLE "account" ADD CONSTRAINT "account_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;

1. Save Changes
   
   Save the modified SQL file.

2. Run the Migration
   
   Run the migration command again:
   

   deno task migrate

This should apply the changes successfully.

Example Fix

Here’s an example of a full migration SQL file before and after the fix.

Before:

CREATE TABLE IF NOT EXISTS "account" (
    "id" text PRIMARY KEY NOT NULL,
    "userId" text NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "account" ADD CONSTRAINT "account_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

After:

CREATE TABLE IF NOT EXISTS "account" (
    "id" text PRIMARY KEY NOT NULL,
    "userId" text NOT NULL
);
--> statement-breakpoint
ALTER TABLE "account" ADD CONSTRAINT "account_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;

Why Does This Work?

The DO $$ blocks are used to handle potential errors, such as adding a constraint that already exists. Since Nile Postgres doesn’t support DO $$, we simplify the operation by assuming the migration is applied on a clean database or managing duplicate errors separately.

Conclusion

If you're using Drizzle ORM with Nile Postgres and encounter the DO $$ issue, simply edit your generated SQL file to remove the unsupported blocks. This quick fix ensures your migrations run smoothly.

Looking forward to the Drizzle team's upcoming update that eliminates the need for this workaround! 😊

If you found this helpful, share it with others who are working with Drizzle ORM and Nile Postgres. Happy coding! 🚀

Check out my repository for more insights and examples: GitHub Repository. 👀