An Interest In:
Web News this Week
- March 22, 2024
- March 21, 2024
- March 20, 2024
- March 19, 2024
- March 18, 2024
- March 17, 2024
- March 16, 2024
Create a database for the application and create first migration via flyway
Links
https://github.com/EndyKaufman/kaufman-bot - source code of bot
https://telegram.me/DevelopKaufmanBot - current bot in telegram
https://flywaydb.org - flyway is an open-source database migration tool. It strongly favors simplicity and convention over configuration
https://www.npmjs.com/package/node-flywaydb - NodeJs wrapper for flywaydb cli
https://github.com/rucken/rucken - 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-flywaydbadded 14 packages, and audited 918 packages in 3s115 packages are looking for funding run `npm fund` for detailsfound 0 vulnerabilities
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-stringup to date, audited 938 packages in 2s115 packages are looking for funding run `npm fund` for details1 high severity vulnerabilityTo address all issues, run: npm audit fixRun `npm audit` for details.
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@latestadded 19 packages, changed 1 package, and audited 938 packages in 4s115 packages are looking for funding run `npm fund` for details1 high severity vulnerabilityTo address all issues, run: npm audit fixRun `npm audit` for details.
Connect flyway
Update package.json
package.json
{... "flyway": "flyway -c .flyway.js", "migrate": "npm run flyway -- migrate", "migrate:local": "export $(xargs < ./.env.local) && npm run migrate"...}
Add configuration file for flyway
.flyway.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. },};
Update env file
.env.local
TELEGRAM_BOT_TOKEN=1111111:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAROOT_POSTGRES_USER=postgresROOT_POSTGRES_PASSWORD=postgresROOT_POSTGRES_URL=postgres://${ROOT_POSTGRES_USER}:${ROOT_POSTGRES_PASSWORD}@localhost:5432/postgres?schema=publicSERVER_POSTGRES_URL=postgres://admin_develop:password_develop@localhost:5432/kaufman_bot_develop?schema=public
Add migrations
Let's create a migration to store the telegram user's language
migrations/V202203252131CreateUserTable.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");
Add test data
migrations/V202203252144ExampleDataForUserTable.pgsql
INSERT INTO "User" ("telegramId") VALUES ('testId')ON CONFLICT ("telegramId") DO NOTHING;
Update dev infra
Update docker compose config
docker/dev/docker-compose.yml
...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...
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# Start only databasedocker-compose -f ./docker/dev/docker-compose.yml --compatibility up -d kaufman-bot-postgres# Wait ready datatbaseuntil docker exec -it $(docker ps -aqf "name=kaufman-bot-postgres") pg_isready -U postgres; do echo "Waiting for postgres..." sleep 2done# 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_URLnpm run rucken -- postgres# Run migrate database for specific databaseexport DATABASE_URL=$SERVER_POSTGRES_URL && npm run migrate# Start all servicesdocker-compose -f ./docker/dev/docker-compose.yml --compatibility up -d
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> [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.shStopping kaufman-bot-server ... doneStopping kaufman-bot-postgres ... doneRemoving kaufman-bot-server ... doneRemoving kaufman-bot-postgres ... doneRemoving network dev_kaufman-bot-networkkaufman-bot-postgres-volume> [email protected] docker:dev:up> export $(xargs < ./.env.local) > /dev/null 2>&1 && ./docker/dev/docker-compose-up.shkaufman-bot-postgres-volumeCreating network "dev_kaufman-bot-network" with the default driverCreating kaufman-bot-postgres ... done/var/run/postgresql:5432 - no responseWaiting 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 RedgateDatabase: 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 - CreateUserTableMigrating schema "public" to version 202203252144 - ExampleDataForUserTableSuccessfully applied 2 migrations to schema "public" (execution time 00:00.043s)kaufman-bot-postgres is up-to-dateCreating kaufman-bot-server ... done
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_developpsql (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)
In the next post, we will create a database in dokku infra and set up migration from github
Original Link: https://dev.to/endykaufman/create-a-database-for-the-application-and-create-first-migration-via-flyway-4hjb
Dev To
An online community for sharing and discovering great ideas, having debates, and making friendsMore About this Source Visit Dev To