How To Run QuestDB SQL database in Docker Container

QuestDB is an open-source high-performance time-series database(TSDB) for applications in IoT, event monitoring, machine learning, DevOps, financial services e.t.c. QuestDB written in Java and C programming language has support for the SQL query language and also implements PostgreSQL wire protocol, REST API, and an InfluxDB Line Protocol.

QuestDB was developed by Vlad Ilyushchenko in 2014 with the main goal of creating low latency trading techniques for databases to improve performance with minimal server resources. Ever since its first release, the QuestDB SQL database has gained popularity all over the world. This is highly contributed by its amazing features that include:

• Supports ANSI SQL and includes language extensions for time series, this helps perform calculations on timestamped data for row- and column-based access.
• Data is stored in a chronological order and table partitions are applied based on time.
• Data is massively parallelized using vectorized query execution.
• Time series, relational joins, downsampling, and unlimited sub-queries are supported.
• High performance achieved from a column-oriented storage model.
• No dependencies required, and is 100% free from garbage collection
• Easy to run queries and inspect table schemas using the web interface

By the end of this guide, you will have gained the required knowledge on how to run the QuestDB SQL database in Docker Container.

1. Install Docker CE on Linux

For this guide, we need to install docker engine on our Linux system. Below is a dedicated guide to aid you in installing Docker Engine.

• How To Install Docker CE on Linux Systems

Start and enable docker

sudo systemctl start docker
sudo systemctl enable docker

Verify that docker is running:

