migrate()

The migrate() function applies SQL migrations to your DuckDB database.

Signature

function migrate<TSchema extends Record<string, unknown>>(
  db: DuckDBDatabase<TSchema>,
  config: DuckDbMigrationConfig
): Promise<void>;

type DuckDbMigrationConfig = MigrationConfig | string;

interface MigrationConfig {
  migrationsFolder: string;
  migrationsTable?: string;
  migrationsSchema?: string;
}

Parameters

db

The Drizzle database instance created by drizzle().

config

Either a string path to the migrations folder, or a configuration object:

Option	Type	Default	Description
`migrationsFolder`	`string`	Required	Path to migrations directory
`migrationsTable`	`string`	`'__drizzle_migrations'`	Table name for tracking applied migrations
`migrationsSchema`	`string`	`'drizzle'`	Schema for the migrations table

DuckDbMigrationConfig is exported from @duckdbfan/drizzle-duckdb. migrationsTable and migrationsSchema cannot contain a double quote ("). migrate() throws before it creates anything when they do.

Basic Usage

import { DuckDBInstance } from '@duckdb/node-api';
import { drizzle, migrate } from '@duckdbfan/drizzle-duckdb';

const instance = await DuckDBInstance.create('./my-database.duckdb');
const connection = await instance.connect();
const db = drizzle(connection);

// Simple usage with just the folder path
await migrate(db, './drizzle/migrations');

With Configuration Object

await migrate(db, {
  migrationsFolder: './drizzle/migrations',
  migrationsTable: 'my_migrations',
  migrationsSchema: 'app',
});

Migration Files

The migrations folder must be generated by Drizzle Kit (or follow the same layout). It holds one SQL file per migration plus meta/_journal.json, which lists each migration’s tag and when timestamp. migrate() reads the journal to find the files and throws Can't find meta/_journal.json file without it:

drizzle/migrations/
  0000_initial.sql
  0001_add_users_table.sql
  0002_add_orders_table.sql
  meta/
    _journal.json

Statements inside one file are split on Drizzle Kit’s --> statement-breakpoint comments.

Example Migration File

-- 0001_add_users_table.sql
CREATE SEQUENCE IF NOT EXISTS users_id_seq;

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY DEFAULT nextval('users_id_seq'),
  email VARCHAR NOT NULL UNIQUE,
  name VARCHAR NOT NULL,
  created_at TIMESTAMPTZ DEFAULT current_timestamp
);

Using with Drizzle Kit

You can use Drizzle Kit to generate migrations:

// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/schema.ts',
  out: './drizzle/migrations',
  dialect: 'postgresql', // Use postgresql dialect
} satisfies Config;

Generate migrations:

bunx drizzle-kit generate

DuckDB Compatibility

Drizzle Kit generates Postgres SQL. Some statements may need manual adjustment for DuckDB compatibility. Common issues:

SERIAL should use sequences with nextval()

JSONB should be JSON

Some constraints syntax may differ

Migration Tracking

The migrate function creates a tracking table to record applied migrations. With the default schema and table names it runs:

CREATE SCHEMA IF NOT EXISTS "drizzle";
CREATE SEQUENCE IF NOT EXISTS "drizzle"."__drizzle_migrations_id_seq";
CREATE SEQUENCE IF NOT EXISTS "drizzle"."migrations_pk_seq";
CREATE TABLE IF NOT EXISTS "drizzle"."__drizzle_migrations" (
  id integer PRIMARY KEY default nextval('"drizzle"."__drizzle_migrations_id_seq"'),
  hash text NOT NULL,
  created_at bigint
);
CREATE UNIQUE INDEX IF NOT EXISTS "__drizzle_migrations_created_at_unique"
  ON "drizzle"."__drizzle_migrations" (created_at);

All pending migrations, and the rows that record them, run in one transaction. A migration is pending when its journal when timestamp is later than the newest created_at already recorded.

Concurrent migrate() calls in one process on the same database run one after another, including separate drizzle(path) databases on one file. Calls from other processes retry on DuckDB write-write conflicts and skip migrations the other call already applied. See Concurrent migrations.

With DuckLake as the default catalog, the tracking table is a plain table without the sequence, primary key and unique index, because DuckLake supports none of them. See DuckLake.

Error Handling

try {
  await migrate(db, './drizzle/migrations');
  console.log('Migrations applied successfully');
} catch (error) {
  console.error('Migration failed:', error);
  // The transaction was rolled back, so none of the pending migrations were applied
}

Complete Example

import { DuckDBInstance } from '@duckdb/node-api';
import { drizzle, migrate } from '@duckdbfan/drizzle-duckdb';
import * as schema from './schema';

async function main() {
  // Create or connect to database
  const instance = await DuckDBInstance.create('./data/app.duckdb');
  const connection = await instance.connect();

  // Create Drizzle instance with schema
  const db = drizzle(connection, { schema });

  // Apply migrations
  console.log('Applying migrations...');
  await migrate(db, {
    migrationsFolder: './drizzle/migrations',
  });
  console.log('Migrations complete');

  // Now use the database
  const users = await db.query.users.findMany();
  console.log('Users:', users);

  // Clean up
  connection.closeSync();
}

main().catch(console.error);