Database Connection

Learn how to connect to DuckDB databases in different scenarios.

Quick Start (Recommended)

The simplest way to connect uses a connection string with automatic pooling:

import { drizzle } from '@duckdbfan/drizzle-duckdb';

// In-memory with auto-pooling (4 connections)
const db = await drizzle(':memory:');

// Local file with auto-pooling
const db = await drizzle('./my-database.duckdb');

// MotherDuck cloud with auto-pooling
const db = await drizzle({
  connection: {
    path: 'md:',
    options: { motherduck_token: process.env.MOTHERDUCK_TOKEN },
  },
});

// DuckLake catalog attach
const db = await drizzle(':memory:', {
  ducklake: {
    catalog: './ducklake.duckdb',
    attachOptions: { dataPath: './ducklake-data' },
  },
});

This creates a connection pool, which matters for MotherDuck throughput (see Connection Pooling).

In-Memory Database

Use an in-memory database for tests and temporary data processing:

import { drizzle } from '@duckdbfan/drizzle-duckdb';

const db = await drizzle(':memory:');

Data is lost when the database closes.

Local File

Persist your data to disk:

const db = await drizzle('./my-database.duckdb');

The file is created if it doesn’t exist.

Calls on the same path share one DuckDB instance in a process, so a migration script, the app and tests all see the same data. Opening the path again with different instance options throws. :memory: is never shared. If you also open the file yourself, use DuckDBInstance.fromCache(path) so your connection joins the same instance. See One instance per file per process.

MotherDuck Cloud

Connect to MotherDuck for cloud-hosted DuckDB:

const db = await drizzle({
  connection: {
    path: 'md:',
    options: { motherduck_token: process.env.MOTHERDUCK_TOKEN },
  },
});

// Or connect to a specific database
const db = await drizzle({
  connection: {
    path: 'md:my_database',
    options: { motherduck_token: process.env.MOTHERDUCK_TOKEN },
  },
});

See the MotherDuck guide for the auto pooling example and db.close() cleanup.

DuckLake

Attach a DuckLake catalog during connection setup:

const db = await drizzle(':memory:', {
  ducklake: {
    catalog: './ducklake.duckdb',
    attachOptions: {
      dataPath: './ducklake-data',
      createIfNotExists: true,
    },
  },
});

For MotherDuck, use the DuckLake metadata catalog created by CREATE DATABASE ... TYPE DUCKLAKE:

const db = await drizzle({
  connection: {
    path: 'md:',
    options: { motherduck_token: process.env.MOTHERDUCK_TOKEN },
  },
  ducklake: {
    catalog: 'md:__ducklake_metadata_my_lake',
  },
});

A local catalog file uses a pool of size 1 unless you set pool. See the DuckLake guide for pooling and the generated ATTACH SQL.

pg_duckdb

For a Postgres server with the pg_duckdb extension, pass a pg client or pool:

import pg from 'pg';
import { drizzle } from '@duckdbfan/drizzle-duckdb';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(pool);

The driver wraps a pg.Pool with createPgDuckConnectionPool(), so each transaction runs on one pooled client, and await db.close() ends the pool. A connected pg.Client works too. The ducklake option cannot be used with pg_duckdb clients.

With Logging

Enable query logging for debugging:

import { DefaultLogger } from 'drizzle-orm';

const db = drizzle(connection, {
  logger: new DefaultLogger(),
});

Or simply:

const db = drizzle(connection, { logger: true });

With Schema

Pass your schema for relational queries:

import * as schema from './schema';

const db = drizzle(connection, { schema });

// Now relational queries work
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

Connection Patterns

Singleton (Recommended for Long-Running Apps)

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

let instance: DuckDBInstance | null = null;
let connection: DuckDBConnection | null = null;

export async function getDb(): Promise<DuckDBDatabase<typeof schema>> {
  if (!instance) {
    instance = await DuckDBInstance.create('./app.duckdb');
  }
  if (!connection) {
    connection = await instance.connect();
  }
  return drizzle(connection, { schema });
}

Cleanup Pattern (Serverless/Short-Lived)

export async function withDb<T>(
  callback: (db: DuckDBDatabase) => Promise<T>
): Promise<T> {
  const instance = await DuckDBInstance.create(':memory:');
  const connection = await instance.connect();

  try {
    const db = drizzle(connection);
    return await callback(db);
  } finally {
    connection.closeSync();
    instance.closeSync();
  }
}

