HTTP Server DuckDB Extension

HTTP Server
REST API
Web Services
API Development
Data Services

This extension transforms DuckDB instances into tiny multi-player HTTP OLAP API services.

Supports Authentication (Basic Auth or X-Token) and includes the play SQL user interface.

Features

  • Turn any DuckDB instance into an HTTP OLAP API Server
  • Use the embedded Play User Interface to query and visualize data
  • Pair with chsql extension for ClickHouse flavoured SQL
  • Work with local and remote datasets.

Extension Functions

  • httpserve_start(host, port, auth): starts the server using provided parameters
  • httpserve_stop(): stops the server thread

Notes

🛑 Run DuckDB in -readonly mode for enhanced security

📦 Installation

INSTALL httpserver FROM community;
LOAD httpserver;

🔌 Usage

Start the HTTP server providing the host, port and auth parameters.
> * If you want no authentication, just pass an empty string as parameter.
> * If you want the API run in foreground set DUCKDB_HTTPSERVER_FOREGROUND=1 > * If you want logs set DUCKDB_HTTPSERVER_DEBUG or DUCKDB_HTTPSERVER_SYSLOG

Basic Auth

D SELECT httpserve_start('localhost', 9999, 'user:pass');

┌───────────────────────────────────────────────┐
│ httpserve_start('0.0.0.0', 9999, 'user:pass') │
│                    varchar                    │
├───────────────────────────────────────────────┤
│ HTTP server started on 0.0.0.0:9999           │
└───────────────────────────────────────────────┘
curl -X POST -d "SELECT 'hello', version()" "http://user:pass@localhost:9999/"

Token Auth

SELECT httpserve_start('localhost', 9999, 'supersecretkey');

┌───────────────────────────────────────────────┐
│ httpserve_start('0.0.0.0', 9999, 'secretkey') │
│                    varchar                    │
├───────────────────────────────────────────────┤
│ HTTP server started on 0.0.0.0:9999           │
└───────────────────────────────────────────────┘

Query your endpoint using the X-API-Key token:

curl -X POST --header "X-API-Key: secretkey" -d "SELECT 'hello', version()" "http://localhost:9999/"

You can perform the same action from DuckDB using HTTP extra_http_headers:

D CREATE SECRET extra_http_headers (
      TYPE HTTP,
      EXTRA_HTTP_HEADERS MAP{
          'X-API-Key': 'secret'
      }
  );

D SELECT * FROM duck_flock('SELECT version()', ['http://localhost:9999']);
┌─────────────┐
│ "version"() │
│   varchar   │
├─────────────┤
│ v1.1.3      │
└─────────────┘

👉 QUERY UI

Browse to your endpoint and use the built-in quackplay interface.

👉 QUERY API

Query your API endpoint using curl GET/POST requests

curl -X POST -d "SELECT 'hello', version()" "http://localhost:9999/?default_format=JSONCompact"
{
  "meta": [
    {
      "name": "'hello'",
      "type": "String"
    },
    {
      "name": "\"version\"()",
      "type": "String"
    }
  ],
  "data": [
    [
      "hello",
      "v1.1.3"
    ]
  ],
  "rows": 1,
  "statistics": {
    "elapsed": 0.01,
    "rows_read": 1,
    "bytes_read": 0
  }
}

👉 CROSS-OVER EXAMPLES

You can now have DuckDB instances query each other and… themselves!

D LOAD json;
D LOAD httpfs;
D SELECT httpserve_start('0.0.0.0', 9999, '');
┌─────────────────────────────────────┐
│  httpserve_start('0.0.0.0', 9999)   │
│               varchar               │
├─────────────────────────────────────┤
│ HTTP server started on 0.0.0.0:9999 │
└─────────────────────────────────────┘
D SELECT * FROM read_json_auto('http://localhost:9999/?q=SELECT version()');
┌─────────────┐
│ "version"() │
│   varchar   │
├─────────────┤
│ v1.1.3      │
└─────────────┘

Flock Macro by @carlopi

Check out this flocking macro from fellow Italo-Amsterdammer @carlopi @ DuckDB Labs

  • a DuckDB CLI, running httpserver extension
  • a DuckDB from Python, running httpserver extension
  • a DuckDB from the Web, querying all 3 DuckDB at the same time

API Documentation

Endpoints Overview

Endpoint Methods Description
/ GET, POST Query API endpoint
/ping GET Health check endpoint

Detailed Endpoint Specifications

Query API

Methods: GET, POST

Parameters:

Parameter Description Supported Values
default_format Specifies the output format JSONEachRow, JSONCompact
query The DuckDB SQL query to execute Any valid DuckDB SQL query
Notes
  • Ensure that your queries are properly formatted and escaped when sending them as part of the request.
  • The root endpoint (/) supports both GET and POST methods, but POST is recommended for complex queries or when the query length exceeds URL length limitations.
  • Always specify the default_format parameter to ensure consistent output formatting.

License

MIT Licensed

Love ❤️ this DuckDB extension? You’ll Love This.

Get the best from Query.Farm — smart tips, powerful tools, and project updates sent directly to your inbox, but only when we’ve got something great to share.