Insert data
This page shows how to insert data into QuestDB using different programming languages and tools.
#
OverviewQuestDB supports the following data ingestion methods:
InfluxDB Line Protocol: the recommended primary ingestion method in QuestDB for high-performance applications.
- Dedicated InfluxDB Line Protocol client libraries available
PostgreSQL wire protocol: interoperability with the PostgreSQL ecosystem.
- SQL
INSERT
andCOPY
statements, including parameterized queries. psql
on the command line- Support for most PostgreSQL keywords and functions
- SQL
HTTP REST API: compatibility with a wide range of libraries and tools.
- SQL
INSERT
for ad-hoc SQL queries curl
command and CSV file upload on the commend line- Accessing QuestDB via the Web Console:
- Code editor for SQL
INSERT
queries - SQL
COPY
for one-off database migration - CSV file upload for uploading batches of CSV files
- Code editor for SQL
- SQL
#
Recommended insert methodThe table below outlines the general recommendation for data ingestion based on the shape of the data and different scenarios:
#
One-off data importCSV Upload | SQL COPY (Web Console) | InfluxDB Line Protocol | PostgreSQL | |
---|---|---|---|---|
Sorted | โ | โ | ||
Lightly out of order | โ | |||
Heavily out of order | โ |
#
Periodic batch ingestCSV Upload | SQL COPY (Web Console) | InfluxDB Line Protocol | PostgreSQL | |
---|---|---|---|---|
Sorted | โ | โ | โ | |
Lightly out of order | โ | โ | โ | |
Heavily out of order | โ | โ | โ |
#
Real-time ingestCSV Upload | SQL COPY (Web Console) | InfluxDB Line Protocol | PostgreSQL | |
---|---|---|---|---|
Sorted | โ | |||
Lightly out of order | โ | |||
Heavily out of order | โ |
Lightly out of order data refers to data with the following traits:
- The expected lag is usually within a few minutes.
- The data is mostly sorted. Timestamps are growing in time with occasional exceptions that are within the lag.
Heavily out of order data refers to data with the following traits:
- The data is mostly unsorted.
- The data belongs to different parts of different partitions in an arbitrary manner.
#
InfluxDB Line Protocol (ILP)The InfluxDB Line Protocol is a text protocol over TCP on port 9009
.
It is a one-way protocol to insert data, focusing on simplicity and performance.
This interface is the preferred ingestion method as it provides the following benefits:
- High-throughput ingestion
- Robust ingestion from multiple sources into tables with dedicated systems for reducing congestion
- Supports on-the-fly, concurrent schema changes
On the InfluxDB line protocol page, you may find additional details on the message format, ports and authentication.
#
Client librariesThe Client Libraries provide user-friendly InfluxDB Line Protocol clients for a growing number of languages.
#
AuthenticationBy default, Open Source InfluxDB Line Protocol Server is unauthenticated. To configure authentication on the server, follow our server configuration guide. To configure authentication on the client, follow the relevant documentation section in the Client Libraries overview.
QuestDB Cloud servers are configured for authentication already. Snippets for all the supported languages can be found at https://cloud.questdb.com under the instance "Connect" tab.
#
ExamplesThese examples send a few rows of input. These use client libraries as well as raw TCP socket connections, when a client library is not available.
- Python
- Go
- Java
- NodeJS
- C#
- C
- C++
- Rust
- Ruby
- PHP
Java client library docs and Maven artifact.
Maven
<dependency>
<groupId>org.questdb</groupId>
<artifactId>questdb</artifactId>
<version>6.5</version>
</dependency>
Gradle
compile group: 'org.questdb', name: 'questdb', version: '6.5'
NodeJS client library repo.
#
TelegrafThe Telegraf guide helps you configure a Telegraf agent to collect and send metrics to QuestDB via InfluxDB Line Protocol.
#
PostgreSQL wire protocolQuestDB also supports the same wire protocol as PostgreSQL, allowing you to connect and query the database with various third-party pre-existing client libraries and tools.
You can connect to TCP port 8812
and use both INSERT
and SELECT
SQL
queries.
PostgreSQL wire protocol is better suited for applications inserting via SQL programmatically as it provides parameterized queries, which avoid SQL injection issues.
tip
InfluxDB Line Protocol is the recommended primary
ingestion method in QuestDB. SQL INSERT
statements over the PostgreSQL offer
feedback and error reporting, but have worse overall performance.
Here are a few examples demonstrating SQL INSERT
queries:
- psql
- Python
- Java
- NodeJS
- Go
- Rust
note
If you using the QuestDB Cloud, your database requires TLS to connect. You can find host, port, and password configuration at https://cloud.questdb.com, on your instance "Connect" tab. To enable SSL from psql in the commands below, please follow this pattern:
psql -h {hostname} -p {port} -U admin "dbname=qdb sslmode=require" -c '{SQL_STATEMENT}'
Create the table:
Insert row:
Query back:
Note that you can also run psql
from Docker without installing the client
locally:
This example uses the pg
package which
allows for quickly building queries using Postgres wire protocol. Details on the
use of this package can be found on the
node-postgres documentation.
This example uses naive Date.now() * 1000
inserts for Timestamp types in
microsecond resolution. For accurate microsecond timestamps, the
process.hrtime.bigint()
call can be used.
This example uses the pgx driver and toolkit for PostgreSQL in Go. More details on the use of this toolkit can be found on the GitHub repository for pgx.
The following example shows how to use parameterized queries and prepared statements using the rust-postgres client.
#
Web ConsoleQuestDB ships with an embedded Web Console running
by default on port 9000
.
#
Inserting data via SQLSQL statements can be written in the code editor and executed by clicking the Run button. Note that the Web Console runs a single statement at a time.
There are two SQL keywords to insert data:
- Creating a table and inserting some data
COPY: For inserting bulk data or migrating data from other databases. See large CSV import.
#
Uploading CSV fileIt is also possible to upload CSV files using the Import tab in the Web Console:

#
HTTP REST APIQuestDB 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 |
/exec?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#
tip
InfluxDB Line Protocol is the recommended primary ingestion method in QuestDB. CSV uploading offers insertion feedback and error reporting, but has worse overall performance.
See /imp
's atomicity
query
parameter to customize behavior on error.
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 | 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.
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.
This first example shows uploading the data.csv
file with automatic schema
detection.
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.
/exec
: SQL INSERT
Query#
The /exec
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.
tip
Prefer the PostgreSQL interface if you are generating sql programmatically.
Prefer InfluxDB Line Protocol if you need high-performance inserts.
- cURL
- Python
- NodeJS
- Go
The node-fetch
package can be installed using npm i node-fetch
.