Create environment for learning database with Docker

Photo by Florian Olivo on Unsplash

Introduction

In studying about database, it is important to run queries by yourself. But it is often troublesome or confusing to create the environment. There are some choices to get it, for example,

• Local
• Cloud (GCP, AWS, Azure, …)

If you choose “Local”, you can execute queries for completely free unlike “Cloud”, but it may take a lot of trouble. As the third option, DB Fiddles may be useful for some people.

DB Fiddle - SQL Database Playground

This service can be used basically for free. We define schema on the left pane and write query in the right (See below image).

The screenshot of DB Fiddle

This service is very useful when you test snippets. But if you want to learn how to embed database in some applications, this may not be enough.

Why Docker?

www.docker.com

As well known, Docker is

a set of platform as a service (PaaS) products that use OS-level virtualization to deliver software in packages called containers. (source: Wikipedia)

By taking advantage of Docker, you can

• build an environment that is independent of your system
• deploy easily if you are creating application
• (destroy securely when no longer use it)

My environment

• Windows 10 (work in Ubuntu 18.04LTS in WSL2)
• Docker 20.10.2
• docker-compose 1.27.4

Let’s start!

We use PostgreSQL as an example here. Pull latest image from Docker Hub by the following command in your shell.

$ docker pull postgres:latest

For convenience, we manage settings in docker-compose.yaml file.

In starting the container, we mount two directories, data and sql. The data directory keeps schema information and data. If you delete the container, of course, these information also destroyed, so this mounting prevent from losing data you stored in database.

The sql directory contain the .sql files. The reason why we mount this directory is only for convenience, I want to write SQL query in my favorite editor!

$ docker-compose up -d

This command runs the container in the background. Check to see if it’s working,

$ docker-compose ps
Name              Command              State    Ports
-------------------------------------------------------
db     docker-entrypoint.sh postgres   Up      5432/tcp

Ok, it looks good. As a test create a database and connect to it. We use postgres user in this example.

$ docker exec -it db /bin/bash
root@92913b69db39:/# createdb -U postgres testdb  # create a database
root@92913b69db39:/# psql -U postgres -d testdb  # connect to it
psql (13.1 (Debian 13.1-1.pgdg100+1))
Type "help" for help.

testdb=# \q
root@92913b69db39:/# exit
$

Next try executing a query to create table.

create_table.sql

$ docker exec -it db psql -U postgres -d testdb -f /home/create_table.sql

If seeing “CREATE TABLE” in the command line, you succeeded!

For convenience, write a shell script like the following,

run_query.sh

So, you can run query by more simple command.

$ chmod +x run_query.sh
$ ./run_query.sh create_table.sql

That’s all.