A powerful wrapper around DuckDB-WASM that provides React hooks and utilities for working with DuckDB in browser environments.

Features

React Integration & Type Safety

• React Hooks: Seamless integration with React applications via useSql
• Runtime Validation: Optional Zod schema validation for query results with type transformations
• Typed Row Accessors: Type-safe row access with validation and multiple iteration methods

Data Management

• File Operations: Import data from various file formats (CSV, JSON, Parquet) with auto-detection
• Arrow Integration: Work directly with Apache Arrow tables for efficient columnar data processing
• Schema Management: Comprehensive database, schema, and table discovery and management
• Qualified Table Names: Full support for database.schema.table naming convention

Performance & Operations

• Query Deduplication: Automatic deduplication of identical running queries to prevent duplicate execution
• Query Cancellation: Cancel running queries with full composability support via QueryHandle interface (learn more)
• Data Export: Export query results to CSV files with pagination for large datasets
• Batch Processing: Handle large datasets efficiently with built-in pagination support

SQL Statement Utilities

splitSqlStatements splits DuckDB SQL without treating semicolons inside quoted strings, dollar-quoted strings, or comments as statement boundaries. Comments are removed safely by default; preserve them when the original SQL will be rewritten or re-executed:

import {splitSqlStatements} from '@sqlrooms/duckdb-core';

const statements = splitSqlStatements(sql, {removeComments: false});

Installation

npm install @sqlrooms/duckdb

Basic Usage

Using the SQL Hook

import {useSql} from '@sqlrooms/duckdb';

function UserList() {
  // Basic usage with TypeScript types
  const {data, isLoading, error} = useSql<{id: number; name: string}>({
    query: 'SELECT id, name FROM users',
  });

  if (isLoading) return <div>Loading...</div>;
  if (error) return <div>Error: {error.message}</div>;
  if (!data) return null;

  return (
    <ul>
      {Array.from(data.rows()).map((user) => (
        <li key={user.id}>{user.name}</li>
      ))}
    </ul>
  );
}

For more information and examples on using the useSql hook, see the useSql API documentation.

Using Zod for Runtime Validation

import {useSql} from '@sqlrooms/duckdb';
import {z} from 'zod';

const userSchema = z.object({
  id: z.number(),
  name: z.string(),
  email: z.string().email(),
  created_at: z.string().transform((str) => new Date(str)),
});

function ValidatedUserList() {
  const {data, isLoading, error} = useSql(userSchema, {
    query: 'SELECT id, name, email, created_at FROM users',
  });

  if (isLoading) return <div>Loading...</div>;
  if (error) {
    if (error instanceof z.ZodError) {
      return <div>Validation Error: {error.errors[0].message}</div>;
    }
    return <div>Error: {error.message}</div>;
  }
  if (!data) return null;

  return (
    <ul>
      {data.toArray().map((user) => (
        <li key={user.id}>
          {user.name} ({user.email}) - Joined:{' '}
          {user.created_at.toLocaleDateString()}
        </li>
      ))}
    </ul>
  );
}

Accessing the Underlying Arrow Table and Schema

You can access the underlying Arrow table and schema of a useSql() query result. This is especially useful if you want to pass the data to a library that expect an Apache Arrow Table as input without additional data transformation:

import {useSql} from '@sqlrooms/duckdb';

function ArrowTableSchemaExample() {
  const {data, isLoading, error} = useSql({
    query: 'SELECT id, name FROM users',
  });

  if (isLoading) return <div>Loading...</div>;
  if (error) return <div>Error: {error.message}</div>;
  if (!data || !data.arrowTable) return null;

  const {arrowTable} = data;
  const fields = arrowTable.schema.fields;
  const numRows = arrowTable.numRows;

  return (
    <table>
      <thead>
        <tr>
          {fields.map((field) => (
            <th key={field.name}>{field.name}</th>
          ))}
        </tr>
      </thead>
      <tbody>
        {Array.from({length: numRows}).map((_, rowIdx) => (
          <tr key={rowIdx}>
            {fields.map((field, colIdx) => (
              <td key={field.name}>
                {String(arrowTable.getChildAt(colIdx)?.get(rowIdx) ?? '')}
              </td>
            ))}
          </tr>
        ))}
      </tbody>
    </table>
  );
}

