AI Migration Conversion Guide — PostgreSQL

For AI models: This file contains the complete rules and examples needed to convert Drizzle Kit PostgreSQL migrations into Liquibase-formatted SQL. Follow these rules exactly. Every rule is mandatory unless explicitly marked as optional.

Task

You are converting Drizzle Kit PostgreSQL migration files (generated by drizzle-kit generate) into Liquibase Formatted SQL files compatible with drizzle-migrations-liquibase.

Input: One or more Drizzle Kit .sql migration files (e.g. 0001_cool_name.sql) Output: Liquibase-formatted .sql files with rollback support Dialect: PostgreSQL — identifiers use double quotes ("users", "id")

Output File Template

Every output file MUST follow this exact structure:

--liquibase formatted sql

--changeset <author>:<changeset_id> splitStatements:false endDelimiter:--> statement-breakpoint

<statement 1>;
--> statement-breakpoint

<statement 2>;
--> statement-breakpoint

--rollback <rollback for statement 2>;
--rollback --> statement-breakpoint
--rollback <rollback for statement 1>;
--rollback --> statement-breakpoint

Rules

Rule 1: File Header

The very first line must be:

--liquibase formatted sql

No blank lines or comments before it.

Rule 2: Changeset Declaration

After one blank line, add:

--changeset <author>:<changeset_id> splitStatements:false endDelimiter:--> statement-breakpoint

<author>: The git email, username, or configured author name. If unknown, use migration.
<changeset_id>: A descriptive snake_case name summarising the migration's SQL content. Drizzle Kit auto-generates opaque or random names (e.g. cool_name, mighty_blob, shiny_wasp). Do NOT use these. Instead, read the SQL statements and derive a meaningful name:
- 0001_cool_name.sql containing CREATE TABLE "users" → create_users_table
- 0003_mighty_blob.sql containing ADD COLUMN "phone" + ADD COLUMN "avatar_url" on "users" → add_phone_and_avatar_to_users
- 0042_add_user_roles.sql → add_user_roles (already descriptive — keep as-is)
splitStatements:false and endDelimiter:--> statement-breakpoint are always required, verbatim.

Rule 3: Statement Separator

Replace every statement-ending ; with:

;
--> statement-breakpoint

There must be a blank line before --> statement-breakpoint only when it aids readability (e.g. after a multi-line CREATE TABLE). Single-line statements can have --> statement-breakpoint on the very next line.

Rule 4: Idempotent DDL

Transform DDL statements to be idempotent:

Original	Converted
`CREATE TABLE "x" (`	`CREATE TABLE IF NOT EXISTS "x" (`
`DROP TABLE "x"`	`DROP TABLE IF EXISTS "x"`
`CREATE INDEX "i" ON`	`CREATE INDEX IF NOT EXISTS "i" ON`
`DROP INDEX "i"`	`DROP INDEX IF EXISTS "i"`
`CREATE UNIQUE INDEX "i" ON`	`CREATE UNIQUE INDEX IF NOT EXISTS "i" ON`
`ADD COLUMN "c"`	`ADD COLUMN IF NOT EXISTS "c"`
`DROP COLUMN "c"`	`DROP COLUMN IF EXISTS "c"`
`CREATE TYPE "t" AS ENUM`	`DO $$ BEGIN CREATE TYPE "t" AS ENUM(...); EXCEPTION WHEN duplicate_object THEN null; END $$`
`DROP TYPE "t"`	`DROP TYPE IF EXISTS "t"`

Leave ALTER COLUMN, SET NOT NULL, DROP NOT NULL, SET DATA TYPE, SET DEFAULT, RENAME unchanged — these are not idempotent by nature and will fail safely if schema doesn't match.

Rule 5: Foreign Key Wrapping

Drizzle Kit foreign key additions:

ALTER TABLE "orders" ADD CONSTRAINT "orders_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE no action ON UPDATE no action;

Must be wrapped in a PL/pgSQL block to handle duplicates:

DO $$ BEGIN
 ALTER TABLE "orders" ADD CONSTRAINT "orders_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

