rds: Reboot the construct library

[nija-at]

The RDS construct library today closely mimics how resources type are organized in CloudFormation -

The additional constructs such as DatabaseSecret does add value over simply using the L1 constructs. However, there are a number of modeling restrictions that come with this. A few instances of this are -

1. The list of database engine versions are different depending on the database engine picked that is picked - https://aws.amazon.com/rds/faqs/#Database_Engine_Versions
2. Some properties apply to only some database engines but not others, such as timezone that applies only to SQL Server. This leads to guard rails based development where there are validations all over the place, and the user has to keep adjusting to avoid these validations and take the working path.
3. An extension of Update docs #2 but applies more broadly to Parameter Groups. Parameters like the inno_db_file_per_table will apply to Mysql and Aurora but not to other database types. Similarly, the aurora_load_from_s3_role applies to Aurora databases but not others.

Codifying all of this in the current RDS model will lead to fragmented code, validations lying around and overall, a poor customer experience where a feature is advertised as available (i.e., it's in the Props interface) but is actually not because there is validation that prevents it based on the database type (or some other condition).

The alternative is to reboot the construct library along the lines of creating constructs such as AuroraDatabase, MysqlDatabase, AuroraDBCluster, etc. which would make this experience significantly better.

[nija-at]

One more customer complaint and case for why we need this - #7072

[eladb]

Is it possible to offer subclasses for specific database types with all the specific APIs in there and leave the current api in tact. We can also do this incrementally (that is implement one subclass, Then another...)

[nija-at]

Definitely possible.

However, my feeling is that module is going to cause long term maintenance and customer usage pain. For instance, this PR - #6611 - the work was to simply add a property to the CDK interface that needs to get passed down to two different CloudFormation resources (the DB cluster and parameter group).
The amount of code that needed to be written behind defensive code block was quite high and the number of iterations of the PR could have been significantly reduced if we had the correct model in place.

[skinny85]

We decide to forgo this large "reboot" in favor of incremental improvements, and minimizing breaking changes.