Classifying Column Types

Use the shared type category helpers when UI code needs semantic answers such as numeric, temporal, categorical, or binary instead of exact DuckDB type names:

import {
  getDuckDbTypeCategory,
  isColumnNumeric,
  isColumnQuantitative,
} from '@sqlrooms/duckdb';

getDuckDbTypeCategory('DECIMAL(10, 2)'); // "number"
isColumnNumeric({name: 'magnitude', type: 'DOUBLE'}); // true
isColumnQuantitative('TIMESTAMP_MS'); // true

Working with Tables

Using the Store for Direct Database Operations

import {Button} from '@sqlrooms/ui';

function DatabaseManager() {
  const createTableFromQuery = useRoomStore(
    (state) => state.db.createTableFromQuery,
  );
  const addTable = useRoomStore((state) => state.db.addTable);
  const dropTable = useRoomStore((state) => state.db.dropTable);
  const tables = useRoomStore((state) => state.db.tables);
  const refreshTableSchemas = useRoomStore(
    (state) => state.db.refreshTableSchemas,
  );

  // Create a table from a query
  const handleCreateTable = async () => {
    const result = await createTableFromQuery(
      'filtered_users',
      'SELECT * FROM users WHERE active = true',
    );
    console.log(`Created table with ${result.rowCount} rows`);
  };

  // Add a table from JavaScript objects
  const handleAddTable = async () => {
    const users = [
      {id: 1, name: 'Alice', email: '[email protected]'},
      {id: 2, name: 'Bob', email: '[email protected]'},
    ];
    await addTable('new_users', users);
  };

  // Drop a table
  const handleDropTable = async () => {
    await dropTable('old_table');
  };

  return (
    <div>
      <Button onClick={handleCreateTable}>Create Filtered Users Table</Button>
      <Button onClick={handleAddTable}>Add New Users Table</Button>
      <Button onClick={handleDropTable}>Drop Old Table</Button>
      <Button onClick={refreshTableSchemas}>Refresh Schemas</Button>

      <h3>Available Tables:</h3>
      <ul>
        {tables.map((table) => (
          <li key={table.table.toString()}>
            {table.table.toString()} ({table.columns.length} columns)
          </li>
        ))}
      </ul>
    </div>
  );
}

Working with Qualified Table Names

import {
  getRawSqlTableReference,
  getTableDisplayName,
  getTableIdentity,
  makeQualifiedTableName,
  parseTableIdentity,
  resolveTableReference,
} from '@sqlrooms/duckdb';

// Support for database.schema.table naming
const qualifiedTable = makeQualifiedTableName({
  database: 'mydb',
  defaultDatabase: 'mydb',
  schema: 'public',
  table: 'users',
});
// qualifiedTable.toString(): '"public"."users"'
// qualifiedTable.toFullString(): '"mydb"."public"."users"'

const tableId = getTableIdentity(qualifiedTable);
// tableId: '"public"."users"'

const rehydratedTableId = parseTableIdentity(tableId);
// rehydratedTableId: '"public"."users"'

const tableSql = getRawSqlTableReference(qualifiedTable);
// tableSql: '"public"."users"'

const resolved = resolveTableReference([{table: qualifiedTable}], 'users');
// resolved.table?.table.toString(): '"public"."users"'

const tableLabel = getTableDisplayName(qualifiedTable);
// tableLabel: 'users'

// Use with table operations
await createTableFromQuery(qualifiedTable, 'SELECT * FROM source_table');
await dropTable(qualifiedTable);
const tableExists = await checkTableExists(qualifiedTable);

Loading Data from Files

Using Load Functions Directly

import {loadCSV, loadJSON, loadParquet, loadObjects} from '@sqlrooms/duckdb';
import {Button} from '@sqlrooms/ui';

