Support popular relational database as a data store

[phucnh]

What would you like to be added:

Support popular relational database like MySQL, MariaDB

Why is this needed:

Relational database like MySQL, MariaDB is widely used, and almost cloud services provide relational database as a service. The user could also create and manage relational database on their own infrastructure easily.

[nghialv]

Thanks for your issue.

As you know, currently we are only supporting NoSQL databases because they are schema-less databases, they encourage flexibility and that is good for this initial phase of PipeCD deployment. We don't want to make PipeCD's user has to do any DB migration tasks while upgrading PipeCD in their environment. That is why we decided to support NoSQL databases first.

But as your suggestion (in the slack chat), MySQL version 5.7.8 introduces a JSON data type that allows us to accomplish the same benefits from schema-less databases.

I think this is a nice way to support MySQL as well as a good way to mitigate the list of databases that PipeCD has to support. In the ideal case, PipeCD supports only MySQL (or PostgreSQL) and users can choose any fully-managed SQL service from their cloud provider such as Google CloudSQL, Amazon RDS, ...

We need to investigate more closely about using JSON type in SQL, but that is totally worth it. So excited about this.

Thank you.

[phucnh]

I agree with you. Schema-less is good for this initial phase and good for users as you've mentioned 👍 . Regarding our discussion, from version 5.7.8, MySQL supports json data type natively, and introduced many JSON functions as well. So, I think we can combine the schema and schema-less to implement pipecd's data store. For optimizing query performance, I think we should design table with primary key and foreign key (stable fields) as table fields, other fields should be stored in a json data type field.

MySQL also supports creating indexes for specified json fields with Secondary Indexes and Generated Columns. So we can create indexes to optimize query performance without enforcing user do the migration.

Any considerations?

[nghialv]

For optimizing query performance, I think we should design table with primary key and foreign key (stable fields) as table fields, other fields should be stored in a json data type field.

Yes, I have the same thoughts.

Can you give us some comparisons of fully-managed SQL services provided by AWS?

And I think not just MySQL but PostgreSQL is also supporting JSON type. So we may need to decide which is better to support. What do you think?

[nghialv]

/assign @khanhtc1202

[phucnh]

@nghialv Sorry for the late response. The AWS is supporting the following SQL databases:

Database	Versions	Ref
PostgreSQL	9.5, 9.6, 10, 11, 12, 13	https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html
MySQL	5.5, 5.6, 5.7, 8.0	https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MySQL.html
MariaDB	10.0, 10.1, 10.2, 10.3, 10.4, 10.5	https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MariaDB.html
Oracle	12c 12.1.0.2, 12c 12.2.0.1, 18c 18.0.0.0, 19c 19.0.0.0	https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html
Microsoft SQL Server	2008, 2012, 2014, 2016, 2017, 2019	https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

AWS also supports Amazon Aurora, the fully managed relational database engine that's compatible with MySQL and PostgreSQL. However, it runs on AWS infrastructures only, so I think we can overlook it.

[nghialv]

@phucnh Thank you for the list. 💯

[khanhtc1202]

The MySQL database is currently available as PipeCD's supported datastore. Thank @phucnh for your cooperation 🥳 /close

[pipecd-bot]

@khanhtc1202: Closed this issue in response to this comment.