Provide a solution to migrate from MySQL or MSSQL to PostgreSQL

[nscuro]

Hyades will exclusively support PostgreSQL. Support for MySQL and MSSQL will be dropped.

The reasoning behind this decision is as follows:

• Supporting multiple RDBMSes multiplies maintenance efforts, for both development and testing
• Syntax, supported features, ANSI SQL coverage varies across RDBMSes
• Each RDBMS behaves slightly different, in various annoying ways:
  • MySQL has a maximum key length for indexes, covering at most 191 characters when using unicode
  • MySQL and PostgreSQL have a JSON / JSONB column type, MSSQL merely has functions to process text as JSON
  • PostgreSQL is capable of returning data from INSERT and UPDATE statements, MySQL and MSSQL merely return the number of modified rows, or the IDs of modified rows at most
• PostgreSQL is FOSS, widely supported, well understood
• PostgreSQL is widely supported
  • Officially listed hosting services: https://www.postgresql.org/support/professional_hosting/
  • PostgreSQL-compatible:
    • Neon: https://neon.tech/
    • Supabase: https://supabase.com/database
    • YugaByte: https://www.yugabyte.com/
    • CockroachDB: https://www.cockroachlabs.com/
• TODO (This list is not complete yet :))

Users of DT 4.x that use any of those RDBMSes will need to migrate, and we need to provide guidance on how to do it.

A while back I documented how this can be achieved using pgloader, moving from MySQL to PostgreSQL:

https://gist.github.com/nscuro/1d32c6099a8a82f98f737fffa936b416#file-instructions-md

Because we have introduced changes to the database schema since branching off from DT v4.x, using off-the-shelf solutions like pgloader is not an option anymore. We will need to write a custom tool that performs the migration.

[nscuro]

Tagging as good first issue since it's a purely technical task that doesn't require too much knowledge of the code base.

This task involves the creation of a CLI tool that:

• connects to a DT v4.x database (can be MSSQL or PostgreSQL, potentially H2)
• connects to a new DT v5.x database
• copies data from DT v4.x to DT v5.x, performing additional mappings as needed

The current database schema for PostgreSQL can be generated using the following script: https://github.com/DependencyTrack/hyades-apiserver/blob/main/dev/scripts/dbschema-generate.sh

The same can be done for DT v4.x with this script: https://github.com/DependencyTrack/dependency-track/blob/master/dev/scripts/dbschema-generate.sh

[worming004]

Could we update the schema in the readme ? It is missleading that we could still use mssql server for hyades

EDIT : fixed with https://github.com/DependencyTrack/hyades/pull/1198