SQLRooms

Appearance

@sqlrooms/duckdb ​

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 and useDataTable
  • 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

Installation ​

bash
npm install @sqlrooms/duckdb

Basic Usage ​

Using the SQL Hook ​

tsx
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.

Monitoring WebSocket DuckDB Connections ​

createWebSocketDuckDbConnector() exposes the persistent WebSocket lifecycle through connectionStatus, subscribeConnectionStatus(), and the optional onConnectionStatusChange callback. Use this for live UI affordances such as lost-connection dialogs. Call reconnect() to reopen the socket and rerun the connector initialization SQL without destroying the connector instance.

tsx
import {createWebSocketDuckDbConnector} from '@sqlrooms/duckdb';

const connector = createWebSocketDuckDbConnector({
  wsUrl: 'ws://localhost:4000',
  onConnectionStatusChange: (status) => {
    console.log('DuckDB WebSocket status:', status);
  },
});

const unsubscribe = connector.subscribeConnectionStatus((status) => {
  if (status === 'disconnected') {
    console.warn('DuckDB WebSocket disconnected');
  }
});

await connector.reconnect();

Looking up Table Metadata ​

Use useDataTable() in React components or db.findTable() from the room store. String references are parsed like SQL identifiers, so use quotes for literal dots in table names.

tsx
import {useDataTable} from '@sqlrooms/duckdb';

function TableColumns() {
  const table = useDataTable('"memory"."main"."earthquakes"');

  return (
    <ul>
      {table?.columns.map((column) => (
        <li key={column.name}>{column.name}</li>
      ))}
    </ul>
  );
}

Using Zod for Runtime Validation ​

tsx
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:

tsx
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>
  );
}

Working with Tables ​

Using the Store for Direct Database Operations ​

tsx
import {useRoomStore} from './store';
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 ​

tsx
import {quoteTableReference, resolveTableReference} from '@sqlrooms/duckdb';
import {useRoomStore} from './store';
import {Button} from '@sqlrooms/ui';

function QualifiedTableOps() {
  const qualifyTableName = useRoomStore((state) => state.db.qualifyTableName);
  const createTableFromQuery = useRoomStore(
    (state) => state.db.createTableFromQuery,
  );
  const dropTable = useRoomStore((state) => state.db.dropTable);
  const checkTableExists = useRoomStore((state) => state.db.checkTableExists);

  const run = async () => {
    // Store-aware qualification knows which database is the default.
    const qualifiedTable = qualifyTableName({
      database: 'mydb',
      schema: 'public',
      table: 'users',
    });
    // toString() is the canonical portable table ID; toFullString() includes
    // the database when explicit catalog qualification is needed.
    const tableSql = quoteTableReference(qualifiedTable.toString());
    const resolved = resolveTableReference([{table: qualifiedTable}], 'users');

    await createTableFromQuery(qualifiedTable, 'SELECT * FROM source_table');
    console.log('Quoted table reference:', tableSql);
    console.log('Fully qualified reference:', qualifiedTable.toFullString());
    console.log('Resolved table:', resolved.table?.table.toString());
    const tableExists = await checkTableExists(qualifiedTable);
    console.log('Table exists after create:', tableExists);
    await dropTable(qualifiedTable);
  };

  return <Button onClick={() => void run()}>Run qualified table ops</Button>;
}

Loading the Schema Catalog ​

Use loadSchemaCatalog() when you need the database/schema/table hierarchy, including empty schemas and attached databases whose main schema has no tables yet. The catalog filter receives typed entries for databases, schemas, and tables, so schema visibility does not depend on fake table names. Related option and filter types are exported for callers that wrap these helpers in their own APIs.

ts
import {
  defaultLoadSchemaCatalogFilter,
  loadSchemaCatalog,
} from '@sqlrooms/duckdb';

const catalog = await loadSchemaCatalog(connector, {
  filterFunction: (entry) =>
    entry.type === 'schema' && entry.schema === 'scratch'
      ? false
      : defaultLoadSchemaCatalogFilter(entry),
});

Loading Data from Files ​

Using Load Functions Directly ​

tsx
import {loadCSV, loadJSON, loadParquet, loadObjects} from '@sqlrooms/duckdb';
import {useRoomStore} from './store';
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 ​

tsx
import * as arrow from 'apache-arrow';
import {useRoomStore} from './store';

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

  const handleFileUpload = async (file: File) => {
    try {
      const connector = await getConnector();
      await connector.loadFile(file, 'uploaded_data', {
        method: 'auto', // Auto-detect file type
        replace: true,
        temp: false,
      });
    } catch (error) {
      console.error('Failed to load uploaded file:', error);
    }
  };

  const handleLoadArrowTable = async (arrowTable: arrow.Table) => {
    try {
      const connector = await getConnector();
      await connector.loadArrow(arrowTable, 'arrow_data');
    } catch (error) {
      console.error('Failed to load Arrow table:', error);
    }
  };

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

Exporting Data to CSV ​

tsx
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 ​

tsx
import {roomStore} from './store';

async function executeCustomQuery() {
  // Plain TS/JS usage: read connector from the store API directly.
  const connector = roomStore.getState().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 ​

tsx
import {roomStore} from './store';

async function cancelExample() {
  const connector = roomStore.getState().db.connector;

  // 1. Manual cancel via the handle
  const query = connector.query('SELECT * FROM large_table');
  setTimeout(() => query.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 ​

tsx
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.

Interfaces ​

Type Aliases ​

Variables ​

Functions ​

Copyright OpenJS Foundation and vis.gl contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.

The OpenJS Foundation | Terms of Use | Privacy Policy | Bylaws | Code of Conduct | Trademark Policy | Trademark List | Cookie Policy