drizzle-kit migrate

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.

  ├ 📂 drizzle       
  │ ├ 📂 _meta
  │ ├ 📜 0000_premium_mister_fear.sql
  │ └ 📜 0001_delicate_professor_xavie.sql
  └ …

┌───────────────────────┐                  
│ $ 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

// 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
  },
});

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

bun drizzle-kit migrate --config=drizzle-dev.config.ts
bun 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
 └ …

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
 │ ├ 📂 _meta
 │ └ 📜 0000_init.sql 
 └ …

-- ./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 ✅