How to Use Multiple Databases in Flask Application?

Hey Database developer, In this article we will explore how can you integrate multiple databases with your flask application. We will also look into the benefits of using multiple databases in your flask application.

 

Good reasons to create separate databases would be to support different availability requirements or simplify administration. For example, if your databases require very different backup schedules or different recovery models. Another reason would be if you may want to run them on different instances.

following are the few advantages of using multiple databases.

 

• Maintenance work, hardware problems, security breaches, and so forth do not necessarily impact the whole platform
• Assuming each database is on separate hardware, scaling up multiple machines yields more performance benefits than scaling up one big one

 

So that was the basic idea behind using multiple databases in your flask application. Now let's head over to the coding and configuration part of the multiple databases integration.

Starting from scratch we will build a minimal flask application to demonstrate the integration of multiple databases in flask application.

Installation:- 

 

We will require the following packages to set up the environment.

• virtualenv:- for a user to create multiple Python environments side-by-side. Thereby, it can avoid compatibility issues between the different versions of the libraries
• flask 
• flask_sqlalchemy:- for mapping the python objects to database relational table.
• psycopg2:- It works as a connecter between the flask application and the PostgreSQL database.
• MySQL client:- It works as a connecter between the flask application and MySQL database.
• Flask_Migrate:- for migrating the flask application model into the database tables.

 

After completing the installation process of the above-mentioned packages. Create a file named as app.py which is the main file of the flask application and add the following code in the file.

#import statements
from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

# initialzing the instance of flask application using Flask() constructor
app = Flask(__name__)

# create database instance of SQLAlchemy
db = SQLAlchemy(app)

# intializing Migrate function to perform migration stuff
migrate = Migrate(app, db)

# The route() function of the Flask class is a decorator,
# which tells the application which URL should call
# the associated function.
@app.route('/mysql')
def index():
    return "Hello DataBase Developer"

# driver function which will run the app variable only from this file
if __name__ == '__main__':
    app.run(debug=True)

Now just run the file and the flask application will be running at 5000 port.

If you will open the 'localhost:5000/MySQL URL you will see "Hello DataBase Developer" will be there on the browser.

Now let's configure our database connection for MySQL and PostgreSQL using flask_sqlalchemy.

In a flask, the database connection is built using SQLALCHEMY_URI, If we are using multiple databases then we need to use SQLALCHEMY_BIND and need to assign a dictionary of Databases URI as key_value pair.

 

Add the following code in your app.py as below which will integrate the multiple databases in the flask application.

#import statements
from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

# initialzing the instance of flask application using Flask() constructor
app = Flask(__name__)

# create database instance of SQLAlchemy
db = SQLAlchemy(app)

# sqlalchemy database URI for mysql as default databse
# SQLALCHEMY_DATABASE_URI syntax => 'databaseEngineName://dbUserName:password@host/databaseName'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:@localhost/roadcast'

# sqlalchemy database URI for postgresql as additional database
app.config['SQLALCHEMY_BINDS'] = {
    'db_postgresql': 'postgresql://postgres:Esh@localhost/RoadCast'
}

# intializing Migrate function to perform migration stuff
migrate = Migrate(app, db)

# The route() function of the Flask class is a decorator,
# which tells the application which URL should call
# the associated function.
@app.route('/mysql')
def index():
    return "Hello DataBase Developer"

# driver function which will run the app variable only from this file
if __name__ == '__main__':
    app.run(debug=True)

So we have made a connection with multiple databases, So it's time to add a few tables into the database.

Now create a file named as models.py file and create a model class that will map with the database tables.

In the model class, we need to specify which model class belongs to which database.

 

• We use __bind_key__ to tell the model class in which database this table should be stored.
• In __bind_key__ you need to assign the key value of SQLALCHEMY_DATABASE_URI which we assign in the SQLALCHEMY_BIND
• If you are using default DATABASE_URI you don't need to add the __bind_key__ in the model class.

 

We are creating here two model classes, Article model class will be added into the PostgreSQL database and the Users model class will be added into the MySQL database. Write the below code in the models.py file.

from app import db

# this model will map with the article table in the RoadCast Database of postgresql engine
class Articles(db.Model):
    # bind key for telling the flask that this table is belongs to postgresql database
    __bind_key__ = 'db_postgresql'

    # column field of articles class
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80),nullable=False)
    slug = db.Column(db.String(80),unique=True, nullable=False)
    desc = db.Column(db.String(200),nullable=False)
    content = db.Column(db.Text(), nullable=False)

    # constructor of Articles model to initialize the instance
    def __init__(self, title,slug, desc, content):
        self.title = title
        self.slug = slug
        self.desc = desc
        self.content = content


