Feature: Automatic Connection Switching & Horizontal Sharding

[crimson-knight]

This is pretty important, especially since most large Rails apps have moved to use the automatic connection switching between readers/writers, and probably also for sharding.

It is currently possible to have multiple connections like this:

Granite::Connections << Granite::Adapter::Mysql.new(name: "mysql_primary", url: "YOUR_PRIMARY_DATABASE_URL")
Granite::Connections << Granite::Adapter::Mysql.new(name: "mysql_reader", url: "YOUR_READER_DATABASE_URL")

However, this does not set the actual roles for the db's shown, and you can only select a single database connection from the model.

I think we'll need to modify the connection macro to allow accepting a NamedTuple or Tuple so we can pass in some configuration settings like:

{
  writer: "mysql_primary",
  reader: "mysql_reader"
}

The current connection macro is here:

granite/src/granite/table.cr

Line 59 in 3f7a415

macro connection(name)

macro connection(connection_details)
  {% if connection_details.is_a?(StringLiteral) %}
  class_getter adapter : Granite::Adapter::Base = Granite::Connections[{{connection_details}}]
  {% elsif connection_details.is_a?(TupleLiteral) || connection_details.is_a?(NamedTupleLiteral) %}
  # Put the code here to set the reader/writer
  {% else %}
  # Probably don't actually want to raise here like this
  raise "No registered connection with the name '{{name.id}}'"
  {% end %}
end

Just throwing this out there for now, but I think it'll be easier than it may first appear to add automatic connection switching.

We would also need:

• A variable to track when the last write transaction was done to get the time since the last write
• A configuration to set the delay from switching between the writer and the reader, probably 2 seconds to allow for propagation
• A way to tell if the action being performed is a read or write action. This is pretty straightforward since writing actions are insert, update & upsert and most other queries will be to read. We want to default to using the reader as often as possible.