DEV Community

ILshat Khamitov
ILshat Khamitov

Posted on

Add postgres to docker compose and dokku infrastructure for telegram bot in NestJS

#nestjs #kaufmanbot #docker #postgres

Links

https://github.com/EndyKaufman/kaufman-bot - source code of bot

https://telegram.me/DevelopKaufmanBot - current bot in telegram

https://github.com/dokku/dokku-postgres - dokku-postgres plugin for docker compose

https://github.com/EndyKaufman/postgres-default - 13 postgres with enabled extensions

Add postgres to dev and prod infrastructure

Setting up a database for prod and dev infra is the same, I describe only for dev infra

Update docker compose file

docker/dev/docker-compose.yml

version: "3"
networks:
    kaufman-bot-network:
        ipam:
            config:
                - subnet: "172.6.0.0/16"

volumes:
    kaufman-bot-postgres-volume:
        external: true

services:
    kaufman-bot-postgres:
        image: 'endykaufman/postgres-default'
        container_name: 'kaufman-bot-postgres'
        environment:
            - POSTGRES_USER=${ROOT_POSTGRES_USER}
            - POSTGRES_PASSWORD=${ROOT_POSTGRES_PASSWORD}
            - POSTGRES_DB=postgres
        env_file:
            - ../../.env.local
        ports:
            - '5432:5432'
        volumes:
            - kaufman-bot-postgres-volume:/var/lib/postgresql/data
        networks:
            - kaufman-bot-network

    kaufman-bot-server:
        image: node:16-alpine
        user: ${CURRENT_UID}
        container_name: "kaufman-bot-server"
        environment:
            - TELEGRAM_BOT_TOKEN=${TELEGRAM_BOT_TOKEN}
            - PORT=3000
        ports:
            - "3000:3000"
            - "9229:9229"
        working_dir: "/app"
        volumes:
            - ./../../:/app
        networks:
            - kaufman-bot-network
        command: "npm run serve"
        tty: true
        depends_on:
            - kaufman-bot-postgres
Enter fullscreen mode Exit fullscreen mode

Update you env file

.env.local

TELEGRAM_BOT_TOKEN=1111111:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ROOT_POSTGRES_USER=postgres
ROOT_POSTGRES_PASSWORD=postgres
Enter fullscreen mode Exit fullscreen mode

Update up script

docker/dev/docker-compose-up.sh

#!/bin/bash
#export UID=$(id -u)
#export GID=$(id -g)
export CURRENT_UID=$(id -u):$(id -g)
docker volume create --name=kaufman-bot-postgres-volume --label=kaufman-bot-postgres-volume
docker-compose -f ./docker/dev/docker-compose.yml --compatibility up -d
Enter fullscreen mode Exit fullscreen mode

Add second down script with drop volume

docker/dev/docker-compose-clean-down.sh

#!/bin/bash
#export UID=$(id -u)
#export GID=$(id -g)
export CURRENT_UID=$(id -u):$(id -g) 
docker-compose -f ./docker/dev/docker-compose.yml down
docker volume rm kaufman-bot-postgres-volume --force
Enter fullscreen mode Exit fullscreen mode

For prod infra, we do the same thing only in other folders

For prod infra, we do the same thing only in other folders

Add new scripts to package.json

package.json

...
    "docker:dev:clean-down": "export $(xargs < ./.env.local) > /dev/null 2>&1 && ./docker/dev/docker-compose-clean-down.sh",
    "docker:dev:clean-restart": "npm run docker:dev:clean-down && npm run docker:dev:up",
    "docker:prod:clean-down": "export $(xargs < ./.env.local) > /dev/null 2>&1 && ./docker/prod/docker-compose-clean-down.sh",
    "docker:prod:clean-restart": "npm run docker:prod:clean-down && npm run docker:prod:up"
...
Enter fullscreen mode Exit fullscreen mode

Restart dev infra

npm run docker:dev:clean-restart

endy@endy-virtual-machine:~/Projects/current/kaufman-bot$ npm run docker:dev:clean-restart

> kaufman-bot@0.0.0 docker:dev:clean-restart
> npm run docker:dev:clean-down && npm run docker:dev:up


> kaufman-bot@0.0.0 docker:dev:clean-down
> export $(xargs < ./.env.local) > /dev/null 2>&1 && ./docker/dev/docker-compose-clean-down.sh

