Intro

Intro#

In this section, there are many examples, most of which are run in Docker. This page shows typical tricks used to run and manipulate SQL servers from a Docker container.

Postgres#

Server#

A key feature of running a PostgreSQL container is that you must pass the POSTGRES_PASSWORD environment variable. Find out more on the PostgreSQL page on Docker Hub.

After running the following cell, you’ll have a Docker container named intro_example_pg.

docker run --rm -d\
    -e POSTGRES_PASSWORD=postgres\
    --name intro_example_pg\
    postgres:15.4 &> /dev/null

Note: Don’t forget to stop the containers afterward.

docker stop intro_example_pg

intro_example_pg

Commands#

In the case of PostgreSQL, to communicate with the container, you have to use the psql tool. It requires specifying the user with the -U option. The exec command must be called in interactive mode (using the -i option). To run scripts with multiple commands, you can use heredoc syntax.

The following cell demonstrates how to execute SELECT statements in the newly created containers.

docker exec -i intro_example_pg psql -U postgres << EOF
SELECT 10 v10, 20 v20, 30 v30, 40 v40;
SELECT 10 v10, 20 v20, 30 v30, 40 v40;
EOF

 v10 | v20 | v30 | v40 
-----+-----+-----+-----
  10 |  20 |  30 |  40
(1 row)

 v10 | v20 | v30 | v40 
-----+-----+-----+-----
  10 |  20 |  30 |  40
(1 row)

Initialisation scripts#

If you need to organise the initialisation at container startup, you can mount a folder containing *.sql and *.sh initialisation scripts on /docker-entrypoint-initdb.d. This will execute the files in /docker-entrypoint-initdb.d.

In the following example, I start the docker container with the folder mounted as /docker-entrypoint-initdb.d. This folder only contains a create_table.sql which creates an empty main_table. Then I check the existence of the table that should be created by create_table.sql.

docker run --rm -d\
    -v ./build_container/initialisation_scripts:/docker-entrypoint-initdb.d\
    -e POSTGRES_PASSWORD=docker_app \
    --name init_scripts_example \
    postgres:15.4 &> /dev/null

sleep 5
echo "=====CHECK TABLE====="
docker exec init_scripts_example bash -c "psql -U postgres -d postgres -c \"\dt\""

docker stop  init_scripts_example &> /dev/null

=====CHECK TABLE=====
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | main_table | table | postgres
(1 row)

Clickhouse#

Server#

Creating a ClickHouse server is really straightforward.

The following cell creates a new Docker container named intro_example_click.

docker run -d --rm\
    --name intro_example_click\
    clickhouse/clickhouse-server:24

60c105781eeb22399d5446ef8e7114ee86d0da60008f6b2dc37b676287737874

Note: Don’t forget to stop the container afterward.

docker stop intro_example_click

intro_example_click

Commands#

In the case of ClickHouse, access to the database is achieved through the clickhouse-client tool. To pass a command to it, you need to use the -q option, followed by a string literal that defines the script to be executed.

The following cell shows how to execute commands in ClickHouse through Docker.

docker exec intro_example_click clickhouse-client -q "
SELECT 10 v10, 20 v20, 30 v30, 40 v40 FORMAT Pretty;
SELECT 10 v10, 20 v20, 30 v30, 40 v40 FORMAT Pretty;
"

   ┏━━━━━┳━━━━━┳━━━━━┳━━━━━┓
   ┃ v10 ┃ v20 ┃ v30 ┃ v40 ┃
   ┡━━━━━╇━━━━━╇━━━━━╇━━━━━┩
1. │  10 │  20 │  30 │  40 │
   └─────┴─────┴─────┴─────┘
   ┏━━━━━┳━━━━━┳━━━━━┳━━━━━┓
   ┃ v10 ┃ v20 ┃ v30 ┃ v40 ┃
   ┡━━━━━╇━━━━━╇━━━━━╇━━━━━┩
1. │  10 │  20 │  30 │  40 │
   └─────┴─────┴─────┴─────┘

Sqlite#

Server#

To run sqlite container we are using keinos/sqlite image.

docker run --rm -itd --name intro_example_sqlite keinos/sqlite3

0b43efc7499e85dad2b4814d94a78b815651a14129d042929fead95c3d3af7ce

Note: Don’t forget to stop the container afterward.

docker stop intro_example_sqlite

intro_example_sqlite

Run command#

To run a command from the terminal in SQLite, use the -cmd parameter and pass the query to it.

The following cell shows the execution of the SELECT 'Hello world' query.

docker exec intro_example_sqlite sqlite3 -cmd "SELECT 'Hello world';"

Hello world

It’s convenient to specify the -box option with the sqlite3 command — the results of the queries will be printed in a more readable format.

The following cell shows creating and querying a table in SQLite:

docker exec intro_example_sqlite sqlite3 -box -cmd "
CREATE TABLE IF NOT EXISTS tab (
    var1 TEXT,
    var2 TEXT
);

INSERT INTO tab (var1, var2) VALUES
(NULL, 'world'),
('hello', NULL),
(NULL, 'test line');

SELECT * FROM tab;
"

┌───────┬───────────┐
│ var1  │   var2    │
├───────┼───────────┤
│       │ world     │
│ hello │           │
│       │ test line │
└───────┴───────────┘