introspect()

The introspect() function generates Drizzle schema TypeScript code from an existing DuckDB database.

Signature

function introspect(
  db: DuckDBDatabase,
  opts?: IntrospectOptions
): Promise<IntrospectResult>;

Options

interface IntrospectOptions {
  // Database/catalog to introspect (default: current database)
  database?: string;

  // Introspect all attached databases instead of just current one
  allDatabases?: boolean;

  // Specific schemas to introspect (default: all non-system schemas)
  schemas?: string[];

  // Include views in output (default: false)
  includeViews?: boolean;

  // Use DuckDB-specific timestamp types (default: true)
  useCustomTimeTypes?: boolean;

  // Map JSON columns to duckDbJson (default: true)
  mapJsonAsDuckDbJson?: boolean;

  // Base import path for local types (default: '@duckdbfan/drizzle-duckdb/helpers')
  importBasePath?: string;
}

Return Value

interface IntrospectResult {
  files: {
    // Generated TypeScript schema code
    schemaTs: string;

    // Metadata about introspected tables
    metaJson: IntrospectedTable[];

    /** @deprecated Never populated. Will be removed in the next major version. */
    relationsTs?: string;
  };
}

interface IntrospectedTable {
  // Database (catalog) that owns the table
  database?: string;
  schema: string;
  name: string;
  kind: 'table' | 'view';
  columns: IntrospectedColumn[];
  constraints: IntrospectedConstraint[];
  indexes: DuckDbIndexRow[];
}

Basic Usage

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

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

// Introspect the database
const result = await introspect(db);

// Write the generated schema
writeFileSync('./src/schema.ts', result.files.schemaTs);

console.log('Schema generated successfully');

With Options

const result = await introspect(db, {
  // Only introspect specific schemas
  schemas: ['main', 'analytics'],

  // Include views
  includeViews: true,

  // Use standard Postgres timestamp types instead of DuckDB-specific
  useCustomTimeTypes: false,
});

MotherDuck Workspaces

When connected to MotherDuck with multiple attached databases:

// Only introspect current database (default behavior)
const result = await introspect(db);

// Introspect a specific database
const result = await introspect(db, {
  database: 'my_share',
});

// Introspect all attached databases
const result = await introspect(db, {
  allDatabases: true,
});

allDatabases skips the connection-local temp catalog and DuckDB’s system catalog.

Limitation: Tables Outside the Current Database

The generated schema does not encode the database (catalog). A table in pgSchema("main") is queried as "main"."orders", which DuckDB resolves against the connection’s current database. Tables that come from any other database, through database or allDatabases, work only after the connection runs USE <database>. The generated file starts with a comment that names those databases, and each affected table has a /* database: "..." */ comment above it. Tables from different databases that share a schema name also share one pgSchema() object. Introspect each database into its own file when one connection needs tables from several databases.

Generated Schema Example

For a database with this table:

CREATE TABLE main.users (
  id INTEGER PRIMARY KEY,
  email VARCHAR NOT NULL UNIQUE,
  name VARCHAR,
  tags VARCHAR[],
  metadata JSON,
  created_at TIMESTAMPTZ
);

The generated schema is (verbatim duckdb-introspect output, before any formatter runs):

import { integer, pgSchema, primaryKey, unique, varchar } from 'drizzle-orm/pg-core';
import { duckDbJson, duckDbList, duckDbTimestamp } from '@duckdbfan/drizzle-duckdb/helpers';

export const mainSchema = pgSchema("main");

export const users = mainSchema.table("users", {
  id: integer("id").notNull(),
  email: varchar("email").notNull(),
  name: varchar("name"),
  tags: duckDbList("tags", "VARCHAR"),
  metadata: duckDbJson("metadata"),
  createdAt: duckDbTimestamp("created_at", { withTimezone: true }),
}, (t) => ({
  usersIdPkey: primaryKey({ columns: [t.id], name: "users_id_pkey" }),
  usersEmailKey: unique("users_email_key").on(t.email),
}));