Note: Add "public". prefix to the referenced table if not already present.

Rule 6: Rollback Block

After all forward statements, add rollback lines. Rules:

Every --rollback line is a SQL comment — prefix each line with --rollback .
Rollback statements are in reverse order of the forward statements.
Each rollback statement ends with:
```
--rollback --> statement-breakpoint
```
Use idempotent rollback SQL (IF EXISTS / IF NOT EXISTS).

Rollback mappings

Forward Statement	Rollback Statement
`CREATE TABLE IF NOT EXISTS "x" (...)`	`DROP TABLE IF EXISTS "x";`
`DROP TABLE IF EXISTS "x"`	`-- WARNING: Cannot rollback DROP TABLE for x`
`ADD COLUMN IF NOT EXISTS "c" <type>`	`ALTER TABLE "x" DROP COLUMN IF EXISTS "c";`
`DROP COLUMN IF EXISTS "c"`	`-- WARNING: Cannot rollback DROP COLUMN for x.c`
`ALTER COLUMN "c" SET NOT NULL`	`ALTER TABLE "x" ALTER COLUMN "c" DROP NOT NULL;`
`ALTER COLUMN "c" DROP NOT NULL`	`ALTER TABLE "x" ALTER COLUMN "c" SET NOT NULL;`
`ALTER COLUMN "c" SET DATA TYPE <new>`	`ALTER TABLE "x" ALTER COLUMN "c" SET DATA TYPE <old>;` (if old type known)
`ALTER COLUMN "c" SET DEFAULT <val>`	`ALTER TABLE "x" ALTER COLUMN "c" DROP DEFAULT;`
`CREATE INDEX IF NOT EXISTS "i"`	`DROP INDEX IF EXISTS "i";`
`DROP INDEX IF EXISTS "i"`	`-- WARNING: Cannot rollback index drop i`
`ADD CONSTRAINT "c" UNIQUE (...)`	`ALTER TABLE "x" DROP CONSTRAINT IF EXISTS "c";`
`ADD CONSTRAINT "c" FOREIGN KEY (...)`	`ALTER TABLE "x" DROP CONSTRAINT IF EXISTS "c";`
`CREATE TYPE "t" AS ENUM (...)`	`DROP TYPE IF EXISTS "t";`
`CREATE POLICY "p" ON "t"`	`DROP POLICY IF EXISTS "p" ON "t";`
`INSERT INTO ...`	`DELETE FROM ... WHERE ...;` (match the inserted row)
`UPDATE ... SET ...`	`-- WARNING: Cannot reliably rollback UPDATE`

Rule 7: Filename

Rename the file:

<timestamp>_<original_name>.sql

Timestamp: YYYYMMDDHHmmss format using the current date/time. If converting a batch, increment by 1 second per file to maintain order.
Descriptive name: Drizzle Kit often generates random/opaque names (cool_name, mighty_blob, shiny_wasp). Replace these with a descriptive snake_case name derived from the SQL content of the migration. If the original name is already descriptive, keep it.
- 0001_cool_name.sql (creates users table) → 20250710092120_create_users_table.sql
- 0003_mighty_blob.sql (adds columns to users) → 20250710092122_add_phone_and_avatar_to_users.sql
- 0042_add_user_roles.sql (already descriptive) → 20250710092121_add_user_roles.sql
Naming heuristics (in priority order):
1. CREATE TABLE "x" → create_x_table
2. ADD COLUMN on "x" → add_<columns>_to_x
3. ALTER COLUMN on "x" → alter_<columns>_in_x
4. DROP TABLE "x" → drop_x_table
5. CREATE INDEX / ADD CONSTRAINT → add_indexes_to_x or add_constraints_to_x
6. CREATE POLICY → add_rls_policies_to_x
7. Mixed operations → pick the most significant operation for the name
8. If the original Drizzle Kit name is already descriptive (contains a verb + noun), keep it

Rule 8: One Changeset Per File (Default)

