Refactor ORM to support offloading read-only queries to another database

[jakxnz]

We want to introduce support for application environments that use multiple databases for performance objectives, by developing support for the ORM to offload read-only queries to an alternate database to allow developers or vendors with platforms/infrastructure for Silverstripe CMS to alleviate load on a primary write database and improve scalability and performance of the overall database system

Related issues

• EPIC Separating to a Read-Access Database from the primary Database .github#133
• RFC: Add support for database read replicas #7267
• Readonly mode for database #5693
• https://github.com/silverstripeltd/product-issues/issues/882

Notes

• Take a look at how this is typically implemented in other ORMs and how it's used. There are likely unknown-unknowns about how this needs to function in order to be truly useful
• We need to decide whether to allow explicitly using the primary DB for read operations for specific scenarios even when there's a read-only db available (e.g. if you immediately need to fetch the value you just wrote to the DB)
• Other ORMs that allow read-only replicas actually allow an arbitrary number of replicas, not just the one. For example see Doctrine's replicas config and their old master/slave setup. We should probably aim to do the same, for maximum flexibility and scalability.
• Will probably look at raw SQL and if it's a SELECT always use a read-replica, otherwise use the primary. However this means that any unsynced data probably won't be there. One solution could be to have a DB::withPrimary($callback) to ensure the primary database is used.
• Laravel has a sticky option that means use the write database for all queries going forward in the current request cycle after a write operation has been performed. This solves the issue of needing to read something after writing straight away. It may make sense for us to do something similar, possibly just have it permanently on and not allow configuring it off as there's probably a million ways something could get out of sync and break. There may however be some scenarios where the database is being written to on every request e.g. custom logging which would disable using the replicas

Acceptance criteria

• At least one additional database can be optionally configured via environment variables for read-only operations
• When a read-only database is available, the primary database is only used for write operations (Steve: I'm not sure about this one, it may make sense to do something like the /admin section and cli always uses the primary, while the website frontend should use a replica though switches to the primary when needed e.g. userform submission)
• The implications of when data is available are fully understood - i.e. if writing to one db and then reading from another, should developers expect the new data to be available immediately or not?
• Documentation clearly outlines both how to set this up, and any gotchas that may arise.
• This will probably involve an API break and require it be in CMS 6, though ideally we can implement this in CMS 5 so that the real world benefits are realised sooner

Kitchen sink CI

• https://github.com/creative-commoners/recipe-kitchen-sink/actions/runs/11246908087
• ^ failure are existing

PRs

• DOC DB read-only replicas developer-docs#587
• FIX Ensure primary connection is updated with temporary database silverstripe-testsession#95
• NEW Allow database read-only replicas #11379
• NEW Allow database read-only replicas silverstripe-admin#1825
• ENH Only query primary DB silverstripe-session-manager#221
• ENH Only query primary DB silverstripe-hybridsessions#114
• MNT fix unit test silverstripe-mfa#569

[GuySartorelli]

PRs merged