DuckDB REST API: Expose Your Local Database

Build a REST API on top of DuckDB with Node.js, Python, or Go. Query your local DuckDB database over HTTP from any client or dashboard tool.

Mark Rachapoom

March 26, 2026·6 min read

DuckDB REST API: Expose Your Local Database

DuckDB is embedded — it doesn't have a built-in server. But adding an HTTP layer is straightforward, and once you do, you can query your DuckDB database from any client: dashboards, mobile apps, browser-based tools, or other services.

Here's how to build a production-quality DuckDB REST API.

Why Expose DuckDB via REST?#

Dashboard tools (Grafana, Metabase) need an HTTP endpoint to query
Multi-client access — web app + mobile app + CLI all hitting the same data
Team access — run DuckDB on a server and let teammates query it
Third-party integrations — webhook receivers, Zapier, anything that speaks HTTP

Python: FastAPI + DuckDB#

FastAPI is the fastest path to a production DuckDB API:

pip install fastapi uvicorn duckdb

# api.py
from fastapi import FastAPI, HTTPException
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
import duckdb
import os
 
app = FastAPI(title="DuckDB API")
 
# Allow cross-origin requests from dashboard tools
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],  # Restrict in production
    allow_methods=["GET", "POST"],
    allow_headers=["*"],
)
 
# Single connection (read-only for safety)
DB_PATH = os.environ.get('DUCKDB_PATH', 'analytics.duckdb')
con = duckdb.connect(DB_PATH, read_only=True)
 
 
class QueryRequest(BaseModel):
    sql: str
    params: list = []
 
 
@app.get("/health")
def health():
    return {"status": "ok"}
 
 
@app.post("/query")
def execute_query(request: QueryRequest):
    """Execute an arbitrary SQL query."""
    try:
        result = con.execute(request.sql, request.params).fetchdf()
        return {
            "columns": result.columns.tolist(),
            "rows": result.values.tolist(),
            "row_count": len(result)
        }
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))
 
 
@app.get("/tables")
def list_tables():
    """List all tables and views."""
    tables = con.execute("""
        SELECT table_name, table_type
        FROM information_schema.tables
        ORDER BY table_type, table_name
    """).fetchdf()
    return tables.to_dict(orient='records')
 
 
@app.get("/tables/{table_name}")
def describe_table(table_name: str):
    """Get schema for a specific table."""
    try:
        schema = con.execute(f"DESCRIBE {table_name}").fetchdf()
        return schema.to_dict(orient='records')
    except Exception as e:
        raise HTTPException(status_code=404, detail=f"Table {table_name} not found")
 
 
@app.get("/tables/{table_name}/data")
def get_table_data(table_name: str, limit: int = 100, offset: int = 0):
    """Get paginated data from a table."""
    try:
        data = con.execute(
            f"SELECT * FROM {table_name} LIMIT ? OFFSET ?",
            [limit, offset]
        ).fetchdf()
        
        count = con.execute(f"SELECT COUNT(*) FROM {table_name}").fetchone()[0]
        
        return {
            "data": data.to_dict(orient='records'),
            "total": count,
            "limit": limit,
            "offset": offset
        }
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))

# Run the API
uvicorn api:app --host 0.0.0.0 --port 8000 --reload
 
# Query it
curl http://localhost:8000/query \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT segment, COUNT(*) FROM users GROUP BY segment"}'

Adding Authentication#

Never expose DuckDB to the internet without authentication:

from fastapi import Depends, HTTPException, status
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
 
security = HTTPBearer()
API_KEY = os.environ['API_KEY']
 
def verify_token(credentials: HTTPAuthorizationCredentials = Depends(security)):
    if credentials.credentials != API_KEY:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Invalid API key"
        )
    return credentials
 
@app.post("/query")
def execute_query(request: QueryRequest, _=Depends(verify_token)):
    # ... same as before

# With auth
curl http://localhost:8000/query \
  -H "Authorization: Bearer your-api-key" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT COUNT(*) FROM events"}'

SQL Injection Protection#

Never interpolate user input into SQL strings. Use parameterized queries:

