Support read and write connection

[josephmancuso]

Should be of lower priority but we should be able to support read and write connections. These should be set on the connection driver.

We will need to specify these in the query builder when creating the connection class as well as the schema class. But we should only specify it in those 2 classes so it should not be a big issue adding this.

The connection details should look something like this:

    'mysql': {
        'read': {
            'host': '192.168.1.1'
        },
        'write': {
            'host': '192.168.1.2'
        },
        'driver': 'mysql',
        'database': 'database',
        'username': 'root',
       ...

Anything inside those read and write configs should override the other configs.

When creating the connection class we will need to fetch this information properly and pass it to the connection class

[girardinsamuel]

I am not sure of understanding the use case here ? Is it for example to be able to read from a database server 1 and to write on an other database server 2 ?

[josephmancuso]

@girardinsamuel Yes exactly. I haven't personally seen a need for that and I've built lots of applications both personally, for clients and for businesses but its a feature in Orator and Laravel's Eloquent and it would be fairly simple to do.

The only trick, and another requirement for this, is to make a sticky option which is a read and write to the same connection within the same request.

So in other words we would need to be able to support something like this:

# Sticky: False
User.create({}) # .. write to 192.168.1.1
User.all() # .. read from 192.168.1.2

But we need to support a sticky options where it will read to the server it just wrote to.

# Sticky: True
# First request
User.create({}) # .. write to 192.168.1.1
User.all() # .. read from 192.168.1.1

# Sticky: True
# Second request
User.all() # .. read from 192.168.1.2

If that make sense. A lot of people will create this second database server only for reads or only for writes which increases performance. But when they write to one of the database servers its not going to be available in the read database server so they need to read from the database server they just wrote to but only for that request cycle.

I've been giving some thought over last few days and really not sure how we will do the sticky option here .. somehow we need to tie an encoded request string like RQyugshajq81 to a specific request connection maybe and then store and fetch from the StickyRequestConnection class maybe. This ORM obviously needs to work outside of a request life cycle. This ORM needs to work in somebodies simple python file.

Orator does not have this sticky option I believe so we will need to come up with our own solution