Each output file should contain one --changeset block unless the input file logically groups related operations (e.g. table creation + its policies + its indexes). In that case, use one changeset for the whole file.

Never split a single Drizzle Kit migration across multiple output files.

Rule 9: Master Changelog

After converting all files, provide the master-changelog.xml with includes in chronological order:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.20.xsd">

    <include file="migrations/<filename1>.sql"/>
    <include file="migrations/<filename2>.sql"/>
    <!-- ... one per file, in timestamp order -->
</databaseChangeLog>

Rule 10: Preserve Drizzle Kit Statement Breakpoints

Drizzle Kit uses --> statement-breakpoint as its own separator. If present in the input, keep it — it's the same delimiter Liquibase uses. Just ensure the --changeset header and --rollback block are added.

Complete Conversion Examples

Example 1: CREATE TABLE + Enum

Input (0001_initial.sql):

CREATE TYPE "public"."user_role" AS ENUM('admin', 'member', 'guest');
--> statement-breakpoint
CREATE TABLE "users" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"email" varchar(255) NOT NULL,
	"role" "user_role" DEFAULT 'member' NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "users_email_unique" UNIQUE("email")
);

Output (20250710092120_initial.sql):

--liquibase formatted sql

--changeset migration:initial splitStatements:false endDelimiter:--> statement-breakpoint

DO $$ BEGIN
 CREATE TYPE "public"."user_role" AS ENUM('admin', 'member', 'guest');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint

CREATE TABLE IF NOT EXISTS "users" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"email" varchar(255) NOT NULL,
	"role" "user_role" DEFAULT 'member' NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "users_email_unique" UNIQUE("email")
);
--> statement-breakpoint

--rollback DROP TABLE IF EXISTS "users";
--rollback --> statement-breakpoint
--rollback DROP TYPE IF EXISTS "public"."user_role";
--rollback --> statement-breakpoint

Example 2: ADD COLUMNS + ALTER COLUMN

Input (0005_add_profile_fields.sql):

ALTER TABLE "users" ADD COLUMN "phone" varchar(20);
--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "avatar_url" text;
--> statement-breakpoint
ALTER TABLE "users" ALTER COLUMN "name" SET NOT NULL;

Output (20250710092124_add_profile_fields.sql):

--liquibase formatted sql

--changeset migration:add_profile_fields splitStatements:false endDelimiter:--> statement-breakpoint

ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "phone" varchar(20);
--> statement-breakpoint

ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "avatar_url" text;
--> statement-breakpoint

ALTER TABLE "users" ALTER COLUMN "name" SET NOT NULL;
--> statement-breakpoint

--rollback ALTER TABLE "users" ALTER COLUMN "name" DROP NOT NULL;
--rollback --> statement-breakpoint
--rollback ALTER TABLE "users" DROP COLUMN IF EXISTS "avatar_url";
--rollback --> statement-breakpoint
--rollback ALTER TABLE "users" DROP COLUMN IF EXISTS "phone";
--rollback --> statement-breakpoint

Example 3: FOREIGN KEYS + INDEX

Input (0008_add_orders.sql):

