Wednesday, 20 October 2021

SQLAlchemy with multiple binds - Dynamically choose bind to query

I have 4 different databases, one for each one of my customers (medical clinics), which all of them have the exact same structure.

In my application, I have models such as Patient, Doctor, Appointment, etc.

Let's take one of them as an example:

class Patient(db.Model):
    __tablename__ = "patients"

    id = Column(Integer, primary_key=True)
    first_name = Column(String, index=True)
    last_name = Column(String, index=True)
    date_of_birth = Column(Date, index=True)

I've figured out that with the help of binds I can create different databases and associate each model to a different bind. So I have this configuration:

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://user:pass@localhost/main'
app.config['SQLALCHEMY_BINDS'] = {
    'clinic1':'mysql://user:pass@localhost/clinic1',
    'clinic2':'mysql://user:pass@localhost/clinic2',
    'clinic3':'mysql://user:pass@localhost/clinic3',
    'clinic4':'mysql://user:pass@localhost/clinic4'
}

Now I'm trying to achieve two things:

  1. I want that when I create tables using db.create_all() it will create the patients table in all 4 databases (clinic1->clinic4)
  2. I want to be able to choose a specific bind dynamically (in runtime), so that any query such as Patient.query.filter().count() will run against the chosen bind database

Ideally, it would behave like this:

with DbContext(bind='client1'):
    patients_count = Patient.query.filter().count()
    print(patients_count)

# outside of the `with` context we are back to the default bind

However, doing this:

patients_count = Patient.query.filter().count()

without specifying a bind, will raise an error (as the patients table does not exist in the default bind)

Any code example that can guide how this can be done would be highly appreciated!

P.S. It might be that you would suggest not to use different databases and instead use one with different columns / tables but please stick to my example and try to explain how this can be done using this pattern of multiple identical databases
Thanks!



from SQLAlchemy with multiple binds - Dynamically choose bind to query

No comments:

Post a Comment