Photo by Nam Anh on Unsplash

PostgreSQL on Docker

Why Postgres?

Postgres is one of the most popular databases supported by almost any framework, including Ruby on Rails and Django. For this reason, it is quite opportune to have an environment in charge that allows you to easily and simply test all the SQL concepts in Postgres.

Why in Container and not in a local installation?

Currently, the advantage that Docker offers is to be able to test different versions of Postgres without having to install them in our local environment. Once the tests are finished, we can eliminate the containers and volumes.

Preload tables

Another advantage that Docker offers us is being able to preload the environment in a simple way with tables ready to experiment.

Project Structure

For this project, we are going to create a basic folder system where we will have the docker-compose.yml file along with other complementary files. We will also have another folder sql where we will place everything related to the database.

.
├── sql
│   ├── fill_tables.sql
│   └── create_tables.sql
├── docker-compose.yml
├── README.md
└── LICENSE

Docker Compose

Next, we are going to write a docker-compose file that allows us to create a container with the Postgres image and allows us to assign the folder where we will store the database, we are also going to create the Tables and preload them with some records.

version: '3.9'
services:
  db:
    image: postgres:14.3-alpine
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - '5432:5432'
    volumes:
      - db-postgres:/var/lib/postgresql/data
      - ./sql/create_tables.sql:/docker-entrypoint-initdb.d/create_tables.sql
      - ./sql/fill_tables.sql:/docker-entrypoint-initdb.d/fill_tables.sql
volumes:
  db-postgres:
    driver: local

You can see the source code in the repository on GitHub, To start the environment you can execute the following command:

docker compose -f docker-compose.yml up --build

Or, if you prefer to run the containers run in the background run this command:

docker compose -f docker-compose.yml up --build -d

Creating the tables

For this section, I will recommend dbdiagram.io, an excellent tool for diagramming databases, I have four related tables, but you can modify this part to your liking.

Leaving something like this at the end:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE "user" (
  "uuid" uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4 (),
  "full_name" varchar NOT NULL,
  "created_at" timestamp DEFAULT (now())
);
CREATE TABLE "budget" (
  "uuid" uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4 (),
  "user_uuid" uuid NOT NULL,
  "name" varchar,
  "created_at" timestamp DEFAULT (now())
);
CREATE TABLE "category" (
  "uuid" uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4 (),
  "budget_uuid" uuid NOT NULL,
  "name" varchar NOT NULL,
  "created_at" timestamp DEFAULT (now())
);
CREATE TABLE "entry" (
  "uuid" uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4 (),
  "user_uuid" uuid NOT NULL,
  "category_uuid" uuid NOT NULL,
  "description" text,
  "amount" numeric(15, 3) DEFAULT (0.000),
  "created_at" timestamp DEFAULT (now())
);
ALTER TABLE "budget" ADD FOREIGN KEY ("user_uuid") REFERENCES "user" ("uuid");
ALTER TABLE "category" ADD FOREIGN KEY ("budget_uuid") REFERENCES "budget" ("uuid");
ALTER TABLE "entry" ADD FOREIGN KEY ("user_uuid") REFERENCES "user" ("uuid");
ALTER TABLE "entry" ADD FOREIGN KEY ("category_uuid") REFERENCES "category" ("uuid");being able to share

Now we are going to fill these tables with some records, and for that, we are going to prepare some Queries:

-- User
WITH c (
    user_uuid
    ) as (
   values (
        uuid_generate_v4 ()
   )
)

insert into "user" (uuid, full_name) values(
    (select user_uuid from c),
    'John Doe'
);
-- Budget
insert into "budget" (user_uuid, name) values (
    (select uuid from "user" limit 1),
    'first_budget'
);
-- Categories
with c (
    budget_uuid
    ) as (
    values (
        (select uuid as budget_uuid from "budget" limit 1)
    )
)
insert into "category" (budget_uuid, name) values
    ((select budget_uuid from c limit 1), 'Food'),
    ((select budget_uuid from c limit 1), 'Transportation'),
    ((select budget_uuid from c limit 1), 'Housing'),
    ((select budget_uuid from c limit 1), 'Savings & Investments');
-- Entries
insert into "entry" (user_uuid, category_uuid, description, amount)
select
    (select uuid from "user" limit 1),
    uuid,
    name,
    random () * (100 - 1) + 3
from "category";

With this we have the tables with preloaded data ready, to confirm we can connect to the container:

docker exec -it docker-db-postgres_db_1 psql -U postgres -W postgres

Conclusion

being able to share

As you can see, building a database with Docker is easier and more practical. Not only do we forget about the locale installation. We can also try different versions. Another great advantage is sharing our test environments with a friend without worrying about the architecture.

I hope to upload more test environments with different databases, ready to test.

This article was updated, thanks to Shiam Beeharry