CREATE TABLE "orders" (
	"id" serial PRIMARY KEY NOT NULL,
	"user_id" uuid NOT NULL,
	"total" numeric(10,2) NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "orders" ADD CONSTRAINT "orders_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade ON UPDATE no action;
--> statement-breakpoint
CREATE INDEX "orders_user_id_idx" ON "orders" ("user_id");

Output (20250710092127_add_orders.sql):

--liquibase formatted sql

--changeset migration:add_orders splitStatements:false endDelimiter:--> statement-breakpoint

CREATE TABLE IF NOT EXISTS "orders" (
	"id" serial PRIMARY KEY NOT NULL,
	"user_id" uuid NOT NULL,
	"total" numeric(10,2) NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint

DO $$ BEGIN
 ALTER TABLE "orders" ADD CONSTRAINT "orders_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint

CREATE INDEX IF NOT EXISTS "orders_user_id_idx" ON "orders" ("user_id");
--> statement-breakpoint

--rollback DROP INDEX IF EXISTS "orders_user_id_idx";
--rollback --> statement-breakpoint
--rollback ALTER TABLE "orders" DROP CONSTRAINT IF EXISTS "orders_user_id_users_id_fk";
--rollback --> statement-breakpoint
--rollback DROP TABLE IF EXISTS "orders";
--rollback --> statement-breakpoint

Example 4: RLS POLICIES

Input (0012_add_rls.sql):

ALTER TABLE "users" ENABLE ROW LEVEL SECURITY;
--> statement-breakpoint
CREATE POLICY "users_select_own" ON "users" AS PERMISSIVE FOR SELECT TO "authenticated" USING ((select auth.uid()) = id);
--> statement-breakpoint
CREATE POLICY "users_update_own" ON "users" AS PERMISSIVE FOR UPDATE TO "authenticated" USING ((select auth.uid()) = id) WITH CHECK ((select auth.uid()) = id);

Output (20250710092131_add_rls.sql):

--liquibase formatted sql

--changeset migration:add_rls splitStatements:false endDelimiter:--> statement-breakpoint

ALTER TABLE "users" ENABLE ROW LEVEL SECURITY;
--> statement-breakpoint

CREATE POLICY "users_select_own" ON "users" AS PERMISSIVE FOR SELECT TO "authenticated" USING ((select auth.uid()) = id);
--> statement-breakpoint

CREATE POLICY "users_update_own" ON "users" AS PERMISSIVE FOR UPDATE TO "authenticated" USING ((select auth.uid()) = id) WITH CHECK ((select auth.uid()) = id);
--> statement-breakpoint

--rollback DROP POLICY IF EXISTS "users_update_own" ON "users";
--rollback --> statement-breakpoint
--rollback DROP POLICY IF EXISTS "users_select_own" ON "users";
--rollback --> statement-breakpoint
--rollback ALTER TABLE "users" DISABLE ROW LEVEL SECURITY;
--rollback --> statement-breakpoint

Example 5: DATA MIGRATION

Input (0020_backfill_status.sql):

ALTER TABLE "orders" ADD COLUMN "status" varchar(20) DEFAULT 'pending';
--> statement-breakpoint
UPDATE "orders" SET "status" = 'completed' WHERE "paid_at" IS NOT NULL;
--> statement-breakpoint
ALTER TABLE "orders" ALTER COLUMN "status" SET NOT NULL;

Output (20250710092139_backfill_status.sql):

--liquibase formatted sql

--changeset migration:backfill_status splitStatements:false endDelimiter:--> statement-breakpoint

ALTER TABLE "orders" ADD COLUMN IF NOT EXISTS "status" varchar(20) DEFAULT 'pending';
--> statement-breakpoint

UPDATE "orders" SET "status" = 'completed' WHERE "paid_at" IS NOT NULL;
--> statement-breakpoint

ALTER TABLE "orders" ALTER COLUMN "status" SET NOT NULL;
--> statement-breakpoint

--rollback ALTER TABLE "orders" ALTER COLUMN "status" DROP NOT NULL;
--rollback --> statement-breakpoint
--rollback -- WARNING: Cannot reliably rollback UPDATE on orders.status
--rollback --> statement-breakpoint
--rollback ALTER TABLE "orders" DROP COLUMN IF EXISTS "status";
--rollback --> statement-breakpoint

Example 6: DROP TABLE + DROP COLUMN (destructive)

Input (0030_cleanup.sql):

DROP TABLE "legacy_logs";
--> statement-breakpoint
ALTER TABLE "users" DROP COLUMN "temp_flag";

Output (20250710092149_cleanup.sql):

--liquibase formatted sql

--changeset migration:cleanup splitStatements:false endDelimiter:--> statement-breakpoint

DROP TABLE IF EXISTS "legacy_logs";
--> statement-breakpoint

ALTER TABLE "users" DROP COLUMN IF EXISTS "temp_flag";
--> statement-breakpoint

--rollback -- WARNING: Cannot rollback DROP COLUMN for users.temp_flag
--rollback --> statement-breakpoint
--rollback -- WARNING: Cannot rollback DROP TABLE for legacy_logs
--rollback --> statement-breakpoint

Batch Conversion Checklist

When converting multiple Drizzle Kit migration files at once:

Sort input files by their numeric prefix (0001, 0002, ...)
Assign incrementing timestamps to output files (maintain original order)
Convert each file independently using the rules above
Generate master-changelog.xml with all files in timestamp order
Verify: every CREATE TABLE has IF NOT EXISTS, every FK is wrapped in DO $$ ... EXCEPTION, every CREATE TYPE is wrapped, every file has rollback

Common Drizzle Kit Patterns to Recognise

Pattern	What It Is	How to Handle
`--> statement-breakpoint`	Drizzle Kit's statement separator	Keep as-is (same delimiter)
`DO $$ BEGIN ... EXCEPTION ... END $$`	FK already wrapped	Keep as-is
`CREATE TABLE ... CONSTRAINT "x" UNIQUE(...)`	Inline unique constraint	Keep inside CREATE TABLE (no extra handling)
`ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY`	Standalone FK	Wrap in `DO $$ BEGIN ... EXCEPTION`
`CREATE TYPE ... AS ENUM`	Enum creation	Wrap in `DO $$ BEGIN ... EXCEPTION`
`ALTER TYPE ... ADD VALUE`	Enum value addition	Keep as-is (already idempotent in PG 13+)
References to `"public"."table"`	Schema-qualified name	Keep as-is
References to `"table"` (no schema)	Unqualified name	Keep as-is for the forward statement; add `"public".` in FK `REFERENCES` if missing
`ENABLE ROW LEVEL SECURITY`	RLS activation	Keep as-is
`CREATE POLICY`	RLS policy	Keep as-is

Edge Cases

Empty migration file: Skip it — don't create an empty Liquibase file.
Comments in input: Preserve SQL comments (-- comment). Don't confuse them with Liquibase directives.
Multiline statements: Keep the original formatting. Only ensure --> statement-breakpoint appears after the closing ;.
Already idempotent SQL: If input already has IF NOT EXISTS / IF EXISTS, don't duplicate it.
Multiple schemas: If the input references schemas other than public, preserve the schema qualifiers.
Drizzle Kit metadata comments: Lines like -- Custom SQL migration file can be removed or kept — they're harmless.

Other Liquibase Changelog Formats

This guide focuses on Formatted SQL because it's the most natural fit for Drizzle Kit migrations (SQL in → SQL out). However, Liquibase supports four changelog formats:

Format	Extension	Best For
SQL	`.sql`	Direct SQL migrations (what this guide produces)
XML	`.xml`	Full Liquibase feature set, cross-database portability
YAML	`.yaml`	Human-readable alternative to XML
JSON	`.json`	Programmatic generation / CI pipelines

All four formats support the same features (changesets, rollbacks, preconditions, contexts, labels). The master changelog (master-changelog.xml) can include files of any format — you can mix SQL migration files with XML/YAML/JSON changelogs in the same project.

For full details on each format, see the Liquibase Changelog documentation.

Liquibase Documentation References

Formatted SQL changelogs: https://docs.liquibase.com/concepts/changelogs/sql-format.html
Changelog formats (XML, YAML, JSON, SQL): https://docs.liquibase.com/concepts/changelogs/home.html
Rollback workflows: https://docs.liquibase.com/workflows/liquibase-community/using-rollback.html
rollbackSqlFile (Pro): https://docs.liquibase.com/concepts/changelogs/sql-format.html#rollbackSqlFile
sqlFile Change Type: https://docs.liquibase.com/change-types/sql-file.html
Changeset attributes: https://docs.liquibase.com/concepts/changelogs/sql-format.html#changeset
Preconditions: https://docs.liquibase.com/concepts/changelogs/preconditions.html
Supported databases: https://www.liquibase.org/get-started/databases

FilesExpand file tree

AI-CONVERSION-GUIDE-POSTGRESQL.md

Latest commit

History