TypeScript + DuckDB

Generate asynchronous TypeScript code for DuckDB databases using the @duckdb/node-api driver (DuckDB Node.js Neo). Includes support for DuckDB’s advanced types like structs, lists, and maps.

Overview

Property	Value
Generator	typescript/duckdb (or typescript/duckdb/node-api)
Runtime	Node.js
API Style	Asynchronous (async/await)
Driver	@duckdb/node-api

Installation

# Install SQG (choose one)
pnpm add -D @sqg/sqg        # pnpm
npm install -D @sqg/sqg     # npm
yarn add -D @sqg/sqg        # yarn

# Install runtime dependency
pnpm add @duckdb/node-api   # or: npm install / yarn add

Example Configuration

sqg.yaml

version: 1
name: my-app

sql:
  - files:
      - queries.sql
    gen:
      - generator: typescript/duckdb
        output: ./src/generated/
        config:
          migrations: true  # generates applyMigrations() function

Quick Start

1. Initialize a project

   sqg init --generator typescript/duckdb

2. Write your SQL

   DuckDB Structs and Arrays

   Example with DuckDB complex types including structs and arrays

   Try in Playground

   -- MIGRATE 1
   CREATE TABLE users (
     id INTEGER PRIMARY KEY,
     name VARCHAR NOT NULL,
     email VARCHAR UNIQUE,
     metadata STRUCT(role VARCHAR, active BOOLEAN),
     tags VARCHAR[],
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   
   -- TESTDATA seed
   INSERT INTO users (id, name, email, metadata, tags)
   VALUES (1, 'Alice', '[email protected]',
           {'role': 'admin', 'active': true},
           ['developer', 'lead']);
   
   -- QUERY all_users
   SELECT * FROM users ORDER BY created_at DESC;
   
   -- QUERY get_user :one
   @set id = 1
   SELECT * FROM users WHERE id = ${id};
   
   -- QUERY get_user_tags :one :pluck
   @set id = 1
   SELECT tags FROM users WHERE id = ${id};
   
   -- EXEC create_user
   @set id = 2
   @set name = 'Bob'
   @set email = '[email protected]'
   INSERT INTO users (id, name, email) VALUES (${id}, ${name}, ${email});

3. Generate code

   sqg sqg.yaml

4. Use the generated code

   import { DuckDBInstance } from '@duckdb/node-api';
   import { MyApp } from './generated/my-app';
   
   async function main() {
     // Create database connection
     const instance = await DuckDBInstance.create(':memory:');
     const connection = await instance.connect();
   
     // Apply migrations (with built-in tracking)
     await MyApp.applyMigrations(connection);
   
     // Create query instance
     const queries = new MyApp(connection);
   
     // Insert data
     await queries.createUser(2, 'Bob', '[email protected]');
   
     // Query data
     const users = await queries.allUsers();
     console.log(users);
   
     const user = await queries.getUser(1);  // Gets Alice from TESTDATA
     console.log(user?.name); // 'Alice'
   
     // Access complex types
     console.log(user?.metadata?.entries.role); // 'admin'
     console.log(user?.tags?.items); // ['developer', 'lead']
   }
   
   main();

5. See the generated code

   Generated TypeScript

   From: DuckDB Structs and Arrays

   See full code in Playground

   // ...
   
     async allUsers(): Promise<
       {
         id: number | null;
         name: string | null;
         email: string | null;
         metadata: { entries: { role: string | null; active: boolean | null } };
         tags: { items: (string | null)[] };
         created_at: { micros: bigint } | null;
       }[]
     > {
       const sql = "SELECT * FROM users ORDER BY created_at DESC;";
       const reader = await this.conn.runAndReadAll(sql, []);
       return reader.getRowObjects() as {
         id: number | null;
         name: string | null;
         email: string | null;
         metadata: { entries: { role: string | null; active: boolean | null } };
         tags: { items: (string | null)[] };
         created_at: { micros: bigint } | null;
       }[];
     }
   
   
   // ...

Complex Types

DuckDB supports advanced types that SQG fully handles:

Structs

SQL:

CREATE TABLE products (
  id INTEGER,
  details STRUCT(name VARCHAR, price DECIMAL(10,2), in_stock BOOLEAN)
);

TypeScript:

interface DetailsStruct {
  entries: {
    name: string | null;
    price: { width: number; scale: number; value: bigint } | null;
    in_stock: boolean | null;
  };
}

Usage:

const product = await queries.getProduct(1);
console.log(product?.details?.entries.name);
console.log(product?.details?.entries.price);

Lists (Arrays)

SQL:

CREATE TABLE posts (
  id INTEGER,
  tags VARCHAR[],
  scores INTEGER[]
);

TypeScript:

interface PostRow {
  id: number;
  tags: { items: (string | null)[] } | null;
  scores: { items: (number | null)[] } | null;
}

Usage:

const post = await queries.getPost(1);
console.log(post?.tags?.items); // ['tech', 'news']
console.log(post?.scores?.items); // [95, 87, 92]

Maps

SQL:

CREATE TABLE settings (
  id INTEGER,
  config MAP(VARCHAR, VARCHAR)
);

TypeScript:

interface SettingsRow {
  id: number;
  config: { entries: { key: string; value: string }[] } | null;
}

Usage:

const settings = await queries.getSettings(1);
for (const entry of settings?.config?.entries ?? []) {
  console.log(`${entry.key}: ${entry.value}`);
}

Nested Complex Types

DuckDB supports deeply nested types:

CREATE TABLE reports (
  id INTEGER,
  data STRUCT(
    summary VARCHAR,
    metrics STRUCT(views INTEGER, clicks INTEGER)[],
    tags MAP(VARCHAR, VARCHAR[])
  )
);

Type Mapping

DuckDB Type	TypeScript Type
INTEGER, BIGINT	number, bigint
DOUBLE, FLOAT	number
VARCHAR, TEXT	string
BOOLEAN	boolean
TIMESTAMP	{ micros: bigint }
DATE	{ days: number }
TIME	{ micros: bigint }
BLOB	{ bytes: Uint8Array }
UUID	{ hugeint: bigint }
DECIMAL(p,s)	{ width: number; scale: number; value: bigint }
STRUCT(...)	{ entries: {...} }
T[]	{ items: T[] }
MAP(K,V)	{ entries: { key: K; value: V }[] }

Async Pattern

All generated methods are async:

// Query methods return Promise
const users: UserRow[] = await queries.allUsers();
const user: UserRow | undefined = await queries.getUser(1);

// Exec methods return Promise<void>
await queries.createUser(1, 'Alice', '[email protected]');
await queries.updateUser(1, 'Alice Smith');

Connection Management

import { DuckDBInstance } from '@duckdb/node-api';

// In-memory database
const instance = await DuckDBInstance.create(':memory:');

// File-based database
const instance = await DuckDBInstance.create('app.duckdb');

// Connect
const connection = await instance.connect();
const queries = new MyApp(connection);

// Multiple connections (for concurrent access)
const conn1 = await instance.connect();
const conn2 = await instance.connect();

// Close connections
connection.closeSync();

Bulk Inserts with Appenders

DuckDB’s Appender API provides high-performance bulk inserts—significantly faster than individual INSERT statements. SQG generates type-safe appender wrappers using the TABLE annotation.

Defining an Appender

Use the TABLE annotation with the :appender modifier:

DuckDB Appender

High-performance bulk inserts using DuckDB's Appender API

Try in Playground

-- MIGRATE 1
CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type VARCHAR NOT NULL,
    payload VARCHAR,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- TABLE events :appender

Generated Code

SQG generates three components:

1. Row interface - Type-safe structure for each row
2. Appender class - Wrapper with append(), appendMany(), flush(), and close() methods
3. Factory method - Creates appender instances from your queries class

Generated TypeScript

From: DuckDB Appender

See full code in Playground

// ...

export interface EventsRow {
  id: number;
  event_type: string;
  payload: string | null;
  timestamp: DuckDBTimestampValue | null;
}

/** Appender for bulk inserts into events */
export class EventsAppender {
  constructor(public readonly appender: DuckDBAppender) {}

  /** Append a single row */
  append(row: EventsRow): this {
    this.appender.appendInteger(row.id);
    this.appender.appendVarchar(row.event_type);
    if (row.payload === null || row.payload === undefined) {
      this.appender.appendNull();
    } else {
      this.appender.appendVarchar(row.payload);
    }
    if (row.timestamp === null || row.timestamp === undefined) {
      this.appender.appendNull();
    } else {
      this.appender.appendTimestamp(row.timestamp);
    }
    this.appender.endRow();
    return this;
  }

  /** Append multiple rows */
  appendMany(rows: EventsRow[]): this {
    for (const row of rows) {
      this.append(row);
    }
    return this;
  }

  /** Flush buffered data to the table */
  flush(): this {
    this.appender.flushSync();
    return this;
  }

  /** Flush and close the appender */
  close(): void {
    this.appender.closeSync();
  }
}

Basic Usage

// Create appender
const appender = await queries.createEventsAppender();

// Append single row
appender.append({
  id: 1,
  event_type: 'page_view',
  payload: '{"url": "/home"}',
  timestamp: null
});

// Append multiple rows
appender.appendMany([
  { id: 2, event_type: 'click', payload: null, timestamp: null },
  { id: 3, event_type: 'scroll', payload: '{"depth": 50}', timestamp: null },
]);

// Flush and close
appender.close();

For large datasets, call flush() periodically to manage memory, then close() when done.

Type Mappings for Appenders

Appender row interfaces use DuckDB’s native value types for temporal and binary data:

DuckDB Column Type	TypeScript Appender Type
INTEGER, SMALLINT, TINYINT	number
BIGINT, HUGEINT	bigint
DOUBLE, FLOAT	number
VARCHAR, TEXT	string
BOOLEAN	boolean
DATE	DuckDBDateValue
TIME	DuckDBTimeValue
TIMESTAMP	DuckDBTimestampValue
BLOB	DuckDBBlobValue
UUID	string

Note: Nullable columns use T | null types.

Working with Temporal Types

For DATE, TIME, and TIMESTAMP columns, use DuckDB’s value constructors:

import {
  DuckDBDateValue,
  DuckDBTimeValue,
  DuckDBTimestampValue
} from '@duckdb/node-api';

// Create timestamp value
const timestamp = DuckDBTimestampValue.fromMicros(BigInt(Date.now()) * 1000n);

// Create date value
const date = DuckDBDateValue.fromDays(Math.floor(Date.now() / 86400000));

appender.append({
  id: 1,
  event_type: 'login',
  payload: null,
  timestamp: timestamp
});

Example: Analytics Query

Analytics Query

Example analytics query with aggregations and date filtering

Try in Playground

-- MIGRATE 1
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    region VARCHAR NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    order_date DATE NOT NULL
);

