duckhouse

Lightweight TypeScript/JavaScript client for DuckHouse - managed DuckDB in the cloud. Execute SQL queries, upload data, and manage tables with a simple API.

Installation

npm install duckhouse
# or
yarn add duckhouse
# or
bun add duckhouse

Quick Start

import { createClient } from 'duckhouse';

const client = createClient({
  url: 'https://your-database.fly.dev',
  token: 'your-auth-token',
});

// Execute a query
const result = await client.query('SELECT * FROM users LIMIT 10');
console.log(result.data);

API Reference

Creating a Client

const client = createClient({
  url: 'https://your-database.fly.dev',  // Your DuckHouse database URL
  token: 'dh_xxxxx',                      // Your authentication token
  timeout: 30000,                         // Optional: Request timeout in ms (default: 30000)
  retries: 3,                            // Optional: Number of retries (default: 3)
});

Querying Data

Basic Query

const result = await client.query('SELECT * FROM sales WHERE year = 2024');
console.log(result.data);       // Array of row objects
console.log(result.columns);    // Column metadata
console.log(result.rowCount);   // Number of rows
console.log(result.executionTime); // Query execution time in ms

Query with Parameters

const result = await client.query(
  'SELECT * FROM users WHERE age > ? AND city = ?',
  {
    params: [18, 'New York']
  }
);

Query with Different Output Formats

// JSON (default)
const jsonResult = await client.query('SELECT * FROM data', { format: 'json' });

// CSV
const csvResult = await client.query('SELECT * FROM data', { format: 'csv' });

// Parquet
const parquetResult = await client.query('SELECT * FROM data', { format: 'parquet' });

// Arrow
const arrowResult = await client.query('SELECT * FROM data', { format: 'arrow' });

Executing Commands

For DDL and DML operations that don't return data:

// Create table
await client.execute(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR,
    email VARCHAR,
    created_at TIMESTAMP
  )
`);

// Insert data
await client.execute(`
  INSERT INTO users (name, email) 
  VALUES ('John Doe', '[email protected]')
`);

// Update data
await client.execute(`
  UPDATE users 
  SET email = '[email protected]' 
  WHERE id = 1
`);

Uploading Data

Upload JSON Data

const data = [
  { id: 1, name: 'Alice', age: 30 },
  { id: 2, name: 'Bob', age: 25 },
];

await client.upload(data, {
  tableName: 'users',
  createTable: true,  // Auto-create table if it doesn't exist
  append: true,       // Append to existing data (false = replace)
});

Upload CSV Data

const csvBuffer = Buffer.from(`
id,name,age
1,Alice,30
2,Bob,25
`);

await client.upload(csvBuffer, {
  tableName: 'users',
  format: 'csv',
  createTable: true,
});

Upload Parquet Data

const parquetBuffer = await fs.readFile('data.parquet');

await client.upload(parquetBuffer, {
  tableName: 'large_dataset',
  format: 'parquet',
  append: true,
});

Table Management

List Tables

const tables = await client.listTables();
tables.forEach(table => {
  console.log(`${table.name}: ${table.rowCount} rows, ${table.size} bytes`);
});

Get Table Information

const info = await client.getTableInfo('users');
console.log('Columns:', info.columns);
console.log('Row count:', info.rowCount);
console.log('Size:', info.size);

Create Table

await client.createTable('products', [
  { name: 'id', type: 'INTEGER', nullable: false },
  { name: 'name', type: 'VARCHAR', nullable: false },
  { name: 'price', type: 'DECIMAL(10,2)', nullable: true },
  { name: 'created_at', type: 'TIMESTAMP', nullable: false },
]);

Drop Table

// Drop if exists
await client.dropTable('old_data', true);

// Drop (will error if doesn't exist)
await client.dropTable('old_data', false);

Utility Methods

Test Connection

const isConnected = await client.ping();
console.log('Connected:', isConnected);

Get Database Version

const version = await client.version();
console.log('DuckDB version:', version);

Error Handling

The client throws DuckHouseError for all errors:

import { DuckHouseError } from 'duckhouse';

try {
  await client.query('SELECT * FROM non_existent_table');
} catch (error) {
  if (error instanceof DuckHouseError) {
    console.error('Error code:', error.code);
    console.error('Message:', error.message);
    console.error('Details:', error.details);
  }
}

TypeScript Support

The client is written in TypeScript and provides full type definitions:

import { 
  DuckHouseClient,
  QueryResult,
  TableInfo,
  DuckHouseError 
} from 'duckhouse';

// Type your query results
interface User {
  id: number;
  name: string;
  email: string;
}

const result = await client.query<User>('SELECT * FROM users');
result.data.forEach(user => {
  console.log(user.name); // TypeScript knows this is a string
});

Advanced Usage

Streaming Large Results

For large result sets, consider using pagination:

const pageSize = 1000;
let offset = 0;
let hasMore = true;

while (hasMore) {
  const result = await client.query(
    `SELECT * FROM large_table LIMIT ${pageSize} OFFSET ${offset}`
  );
  
  // Process batch
  await processBatch(result.data);
  
  hasMore = result.rowCount === pageSize;
  offset += pageSize;
}

Connection Pooling

For applications with high concurrency, create multiple client instances:

class DuckHousePool {
  private clients: DuckHouseClient[] = [];
  private current = 0;

  constructor(config: DuckHouseConfig, size = 5) {
    for (let i = 0; i < size; i++) {
      this.clients.push(createClient(config));
    }
  }

  getClient(): DuckHouseClient {
    const client = this.clients[this.current];
    this.current = (this.current + 1) % this.clients.length;
    return client;
  }
}

const pool = new DuckHousePool(config);
const result = await pool.getClient().query('SELECT * FROM data');

License

MIT

Contributing

Contributions are welcome! Please see our Contributing Guide for details.

Support

• GitHub Issues
• Documentation
• Discord Community