This page will give you the steps you need to take for making your application use PostgreSQL, which is a production database system. This will show you how to set it up in Heroku.

Heroku setup

In order to allow the application to use PostgreSQL, we need to add it as an add-on for our Heroku application. You can do this through the website, or more simply through the use of the following command:

heroku addons:create heroku-postgresql:hobby-dev

This will create a free instance of PostgreSQL that you can use for your project. It will define the URL to access the server as an environment variable called DATABASE_URL, which we will use to connect to our database, both for SQLite and SQLAlchemy.

SQLAlchemy conversion

Converting your SQLAlchemy to a PostgreSQL application is quite simple. The only thing you need to change is the URL for the database, SQLAlchemy will handle the rest automatically.

Normally when using SQLite3, you would initialize your database in a way similar to this:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

In order to use the PostgreSQL database on Heroku, we simply change the SQLAlchemy configuration to get this value from the operating system like this:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os

app = Flask(__name__)
uri = os.getenv("DATABASE_URL")  # or other relevant config var
if uri.startswith("postgres://"): # from SQLAlchemy 1.14, the uri must start with postgresql, not postgres, which heroku provides
    uri = uri.replace("postgres://", "postgresql://", 1)
app.config['SQLALCHEMY_DATABASE_URI'] = uri
db = SQLAlchemy(app)

After making these changes, it should use the value for the configured DATABASE_URL, which in our case will be set by Heroku.

SQLite3 conversion

Converting SQLite3 to use PostgreSQL is a bit more challenging compared to SQLAlchemy. Essentially we need to replace the SQLite3 library in the code with a library called psycopg2.

Installing PostgreSQL

First install the library. To do this, you need to install PostgreSQL on your machine, see this link for instructions. After that, we can install the library using:

pip install psycopg2

Also remember to add this library to your requirements.txt

Changing the code

Using the flasktutorial project as an example, we will see how you can make the changes to use the newly installed psycopg2 library. As a refresher, the part of this application where most of the database logic is defined is in __init__.py. Here is the old version of the file:

from flask import Flask, g
import sqlite3
app = Flask(__name__)

DATABASE = 'database.db'


def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
    db.row_factory = sqlite3.Row
    return db


@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()


def init_db():
    with app.app_context():
        db = get_db()
        with app.open_resource('schema.sql', mode='r') as f:
            db.cursor().executescript(f.read())
        db.commit()


def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    rv = cur.fetchall()
    cur.close()
    return (rv[0] if rv else None) if one else rv


import flasktutorial.views

First, we will change the imports and the DATABASE variable like so:

from flask import Flask, g
import psycopg2
import psycopg2.extras
import os

app = Flask(__name__)

DATABASE = os.environ['DATABASE_URL']

This imports the psycopg2 library, along with the extras from that same library, which we will use later. We also set the DATABASE variable to be the value of the DATABASE_URL, which will be provided by Heroku.

Next, for the get_db function, we need to replace SQLite with the new module:

def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = psycopg2.connect(DATABASE)
    return db

It should be noted that we removed the row factory specification from this function. That part works a little differently for psycopg2. Other than that, sqlite3 has simply been replaced with psycopg2.

The tear-down function close_connection is exactly the same as before.

Our function init_db creates the application for the first run by running the SQL statements in the schema.sql file. Here the only change is that we use db.cursor().execute instead of db.cursor.executescript

def init_db():
    with app.app_context():
        db = get_db()
        with app.open_resource('schema.sql', mode='r') as f:
            db.cursor().execute(f.read())
        db.commit()

For the query_db function, the only difference is that we explicitly get the cursor object before we can execute the query. Also note that we set cursor_factory to be a DictCursor. This allows us to access the rows like dictionaries, like we did with SQLite3's row factory previously.

def query_db(query, args=(), one=False):
    cur = get_db().cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute(query, args)
    rv = cur.fetchall()
    cur.close()
    return (rv[0] if rv else None) if one else rv

Query syntax

The query syntax is very similar to SQLite3, but slightly different. We will make one change in the views.py file to illustrate this.

The following is what we used in SQLite3:

article = query_db('SELECT * FROM article WHERE id=?;', (str(article_id)), True)

And this is what we use for PostgreSQL:

article = query_db('SELECT * FROM article WHERE id=%s;', (article_id,), True)

Here we change the ? sign in the SQL query to %s. We also simplify things and simply pass in a tuple containing the artcle_id.

---

Remember that the full code is available from the GitHub repository. Also let me know if you experience any issues regarding this, just let me know, and I will try to help you and update this document accordingly!