How to create database in database docker container?

Question

I'm new in docker, so cant understand - if I want to build container of mysql/postgresql/clickhouse etc - how to create database and schema of database/table? Maybe in Dockerfile or i can do it from docker-compose.yml?

I mean, that I dont know when and where to use CREATE DATABASE; CREATE TABLE ...; queries if I use docker containers of popular databases

Answer 1

You can use both docker and docker-compose. For example with docker compose.

Create a file called docker-compose.yml like:

version: '3'
services:
  db:
    image: percona:5.7
    container_name: whatever_you_want
    environment:
      - MYSQL_DATABASE=${DATABASE}
      - MYSQL_ROOT_PASSWORD=${ROOT_PASSWORD}
      - MYSQL_USER=${USER}
      - MYSQL_PASSWORD=${PASSWORD}
    volumes:
      - ./data:/docker-entrypoint-initdb.d
    ports:
      - "3306:3306"

Additionally you need a file under ./data with whatever SQL commands you want to run and and .env file where you define the environmental variables I used in the docker-compose.yml file above like: ${DATABASE}

Your .env file:

# MySQL
DATABASE=db_name_here
ROOT_USER=root
ROOT_PASSWORD=root
USER=dev
PASSWORD=dev

Your file with SQL commands to execute ./data/init.sql (you can name the file whatever you want)

CREATE DATABASE 'whatever';
DROP DATABASE 'whatever';
-- you can do whatever you want here

This file will be executed each time you do:

docker-compose up -d db