Database Deployments (Migrations): setup Flyway for the project

[lsulak]

Feature Description

There is no automatic and solid approach for handling database objects (tables, constraints, etc) - their implementation and their deployment onto a real database. The original author probably deployed all tables (DDL statements, read this: https://en.wikipedia.org/wiki/Data_definition_language#:~:text=In%20the%20context%20of%20SQL,tables%2C%20indices%2C%20and%20users.) manually in the past.

Problem / Opportunity

DB is company's asset and should be treated as a first class citizen.

Currently, our treatment of database objects (mostly a few database tables at the moment) is not mature enough. There are no DDL files with SQL code stating CREATE TABLE etc. It's hard to understand what and how it's deployed on our database - basically, we cannot know what's deployed from the code simply.

Also, the database role (users) management is not mature either, we do not even have implemented custom roles and the usage of master role (=postgres user) for an application writes is not best practice (principle of least priviledge is massivelly violated for instance).

Acceptance Criteria

DB is properly and automatically handled in terms of db object migration - we version and keep all DB related code under a single place, under root directory database/ and put all DDL files there (and anything that is supposed to live in the DB - materialized views, indexes, DB functions, constraints, and so on, not just table and user creation statements).

Proposed Solution

See what we did in Atum Service: https://github.com/AbsaOSS/atum-service/tree/master/database and https://github.com/absa-group/atum-service-deployment/blob/master/.github/workflows/cd-workflow.yml#L101-L130

So, create a new directory database/ and follow the correct and incremental numbering of filename prefixes along with filename format and conventions from the Atum repository. Also, see how we deploy it in the CD files of Atum.

Optional but recommended steps:

• Install flyway manually on your laptop
• Read its documentation: https://documentation.red-gate.com/flyway/getting-started-with-flyway
• Setup a Postgres Database locally, you can even use Docker for it (ideally Ranger Desktop and you can spin up a DB super quickly by running something like this: docker run --name=eventgate_db -e POSTGRES_PASSWORD=changeme -e POSTGRES_DB=eventgate_db -p 5432:5432 -d postgres:16)
• Make sure you can access that DB by running psql or dbeaver into it
• Implement the new files, mostly DDL for now - DB creation, DB role creation, and DB table creation statements, see syntax in Atum, it has this mature
• Apply the flyway commands using the whole directory into your local machine
• If this works, implement similar commands on the CD - again, just check Atum Service Deployment repository for inspiration

Further Reading

• https://documentation.red-gate.com/flyway/getting-started-with-flyway/why-database-migrations
• https://medium.com/@rumeysa_25373/how-to-use-flyway-8f4a424fe2dc