$ systemctl status docker
● docker.service - Docker Application Container Engine
     Loaded: loaded (/lib/systemd/system/docker.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2022-01-13 02:30:03 EST; 4min 5s ago
TriggeredBy: ● docker.socket
       Docs: https://docs.docker.com
   Main PID: 2741 (dockerd)
      Tasks: 8
     Memory: 30.4M
        CPU: 239ms
     CGroup: /system.slice/docker.service
             └─2741 /usr/bin/dockerd -H fd:// --containerd=/run/containerd/containerd.sock

Ensure that your system user is added to the docker group as below.

sudo usermod -aG docker $USER
newgrp docker

Confirm that you can run docker command without sudo or being a root user.

docker info

2. Run QuestDB SQL database in Docker Container.

There are two methods on how to navigate along:

• Pull the container image and run directly
• Use Docker-compose

Option 1 – Pull the QuestDB image and Run QuestDB Container

Once Docker is installed on your system, you can pull the QuestDB container image and run it or combine both in one command as below.

cd ~
docker run  -t -d \
 -p 9000:9000 \
 -p 9009:9009 \
 -p 8812:8812 \
 -p 9003:9003 \
 --name docker_questdb \
 questdb/questdb

In the above command, we have exposed the below ports:

• 9000:9000 -For REST API and Web Console
• 8812:8812 – For Postgres wire protocol
• 9009:9009 – For InfluxDB line protocol
• 9003:9003 – For Min health server

Data persistence

We can also add a persistent volume for data using the -v argument. Normally the QuestDB root directory will be at /root/.questdb/. You can mount Docker Volumes to the QuestDB Docker container so that data may be persisted or server configuration settings may be passed to an instance.

First stop and delete running container

$ docker rm -f docker_questdb
docker_questdb

$ docker ps
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES

In the following example we demonstrate how to mount the directory ./questdb_data to a QuestDB container using the -v flag in a Docker run command:

cd ~
docker run -t -d \
 -p 9000:9000 \
 -p 9009:9009 \
 -p 8812:8812 \
 -p 9003:9003 \
 --name docker_questdb \
 -v "$(pwd)/questdb_data:/root/.questdb/" questdb/questdb

Now you will have data stored in the path convenient for migration or backups.

$ ls questdb_data/
conf  db  public

Now check the status of the container.

$ docker ps
CONTAINER ID   IMAGE             COMMAND                  CREATED         STATUS         PORTS                                                                                                                                                                        NAMES
9f69d05b8604   questdb/questdb   "/usr/bin/env QDB_PA…"   5 seconds ago   Up 4 seconds   0.0.0.0:8812->8812/tcp, :::8812->8812/tcp, 0.0.0.0:9000->9000/tcp, :::9000->9000/tcp, 0.0.0.0:9003->9003/tcp, :::9003->9003/tcp, 0.0.0.0:9009->9009/tcp, :::9009->9009/tcp   docker_questdb

Option 2 – Run QuestDB SQL database with Docker-Compose

Here, we will create a QuestDB SQL database docker-compose YAML file. The file will contain all the required information about our container.

With Docker installed, proceed and install Docker-compose using the guide below.

• How To Install Docker Compose on Linux

Using your favorite editor, create the YAML file as below.

cd ~
mkdir questdb
cd questdb
vim docker-compose.yml

In the file, add the below lines:

version: "3"

services:
  questdb:
    image: questdb/questdb
    container_name: docker_questdb
    restart: always
    ports:
      - "8812:8812"
      - "9000:9000"
      - "9009:9009"
      - "9003:9003"
    volumes:
      - ./data/db:/root/.questdb/db

Now run the container as below.

docker-compose up -d

Command execution output:

[+] Running 2/2
 ⠿ Network questdb_default   Created                                                                                                                                                             0.1s
 ⠿ Container docker_questdb  Started

Once complete, verify if the container is running:

$ docker ps
CONTAINER ID   IMAGE             COMMAND                  CREATED          STATUS          PORTS                                                                                                                                                                        NAMES
1bfbaa565e5a   questdb/questdb   "/usr/bin/env QDB_PA…"   19 seconds ago   Up 17 seconds   0.0.0.0:8812->8812/tcp, :::8812->8812/tcp, 0.0.0.0:9000->9000/tcp, :::9000->9000/tcp, 0.0.0.0:9003->9003/tcp, :::9003->9003/tcp, 0.0.0.0:9009->9009/tcp, :::9009->9009/tcp   docker_questdb

3. Connect to QuestDB SQL database.

There are several methods one can use to connect and interact with QuestDB. They include:

1. Web Console listening on port 9000
2. REST API on port 9000
3. PostgreSQL wire protocol on port 8812
4. InfluxDB line protocol for high-throughput ingestion on port 9009

In this guide, I will demonstrate how to connect and interact with QuestDB using the Web console and the REST API.

1. Connect to QuestDB SQL database Web Console.

The QuestDB web console is listening on port 9000 and can be accessed using the URL http://IP_Address:9000. If you have trouble accessing the page, allow the port through the firewall.

You should be able to see this page:

Here, you can run your scripts. For example, begin by creating a table:

CREATE TABLE sensors (ID LONG, make STRING, city STRING);

Once the table is created, insert data into it.

INSERT INTO sensors
    SELECT
        x ID, --increasing integer
        rnd_str('Eberle', 'Honeywell', 'Omron', 'United Automation', 'RS Pro') make,
        rnd_str('Kampala', 'Nairobi', 'Bamako', 'Segere', 'Dodoma') city
    FROM long_sequence(10000) x
;

On the web console, the commands should be entered as below and click Run.

You will have your data added as below.

View data in the tables as below

SELECT * FROM sensors WHERE city='Nairobi';

Sample Output:

You can also import data from a file.

2. Connect to QuestDB SQL database using REST API

REST API is based on HTTP and provides the best and simple way to interact with QuestDB.

Here, API functions are keyed on the URL and use query parameters as arguments. For example, to import data, you will use the below command:

curl -F data=@file.csv \
'http://localhost:9000/imp'

You can as well execute queries that return a JSON response.

For example. Create a table as below

curl -G \
  --data-urlencode "query=CREATE TABLE sensors (ID LONG, make STRING, city STRING);" \
  --data-urlencode "count=true" \
  http://localhost:9000/exec

Insert data into the table.

curl -G \
  --data-urlencode "query=INSERT INTO sensors
    SELECT
        x ID, --increasing integer
        rnd_str('Eberle', 'Honeywell', 'Omron', 'United Automation', 'RS Pro') make,
        rnd_str('Kampala', 'Nairobi', 'Bamako', 'Segere', 'Dodoma') city
    FROM long_sequence(10000) x
;" \
  --data-urlencode "count=true" \
  http://localhost:9000/exec

Now view the data in the table

curl -G \
  --data-urlencode "query=SELECT * FROM sensors WHERE city='Nairobi' limit 5;" \
  --data-urlencode "count=true" \
  http://localhost:9000/exec

Sample Output:

4. Manage the QuestDB SQL database Container.

The QuestDB SQL database container can be managed as below:

• Start the container.

docker start docker_questdb

• Stop the container.

docker stop docker_questdb

Next see how you can monitor it using Prometheus and Grafana as discussed in the article below.

• Monitor QuestDB Database With Prometheus and Grafana

Conclusions

That marks the end of this guide on how to run the QuestDB SQL database in Docker Container. I hope this guide was of significance to you.

Related posts:

• Run Microsoft SQL Server in Podman|Docker Container
• Run Bitwarden Password Manager in Docker Container
• How To Run Mattermost Server in Docker Containers
• Run Docker/Podman Containers as Systemd Service