Stopping kaufman-bot-server   ... done
Stopping kaufman-bot-postgres ... done
Removing kaufman-bot-server   ... done
Removing kaufman-bot-postgres ... done
Removing network dev_kaufman-bot-network
kaufman-bot-postgres-volume

> kaufman-bot@0.0.0 docker:dev:up
> export $(xargs < ./.env.local) > /dev/null 2>&1 && ./docker/dev/docker-compose-up.sh

kaufman-bot-postgres-volume
Creating network "dev_kaufman-bot-network" with the default driver
Creating kaufman-bot-postgres ... done
Creating kaufman-bot-server   ... done
Enter fullscreen mode Exit fullscreen mode

Restart dev infra

Check database

Connect to container with database

docker exec -it $(docker ps -aqf "name=kaufman-bot-postgres") sh
Connect to container with database

Switch user

su postgres

Run psql mode

psql

Select database name

SELECT current_database();

endy@endy-virtual-machine:~/Projects/current/kaufman-bot$ docker exec -it $(docker ps -aqf "name=kaufman-bot-postgres") sh
# su postgres
postgres@48966265f189:/$ psql
psql (13.3 (Debian 13.3-1.pgdg100+1))
Type "help" for help.

postgres=# SELECT current_database();
 current_database 
------------------
 postgres
(1 row)

postgres=#
Enter fullscreen mode Exit fullscreen mode

Check database

Add postgres to dokku infrastructure (on server)

Install dokku-postgres

Now we connect via ssh to our server on Ubuntu and set up work with postgres

sudo dokku plugin:install https://github.com/dokku/dokku-postgres.git postgres

root@vps17825:~# sudo dokku plugin:install https://github.com/dokku/dokku-postgres.git postgres
-----> Cloning plugin repo https://github.com/dokku/dokku-postgres.git to /var/lib/dokku/plugins/available/postgres
Cloning into 'postgres'...
remote: Enumerating objects: 2356, done.
remote: Counting objects: 100% (417/417), done.
remote: Compressing objects: 100% (276/276), done.
remote: Total 2356 (delta 244), reused 267 (delta 118), pack-reused 1939
Receiving objects: 100% (2356/2356), 483.24 KiB | 3.48 MiB/s, done.
Resolving deltas: 100% (1580/1580), done.
-----> Plugin postgres enabled
Adding user dokku to group adm
Starting nginx (via systemctl): nginx.service.
14.1: Pulling from library/postgres
5eb5b503b376: Pull complete 
daa0467a6c48: Pull complete 
7cf625de49ef: Pull complete 
bb8afcc973b2: Pull complete 
c74bf40d29ee: Pull complete 
2ceaf201bb22: Pull complete 
1255f255c0eb: Pull complete 
d27501cd0cca: Pull complete 
ff5b6d09a5d0: Pull complete 
f635aec27645: Pull complete 
a165c6729250: Pull complete 
b0aa4f86b611: Pull complete 
9efc4664d9d2: Pull complete 
Digest: sha256:3162a6ead070474b27289f09eac4c865e75f93847a2d7098f718ee5a721637c4
Status: Downloaded newer image for postgres:14.1
docker.io/library/postgres:14.1
1.31.1-uclibc: Pulling from library/busybox
76df9210b28c: Pull complete 
Digest: sha256:cd421f41ebaab52ae1ac91a8391ddbd094595264c6e689954b79b3d24ea52f88
Status: Downloaded newer image for busybox:1.31.1-uclibc
docker.io/library/busybox:1.31.1-uclibc
0.3.3: Pulling from dokku/ambassador
aad63a933944: Pull complete 
2888dfab2eb5: Pull complete 
51ccf60e0642: Pull complete 
Digest: sha256:87c0214e190e7f6975953027157a8933701596b4b864ff66dd3cc3f6ead5c38d
Status: Downloaded newer image for dokku/ambassador:0.3.3
docker.io/dokku/ambassador:0.3.3
0.10.3: Pulling from dokku/s3backup
aad63a933944: Already exists 
6654c5b7b2dc: Pull complete 
26abcd9faf98: Pull complete 
d1a36cd3ba61: Pull complete 
9517d44e685b: Pull complete 
32e8b2c4797f: Pull complete 
Digest: sha256:3651f8ef12000206df55fec8ad4860d6f26b2b5af1308c0e2358253641626024
Status: Downloaded newer image for dokku/s3backup:0.10.3
docker.io/dokku/s3backup:0.10.3
0.4.3: Pulling from dokku/wait
aad63a933944: Already exists 
3409ea528c35: Pull complete 
88e35d065209: Pull complete 
Digest: sha256:5eb9da766abdd5e8cedbde9870acd4b54c1c7e63e72c99e338b009d06f808f04
Status: Downloaded newer image for dokku/wait:0.4.3
docker.io/dokku/wait:0.4.3
-----> Priming bash-completion cache
root@vps17825:~#
Enter fullscreen mode Exit fullscreen mode

