DuckDB v2.0 Server Mode Complete Guide: Turn DuckDB Into a Real Database Server

# Install CLI on Linux/macOS
curl https://install.duckdb.org | DUCKDB_VERSION=alpha bash

# Verify version
~/.duckdb/cli/latest/duckdb --version
# DuckDB v2.0.0-alpha39998

# Install Python package
pip install duckdb --pre --upgrade
python3 -c "import duckdb; print(duckdb.version())"
# 1.6.0.dev379 (with duckdb 2.0.0-alpha39998)

# Simplest approach: start built-in server
duckdb server my_analytics.duckdb

# With custom port and bind address
duckdb server my_analytics.duckdb --port 7432 --bind 0.0.0.0

# With authentication mode
duckdb server my_analytics.duckdb --config log_level=info \
  --config credential_auth_server=localhost:8080

import duckdb
import threading

# Create tenant databases
tenants = ["tenant_a", "tenant_b", "tenant_c"]
for t in tenants:
    conn = duckdb.connect(f"{t}.duckdb")
    conn.execute("CREATE TABLE sales AS SELECT * FROM read_csv_auto('sales_2026.csv')")
    conn.close()

# Start Server in background thread
server_thread = threading.Thread(
    target=duckdb.start_server,
    kwargs={"database": "my_analytics.duckdb", "port": 7432}
)
server_thread.daemon = True
server_thread.start()

print("🦆 DuckDB Server started on port 7432")

# Connect to local server
duckdb "jdbc:duckdb://localhost:7432/my_analytics"

# List available databases
SHOW DATABASES;

# Switch to a specific tenant database
USE tenant_a;

# Run analytical queries
SELECT 
    product_category,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers,
    AVG(order_value) AS avg_order_value
FROM sales
GROUP BY product_category
ORDER BY total_revenue DESC;

import pyodbc

# Connect to DuckDB Server
conn_str = (
    "DRIVER={DuckDB};"
    "HOST=localhost;"
    "PORT=7432;"
    "DATABASE=tenant_a;"
)
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# Execute query
cursor.execute("""
    SELECT 
        product_category,
        SUM(revenue) AS total_revenue
    FROM sales
    GROUP BY product_category
""")

for row in cursor.fetchall():
    print(f"Category: {row[0]}, Revenue: ${row[1]:,.2f}")

conn.close()

import java.sql.*;

public class DuckDBServerDemo {
    public static void main(String[] args) throws Exception {
        Class.forName("org.duckdb.DuckDBJDBCDriver");
        
        String url = "jdbc:duckdb://localhost:7432/tenant_a";
        try (Connection conn = DriverManager.getConnection(url);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                 "SELECT product_category, SUM(revenue) as total " +
                 "FROM sales GROUP BY product_category")) {
            
            while (rs.next()) {
                System.out.println(rs.getString(1) + ": $" + rs.getDouble(2));
            }
        }
    }
}

-- Create tenant databases in the main server
CREATE DATABASE tenant_a;
CREATE DATABASE tenant_b;
CREATE DATABASE tenant_c;

-- Configure authentication backend:
-- user: tenant_a_user, password: xxx, database: tenant_a
-- user: tenant_b_user, password: xxx, database: tenant_b

-- Create schemas within a tenant database
CREATE SCHEMA analytics;
CREATE SCHEMA reporting;

-- Assign permissions by role
GRANT USAGE ON SCHEMA analytics TO analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst_role;
GRANT ALL PRIVILEGES ON SCHEMA reporting TO admin_role;

import duckdb
import time
import concurrent.futures

def benchmark_embedded():
    """Embedded mode benchmark"""
    con = duckdb.connect(":memory:")
    con.execute("CREATE TABLE data AS SELECT * FROM read_csv_auto('/data/sales.parquet')")
    
    queries = [
        "SELECT category, SUM(revenue) FROM data GROUP BY category",
        "SELECT * FROM data WHERE revenue > 1000 LIMIT 100",
        "SELECT month, AVG(revenue) FROM data GROUP BY month",
    ]
    
    start = time.time()
    for _ in range(100):
        for q in queries:
            con.execute(q).fetchall()
    elapsed = time.time() - start
    
    return {
        "Mode": "Embedded",
        "Total Queries": 300,
        "Avg Latency_ms": round(elapsed / 300 * 1000, 2),
        "QPS": round(300 / elapsed, 0)
    }