Type Mapping

DuckDB Type	Generated Drizzle Type
`TINYINT`, `SMALLINT`, `INTEGER`	`integer()`
`UTINYINT`, `USMALLINT`, `UINTEGER`	`integer()`
`BIGINT`	`bigint({ mode: 'number' })`
`UBIGINT`, `HUGEINT`, `UHUGEINT`	`bigint({ mode: 'bigint' })`
`VARCHAR` (also `TEXT`)	`varchar()`
`BOOLEAN`	`boolean()`
`DOUBLE`	`doublePrecision()`
`FLOAT`, `REAL`	`real()`
`DECIMAL(p,s)`	`numeric({ precision: p, scale: s })`
`UUID`	`uuid()`
`TIMESTAMP`	`duckDbTimestamp()`
`TIMESTAMPTZ`	`duckDbTimestamp({ withTimezone: true })`
`DATE`	`duckDbDate()`
`TIME`	`duckDbTime()`
`JSON`	`duckDbJson()`
`VARCHAR[]`	`duckDbList('VARCHAR')`
`INTEGER[3]`	`duckDbArray('INTEGER', 3)`
`STRUCT(...)`	`duckDbStruct({...})`
`MAP(VARCHAR, INTEGER)`	`duckDbMap('INTEGER')`
`MAP(INTEGER, VARCHAR)`	`duckDbMap('VARCHAR', { keyType: 'INTEGER' })`
`BLOB`	`duckDbBlob()`
`INET`	`duckDbInet()`
`INTERVAL`	`duckDbInterval()`
Anything else	`text()` with an `unsupported DuckDB type` comment

With useCustomTimeTypes: false, timestamp, date and time columns use the pg-core builders, and timestamp columns with a current_timestamp default get .defaultNow(). With the DuckDB helpers they get .default(sql`current_timestamp`). Other non-literal defaults, such as gen_random_uuid() or current_date, become .default(sql`...`). Generated columns become .generatedAlwaysAs(sql`...`). With includeViews, views become schema.view(name, { ...columns }).existing(). CHECK constraints and CREATE INDEX indexes are listed as comments above their table instead of Drizzle config. See the Introspection guide for the full mapping.

CLI Usage

A CLI tool is also available for quick introspection:

# Introspect and output to file
bunx duckdb-introspect --url ./my-database.duckdb --out ./src/schema.ts

# With options (--schema takes a comma-separated list)
bunx duckdb-introspect \
  --url ./my-database.duckdb \
  --schema main,analytics \
  --include-views \
  --out ./src/schema.ts

See CLI options for every flag and the exit codes.

Complete Example

import { DuckDBInstance } from '@duckdb/node-api';
import { drizzle, introspect } from '@duckdbfan/drizzle-duckdb';
import { writeFileSync, mkdirSync } from 'fs';
import { dirname } from 'path';

async function generateSchema() {
  const instance = await DuckDBInstance.create('./data/app.duckdb');
  const connection = await instance.connect();
  const db = drizzle(connection);

  try {
    const result = await introspect(db, {
      schemas: ['main'],
      includeViews: true,
      useCustomTimeTypes: true,
      mapJsonAsDuckDbJson: true,
    });

    // Ensure output directory exists
    const outputPath = './src/db/schema.ts';
    mkdirSync(dirname(outputPath), { recursive: true });

    // Write schema file
    writeFileSync(outputPath, result.files.schemaTs);

    // Optionally write metadata for tooling
    writeFileSync(
      './src/db/schema-meta.json',
      JSON.stringify(result.files.metaJson, null, 2)
    );

    console.log(`Schema written to ${outputPath}`);
    console.log(`Found ${result.files.metaJson.length} tables/views`);
  } finally {
    connection.closeSync();
  }
}

generateSchema().catch(console.error);