-- TESTDATA seed
INSERT INTO orders (id, region, amount, order_date) VALUES
  (1, 'North', 100.50, DATE '2024-01-15'),
  (2, 'South', 250.75, DATE '2024-02-20'),
  (3, 'North', 175.25, DATE '2024-03-10'),
  (4, 'East', 300.00, DATE '2024-04-05'),
  (5, 'South', 125.50, DATE '2024-05-12');

-- QUERY sales_by_region
@set start_date = 2024-01-01
@set end_date = 2024-12-31
SELECT
  region,
  COUNT(*) as order_count,
  SUM(amount) as total_sales,
  AVG(amount) as avg_order_value
FROM orders
WHERE order_date BETWEEN ${start_date} AND ${end_date}
GROUP BY region
ORDER BY total_sales DESC;

Generated TypeScript

From: Analytics Query

See full code in Playground

// ...

  async salesByRegion(
    start_date: string,
    end_date: string,
  ): Promise<
    {
      region: string | null;
      order_count: bigint | null;
      total_sales: { width: number; scale: number; value: bigint } | null;
      avg_order_value: number | null;
    }[]
  > {
    const sql = `SELECT
  region,
  COUNT(*) as order_count,
  SUM(amount) as total_sales,
  AVG(amount) as avg_order_value
FROM orders
WHERE order_date BETWEEN ? AND ?
GROUP BY region
ORDER BY total_sales DESC;`;
    const reader = await this.conn.runAndReadAll(sql, [start_date, end_date]);
    return reader.getRowObjects() as {
      region: string | null;
      order_count: bigint | null;
      total_sales: { width: number; scale: number; value: bigint } | null;
      avg_order_value: number | null;
    }[];
  }
}

See Also

• DuckDB Documentation
• @duckdb/node-api (Node.js Neo)