DuckDB Full-Stack Development

Installation

Python:

pip install duckdb pandas pyarrow
# or with Polars support
pip install duckdb polars pyarrow

React/WASM:

npm install @duckdb/duckdb-wasm

Bundled Scripts

This skill includes helper scripts in scripts/:

Usage:

# Validate SQL syntax
python scripts/validate_sql.py "SELECT * FROM events WHERE date > '2024-01-01'"

# Generate TypeScript types
python scripts/generate_types.py "SELECT id, name, price FROM products" --name Product --output types.ts

Domain Decision Tree

When to Use Python API

• Data engineering pipelines
• ETL workflows with pandas/Polars
• Large-scale analytics (10M+ rows)
• Background processing jobs
• Integration with ML workflows

When to Use WASM (Browser)

• Client-side analytics dashboards
• Data exploration tools
• Privacy-sensitive data (kept in browser)
• Real-time filtering of medium datasets (<1GB)
• Prototyping before Python backend

Common Patterns

1. Connection Management (Python)

import duckdb

# In-memory (fastest for <10GB)
conn = duckdb.connect()

# Persistent database
conn = duckdb.connect("analytics.db")

# Context manager (recommended)
with duckdb.connect("analytics.db") as conn:
    result = conn.execute("SELECT * FROM events").fetchdf()

Critical: Connections are not thread-safe. Create one connection per thread.

2. Querying Different Sources

# CSV files
df = conn.execute("SELECT * FROM 'data.csv' LIMIT 100").fetchdf()

# Parquet files (columnar, faster)
df = conn.execute("SELECT * FROM 'data.parquet'").fetchdf()

# Arrow directly (zero-copy)
arrow_table = conn.execute("SELECT * FROM events").fetch_arrow_table()

# Pandas DataFrame
conn.execute("CREATE TABLE events AS SELECT * FROM df")

3. React + WASM Setup

import * as duckdb from '@duckdb/duckdb-wasm';

// Initialize in useEffect
const initDb = async () => {
  const JSDELIVR_BUNDLES = duckdb.getJsDelivrBundles();
  const bundle = await duckdb.selectBundle(JSDELIVR_BUNDLES);
  
  const worker_url = URL.createObjectURL(
    new Blob([`importScripts("${bundle.mainWorker}");`], {type: 'text/javascript'})
  );
  
  const worker = new Worker(worker_url);
  const logger = new duckdb.ConsoleLogger();
  const db = new duckdb.AsyncDuckDB(logger, worker);
  await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
  
  return db;
};

Extensions

Essential extensions for common workflows:

S3/Cloud Example

INSTALL httpfs;
LOAD httpfs;

SET s3_region='us-east-1';
SET s3_access_key_id='...';
SET s3_secret_access_key='...';

SELECT * FROM 's3://bucket/data.parquet' LIMIT 100;

Performance Tips

1. Use Parquet for large files - Columnar format, compression, predicate pushdown
2. Create indexes on join keys - CREATE INDEX idx ON table(column);
3. Use the relational API for lazy evaluation - Build query plans without executing
4. Batch inserts - Insert 100K-1M rows at once, not row-by-row
5. Enable parallel CSV reading - SET preserve_insertion_order=false;

Reference Selection Guide

Python developers:

• New to DuckDB? → python-overview.md
• Building complex pipelines? → python-relational.md
• Loading data from files? → python-data.md
• Threading, UDFs, extensions? → python-advanced.md

React developers:

• Architecture overview → wasm-overview.md
• React hooks and patterns → wasm-react.md

Everyone:

• Syntax lookup → quick-reference.md
• Error solutions → troubleshooting.md
• Type generation → Use scripts/generate_types.py

Migration from Other Tools

Common Pitfalls

1. Don't share connections across threads - Create connection per thread
2. Don't use f-strings for SQL - Use parameterized queries
3. Don't load entire datasets into pandas - Use Arrow for large data
4. Don't forget to LOAD extensions - INSTALL is not enough
5. Don't rely on insertion order - Use explicit ORDER BY