drizzle-kit migrate lets you apply SQL migrations generated by drizzle-kit generate. It’s designed to cover code first(option 3) approach of managing Drizzle migrations.

How it works under the hood?

Drizzle Kit migrate command triggers a sequence of events:

1. Reads through migration folder and read all .sql migration files
2. Connects to the database and fetches entries from drizzle migrations log table
3. Based on previously applied migrations it will decide which new migrations to run
4. Runs SQL migrations and logs applied migrations to drizzle migrations table

  ├ 📂 drizzle       
  │ ├ 📂 20242409125510_premium_mister_fear
  │ └ 📂 20242409135510_delicate_professor_xavie
  └ …

┌───────────────────────┐                  
│ $ drizzle-kit migrate │                  
└─┬─────────────────────┘                  
  │                                                         ┌──────────────────────────┐                                         
  └ 1. reads migration.sql files in migrations folder       │                          │
    2. fetch migration history from database -------------> │                          │
  ┌ 3. pick previously unapplied migrations <-------------- │         DATABASE         │
  └ 4. apply new migration to the database ---------------> │                          │
                                                            │                          │
                                                            └──────────────────────────┘
[✓] done!        

drizzle-kit migrate command requires you to specify both dialect and database connection credentials, you can provide them either via drizzle.config.ts config file or via CLI options

With config file

As CLI options

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema.ts",
  dbCredentials: {
    url: "postgresql://user:password@host:port/dbname"
  },
});

npx drizzle-kit migrate

npx drizzle-kit migrate --dialect=postgresql --url=postgresql://user:password@host:port/dbname

Applied migrations log in the database

Upon running migrations Drizzle Kit will persist records about successfully applied migrations in your database. It will store them in migrations log table named __drizzle_migrations.

You can customise both table and schema(PostgreSQL only) of that table via drizzle config file:

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema.ts",
  dbCredentials: {
    url: "postgresql://user:password@host:port/dbname"
  },
  migrations: {
    table: 'my-migrations-table', // `__drizzle_migrations` by default
    schema: 'public', // used in PostgreSQL only, `drizzle` by default
  },
});

Ignore conflicts

IMPORTANT

--ignore-conflicts available starting from drizzle-orm@1.0.0-beta.16

In case you need migrate command to skip commutativity checks and bypass it, you can use --ignore-conflicts. If there is a situation you want to use it, then there is a big chance that drizzle-kit didn’t check migrations right and it’s a bug. Please report us your case, so we can fix it

drizzle-kit migrate --ignore-conflicts

Multiple configuration files in one project

You can have multiple config files in the project, it’s very useful when you have multiple database stages or multiple databases on the same project:

npx drizzle-kit migrate --config=drizzle-dev.config.ts
npx drizzle-kit migrate --config=drizzle-prod.config.ts

yarn drizzle-kit migrate --config=drizzle-dev.config.ts
yarn drizzle-kit migrate --config=drizzle-prod.config.ts

pnpm drizzle-kit migrate --config=drizzle-dev.config.ts
pnpm drizzle-kit migrate --config=drizzle-prod.config.ts

bunx drizzle-kit migrate --config=drizzle-dev.config.ts
bunx drizzle-kit migrate --config=drizzle-prod.config.ts

📦 <project root>
 ├ 📂 drizzle
 ├ 📂 src
 ├ 📜 .env
 ├ 📜 drizzle-dev.config.ts
 ├ 📜 drizzle-prod.config.ts
 ├ 📜 package.json
 └ 📜 tsconfig.json

Extended example

Let’s generate SQL migration and apply it to our database using drizzle-kit generate and drizzle-kit migrate commands

📦 <project root>
 ├ 📂 drizzle
 ├ 📂 src
 │ ├ 📜 schema.ts
 │ └ 📜 index.ts
 ├ 📜 drizzle.config.ts
 └ …

drizzle.config.ts

src/schema.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema.ts",
  dbCredentials: {
    url: "postgresql://user:password@host:port/dbname"
  },
  migrations: {
    table: 'journal', 
    schema: 'drizzle', 
  },
});

import * as p from "drizzle-orm/pg-core";

export const users = p.pgTable("users", {
  id: p.serial().primaryKey(),
  name: p.text(),
})

Now let’s run

npx drizzle-kit generate --name=init

it will generate

📦 <project root>
 ├ …
 ├ 📂 migrations
 │ ├ 📂 20242409125510_init
 └ …

-- ./drizzle/0000_init.sql

CREATE TABLE "users"(
  id serial primary key,
  name text
)

Now let’s run

npx drizzle-kit migrate

and our SQL migration is now successfully applied to the database ✅