Migrations

Drizzle DuckDB supports running SQL migration files against your DuckDB database using the migrate function.

Basic Usage

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

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

await migrate(db, { migrationsFolder: './drizzle' });

connection.closeSync();

Migration Folder Structure

Migrations should be SQL files in a folder, typically generated by Drizzle Kit:

drizzle/
├── 0000_init.sql
├── 0001_add_users.sql
├── 0002_add_posts.sql
└── meta/
    └── _journal.json

Each migration file contains raw SQL statements:

-- 0000_init.sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  user_id INTEGER REFERENCES users(id)
);

Configuration Options

migrationsFolder

Path to the folder containing migration files:

await migrate(db, {
  migrationsFolder: './drizzle',
});

Or pass just the path string:

await migrate(db, './drizzle');

migrationsSchema

Schema where the migrations table is created (default: 'drizzle'):

await migrate(db, {
  migrationsFolder: './drizzle',
  migrationsSchema: 'my_schema',
});

migrationsTable

Name of the table tracking applied migrations (default: '__drizzle_migrations'):

await migrate(db, {
  migrationsFolder: './drizzle',
  migrationsTable: 'schema_migrations',
});

How It Works

1. Creates schema - The migrations schema is created if it doesn’t exist
2. Creates tracking table - A table stores which migrations have been applied:

   CREATE TABLE drizzle.__drizzle_migrations (
     id INTEGER PRIMARY KEY,
     hash TEXT NOT NULL,
     created_at BIGINT
   )
   

3. Creates sequence - A sequence generates migration IDs
4. Runs migrations - Each new migration runs in a transaction
5. Records completion - Successful migrations are recorded in the tracking table

Migration Tracking

The driver tracks migrations using a hash of each migration file. This means:

• Migrations only run once
• The order is determined by the created_at timestamp in the journal
• You cannot modify already-applied migrations (the hash won’t match)

To check which migrations have been applied:

import { sql } from 'drizzle-orm';

const applied = await db.execute(sql`
  SELECT hash, created_at
  FROM drizzle.__drizzle_migrations
  ORDER BY created_at
`);
console.log(applied);

Using with Drizzle Kit

While Drizzle Kit doesn’t have native DuckDB support, you can generate migrations using the Postgres dialect since DuckDB’s SQL is largely compatible:

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

export default defineConfig({
  schema: './src/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
});

Generate migrations:

bunx drizzle-kit generate

Then apply them with this package:

await migrate(db, './drizzle');

Note

Some generated SQL may need manual adjustment for DuckDB compatibility. Check the generated files before applying.

Full Example

import { DuckDBInstance } from '@duckdb/node-api';
import { drizzle, migrate } from '@leonardovida-md/drizzle-neo-duckdb';
import { sql } from 'drizzle-orm';

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

  try {
    console.log('Running migrations...');
    await migrate(db, {
      migrationsFolder: './drizzle',
      migrationsSchema: 'drizzle',
      migrationsTable: '__drizzle_migrations',
    });
    console.log('Migrations complete!');

    // Verify
    const tables = await db.execute(sql`
      SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'main'
    `);
    console.log(
      'Tables:',
      tables.map((t) => t.table_name)
    );
  } finally {
    connection.closeSync();
  }
}

runMigrations().catch(console.error);

Troubleshooting

“Migration already applied” errors

If you need to re-run a migration (e.g., during development), you can clear the tracking table:

await db.execute(sql`DELETE FROM drizzle.__drizzle_migrations`);
await migrate(db, './drizzle');

Sequence errors

If you see sequence-related errors, ensure the sequence exists:

await db.execute(sql`
  CREATE SEQUENCE IF NOT EXISTS drizzle.__drizzle_migrations_id_seq
`);

Schema doesn’t exist

Make sure the migrations schema is created:

await db.execute(sql`CREATE SCHEMA IF NOT EXISTS drizzle`);
await migrate(db, './drizzle');