// Usage
const users = await withDb(async (db) => {
  return db.select().from(usersTable);
});

Connection Pooling

DuckDB/MotherDuck runs one query per connection. Without pooling, concurrent requests serialize and cause slow response times. The async drizzle() entrypoints create a pool (default size: 4) when given a connection string. pool applies only to the connection-string and { connection } forms. It is ignored when you pass a connection or pool instance.

Pool Size Configuration

// Default: 4 connections
const db = await drizzle(':memory:');

// Custom pool size
const db = await drizzle('md:', { pool: { size: 8 } });

// Use a preset for MotherDuck instance types
const db = await drizzle('md:', { pool: 'jumbo' }); // 8 connections
const db = await drizzle('md:', { pool: 'giga' }); // 16 connections

// Disable pooling (single connection)
const db = await drizzle('md:', { pool: false });

The pool option on drizzle() accepts a preset name or an object with size plus the timeout and recycling options listed under Advanced Pool Options. Create the pool manually when you need a setup hook or want to share one pool across several drizzle() instances.

Pool Presets for MotherDuck

Preset	Size	Use Case
'pulse'	4	Auto-scaling, ad-hoc analytics
'standard'	6	Balanced ETL/ELT workloads
'jumbo'	8	Complex queries, high-volume
'mega'	12	Large-scale transformations
'giga'	16	Maximum parallelism
'local'	8	Local DuckDB file
'memory'	4	In-memory testing

Manual Pool Creation (Advanced)

For more control, create the pool manually:

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

const instance = await DuckDBInstance.create('./app.duckdb');
const pool = createDuckDBConnectionPool(instance, { size: 4 });
const db = drizzle(pool);

Advanced Pool Options

Auto-created pools support the same tuning options through drizzle('path', { pool: { ... } }), and createDuckDBConnectionPool exposes the same controls for manual pools:

• acquireTimeout (ms, default 30_000): fail if a connection isn’t available in time. 0 or Infinity waits without a timeout. Values above the setTimeout limit of about 24.8 days are capped to it. Negative values and NaN throw
• maxWaitingRequests (default 100): cap queued acquires. Further acquires throw when the queue is full. It must be a non-negative integer or Infinity. 0 allows no waiting and Infinity removes the cap
• maxLifetimeMs: recycle connections after this age
• idleTimeoutMs: recycle idle connections after this idle period

const pool = createDuckDBConnectionPool(instance, {
  size: 8,
  acquireTimeout: 20_000,
  maxWaitingRequests: 200,
  maxLifetimeMs: 10 * 60_000,
  idleTimeoutMs: 60_000,
});

Transactions pin a single pooled connection for the entire callback. Other queries continue to use the pool.

Multiple Connections Without Pooling

DuckDB supports multiple independent connections:

const instance = await DuckDBInstance.create('./app.duckdb');

const conn1 = await instance.connect();
const conn2 = await instance.connect();

const db1 = drizzle({ client: conn1 });
const db2 = drizzle({ client: conn2 });

Configuration Options

const db = await drizzle(':memory:', {
  // Enable query logging (or pass a custom logger such as new DefaultLogger())
  logger: true,

  // Schema for relational queries
  schema: mySchema,

  // Pool configuration (size/preset plus timeout and recycling options)
  pool: { size: 8, idleTimeoutMs: 60_000 },

  // Throw on Postgres-style array literals (default: false)
  rejectStringArrayLiterals: false,

  // Map camelCase keys to snake_case column names
  casing: 'snake_case',
});

Note: Postgres array operators (@>, <@, &&) run natively in DuckDB and are sent unchanged.

See Configuration for all options.

Closing Connections

When using connection strings, call close() to clean up:

const db = await drizzle('./app.duckdb');

try {
  // Use db...
} finally {
  await db.close();
}

close() interrupts queries that are still running, so they reject instead of staying pending. See close().

For manual connections, close them explicitly:

const instance = await DuckDBInstance.create('./app.duckdb');
const connection = await instance.connect();
const db = drizzle({ client: connection });

try {
  // Use db...
} finally {
  connection.closeSync();
  instance.closeSync();
}

Error Handling

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

  // Use database...
} catch (error) {
  if (error.message.includes('Permission denied')) {
    console.error('Cannot write to database file');
  } else if (error.message.includes('Could not open')) {
    console.error('Database file not found or corrupted');
  } else {
    throw error;
  }
}

See Also

• drizzle(): API reference
• Configuration: all options
• MotherDuck: cloud connection