Implement additional database support

[freben]

Feature Suggestion

Implement official support for more databases!

Backstage has been prepared from the start to support multiple databases, and a lot of the ground work is in place - we use the knex library for both database queries and migrations, and we are mindful of different database engines when constructing the different plugins. We also have some support in place for continuously running tests against many database engines.

So far, we have been concentrating on PostgreSQL for production use, and SQLite for development use. But we want to expand to support the other database engines that knex supports. The remaining ones at the time that this was written, are MSSQL, MySQL, MariaDB, Oracle, and Amazon Redshift.

Possible Implementation

At least the following parts may be affected:

• Extend the test utils to more engines
• Update the backend-common database code, in particular the DatabaseManager and its connectors folder.
• Go through all of the database migrations (e.g. folders like this), and make sure that they can complete successfully
• A lot of existing tests should be fleshed out to actually make use of the above mentioned test utils

Databases

These are the engines that are supported by Knex, that aren't yet officially supported by Backstage. This can be used as a progress tracker - please link related pull requests to this one, and tick off the box when they are merged and deemed complete!

• [ ] MySQL
• [ ] Amazon Redshift
• [ ] MariaDB
• [ ] Microsoft SQL
• [ ] Oracle

Context

See https://github.com/backstage/backstage/discussions/6241

[benjdlambert]

One would assume that we should have support for Redshift already as it's Postgres compliant. Worth a try though to confirm first maybe.

[freben]

Yep and since it has a dedicated entry in the knex docs, it led me to believe it at least had some different behaviours somewhere - perhaps in the database creation stuff at least, or regarding auth, or connection string format, or ... yeah you know.

[freben]

I think this in general goes for some of the hosted solutions, right? It may be the case that they have some layer of service account auth or other things that get in the way of the full end-to-end picture - even if the SQL queries that run inside the finally established connections happen to be exactly compliant

[GoWind]

I would really like to STRONGLY push back against this : What problem does supporting multiple databases solve ?

b) Is this being done because Knex can support multiple databases ? Do we actually have a usecase where multi-DB support is needed ?

Multi-database is tricky. there are simply far too many differences between the behavior of various RDBMS and it might be easier to support multi-cloud deployments correctly than dealing with multi-DB solutions.

Given that most organizations are going to deploy backstage as a green field project, I don't think there is any reason to support non-Postgres (maybe Redshift) databases (and keep this project simple)

[julioz]

I am not sure I understand the motivation of the issue, does multi-database mean running different engines side-by-side? Is that the intended goal?

For our use-case, what we would like is to be able to run MySQL as RDBMS instead of the current options (SQLite/Postgres). Internally we have set up infrastructure to support scaling MySQL database clusters, and have tooling and experience operating it. In that sense, it would be a big plus and a reason for us to adopt Backstage if it did fit with our existing database offer.

[freben]

Yeah, only one db at a time! MySQL is indeed the one I'd prioritize highest to add. The issue is about making sure that the existing backstage code can run successfully on a larger number of database engines than today.

[shoukoo]

We would also like to have MYSQL to be supported in Backstage. For the same reason as @julioz, our internal teams have more experience scaling/operating MYSQL.

[silvaleandrodp]

We would also like to have MYSQL to be supported in Backstage. For the same reason as @julioz and @shoukoo, our internal teams have more experience scaling/operating MYSQL.

[freben]

We'd love some help to make this happen!

[josesimon]

I´m also expecting to have mysql support, i would appreciatte if you could make it

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[barmatz]

I work with a team that wishes to run their own version of Backstage for a large organisation. The org is not able to use PostgreSQL and requires MySQL or MSSQL. Lack of support and guidance for additional databases may hinder the eventual adoption of the platform.

Are there any recommendations on how we could support a different type of database?

[jhaals]

Supporting another database would be a fairly large effort not just once but also over time to make sure it's properly supported going forward from multiple individuals or an organisation that are willing to invest time and effort long term. We currently use sql features in catalog and likely in other plugins that vary slightly from the different SQL implementations so we would have to thread carefully to make sure that it works across versions but also not to pollute the code with if statements depending on vendor. We already have quite a bit of comparisons between postgres and sqlite where those two differ. This is in the back of our mind in the maintainer team but is unfortunately not a prioritised effort currently.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[dalvarado]

Hello, @freben we're looking to contribute and add MySQL support to Backstage. What is the best way to proceed? Open a new issue (feature request)? Asking mainly due to the checkbox about whether the feature request has been raised before. Thanks!