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 parametershttpserve_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_DEBUGorDUCKDB_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
| Endpoint | Methods | Description |
|---|---|---|
/ |
GET, POST | Query API endpoint |
/ping |
GET | Health check endpoint |
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 |
- 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_formatparameter to ensure consistent output formatting.
Clone the repository and all its submodules
git clone <your-fork-url>
git submodule update --init --recursiveOpening project:
Configuring CLion with the extension template requires a little work. Firstly, make sure that the DuckDB submodule is available.
Then make sure to open ./duckdb/CMakeLists.txt (so not the top level CMakeLists.txt file from this repo) as a project in CLion.
Now to fix your project path go to tools->CMake->Change Project Root(docs) to set the project root to the root dir of this repo.
Debugging:
To set up debugging in CLion, there are two simple steps required. Firstly, in CLion -> Settings / Preferences -> Build, Execution, Deploy -> CMake you will need to add the desired builds (e.g. Debug, Release, RelDebug, etc). There's different ways to configure this, but the easiest is to leave all empty, except the build path, which needs to be set to ../build/{build type}. Now on a clean repository you will first need to run make {build type} to initialize the CMake build directory. After running make, you will be able to (re)build from CLion by using the build target we just created. If you use the CLion editor, you can create a CLion CMake profiles matching the CMake variables that are described in the makefile, and then you don't need to invoke the Makefile.
The second step is to configure the unittest runner as a run/debug configuration. To do this, go to Run -> Edit Configurations and click + -> Cmake Application. The target and executable should be unittest. This will run all the DuckDB tests. To specify only running the extension specific tests, add --test-dir ../../.. [sql] to the Program Arguments. Note that it is recommended to use the unittest executable for testing/development within CLion. The actual DuckDB CLI currently does not reliably work as a run target in CLion.
To run the E2E test install all packages necessary:
pip install -r requirements.txtThen run the test suite:
pytest pytest test_http_api