DEV Community 👩‍💻👨‍💻

ILshat Khamitov
ILshat Khamitov

Posted on

Create a database for the application and create first migration via flyway

Links - source code of bot - current bot in telegram - flyway is an open-source database migration tool. It strongly favors simplicity and convention over configuration - NodeJs wrapper for flywaydb cli - my little utilities for nx monorepositories

Install dependencies

Install the cross database tool for working with migrations

npm i --save-dev node-flywaydb

endy@endy-virtual-machine:~/Projects/current/kaufman-bot$ npm i --save-dev node-flywaydb

added 14 packages, and audited 918 packages in 3s

115 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities
Enter fullscreen mode Exit fullscreen mode

Install a utility for parsing database connection strings

npm i --save connection-string

endy@endy-virtual-machine:~/Projects/current/kaufman-bot$ npm i --save connection-string

up to date, audited 938 packages in 2s

115 packages are looking for funding
  run `npm fund` for details

1 high severity vulnerability

To address all issues, run:
  npm audit fix

Run `npm audit` for details.
Enter fullscreen mode Exit fullscreen mode

Update or install the latest version of the utility for nx monorepositories, the latest version has a command to create a non-root application database

npm i --save-dev rucken@latest

endy@endy-virtual-machine:~/Projects/current/kaufman-bot$ npm i --save-dev rucken@latest

added 19 packages, changed 1 package, and audited 938 packages in 4s

115 packages are looking for funding
  run `npm fund` for details

1 high severity vulnerability

To address all issues, run:
  npm audit fix

Run `npm audit` for details.
Enter fullscreen mode Exit fullscreen mode

Connect flyway

Update package.json


    "flyway": "flyway -c .flyway.js",
    "migrate": "npm run flyway -- migrate",
    "migrate:local": "export $(xargs < ./.env.local) && npm run migrate"
Enter fullscreen mode Exit fullscreen mode

Add configuration file for flyway