# this model will map with the user table in the Roadcast database of mysql engine
class Users(db.Model):

    # no need to pass the bind key because it's a default SQLALCHEMY DATABASE URI
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80),nullable=False)
    email = db.Column(db.String(80),unique=True, nullable=False)
    username = db.Column(db.String(80),unique=True, nullable=False)

    # constructor method of user model
    def __init__(self, name,email, username):
        self.name = name
        self.email = email
        self.username = username

Now import models into app.py file just after the database connection as below.

#importing models from models.py file
import models

So now It's time to migrate the Model class which will map with database table by migration. If there is any existing table it will just map with it and if not if will create a table on the bases of model class.

Now Run following 3 command on your terminal one after another.

# this command will create a migration folder in you application directory that will manage all the version of your migrations.
flask db init

# You can then generate an initial migration:
flask db migrate -m "Initial migration."

# Then you can apply the migration to the database:
flask db upgrade

Now if you will check your databases Article table will be create in the PostgreSql database and Users table will be created in the MySql database.

Now it's time to perform some queries to check that our databases are working according to our need or not. So add the following route and respective view function in your app.py file.

# route for mysql data fetch
@app.route('/mysql')
def index():
    # fetching data from the user table of mysql
    user_obj = models.Users.query.all()
    return render_template('index.html', users= user_obj)


# route for postgresql data fetch
@app.route('/postgresql')
def postgre():
    #fetching data from the article table of postgresql
    article_obj = models.Articles.query.all()
    return render_template('index.html', articles= article_obj)

And now create a folder named as templates and add a index.html file. Open your index.html file and add the following code in the file.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Flask Application</title>
</head>
<body>
    <section>
        <table id="users">
            <!-- if user will be return from the database then this code will execute -->
            {% if users %}
                <h3>Users...</h3>
                <tr>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Username</th>
                </tr>
                <!-- iterating over user list -->
                {% for user in users %}
                    <tr>
                        <td>{{user.name}}</td>
                        <td>{{user.email}}</td>
                        <td>{{user.username}}</td>
                    </tr>
            {% endfor %}
            <!-- if article will return from database then this code will execute -->
            {% elif articles %}
                <h3>Articles...</h3>
                <tr>
                    <th>Title</th>
                    <th>Desc</th>
                    <th>Content</th>
                </tr>
                <!-- iterating over articles list -->
                {% for article in articles %}
                    <tr>
                        <td>{{article.title}}</td>
                        <td>{{article.desc}}</td>
                        <td>{{article.content}}</td>
                    </tr>
                {% endfor %}
            <!-- if neither user nor article will execute then this code will execute -->
            {% else %}
                <h3>Nothing is available...</h3>
            {% endif %}
          </table>          
    </section>
</body>
</html>

For styling we need to create a folder named as static and create a style.css file in the folder. Write below CSS code in the folder.

*{
    margin:0;
    padding:0;
    box-sizing: border-box;
}
section{
    display: flex;
    justify-content: center;
    align-items: center;
    flex-direction: column;
    min-height: 100vh;
    width:70%;
    margin:auto;
}

section h3{
  margin:10px 0px;
  font-size:30px;
  font-weight: 600;
}

#articles ,#users {
    font-family: Arial, Helvetica, sans-serif;
    border-collapse: collapse;
    width: 100%;
  }
  
td,th {
    border: 1px solid #ddd;
    padding: 8px;
  }
  
tr:nth-child(even){background-color: #f2f2f2;}
  
tr:hover {background-color: #ddd;}
  
th {
    padding-top: 12px;
    padding-bottom: 12px;
    text-align: left;
    background-color: #E03B8B;
    color: white;
  }

So finally we have reached at the end of this session. we are just one step ahead from achieving the goal of this article. 

Now link this CSS file with you HTML file (index.html) using this line of code in the head section.

<!-- connecting stylesheet -->
    <link rel="stylesheet" type="text/css" href="/static/css/style.css">

That's it we have successfully built a Flask Application with Multiple Databases. Now you can run your application using the "flask run" command in your terminal. We have integrated the PostgreSQL and MySQL database with flask application using SQLALCHEMY_BIND property.

If you have any queries related to this article you can tell me in the comment section below or contact me using email.

Thanks for Reading.