Configuration

Reference for the configuration options in Drizzle DuckDB.

drizzle() Options

The drizzle() function accepts a configuration object:

const db = await drizzle(':memory:', {
  logger: true,
  schema: mySchema,
  rejectStringArrayLiterals: false,
  pool: { size: 6, idleTimeoutMs: 60_000 },
});

pool and ducklake need the connection-string or { connection } form, because the driver creates the connections. The other options also work when you pass your own client, as in drizzle(connection, { logger: true }).

logger

Enable query logging for debugging.

Type Default Description
boolean \| Logger undefined Enable logging or provide custom logger

Usage:

// Use default logger (logs to console)
const db = drizzle(connection, { logger: true });

// Use custom logger
import { DefaultLogger } from 'drizzle-orm';

const db = drizzle(connection, {
  logger: new DefaultLogger({
    writer: {
      write(message: string) {
        // Custom logging logic
        myLogger.debug(message);
      },
    },
  }),
});

Example output for db.execute(sql`SELECT * FROM users WHERE id = ${1}`):

Query: SELECT * FROM users WHERE id = $1 -- params: [1]

schema

Schema definition for relational queries.

Type Default Description
Record<string, unknown> undefined Schema object with tables and relations

Usage:

// schema.ts
import { pgTable, integer, text } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  name: text('name'),
});

export const posts = pgTable('posts', {
  id: integer('id').primaryKey(),
  userId: integer('user_id'),
  title: text('title'),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

// db.ts
import * as schema from './schema';

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

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

casing

Map TypeScript keys to column names, as in other Drizzle drivers.

Type Default Description
'snake_case' \| 'camelCase' undefined Derive column names from the TypeScript key 
const users = pgTable('users', { id: integer(), userName: text() });
const db = drizzle(connection, { casing: 'snake_case' });
// db.select().from(users) reads "user_name" into userName

DrizzleConfig also has a cache option. The DuckDB driver does not apply it.

Array SQL Handling

Postgres array operators (@>, <@, &&) are sent unchanged. DuckDB supports them on LIST and fixed-size ARRAY values with the same results as array_has_all and array_has_any.

Postgres first-dimension array bounds calls are rewritten via AST transformation anywhere in a statement, including casts, ORDER BY, GROUP BY and UPDATE. This is always enabled and cannot be disabled:

Postgres Function Rewritten To
array_lower(a, 1) CASE WHEN array_length(a) > 0 THEN 1 ELSE NULL END
array_upper(a, 1) NULLIF(array_length(a), 0)

Expressions containing function calls bind the length in a one-element list before NULLIF, because DuckDB can evaluate a NULLIF argument twice. This preserves one evaluation per row for volatile functions and keeps each literal and parameter once in the rewritten SQL.

Example:

// Postgres-style code works as written
const results = await db
  .select()
  .from(products)
  .where(arrayContains(products.tags, ['sale']));
// Generated: WHERE "products"."tags" @> $1

prepareCache

Enable a per-connection prepared statement cache.

Type Default Description
boolean / number / { size?: number } false Cache prepared statements. Numbers or size set the LRU size.

Usage:

// Enable with default size (32)
const db = drizzle(connection, { prepareCache: true });

// Custom cache size
const db = drizzle(connection, { prepareCache: { size: 16 } });

SQL with several statements, or with only comments, cannot be prepared. It runs without the cache, the same as with prepareCache off. This covers migration files without --> statement-breakpoint markers.

Use getPreparedStatementCacheStats(connection) to inspect hit, miss and eviction counters on a node-api connection without creating a cache.

decimalMode

'number' is the default for raw DECIMAL results. Set 'string' to preserve exact values in raw results, nested native values, streaming and columnar reads, and numeric columns inside relational results. numeric() builder columns keep exact strings in either mode. Explicit Number decoders still produce numbers.

const db = drizzle(connection, { decimalMode: 'string' });

qualifyRawJoinColumns

Defaults to false. Builder fields retain qualified names, and raw SQL follows DuckDB’s ownership rules. Set true only to restore the previous heuristic for unqualified join references. Explicit qualifiers avoid assumptions about which source owns a column.

rejectStringArrayLiterals

Throw an error when a parameter without column information looks like a Postgres-style array literal ('{...}').

Type Default Description
boolean false Throw instead of converting and warning on '{...}'

Values bound to a column, such as inserts or eq(column, value), are stored and compared as written and never checked. The check covers plain sql template parameters, sql.param(...) values and bare sql.placeholder(...) values. By default such a parameter is converted to a list when its contents parse as a JSON array after the braces become brackets ('{1,2}' becomes [1, 2]), and the driver logs a warning once per session.

Usage:

// Default: converts '{1,2}' to [1, 2] and warns through the logger once per session
const db = drizzle(connection, { rejectStringArrayLiterals: false });
await db.execute(sql`SELECT * FROM t WHERE scores = ${'{1,2}'}`);
// Logged: [duckdb] Received a stringified Postgres-style array literal. ...

// Strict mode: throws error
const db = drizzle(connection, { rejectStringArrayLiterals: true });
await db.execute(sql`SELECT * FROM t WHERE scores = ${'{1,2}'}`);
// Error: Stringified array literals are not supported. Use duckDbList()/duckDbArray() or pass native arrays.

The full warning text is [duckdb] Received a stringified Postgres-style array literal. Use duckDbList()/duckDbArray() or pass native arrays instead. You can also set rejectStringArrayLiterals=true to throw. It goes to the configured logger, so you only see it when logger is set, unless you pass arrayLiteralWarning.

arrayLiteralWarning

Handle the first detected Postgres-style array literal warning yourself instead of sending it to the logger.

Type Default Description
(query: string) => void logger Called once per session with the SQL text when a '{...}' parameter without column information is found

Usage:

const db = drizzle(connection, {
  arrayLiteralWarning: (query) => {
    console.warn('Array literal parameter detected', query);
  },
});

pool

Control connection pooling for the connection-string and { connection } forms. DuckDB runs one query per connection, so pooling enables parallelism. pool is ignored when you pass a connection or pool instance.

Type Default Description
false 4 Disable pooling (single connection)
{ size?, acquireTimeout?, maxWaitingRequests?, maxLifetimeMs?, idleTimeoutMs? } 4 Pool size plus timeout and recycling options
'pulse', 'standard', 'jumbo', 'mega', 'giga', 'local', 'memory' 4 Preset sizes (MotherDuck/local defaults)

Usage:

// Auto-pooling (default size 4)
const db = await drizzle('md:');

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

// MotherDuck preset
const db = await drizzle('md:', { pool: 'jumbo' }); // 8 connections

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

// Timeouts, queue limits, and recycling
const db = await drizzle('md:', {
  pool: {
    size: 8,
    acquireTimeout: 20_000,
    maxWaitingRequests: 200,
    maxLifetimeMs: 10 * 60_000,
    idleTimeoutMs: 60_000,
  },
});

Pool object options:

Option Default Values
size 4 Number of connections
acquireTimeout 30_000 Queue wait in milliseconds, excluding connection creation and setup. 0 or Infinity disables the queue deadline. Negative and NaN throw
maxWaitingRequests 100 Queued acquires before new ones throw. A non-negative integer or Infinity. 0 allows no waiting
maxLifetimeMs none Recycle connections after this age
idleTimeoutMs none Recycle connections after this idle period

Build the pool manually when you need the setup hook or want to share one pool across several drizzle() instances:

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

const instance = await DuckDBInstance.create('md:', {
  motherduck_token: process.env.MOTHERDUCK_TOKEN,
});
const pool = createDuckDBConnectionPool(instance, {
  size: 8,
  acquireTimeout: 20_000,
  maxWaitingRequests: 200,
  maxLifetimeMs: 10 * 60_000,
  idleTimeoutMs: 60_000,
});
const db = drizzle(pool);

ducklake

Attach a DuckLake catalog when creating a connection with drizzle().

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

Options:

  • catalog string. Required. The catalog value after the ducklake: prefix.
  • alias string. Optional. Defaults to ducklake.
  • use boolean. Optional. Defaults to true.
  • install boolean. Optional. Defaults to false.
  • load boolean. Optional. Defaults to false.
  • attachOptions object with fields createIfNotExists, dataInliningRowLimit, dataPath, encrypted, metaParameters, metadataCatalog, overrideDataPath, and readOnly. dataInliningRowLimit must be a non-negative integer. metaParameters is a Record<string, string> and each entry becomes META_<KEY> 'value', for example { type: 'duckdb' } emits META_TYPE 'duckdb'. Keys must match /^[A-Za-z_][A-Za-z0-9_]*$/. The deprecated metaParameterName field throws, because DuckLake has no META_PARAMETER_NAME option.

Connection setup throws after the attach when the alias already names a different database, such as a main database file named ducklake.duckdb or another DuckLake catalog. Set alias to a different name in that case. Pooled connections are set up on first use, so with a pool these errors, and invalid attachOptions, come from the first query.

When the DuckLake catalog is local and pool is not set, drizzle() uses a pool of size 1. A catalog counts as local when it is :memory:, starts with duckdb: or file:, or has no scheme and looks like a file path, such as meta.db or ./lake/meta. Catalogs with another scheme, such as md:, s3:, postgres: or sqlite:, do not. A bare name such as my_lake does not either, because DuckLake reads it as a secret name. A larger pool works in one process because all connections share the attached catalog and the driver sets up one connection at a time. With a local catalog and a pool size above 1, drizzle() logs a [ducklake] warning. See DuckLake pooling.

migrate() Options

The migrate() function accepts either a string path or configuration object:

// Simple: just the path
await migrate(db, './drizzle');

// Full configuration
await migrate(db, {
  migrationsFolder: './drizzle',
  migrationsTable: '__drizzle_migrations',
  migrationsSchema: 'drizzle',
});

migrationsFolder

Path to the folder containing SQL migration files.

Type Default Description
string Required Path to migrations directory

migrationsTable

Name of the table used to track applied migrations.

Type Default Description
string '__drizzle_migrations' Migration tracking table name

migrationsSchema

Schema where the migrations tracking table is created.

Type Default Description
string 'drizzle' Schema for migrations table

migrationsTable and migrationsSchema cannot contain a double quote ("). migrate() throws an error such as Invalid migrationsTable "...": migration journal names cannot contain double quotes ("). before it creates anything.

introspect() Options

Options for schema introspection:

const result = await introspect(db, {
  database: 'my_database',
  schemas: ['main', 'analytics'],
  includeViews: true,
  useCustomTimeTypes: true,
  mapJsonAsDuckDbJson: true,
  importBasePath: '@duckdbfan/drizzle-duckdb/helpers',
});

database

Specific database to introspect.

Type Default Description
string Current database Target database name

allDatabases

Introspect all attached databases (ignored if database is set).

Type Default Description
boolean false Include all attached databases

schemas

Specific schemas to introspect.

Type Default Description
string[] All non-system schemas Schemas to include

includeViews

Include views in the output.

Type Default Description
boolean false Generate schema for views

useCustomTimeTypes

Use DuckDB-specific timestamp types.

Type Default Description
boolean true Use duckDbTimestamp etc.

When true, generates:

createdAt: duckDbTimestamp('created_at'),

When false, generates:

createdAt: timestamp('created_at'),

mapJsonAsDuckDbJson

Map JSON columns to duckDbJson.

Type Default Description
boolean true Use duckDbJson for JSON columns

importBasePath

Base path for local type imports.

Type Default Description
string '@duckdbfan/drizzle-duckdb/helpers' Import path for types

Environment Variables

Common environment variables used with Drizzle DuckDB:

MOTHERDUCK_TOKEN

Authentication token for MotherDuck connections.

MOTHERDUCK_TOKEN=your_token_here

const instance = await DuckDBInstance.create('md:', {
  motherduck_token: process.env.MOTHERDUCK_TOKEN,
});

DRIZZLE_DUCKDB_FORCE_LITERAL_TIMESTAMPS

Any non-empty value other than 0 makes duckDbTimestamp columns in bindMode: 'auto' send SQL literals instead of native timestamp parameters, as they do on Bun. Both paths store the same value. See Native Value Binding.

DRIZZLE_DUCKDB_FORCE_LITERAL_TIMESTAMPS=1

TypeScript Configuration

Recommended tsconfig.json settings:

{
  "compilerOptions": {
    "module": "ESNext",
    "moduleResolution": "bundler",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true
  }
}

See Also

Released under the Apache 2.0 License. Copyright 2025-present.