const { ConnectionString } = require('connection-string');
const cs = new ConnectionString(
  process.env.POSTGRES_URL || process.env.DATABASE_URL
const {
  user: USERNAME,
  password: PASSWORD,
  HOST =,
  DATABASE = cs.path && cs.path[0],
  SCHEMA = cs.params && cs.params.schema,
  SCHEMAS = cs.params && cs.params.schemas,
} = cs;

module.exports = {
  flywayArgs: {
    url: `jdbc:postgresql://${HOST}/${DATABASE}`,
    schemas: SCHEMAS || SCHEMA,
    defaultSchema: SCHEMA,
    locations: `filesystem:migrations`,
    user: USERNAME,
    password: PASSWORD,
    table: '__migrations',
    sqlMigrationSuffixes: '.pgsql',
  // Use to configure environment variables used by flyway
  env: {
    JAVA_ARGS: '-Djava.util.logging.config.file=./conf/',
  version: '6.3.2', // optional, empty or missing will download the latest
  mavinPlugins: [
      // optional, use to add any plugins (ie. logging)
      groupId: 'org.slf4j',
      artifactId: 'slf4j-api',
      version: '1.7.25',
      // This can be a specifc url to download that may be different then the auto generated url.
      groupId: 'org.slf4j',
      artifactId: 'slf4j-jdk14',
      version: '1.7.25',
  downloads: {
    storageDirectory: `${__dirname}/tmp`, // optional, the specific directory to store the flyway downloaded files. The directory must be writable by the node app process' user.
    expirationTimeInMs: -1, // optional, -1 will never check for updates, defaults to 1 day.
Enter fullscreen mode Exit fullscreen mode

Update env file


Enter fullscreen mode Exit fullscreen mode

Add migrations

Let's create a migration to store the telegram user's language


    id uuid DEFAULT uuid_generate_v4 () NOT NULL,
    "telegramId" varchar(64) NOT NULL,
    "langCode" varchar(64) DEFAULT 'en' NOT NULL,

Enter fullscreen mode Exit fullscreen mode

Add test data


INSERT INTO "User" ("telegramId")
    VALUES ('testId')
ON CONFLICT ("telegramId")
Enter fullscreen mode Exit fullscreen mode

Update dev infra

Update docker compose config


        image: "endykaufman/postgres-default"
        container_name: "kaufman-bot-postgres"
            - POSTGRES_DB=postgres
            - ../../.env.local
            - "5432:5432"
            - kaufman-bot-postgres-volume:/var/lib/postgresql/data
            - kaufman-bot-network
            test: ["CMD-SHELL", "pg_isready -U postgres"]
            interval: 5s
            timeout: 5s
            retries: 5
Enter fullscreen mode Exit fullscreen mode

Update up script


#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
# Start only database
docker-compose -f ./docker/dev/docker-compose.yml --compatibility up -d kaufman-bot-postgres
# Wait ready datatbase
until docker exec -it $(docker ps -aqf "name=kaufman-bot-postgres") pg_isready -U postgres; do
    echo "Waiting for postgres..."
    sleep 2
# Create all need application databases by exists match evn key and nx app name
# for app: "server" - env: SERVER_POSTGRES_URL
# for app: "core-server" - env: CORE_SERVER_POSTGRES_URL
npm run rucken -- postgres
# Run migrate database for specific database
export DATABASE_URL=$SERVER_POSTGRES_URL && npm run migrate
# Start all services
docker-compose -f ./docker/dev/docker-compose.yml --compatibility up -d
Enter fullscreen mode Exit fullscreen mode

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

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/

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@0.0.0 docker:dev:up
> export $(xargs < ./.env.local) > /dev/null 2>&1 && ./docker/dev/

Creating network "dev_kaufman-bot-network" with the default driver
Creating kaufman-bot-postgres ... done
/var/run/postgresql:5432 - no response
Waiting for postgres...
/var/run/postgresql:5432 - accepting connections

> kaufman-bot@0.0.0 rucken
> rucken "postgres"

> kaufman-bot@0.0.0 migrate
> npm run flyway -- migrate

> kaufman-bot@0.0.0 flyway
> flyway -c .flyway.js "migrate"

Flyway Community Edition 6.3.2 by Redgate
Database: jdbc:postgresql://localhost:5432/kaufman_bot_develop (PostgreSQL 13.3)
WARNING: Flyway upgrade recommended: PostgreSQL 13.3 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 12.
Successfully validated 2 migrations (execution time 00:00.013s)
Creating Schema History table "public"."__migrations" ...
Current version of schema "public": << Empty Schema >>
Migrating schema "public" to version 202203252131 - CreateUserTable
Migrating schema "public" to version 202203252144 - ExampleDataForUserTable
Successfully applied 2 migrations to schema "public" (execution time 00:00.043s)
kaufman-bot-postgres is up-to-date
Creating kaufman-bot-server ... done
Enter fullscreen mode Exit fullscreen mode

Check database

Connect to container with database

docker exec -it $(docker ps -aqf "name=kaufman-bot-postgres") sh

Connect with psql to application database

set PGPASSWORD=password_develop&& psql -d kaufman_bot_develop -U admin_develop

Select telegram users

select * from "User";

endy@endy-virtual-machine:~/Projects/current/kaufman-bot$ docker exec -it $(docker ps -aqf "name=kaufman-bot-postgres") sh
# set PGPASSWORD=password_develop&& psql -d kaufman_bot_develop -U admin_develop
psql (13.3 (Debian 13.3-1.pgdg100+1))
Type "help" for help.

kaufman_bot_develop=> select * from "User";
                  id                  | telegramId | langCode 
 c98e49b5-2fa5-4748-896d-1dbca9cc7112 | testId     | en
(1 row)
Enter fullscreen mode Exit fullscreen mode

Check database

In the next post, we will create a database in dokku infra and set up migration from github

Top comments (0)

Hacktoberfest is happening now!

It is a month-long celebration of open source. For a lot of devs, its their introduction to open source.

Check out the Hacktoberfest tag on DEV to keep up with the latest!