sqlalchemy multiple db

Setting Up SqlAlchemy To Use Multiple Databases

In my latest project, PriceMind I wanted to make the database more scalable. I had one database used by Flask. Inside that one db I had the tables a user needs to reach. Also, I had the users table inside this db. But it was dumb because each user has to have its own database with its own tables in order to scale properly. But then how do I access the users table? Should I create a new database for user table?

So I realized that in order to have a scalable system I need to have a separate database only to store user information like email, name etc. Considering my stack, I had to research how to setup SqlAlchemy and Flask to use multiple databases. One for user management. One for querying user-specific data.

SQLAlchemy Binding

You can setup an arbitrary number of separate databases with SQLAlchemy. You just have to “bind” each database URI to a unique name. So when you create a db model in your application you need to bind which database to use. Otherwise the one will be used which is defined by SQLALCHEMY_DATABASE_URI.

SQLAlchemy Config

In my application I have a file which contains the configuration settings for SQLAlchemy.

def __init__(self, user):
    self.SQLALCHEMY_DATABASE_URI = 'mysql://mysqluser:password@ipaddress/db_'+user
    self.SECRET_KEY = 'secret_key'
    self.DEBUG = True
        'users': 'mysql://mysqluser:password@ipaddress/users_db',
        'shop': self.SQLALCHEMY_DATABASE_URI

As you can see, I defined SQLALCHEMY_DATABASE_URI which is dynamically created so it always uses the db which belongs to the current user.

Also, I defined two binds in SQLALCHEMY_BINDS. One for users table and another one for heavy data tables. But I only need to explicitly bind the users db because the other one is used by default.

User Model Binding

So we’ve created the binds, now invoke it in the db model.

This is a pretty basic user model in my app.

class User(db.Model):
    __bind_key__ = 'users'
    __tablename__ = "users"
    id = db.Column('id',db.Integer, primary_key=True)
    username = db.Column('username', db.String(255), unique=True)
    password = db.Column('password' , db.String(255))
    email = db.Column('email',db.String(255),unique=True)
    since = db.Column('since' , db.Date)
    shop = db.Column('shop' , db.String(255))

    def __init__(self , username ,password , email):
        self.username = username
        self.email = email
        self.since = datetime.datetime.utcnow()

    def register(self):

    def set_password(self , password):
        self.password = generate_password_hash(password)

    def check_password(self , password):
        return check_password_hash(self.password , password)

    def is_authenticated(self):
        return True

    def is_active(self):
        return True

    def is_anonymous(self):
        return False

    def get_id(self):
        return unicode(self.id)

    def __repr__(self):
        return '<User %r>' % (self.username)

Simply, using __bind_key__ we’ve told SQLAlchemy to use the users database to manage users. If we don’t define a bind SQLALCHEMY_DATABASE_URI will be used.

Download FREE ebook!