アプリケーションのデータベースを作成し、フライウェイを介して最初の移行を作成する


リンク


https://github.com/EndyKaufman/kaufman-bot - ボットのソースコード
https://telegram.me/DevelopKaufmanBot - 現在のロボット
https://flywaydb.org - フライウェイは、オープンソースのデータベース移行ツールです.それは、単純さと構成の上の規則を強く支持します
https://www.npmjs.com/package/node-flywaydb - FlywayDB CLIのためのnodejsラッパー
https://github.com/rucken/rucken - NX monorepositivesのための私の小さいユーティリティ

依存関係のインストール


移動で作業するためのクロスデータベースツールをインストールします

npm i --save-dev node-flywaydb


[email protected]:~/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
データベース接続文字列を解析するユーティリティをインストールする

npm i --save connection-string


[email protected]:~/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.
NX monoreposiposisのためにユーティリティの最新バージョンを更新するか、インストールしてください

npm i --save-dev [email protected]


[email protected]:~/Projects/current/kaufman-bot$ npm i --save-dev [email protected]

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.

接続フライウェイ


アップデートパッケージ。JSON


パッケージ.JSON
{
...
    "flyway": "flyway -c .flyway.js",
    "migrate": "npm run flyway -- migrate",
    "migrate:local": "export $(xargs < ./.env.local) && npm run migrate"
...
}

フライウェイの設定ファイルを追加


フライウェイjs
const { ConnectionString } = require('connection-string');
const cs = new ConnectionString(
  process.env.POSTGRES_URL || process.env.DATABASE_URL
);
const {
  user: USERNAME,
  password: PASSWORD,
  HOST = cs.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/logging.properties',
  },
  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.
      downloadUrl:
        'https://repo1.maven.org/maven2/org/slf4j/slf4j-api/1.7.25/slf4j-api-1.7.25.jar',
    },
    {
      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.
  },
};

更新envファイル


envローカル
TELEGRAM_BOT_TOKEN=1111111:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ROOT_POSTGRES_USER=postgres
ROOT_POSTGRES_PASSWORD=postgres
ROOT_POSTGRES_URL=postgres://${ROOT_POSTGRES_USER}:${ROOT_POSTGRES_PASSWORD}@localhost:5432/postgres?schema=public
SERVER_POSTGRES_URL=postgres://admin_develop:[email protected]:5432/kaufman_bot_develop?schema=public

移行を加える


電報ユーザーの言語を保存するためのマイグレーションを作成しましょう
ユーザー名.pgsql
CREATE TABLE IF NOT EXISTS "User" (
    id uuid DEFAULT uuid_generate_v4 () NOT NULL,
    "telegramId" varchar(64) NOT NULL,
    "langCode" varchar(64) DEFAULT 'en' NOT NULL,
    CONSTRAINT "PK_USERS" PRIMARY KEY (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS "UQ_USERS__TELEGRAM_ID" ON "User" ("telegramId");
テストデータの追加
データの取り込みpgsql
INSERT INTO "User" ("telegramId")
    VALUES ('testId')
ON CONFLICT ("telegramId")
    DO NOTHING;

devの最新版を更新


アップデートDockerの設定


Docker/dev/dockerが作成されます.気象研
...
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
        healthcheck:
            test: ["CMD-SHELL", "pg_isready -U postgres"]
            interval: 5s
            timeout: 5s
            retries: 5
...

スクリプトの更新


Docker/dev/dockerを作成します.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
# 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
done
# 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
prodインフラについては、他のフォルダでのみ同じことを行います

devを起動する


npm run docker:dev:clean-restart


[email protected]:~/Projects/current/kaufman-bot$ npm run docker:dev:clean-restart

> [email protected] docker:dev:clean-restart
> npm run docker:dev:clean-down && npm run docker:dev:up


> [email protected] 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

> [email protected] 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
/var/run/postgresql:5432 - no response
Waiting for postgres...
/var/run/postgresql:5432 - accepting connections

> [email protected] rucken
> rucken "postgres"


> [email protected] migrate
> npm run flyway -- migrate


> [email protected] 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

チェックデータベース


データベースとコンテナに接続する

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


アプリケーションデータベースへのpsqlの接続

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


ユーザー選択

select * from "User";


[email protected]:~/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)

次のポストでは、DOKKU INROCAでデータベースを作成し、Githubから移行を設定します