print(benchmark_embedded())

import duckdb
import concurrent.futures
import time

def concurrent_query(client_id):
    """Simulate concurrent queries from different tenants"""
    con = duckdb.connect(f"tenant_{client_id}.duckdb")
    
    result = con.execute("""
        SELECT 
            category,
            SUM(revenue) as total,
            COUNT(*) as orders
        FROM sales
        WHERE date >= '2026-01-01'
        GROUP BY category
        ORDER BY total DESC
        LIMIT 10
    """).fetchall()
    
    con.close()
    return client_id, len(result), time.time()

# Simulate 50 tenants querying simultaneously
start = time.time()
with concurrent.futures.ThreadPoolExecutor(max_workers=50) as executor:
    futures = [executor.submit(concurrent_query, i) for i in range(50)]
    results = [f.result() for f in concurrent.futures.as_completed(futures)]

elapsed = time.time() - start
print(f"50 tenants concurrent queries completed in {elapsed:.2f}s")
print(f"Avg response time: {elapsed/50*1000:.1f}ms")

# Complete configuration example
duckdb server analytics.duckdb \
  --port 7432 \
  --bind 0.0.0.0 \
  --config log_level=info \
  --config max_threads=8 \
  --config temp_directory=/tmp/duckdb_temp \
  --config memory_limit=4GB \
  --config autoinstall='httpfs,ducklake,iceberg'

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
import duckdb
import os

app = FastAPI(title="DuckDB Multi-Tenant Analytics API")

class QueryRequest(BaseModel):
    tenant_id: str
    sql: str

@app.post("/query")
def execute_query(req: QueryRequest):
    """Execute query for a tenant"""
    db_path = f"/data/tenants/{req.tenant_id}.duckdb"
    
    if not os.path.exists(db_path):
        raise HTTPException(status_code=404, detail="Tenant not found")
    
    try:
        con = duckdb.connect(db_path)
        result = con.execute(req.sql).fetchdf()
        con.close()
        return {"columns": result.columns.tolist(), "data": result.values.tolist()}
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))

@app.get("/health")
def health_check():
    return {"status": "healthy", "server": "duckdb-v2.0"}

import duckdb
import pandas as pd
from pathlib import Path

def setup_tenant(tenant_id: str, csv_files: list[str]):
    """Initialize a new tenant's database and import data"""
    db_path = Path(f"/data/tenants/{tenant_id}.duckdb")
    
    with duckdb.connect(str(db_path)) as con:
        con.execute("CREATE SCHEMA IF NOT EXISTS sales")
        con.execute("CREATE SCHEMA IF NOT EXISTS customers")
        
        for pattern in csv_files:
            table_name = Path(pattern).stem
            con.execute(f"""
                CREATE TABLE sales.{table_name} AS 
                SELECT * FROM read_csv_auto('{pattern}')
            """)
        
        # Create pre-aggregated view for faster queries
        con.execute("""
            CREATE MATERIALIZED VIEW sales.monthly_summary AS
            SELECT 
                DATE_TRUNC('month', order_date) AS month,
                product_category,
                SUM(revenue) AS total_revenue,
                COUNT(*) AS order_count
            FROM sales.orders
            GROUP BY DATE_TRUNC('month', order_date), product_category
        """)
    
    print(f"✅ Tenant {tenant_id} initialized")

# Bulk initialize tenants
import glob
for tenant_dir in glob.glob("/data/new_tenants/*"):
    tenant_id = Path(tenant_dir).name
    csv_files = glob.glob(f"{tenant_dir}/*.csv")
    setup_tenant(tenant_id, csv_files)