function DataLoader() {
  const getConnector = useRoomStore((state) => state.db.getConnector);

  const handleLoadCSV = async (file: File) => {
    const connector = await getConnector();

    // Generate SQL to load CSV file
    const sql = loadCSV('my_table', file.name, {
      auto_detect: true,
      replace: true,
    });

    // Execute the load operation
    await connector.query(sql).result;
  };

  const handleLoadObjects = async () => {
    const connector = await getConnector();
    const data = [
      {id: 1, name: 'Alice'},
      {id: 2, name: 'Bob'},
    ];

    // Generate SQL to load objects
    const sql = loadObjects('users', data, {replace: true});
    await connector.query(sql).result;
  };

  return (
    <div>
      <input
        type="file"
        accept=".csv"
        onChange={(e) => {
          if (e.target.files?.[0]) handleLoadCSV(e.target.files[0]);
        }}
      />
      <Button onClick={handleLoadObjects}>Load Sample Data</Button>
    </div>
  );
}

Using the Connector Directly

function AdvancedDataLoader() {
  const connector = useRoomStore((state) => state.db.connector);

  const handleFileUpload = async (file: File) => {
    // Load file directly using the connector
    await connector.loadFile(file, 'uploaded_data', {
      method: 'auto', // Auto-detect file type
      replace: true,
      temp: false,
    });
  };

  const handleLoadArrowTable = async (arrowTable: arrow.Table) => {
    // Load Arrow table directly
    await connector.loadArrow(arrowTable, 'arrow_data');
  };

  return (
    <input
      type="file"
      accept=".csv,.json,.parquet"
      onChange={(e) => {
        if (e.target.files?.[0]) handleFileUpload(e.target.files[0]);
      }}
    />
  );
}

Exporting Data to CSV

import {useExportToCsv} from '@sqlrooms/duckdb';
import {Button} from '@sqlrooms/ui';

function ExportButton() {
  const {exportToCsv} = useExportToCsv();

  const handleExport = async () => {
    await exportToCsv('SELECT * FROM users ORDER BY name', 'users_export.csv');
  };

  return <Button onClick={handleExport}>Export to CSV</Button>;
}

Low-Level DuckDB Access

Basic direct usage

async function executeCustomQuery() {
  // Grab the connector directly (no React hook necessary inside plain TS)
  const connector = useRoomStore((state) => state.db.connector);

  // QueryHandle is promise-like – await it directly
  const result = await connector.query('SELECT COUNT(*) AS count FROM users');

  // Inspect Arrow table
  const count = result.getChildAt(0)?.get(0);
  console.log(`Total users: ${count}`);
}

Cancellation examples

async function cancelExample() {
  const connector = useRoomStore((state) => state.db.connector);

  // 1. Manual cancel via the handle
  const query = connector.query('SELECT * FROM large_table');
  setTimeout(() => h.cancel(), 2000); // cancel after 2 s
  await query; // throws if cancelled

  // 2. Composable cancellation – many queries, one controller
  const controller = new AbortController();
  const q1 = connector.query('SELECT 1', {signal: controller.signal});
  const q2 = connector.query('SELECT 2', {signal: controller.signal});
  controller.abort(); // cancels q1 & q2
  await Promise.allSettled([q1, q2]);
}

Advanced operations with the Zustand store

import {Button} from '@sqlrooms/ui';

function AdvancedOperations() {
  const executeSql = useRoomStore((s) => s.db.executeSql);
  const sqlSelectToJson = useRoomStore((s) => s.db.sqlSelectToJson);
  const checkTableExists = useRoomStore((s) => s.db.checkTableExists);

  const handleAdvancedQuery = async () => {
    // Cached execution with deduplication
    const query = await executeSql('SELECT * FROM users LIMIT 10');
    if (query) {
      const rows = await query; // await handle directly
      console.log('Query result:', rows);
    }

    // Parse SQL to JSON (analysis tool)
    const parsed = await sqlSelectToJson('SELECT id, name FROM users');
    console.log('Parsed query:', parsed);

    // Safety check before destructive operations
    const exists = await checkTableExists('users');
    console.log('Table exists:', exists);
  };

  return <Button onClick={handleAdvancedQuery}>Run Advanced Operations</Button>;
}

For more information, visit the SQLRooms documentation.