# ❌ DANGEROUS
con.execute(f"SELECT * FROM users WHERE name = '{user_input}'")
 
# ✅ SAFE — use parameters
con.execute("SELECT * FROM users WHERE name = ?", [user_input])

For the query endpoint, add an allowlist or use a read-only connection to limit blast radius:

# Use read-only connection — prevents any write operations
con = duckdb.connect('analytics.duckdb', read_only=True)
 
# Optional: allowlist of allowed query patterns
import re
 
FORBIDDEN_PATTERNS = [
    r'\bDROP\b', r'\bDELETE\b', r'\bTRUNCATE\b',
    r'\bINSERT\b', r'\bUPDATE\b', r'\bCREATE\b'
]
 
def is_safe_query(sql: str) -> bool:
    sql_upper = sql.upper()
    return not any(re.search(p, sql_upper) for p in FORBIDDEN_PATTERNS)

Node.js: Express + DuckDB#

npm install express @duckdb/node-api

// server.js
const express = require('express');
const { DuckDBInstance } = require('@duckdb/node-api');
 
const app = express();
app.use(express.json());
 
let db;
 
async function init() {
    const instance = await DuckDBInstance.create('analytics.duckdb', {
        access_mode: 'READ_ONLY'
    });
    db = await instance.connect();
}
 
app.post('/query', async (req, res) => {
    try {
        const { sql } = req.body;
        const reader = await db.runAndReadAll(sql);
        const rows = reader.getRows();
        const columns = reader.columnNames();
        res.json({ columns, rows, row_count: rows.length });
    } catch (error) {
        res.status(400).json({ error: error.message });
    }
});
 
app.get('/health', (req, res) => res.json({ status: 'ok' }));
 
init().then(() => {
    app.listen(8000, () => console.log('DuckDB API running on :8000'));
});

Go: Gin + DuckDB#

go get github.com/gin-gonic/gin
go get github.com/marcboeker/go-duckdb

package main
 
import (
    "database/sql"
    "github.com/gin-gonic/gin"
    _ "github.com/marcboeker/go-duckdb"
)
 
func main() {
    db, _ := sql.Open("duckdb", "analytics.duckdb?access_mode=read_only")
    defer db.Close()
 
    r := gin.Default()
 
    r.POST("/query", func(c *gin.Context) {
        var req struct {
            SQL string `json:"sql"`
        }
        c.BindJSON(&req)
 
        rows, err := db.Query(req.SQL)
        if err != nil {
            c.JSON(400, gin.H{"error": err.Error()})
            return
        }
        defer rows.Close()
 
        cols, _ := rows.Columns()
        var results []map[string]interface{}
 
        for rows.Next() {
            values := make([]interface{}, len(cols))
            valuePtrs := make([]interface{}, len(cols))
            for i := range values {
                valuePtrs[i] = &values[i]
            }
            rows.Scan(valuePtrs...)
 
            row := make(map[string]interface{})
            for i, col := range cols {
                row[col] = values[i]
            }
            results = append(results, row)
        }
 
        c.JSON(200, gin.H{"rows": results, "columns": cols})
    })
 
    r.Run(":8000")
}

CORS and Dashboard Integration#

For Grafana or Metabase to query your API:

app.add_middleware(
    CORSMiddleware,
    allow_origins=["http://localhost:3000", "http://grafana.internal"],
    allow_methods=["GET", "POST", "OPTIONS"],
    allow_headers=["Authorization", "Content-Type"],
)

DenchClaw's Internal API#

DenchClaw exposes its DuckDB database to apps through the bridge API — a built-in HTTP/WebSocket layer that Dench Apps use to query data:

// Inside a DenchClaw app — equivalent to a REST API call
const data = await dench.db.query("SELECT * FROM v_deals WHERE \"Stage\" = 'Proposal'");

This is DuckDB over HTTP, but with authentication, rate limiting, and workspace context handled automatically by DenchClaw. If you're building apps inside DenchClaw, you don't need to build your own REST API — the bridge API is already there.

For external access (exposing DenchClaw data to other tools), you can use the pattern above — run a lightweight FastAPI wrapper against the same DuckDB file in read-only mode.