REST API
The QuestDB REST API is based on standard HTTP features and is understood by off-the-shelf HTTP clients. It provides a simple way to interact with QuestDB and is compatible with most programming languages. API functions are fully keyed on the URL and they use query parameters as their arguments.
The Web Console is the official Web client for QuestDB, that relies on the REST API.
Available methods
/impfor importing data from.CSVfiles/executeto execute a SQL statement, previously named/exec/validateto compile a SQL statement without running it/expto export data
Browser requests and reverse proxies
QuestDB rejects requests that another website sends through a visitor's
browser, even when the request carries valid credentials. The
HTTP browser check
uses Sec-Fetch-Site and Origin, and applies to both Open Source and
Enterprise.
For a trusted web application on another origin, list its origin in
http.browser.allowed.origins.
This allows the request through QuestDB's browser check; it does not enable
CORS. A cross-origin REST integration still needs a proxy that adds the
appropriate CORS response headers and handles preflight requests. QWP
WebSockets use a separate origin setting.
Links and embedded resources on other sites normally send no Origin
header. Over HTTPS and on localhost, these requests get 403 Forbidden, even
if the referring site's origin is listed. For example, a link to an /exp
URL on a wiki page is rejected. Paste the API URL into the address bar or open
it from a bookmark instead. Links to the Web Console's static pages still
work.
Reverse proxies
When the Web Console is served over plain HTTP at an address other than
localhost, a proxy that rewrites Host can cause CSV imports and settings
saves to fail with 403 Forbidden. The browser's Origin no longer matches
the Host QuestDB receives. Preserve the browser's Host, including its
port. In nginx, add this to the location block that proxies QuestDB:
proxy_set_header Host $http_host;
Alternatively, list the console's origin in http.browser.allowed.origins.
Without authentication, the host name received by QuestDB must also pass
http.allowed.hosts.
Behind a TLS-terminating proxy, current browsers report the console's
requests as Sec-Fetch-Site: same-origin, which takes precedence over the
HTTPS Origin not matching QuestDB's plain HTTP connection. Preserve this
header. The QWP-specific
qwp.browser.tls.termination.enabled
setting does not change REST request checks.
Browsers send Sec-Fetch-Site to HTTPS and localhost URLs, but not to remote
plain-HTTP URLs. A GET from a link or image on another site can carry neither
Sec-Fetch-Site nor Origin when it targets a remote plain-HTTP address, and
passes the browser check. Serve QuestDB over HTTPS and enable authentication
when browsers on other machines can reach it. These checks are not access
control: non-browser clients can omit or forge the headers.
Examples
QuestDB exposes a REST API for compatibility with a wide range of libraries and
tools. The REST API is accessible on port 9000 and has the following
insert-capable entrypoints:
| Entrypoint | HTTP Method | Description | API Docs |
|---|---|---|---|
/imp | POST | Import CSV data | Reference |
/api/v1/sql/execute?query=.. | GET | Run SQL Query returning JSON result set | Reference |
For details such as content type, query parameters and more, refer to the REST API docs.
/imp: Uploading Tabular Data
Let's assume you want to upload the following data via the /imp entrypoint:
- CSV
- Table
col1,col2,col3
a,10.5,True
b,100,False
c,,True
| col1 | col2 | col3 |
|---|---|---|
| a | 10.5 | true |
| b | 100 | false |
| c | NULL | true |
You can do so via the command line using cURL or programmatically via HTTP
APIs in your scripts and applications.
By default, the response is designed to be human-readable. Use the fmt=json
query argument to obtain a response in JSON. You can also specify the schema
explicitly. See the second example in Python for these features.
- cURL
- Python
- NodeJS
- Go
This example imports a CSV file with automatic schema detection.
curl -F data=@data.csv http://localhost:9000/imp?name=table_name
This example overwrites an existing table and specifies a timestamp format and a designated timestamp column. For more information on the optional parameters to specify timestamp formats, partitioning and renaming tables, see the REST API documentation.
curl \
-F schema='[{"name":"ts", "type": "TIMESTAMP", "pattern": "yyyy-MM-dd - HH:mm:ss"}]' \
-F data=@weather.csv 'http://localhost:9000/imp?overwrite=true×tamp=ts'
This first example shows uploading the data.csv file with automatic schema
detection.
import sys
import requests
csv = {'data': ('my_table', open('./data.csv', 'r'))}
host = 'http://localhost:9000'
try:
response = requests.post(host + '/imp', files=csv)
print(response.text)
except requests.exceptions.RequestException as e:
print(f'Error: {e}', file=sys.stderr)
The second example creates a CSV buffer from Python objects and uploads them with a custom schema. Note UTF-8 encoding.
The fmt=json parameter allows us to obtain a parsable response, rather than a
tabular response designed for human consumption.
import io
import csv
import requests
import pprint
import json
def to_csv_str(table):
output = io.StringIO()
csv.writer(output, dialect='excel').writerows(table)
return output.getvalue().encode('utf-8')
def main():
table_name = 'example_table2'
table = [
['col1', 'col2', 'col3'],
['a', 10.5, True],
['b', 100, False],
['c', None, True]]
table_csv = to_csv_str(table)
print(table_csv)
schema = json.dumps([
{'name': 'col1', 'type': 'SYMBOL'},
{'name': 'col2', 'type': 'DOUBLE'},
{'name': 'col3', 'type': 'BOOLEAN'}])
response = requests.post(
'http://localhost:9000/imp',
params={'fmt': 'json'},
files={
'schema': schema,
'data': (table_name, table_csv)}).json()
# You can parse the `status` field and `error` fields
# of individual columns. See Reference/API/REST docs for details.
pprint.pprint(response)
if __name__ == '__main__':
main()
const fetch = require("node-fetch")
const FormData = require("form-data")
const fs = require("fs")
const HOST = "http://127.0.0.1:9000"
async function run() {
const form = new FormData()
form.append("data", fs.readFileSync(__dirname + "/data.csv"), {
filename: "data.csv",
contentType: "application/octet-stream",
})
try {
const r = await fetch(`${HOST}/imp`, {
method: "POST",
body: form,
headers: form.getHeaders(),
})
console.log(r)
} catch (e) {
console.error(e)
}
}
run()
package main
import (
"bytes"
"fmt"
"io"
"io/ioutil"
"log"
"mime/multipart"
"net/http"
"net/url"
"os"
)
func main() {
u, err := url.Parse("http://localhost:9000")
checkErr(err)
u.Path += "imp"
url := fmt.Sprintf("%v", u)
fileName := "/path/to/data.csv"
file, err := os.Open(fileName)
checkErr(err)
defer file.Close()
buf := new(bytes.Buffer)
writer := multipart.NewWriter(buf)
uploadFile, _ := writer.CreateFormFile("data", "data.csv")
_, err = io.Copy(uploadFile, file)
checkErr(err)
writer.Close()
req, err := http.NewRequest(http.MethodPut, url, buf)
checkErr(err)
req.Header.Add("Content-Type", writer.FormDataContentType())
client := &http.Client{}
res, err := client.Do(req)
checkErr(err)
defer res.Body.Close()
body, err := ioutil.ReadAll(res.Body)
checkErr(err)
log.Println(string(body))
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}
/execute: SQL INSERT Query
The /execute entrypoint takes a SQL query and returns results as
JSON.
We can use this for quick SQL inserts too, but note that there's no support for parameterized queries that are necessary to avoid SQL injection issues. Prefer InfluxDB Line Protocol if you need high-performance inserts.
- cURL
- Python
- NodeJS
- Go
# Create Table
curl -G \
--data-urlencode "query=CREATE TABLE IF NOT EXISTS trades(name STRING, value INT)" \
http://localhost:9000/api/v1/sql/execute
# Insert a row
curl -G \
--data-urlencode "query=INSERT INTO trades VALUES('abc', 123456)" \
http://localhost:9000/api/v1/sql/execute
import sys
import requests
import json
host = 'http://localhost:9000'
def run_query(sql_query):
query_params = {'query': sql_query, 'fmt' : 'json'}
try:
response = requests.get(host + '/api/v1/sql/execute', params=query_params)
json_response = json.loads(response.text)
print(json_response)
except requests.exceptions.RequestException as e:
print(f'Error: {e}', file=sys.stderr)
# create table
run_query("CREATE TABLE IF NOT EXISTS trades (name STRING, value INT)")
# insert row
run_query("INSERT INTO trades VALUES('abc', 123456)")
The node-fetch package can be installed using npm i node-fetch.
const fetch = require("node-fetch")
const HOST = "http://127.0.0.1:9000"
async function createTable() {
try {
const query = "CREATE TABLE IF NOT EXISTS trades (name STRING, value INT)"
const response = await fetch(
`${HOST}/api/v1/sql/execute?query=${encodeURIComponent(query)}`,
)
const json = await response.json()
console.log(json)
} catch (error) {
console.log(error)
}
}
async function insertData() {
try {
const query = "INSERT INTO trades VALUES('abc', 123456)"
const response = await fetch(
`${HOST}/api/v1/sql/execute?query=${encodeURIComponent(query)}`,
)
const json = await response.json()
console.log(json)
} catch (error) {
console.log(error)
}
}
createTable().then(insertData)
package main
import (
"fmt"
"io/ioutil"
"log"
"net/http"
"net/url"
)
func main() {
u, err := url.Parse("http://localhost:9000")
checkErr(err)
u.Path += "api/v1/sql/execute"
params := url.Values{}
params.Add("query", `
CREATE TABLE IF NOT EXISTS
trades (name STRING, value INT);
INSERT INTO
trades
VALUES(
"abc",
123456
);
`)
u.RawQuery = params.Encode()
url := fmt.Sprintf("%v", u)
res, err := http.Get(url)
checkErr(err)
defer res.Body.Close()
body, err := ioutil.ReadAll(res.Body)
checkErr(err)
log.Println(string(body))
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}
/exec - Execute queries
/exec is the older name for /execute, documented immediately
below. It is still supported and takes the same parameters and returns the same
response. Both paths follow the same
HTTP request checks. Use /execute
for new work.
/execute - Execute queries
/execute compiles and executes the SQL query supplied as a parameter and
returns a JSON response. It was previously named
/exec.
Its full path is /api/v1/sql/execute.
The query execution terminates automatically when the socket connection is closed.
Overview
Parameters
/execute expects an HTTP GET request with the following query
parameters. POST is not supported and returns 405.
| Parameter | Required | Default | Description |
|---|---|---|---|
count | No | false | true or false. Counts the number of rows and returns this value. |
limit | No | Allows limiting the number of rows to return. limit=10 will return the first 10 rows (equivalent to limit=1,10), limit=10,20 will return row numbers 10 through to 20 inclusive. | |
nm | No | false | true or false. Skips the metadata section of the response when set to true. |
query | Yes | URL encoded query text. It can be multi-line. | |
timings | No | false | true or false. When set to true, QuestDB will also include a timings property in the response which gives details about the execution times. |
explain | No | false | true or false. When set to true, QuestDB will also include an explain property in the response which gives details about the execution plan. |
quoteLargeNum | No | false | true or false. When set to true, QuestDB will surround LONG type numbers with double quotation marks that will make them parsed as strings. |
The parameters must be URL encoded.
Headers
Supported HTTP headers:
| Header | Required | Description |
|---|---|---|
Statement-Timeout | No | Query timeout in milliseconds, overrides default timeout from server.conf |
Examples
SELECT query example:
curl -G \
--data-urlencode "query=SELECT timestamp, price FROM trades LIMIT 2;" \
--data-urlencode "count=true" \
http://localhost:9000/api/v1/sql/execute
A HTTP status code of 200 is returned with the following response body:
{
"query": "SELECT timestamp, price FROM trades LIMIT 2;",
"columns": [
{
"name": "timestamp",
"type": "TIMESTAMP"
},
{
"name": "price",
"type": "DOUBLE"
}
],
"timestamp": 0,
"dataset": [
["2024-01-01T00:00:00.000000Z", 142.5],
["2024-01-01T00:00:01.000000Z", 142.75]
],
"count": 2
}
SELECT query returns response in the following format:
{
"query": string,
"columns": Array<{ "name": string, "type": string }>
"dataset": Array<Array<Value for Column1, Value for Column2>>,
"timestamp": number,
"count": Optional<number>,
"timings": Optional<{ compiler: number, count: number, execute: number }>,
"explain": Optional<{ jitCompiled: boolean }>
}
You can find the exact list of column types in the dedicated page.
The timestamp field indicates which of the columns in the result set is the
designated timestamp, or -1 if there isn't one.
limit bounds the rows returned in dataset, while count reports how many
rows the query matched in total.
UPDATE query example:
This request executes an update of table weather setting 2 minutes query
timeout
curl -G \
-H "Statement-Timeout: 120000" \
--data-urlencode "query=UPDATE weather SET tempF = tempF + 0.12 WHERE tempF > 60" \
http://localhost:9000/api/v1/sql/execute
A HTTP status code of 200 is returned with the following response body:
{
"ddl": "OK",
"updated": 34
}
CREATE TABLE query example:
This request creates a basic table, with a designated timestamp.
curl -G \
-H "Statement-Timeout: 120000" \
--data-urlencode "query=CREATE TABLE foo ( a INT, ts TIMESTAMP) timestamp(ts)" \
http://localhost:9000/api/v1/sql/execute
A HTTP status code of 200 is returned with the following response body:
{
"ddl": "OK"
}
/exp - Export data
This endpoint allows you to pass url-encoded queries but the request body is returned in a tabular form to be saved and reused as opposed to JSON.
Overview
/exp is expecting an HTTP GET request with following parameters:
| Parameter | Required | Description |
|---|---|---|
query | Yes | URL encoded query text. It can be multi-line. |
limit | No | Paging opp parameter. For example, limit=10,20 will return row numbers 10 through to 20 inclusive and limit=20 will return first 20 rows, which is equivalent to limit=0,20. limit=-20 will return the last 20 rows. |
nm | No | true or false. Skips the metadata section of the response when set to true. |
fmt | No | Export format. Valid values: parquet, csv. When set to parquet, exports data in Parquet format instead of CSV. |
timeout | No | Query timeout in seconds. Overrides the default timeout. For Parquet exports, the default is the server's export timeout. For CSV exports, the default is the general query timeout. |
Parquet Export Parameters
When fmt=parquet, the following additional parameters are supported:
| Parameter | Required | Default | Description |
|---|---|---|---|
partition_by | No | NONE | Partition unit: NONE, HOUR, DAY, WEEK, MONTH, or YEAR. |
compression_codec | No | ZSTD | Compression algorithm: UNCOMPRESSED, SNAPPY, GZIP, LZ4, ZSTD, LZ4_RAW, BROTLI, LZO. |
compression_level | No | 9 | Compression level (codec-specific). Higher values = better compression but slower. |
row_group_size | No | 100000 | Number of rows per Parquet row group. |
data_page_size | No | 1048576 | Size of data pages in bytes (default 1MB). |
statistics_enabled | No | true | Enable Parquet column statistics: true or false. |
parquet_version | No | 2 | Parquet format version: 1 (v1.0) or 2 (v2.0). |
raw_array_encoding | No | false | Use raw encoding for arrays: true (lighter-weight, less compatible) or false (heavier-weight, more compatible) |
bloom_filter_columns | No | (none) | Comma-separated column names for bloom filter generation. |
bloom_filter_fpp | No | 0.01 | False positive probability for bloom filters (0.0 to 1.0). Lower values produce larger but more accurate filters. |
The parameters must be URL encoded.
Examples
CSV Export (default)
Considering the query:
curl -G \
--data-urlencode "query=SELECT AccidentIndex2, Date, Time FROM 'Accidents0514.csv'" \
--data-urlencode "limit=5" \
http://localhost:9000/exp
A HTTP status code of 200 is returned with the following response body:
"AccidentIndex","Date","Time"
200501BS00001,"2005-01-04T00:00:00.000Z",17:42
200501BS00002,"2005-01-05T00:00:00.000Z",17:36
200501BS00003,"2005-01-06T00:00:00.000Z",00:15
200501BS00004,"2005-01-07T00:00:00.000Z",10:35
200501BS00005,"2005-01-10T00:00:00.000Z",21:13
Parquet Export
Export query results to Parquet format:
curl -G \
--data-urlencode "query=SELECT * FROM trades WHERE timestamp IN today()" \
--data-urlencode "fmt=parquet" \
http://localhost:9000/exp > trades_today.parquet
Parquet Export with Custom Options
Export with custom compression and partitioning:
curl -G \
--data-urlencode "query=SELECT * FROM trades" \
--data-urlencode "fmt=parquet" \
--data-urlencode "partition_by=DAY" \
--data-urlencode "compression_codec=ZSTD" \
--data-urlencode "compression_level=9" \
--data-urlencode "row_group_size=1000000" \
http://localhost:9000/exp > trades.parquet
Parquet Export with LZ4 Compression
Export with LZ4_RAW compression for faster export:
curl -G \
--data-urlencode "query=SELECT symbol, price, amount FROM trades WHERE timestamp > dateadd('h', -1, now())" \
--data-urlencode "fmt=parquet" \
--data-urlencode "compression_codec=LZ4_RAW" \
http://localhost:9000/exp > recent_trades.parquet
Parquet Export with Bloom Filters
Export with bloom filters for faster equality lookups on the exported file:
curl -G \
--data-urlencode "query=SELECT * FROM trades" \
--data-urlencode "fmt=parquet" \
--data-urlencode "bloom_filter_columns=symbol,side" \
http://localhost:9000/exp > trades_bloom.parquet
/imp - Import data
For a complete guide including text loader configuration and troubleshooting, see CSV Import.
/imp streams tabular text data directly into a table. It supports CSV, TAB and
pipe (|) delimited inputs with optional headers. There are no restrictions on
data size. Data types and structures are detected automatically, without
additional configuration. In some cases, additional configuration can be
provided to improve the automatic detection as described in
user-defined schema.
The structure detection algorithm analyses the chunk in the beginning of the file and relies on relative uniformity of data. When the first chunk is non-representative of the rest of the data, automatic imports can yield errors.
If the data follows a uniform pattern, the number of lines which are analyzed
for schema detection can be reduced to improve performance during uploads using
the http.text.analysis.max.lines key. Usage of this setting is described in
the HTTP server configuration documentation.
URL parameters
/imp is expecting an HTTP POST request using the multipart/form-data
Content-Type with following optional URL parameters which must be URL encoded:
| Parameter | Required | Default | Description |
|---|---|---|---|
atomicity | No | skipCol | abort, skipRow or skipCol. Behaviour when an error is detected in the data. abort: the entire file will be skipped. skipRow: the row is skipped. skipCol: the column is skipped. |
delimiter | No | URL encoded delimiter character. When set, import will try to detect the delimiter automatically. Since automatic delimiter detection requires at least two lines (rows) to be present in the file, this parameter may be used to allow single line file import. | |
fmt | No | tabular | Can be set to json to get the response formatted as such. |
forceHeader | No | false | true or false. When false, QuestDB will try to infer if the first line of the file is the header line. When set to true, QuestDB will expect that line to be the header line. |
name | No | Name of the file | Name of the table to create, see below. |
overwrite | No | false | true or false. When set to true, any existing data or structure will be overwritten. |
partitionBy | No | NONE | See partitions. |
o3MaxLag | No | Sets upper limit on the created table to be used for the in-memory out-of-order buffer. Can be also set globally via the cairo.o3.max.lag configuration property. | |
maxUncommittedRows | No | Maximum number of uncommitted rows to be set for the created table. When the number of pending rows reaches this parameter on a table, a commit will be issued. Can be also set globally via the cairo.max.uncommitted.rows configuration property. | |
skipLev | No | false | true or false. Skip “Line Extra Values”, when set to true, the parser will ignore those extra values rather than ignoring entire line. An extra value is something in addition to what is defined by the header. |
timestamp | No | Name of the column that will be used as a designated timestamp. | |
create | No | true | true or false. When set to false, QuestDB will not automatically create a table 'name' if one does not exist, and will return an error instead. |
If you experience large latencies when importing big CSV files with out-of-order
timestamps, try increasing maxUncommittedRows parameter from the default
500,000 value.
curl -F data=@weather.csv \
'http://localhost:9000/imp?overwrite=true&name=new_table×tamp=ts&partitionBy=MONTH'
Further example queries with context on the source CSV file contents relative and the generated tables are provided in the examples section below.
Names
Table and column names are subject to restrictions, the following list of characters are automatically removed:
[whitespace]
.
?
,
:
\
/
\\
\0
)
(
_
+
-
*
~
%
When the header row is missing, column names are generated automatically.
Consistency guarantees
/imp benefits from the properties of the QuestDB
storage engine,
although Atomicity and Durability can be relaxed to meet convenience and
performance demands.
Atomicity
QuestDB is fully insured against any connection problems. If the server detects
closed socket(s), the entire request is rolled back instantly and transparently
for any existing readers. The only time data can be partially imported is when
atomicity is in relaxed mode and data cannot be converted to column type. In
this scenario, any "defective" row of data is discarded and /imp continues to
stream request data into table.
Consistency
This property is guaranteed by consistency of append transactions against QuestDB storage engine.
Isolation
Data is committed to QuestDB storage engine at end of request. Uncommitted transactions are not visible to readers.
Durability
/imp streams data from network socket buffer directly into memory mapped
files. At this point data is handed over to the OS and is resilient against
QuestDB internal errors and unlikely but hypothetically possible crashes. This
is default method of appending data and it is chosen for its performance
characteristics.
Examples
Automatic schema detection
The following example uploads a file ratings.csv which has the following
contents:
| ts | visMiles | tempF | dewpF |
|---|---|---|---|
| 2010-01-01T00:00:00.000000Z | 8.8 | 34 | 30 |
| 2010-01-01T00:51:00.000000Z | 9.100000000000 | 34 | 30 |
| 2010-01-01T01:36:00.000000Z | 8.0 | 34 | 30 |
| ... | ... | ... | ... |
An import can be performed with automatic schema detection with the following request:
curl -F data=@weather.csv 'http://localhost:9000/imp'
A HTTP status code of 200 will be returned and the response will be:
+-------------------------------------------------------------------------------+
| Location: | weather.csv | Pattern | Locale | Errors |
| Partition by | NONE | | | |
| Timestamp | NONE | | | |
+-------------------------------------------------------------------------------+
| Rows handled | 49976 | | | |
| Rows imported | 49976 | | | |
+-------------------------------------------------------------------------------+
| 0 | ts | TIMESTAMP | 0 |
| 1 | visMiles | DOUBLE | 0 |
| 2 | tempF | INT | 0 |
| 3 | dewpF | INT | 0 |
+-------------------------------------------------------------------------------+
User-defined schema
To specify the schema of a table, a schema object can be provided:
curl \
-F schema='[{"name":"dewpF", "type": "STRING"}]' \
-F data=@weather.csv 'http://localhost:9000/imp'
+------------------------------------------------------------------------------+
| Location: | weather.csv | Pattern | Locale | Errors |
| Partition by | NONE | | | |
| Timestamp | NONE | | | |
+------------------------------------------------------------------------------+
| Rows handled | 49976 | | | |
| Rows imported | 49976 | | | |
+------------------------------------------------------------------------------+
| 0 | ts | TIMESTAMP | 0 |
| 1 | visMiles | DOUBLE | 0 |
| 2 | tempF | INT | 0 |
| 3 | dewpF | STRING | 0 |
+------------------------------------------------------------------------------+
Non-standard timestamp formats
Given a file weather.csv with the following contents which contains a
timestamp with a non-standard format:
| ts | visMiles | tempF | dewpF |
|---|---|---|---|
| 2010-01-01 - 00:00:00 | 8.8 | 34 | 30 |
| 2010-01-01 - 00:51:00 | 9.100000000000 | 34 | 30 |
| 2010-01-01 - 01:36:00 | 8.0 | 34 | 30 |
| ... | ... | ... | ... |
The file can be imported as usual with the following request:
curl -F data=@weather.csv 'http://localhost:9000/imp'
A HTTP status code of 200 will be returned and the import will be successful,
but the timestamp column is detected as a VARCHAR type:
+-------------------------------------------------------------------------------+
| Location: | weather.csv | Pattern | Locale | Errors |
| Partition by | NONE | | | |
| Timestamp | NONE | | | |
+-------------------------------------------------------------------------------+
| Rows handled | 49976 | | | |
| Rows imported | 49976 | | | |
+-------------------------------------------------------------------------------+
| 0 | ts | VARCHAR | 0 |
| 1 | visMiles | DOUBLE | 0 |
| 2 | tempF | INT | 0 |
| 3 | dewpF | INT | 0 |
+-------------------------------------------------------------------------------+
To amend the timestamp column type, this example curl can be used which has a
schema JSON object to specify that the ts column is of TIMESTAMP type with
the pattern yyyy-MM-dd - HH:mm:ss
Additionally, URL parameters are provided:
overwrite=trueto overwrite the existing tabletimestamp=tsto specify that thetscolumn is the designated timestamp column for this tablepartitionBy=MONTHto set a partitioning strategy on the table byMONTH
curl \
-F schema='[{"name":"ts", "type": "TIMESTAMP", "pattern": "yyyy-MM-dd - HH:mm:ss"}]' \
-F data=@weather.csv \
'http://localhost:9000/imp?overwrite=true×tamp=ts&partitionBy=MONTH'
The HTTP status code will be set to 200 and the response will show 0 errors
parsing the timestamp column:
+------------------------------------------------------------------------------+
| Location: | weather.csv | Pattern | Locale | Errors |
| Partition by | MONTH | | | |
| Timestamp | ts | | | |
+------------------------------------------------------------------------------+
| Rows handled | 49976 | | | |
| Rows imported | 49976 | | | |
+------------------------------------------------------------------------------+
| 0 | ts | TIMESTAMP | 0 |
| 1 | visMiles | DOUBLE | 0 |
| 2 | tempF | INT | 0 |
| 3 | dewpF | INT | 0 |
+------------------------------------------------------------------------------+
JSON response
If you intend to upload CSV programmatically, it's easier to parse the response
as JSON. Set fmt=json query argument on the request.
Here's an example of a successful response:
{
"status": "OK",
"location": "example_table",
"rowsRejected": 0,
"rowsImported": 3,
"header": false,
"columns": [
{ "name": "col1", "type": "SYMBOL", "size": 4, "errors": 0 },
{ "name": "col2", "type": "DOUBLE", "size": 8, "errors": 0 },
{ "name": "col3", "type": "BOOLEAN", "size": 1, "errors": 0 }
]
}
Here is an example with request-level errors:
{
"status": "not enough lines [table=example_table]"
}
Here is an example with column-level errors due to unsuccessful casts:
{
"status": "OK",
"location": "example_table2",
"rowsRejected": 0,
"rowsImported": 3,
"header": false,
"columns": [
{ "name": "col1", "type": "DOUBLE", "size": 8, "errors": 3 },
{ "name": "col2", "type": "SYMBOL", "size": 4, "errors": 0 },
{ "name": "col3", "type": "BOOLEAN", "size": 1, "errors": 0 }
]
}
/validate - Validate queries
/validate compiles a SQL query and returns the metadata of the result set it
would produce, without executing it. Use it to check that a statement parses and
to discover its column names and types up front, without paying the cost of
running the query.
Its full path is /api/v1/sql/validate.
It takes the same query parameter as /execute and returns the
same response, minus the dataset and count fields:
curl -G \
--data-urlencode "query=SELECT 1 AS n" \
http://localhost:9000/api/v1/sql/validate
{
"query": "SELECT 1 AS n",
"columns": [{ "name": "n", "type": "INT" }],
"timestamp": -1
}
A statement that fails to compile returns HTTP 400 with an error message
and the character position at which compilation failed:
{
"query": "SELECT nonexistent_col FROM nothing_here",
"error": "table does not exist [table=nothing_here]",
"position": 28
}
Error responses
HTTP request rejections
The HTTP browser and host checks return 403 Forbidden with a plain-text
reason, rather than the SQL error JSON described below:
| Message starts with | Cause and action |
|---|---|
host name rejected | HTTP authentication is disabled and the request's Host is not accepted. Add the name to http.allowed.hosts. This also affects scripts, ingestion clients, and monitoring probes. |
cross-site browser request rejected | Sec-Fetch-Site is neither same-origin nor none. List a trusted application's Origin in http.browser.allowed.origins. If the message says there is no Origin, open the URL directly; the list cannot admit that request. |
cross-origin browser request rejected | There is no Sec-Fetch-Site, and Origin does not match the Host and connection scheme. Check the reverse proxy configuration or list the trusted application's origin. |
An endpoint that requires authentication returns 401 Unauthorized instead
when the request lacks valid credentials. Valid credentials do not bypass the
browser check.
Malformed queries
A successful call to /execute or /exp which also contains a
malformed query will return response bodies with the following format:
{
"query": string,
"error": string,
"position": number
}
The position field is the character number from the beginning of the string
where the error was found.
Considering the query:
curl -G \
--data-urlencode "query=SELECT * FROM table;" \
http://localhost:9000/exp
A HTTP status code of 400 is returned with the following response body:
{
"query": "SELECT * FROM table;",
"error": "function, literal or constant is expected",
"position": 8
}
Authentication
The REST API supports two authentication types:
- HTTP basic authentication, available in QuestDB Open Source and QuestDB Enterprise.
- Token-based authentication, available in QuestDB Enterprise only.
Role-based Access Control (RBAC) is available in QuestDB Enterprise. On QuestDB Enterprise, prefer token-based authentication over basic authentication.
HTTP basic authentication
Basic authentication is the method web browsers use when they prompt for a username and password, and it is the only method available in QuestDB Open Source.
In QuestDB Open Source, enable it by setting the configuration options
http.user and http.password in server.conf:
http.user=my_user
http.password=my_password
In QuestDB Enterprise, basic authentication uses the password of an existing
database user, so there is nothing to enable in server.conf.
You can also apply the credentials programmatically in an Authorization: Basic
header. The curl flag -u builds that header for you. This example executes a
SELECT 1; query:
curl -G --data-urlencode "query=SELECT 1;" \
-u "my_user:my_password" \
http://localhost:9000/api/v1/sql/execute
Building the Authorization header manually
Not every HTTP client has an equivalent of curl's -u flag. In that case, build
the header value yourself: join the username and password with a colon, then
Base64-encode the result.
printf 'my_user:my_password' | base64
# bXlfdXNlcjpteV9wYXNzd29yZA==
Use printf rather than echo, which appends a newline that ends up encoded as
part of the password.
Send the encoded value using the Basic scheme:
curl -G --data-urlencode "query=SELECT 1;" \
-H "Authorization: Basic bXlfdXNlcjpteV9wYXNzd29yZA==" \
http://localhost:9000/api/v1/sql/execute
The colon is the delimiter, so the username cannot contain one. Passwords can, since everything after the first colon is treated as the password.
Base64 is an encoding, not encryption. Anyone who can read the header can recover the credentials, so only send basic authentication over HTTPS or on a trusted network.
Authentication via token in QuestDB Enterprise
Token-based authentication is the recommended method for programmatic access on QuestDB Enterprise. Unlike a password, a REST API token can be issued to a user or a service account, carries its own permission granularity, can be given an expiry, and can be revoked on its own without disrupting the user's other credentials.
Specify the token in an Authorization: Bearer header:
curl -G --data-urlencode "query=SELECT 1;" \
-H "Authorization: Bearer qt1cNK6s2t79f76GmTBN9k7XTWm5wwOtF7C0UBxiHGPn44" \
http://localhost:9000/api/v1/sql/execute
Refer to the user management page to learn more on how to generate a REST API token.
HTTP sessions
A client can authenticate once, then send a session cookie instead of its
credentials, as the Web Console does. To open a session, add session=true to
an authenticated request: QuestDB returns the session ID in the qdb_session
cookie. A later request that carries the cookie and no Authorization header
runs as the user who opened the session. To close the session, add
session=false to a request.
# authenticate with the credentials in QDB_USER and QDB_PASSWORD, open a
# session, and store its cookie in cookies.txt
curl -G -c cookies.txt -u "$QDB_USER:$QDB_PASSWORD" \
--data-urlencode "query=SELECT 1;" \
--data-urlencode "session=true" \
http://localhost:9000/api/v1/sql/execute
# send the cookie instead of the credentials
curl -G -b cookies.txt -c cookies.txt \
--data-urlencode "query=SELECT current_user();" \
http://localhost:9000/api/v1/sql/execute
QuestDB Open Source opens sessions only when http.user and http.password
are set. A session ends when it has been idle for
http.session.timeout,
30 minutes by default. QuestDB also replaces the session ID from time to time,
so store the cookie that each response sets, as -c does above.
Since QuestDB 10.1.0 and QuestDB Enterprise 4.1.0, a request that carries both
the cookie and an Authorization header is authenticated with the header.
When QuestDB rejects the credential, the request fails with
401 Unauthorized, and the session ends. Earlier versions served such a
request from the session, so a wrong password or an expired token kept working
for as long as the cookie did. Only the requests that QuestDB authorizes keep a
session alive, but a refused request with session=false still closes it. In
QuestDB Enterprise 4.1.0 and later, a session also ends when the credential
that opened it is revoked, as described in
HTTP sessions of the RBAC reference.