In a recent project I needed to work on an application which aggregated data from multiple databases. Each database was a MySQL database but with different schemas and purposes. The application was built using Flask and used Flask-SQLAlchemy as its database connector.

The task was to set up the app to be able to query multiple (3 in my case) databases at the same time – using Flask-SQLAlchemy. Luckily, since version 0.12, you don’t need any kind of witchcraft to do this because Flask-SQLAlchemy has excellent support for using multiple databases.

In this article you will learn how to use Flask-SQLAlchemy to connect to two or more databases in your app. I’m going to set up connections with MySQL databases in the examples below, but you can set up any other kind of connection that SQLAlchemy supports.

Configure one database Link to heading

As a start, let’s see how you can configure even just one database:

SQLALCHEMY_DATABASE_URI = 'mysql://user:pass@localhost/database'

You might also need to specify the location of mysql.sock:

SQLALCHEMY_DATABASE_URI = 'mysql://user:pass@localhost/database?unix_socket=/var/run/mysqld/mysql.sock

With this configuration, you can only access this database in your application.

Configure multiple databases with binds Link to heading

In order to use multiple databases, you need to set up “binds”. In SQLAlchemy, binds can be either database engines or connections. In Flask-SQLAlchemy, on the other hand, binds are always engines.

If there’s no bind specified, the default database will be the one configured in SQLALCHEMY_DATABASE_URI – just like in the example above.

You can set up an arbitrary number of separate databases with SQLAlchemy. You just have to “bind” each database URI to a unique name.

Let’s see how to set up two binds for two additional MySQL databases. Here’s the configuration:

SQLALCHEMY_DATABASE_URI = 'mysql://user:password@localhost/database’
SQLALCHEMY_BINDS = {
        ‘db2’: 'mysql://user:pass@localhost/database2’,
        'db3': 'mysql://user:pass@localhost/database3’
}

In this example, the default database is still called database. But we added two other databases with binds:

  • ‘db2’ refers to database2
  • ‘db3’ refers to database3

Declare a model Link to heading

Then, when you declare a new model, you also need to specify the correct database bind that you’d like to use. If you don’t specify any binds for the model, the default database engine will be used.

Let’s see two examples with a simple User model:

Use the default database Link to heading

Normal way to declare a model, using the default database:

class User(db.Model):
    __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)

As you can see, I only specified the table name, and some attributes. But no bind, so the default engine will be used.

Use a bind Link to heading

To use a binded database, you need to specify it with the bind_key attribute.

Declare a model using database2 – which is binded to “db2”.

class User(db.Model):
    __bind_key__ = ‘db2’
    __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)

Simply, using bind_key you’ve told Flask-SQLAlchemy to use the db2 (aka database2) database to declare the model.

Create a separate config file Link to heading

If you’re like me and like to keep things clean and organized you might want to put all your Flask-SQLAlchemy (and other) configurations into a separate file – so it’s not mixed with your application code.

Let’s call it config.py:

class DbConfig(object):
    database = "database_name"
    user = "db_user"
    password = "password"
    host = "localhost"
    unix_socket = "/var/run/mysqld/mysqld.sock"
    SQLALCHEMY_DATABASE_URI = 'mysql://'+ user + ':' + password + '@' + host + '/' + database +'?unix_socket='+ unix_socket
    SQLALCHEMY_BINDS = {
        ‘db2’: 'mysql://user:pass@localhost/database2’,
        'db3': 'mysql://user:pass@localhost/database3’
}

Then inside your app just invoke the from_object method to set the configuration using the DbConfig object:

application = Flask(__name__)
application.config.from_object(DbConfig)

Going one step further, you can also have multiple configurations, for example one for production and one for testing. This way, you can always set the correct configuration object by only changing it in one place, in your config.py file:

class TestingConfig(object):
    # your config for testing environment

class ProductionConfig(object):
    # your config for production environment

class AppConfig(TestingConfig):
    def __init__(self):
            pass

in your application code:

application = Flask(__name__)
application.config.from_object(AppConfig)
# now you're using TestingConfig, because that’s the parent of AppConfig
# but if you want to use ProductionConfig, you only need to change the parent of AppConfig

Wrapping up Link to heading

So that’s how you set up multiple databases using Flask-SQLAlchemy. I hope this quick tutorial was helpful. Here’s some relevant resources which you might find useful: