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.

The extension goal is to replace the functionality currently offered by quackpipe

• 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 including MotherDuck 🐤
• 100% Opensource, ready to use and extend by the Community!

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

🛑 Run DuckDB in -readonly mode for enhanced security

INSTALL httpserver FROM community;
LOAD httpserver;

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

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/"

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      │
└─────────────┘

Browse to your endpoint and use the built-in quackplay interface (experimental)

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
  }
}

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      │
└─────────────┘

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

Methods: GET, POST

Parameters:

• 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.