Create database service

I am using my custom build with UUID extension enabled https://hub.docker.com/r/endykaufman/postgres-default

dokku postgres:create global-postgres --image "endykaufman/postgres-default" --image-version latest --root-password=ROOT_PASSWORD --password=ADMIN_PASSWORD

root@vps17825:~# dokku postgres:create global-postgres --image "endykaufman/postgres-default" --root-password=ROOT_PASSWORD --password=ADMIN_PASSWORD
/var/lib/dokku/plugins/enabled/postgres/subcommands/create: illegal option -- -
/var/lib/dokku/plugins/enabled/postgres/subcommands/create: illegal option -- -
Error response from daemon: manifest for endykaufman/postgres-default:14.1 not found: manifest unknown: manifest unknown
 !     Postgres image endykaufman/postgres-default:14.1 pull failed
root@vps17825:~# dokku postgres:create global-postgres --image "endykaufman/postgres-default" --image-version latest --root-password=ROOT_PASSWORD --password=ADMIN_PASSWORD
/var/lib/dokku/plugins/enabled/postgres/subcommands/create: illegal option -- -
/var/lib/dokku/plugins/enabled/postgres/subcommands/create: illegal option -- -
latest: Pulling from endykaufman/postgres-default
b4d181a07f80: Pull complete 
46ca1d02c28c: Pull complete 
a756866b5565: Pull complete 
36c49e539e90: Pull complete 
664019fbcaff: Pull complete 
727aeee9c480: Pull complete 
796589e6b223: Pull complete 
6664992e747d: Pull complete 
0f933aa7ccec: Pull complete 
99b5e5d88b32: Pull complete 
a901b82e6004: Pull complete 
625fd35fd0f3: Pull complete 
9e37bf358a5d: Pull complete 
8c5f37d7fa57: Pull complete 
Digest: sha256:a3d342741451f717b79b2404e88363ea902a769d45a0bd7dbbbeeb73bb443f93
Status: Downloaded newer image for endykaufman/postgres-default:latest
docker.io/endykaufman/postgres-default:latest
 !     Specified password may not be as secure as the auto-generated password
       Waiting for container to be ready
       Creating container database
       Securing connection to database
=====> Postgres container created: global-postgres
=====> global-postgres postgres service information
       Config dir:          /var/lib/dokku/services/postgres/global-postgres/data
       Config options:                               
       Data dir:            /var/lib/dokku/services/postgres/global-postgres/data
       Dsn:                 postgres://postgres:assword=ADMIN_PASSWORD@dokku-postgres-global-postgres:5432/global_postgres
       Exposed ports:       -                        
       Id:                  50dbeaef39b80ca97823ad35ac771b241c2214eb3bd5cd81564f9dee546ae783
       Internal ip:         172.17.0.5               
       Links:               -                        
       Service root:        /var/lib/dokku/services/postgres/global-postgres
       Status:              running                  
       Version:             endykaufman/postgres-default:latest
Enter fullscreen mode Exit fullscreen mode

List all postgres services

dokku postgres:list

List all postgres services

Check database

Connect to database

dokku postgres:connect global-postgres
Connect to database

Select database name

SELECT current_database();

root@vps17825:~# dokku postgres:connect global-postgres
psql (13.3 (Debian 13.3-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

global_postgres=# SELECT current_database();
 current_database 
------------------
 global_postgres
(1 row)

global_postgres=#
Enter fullscreen mode Exit fullscreen mode

Check database

In the next post, we will create a database for the application and the first migration via flyway

Top comments (0)

Read next

blissfelix3 profile image

How To Get Started From Express To NestJS: Complete Guide

Bliss Abhademere -

adrianbailador profile image

Installing Docker in .NET 8 and React Projects

Adrián Bailador -

leandronoijo profile image

NET8 Web Api Monitoring Fast and Easy With Traces, Logs and Metrics.

leandronoijo -

hyperkai profile image

FOREACH and FOR statements with arrays in PostgreSQL

Super Kai (Kazuya Ito) -