Continuing my Raspberry Pi/ Docker/ Flask experiment, I wanted to connect a database to my flask app. I decided on postgresql for my database and running on a separate docker container.

Docker setup

To setup postgres I used the official postgresql instance with docker pull postgres and I start it with docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgres.

To setup a postgres table, start the postgres instance with: `docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgres

Then enter the container with docker exec -it some-postgres bash

To create a postgres table, enter postgres with: psql -U postgres

Then create a database with CREATE DATABASE testdb;

Connecting Postgres to flask

To use postgresql with flask, I use SQLAlchemy, a python wrapper for SQL and flask-migrate, a wrapper for Alembic, a database migration framework for SQLAlchemy. To use postgres with python, I also need Psycopg2, both a python package and Arch package. I also use python-dotenv to load environmental variables declaring postgres.

First install Psycopg2 to Arch with pacman -S python-psycopg2

Then run pip install flask-sqlalchemy flask-migrate psycopg2 python-dotenv to install the necessary python packages.

To connect postgresql to flask, create an environmental variable file in your flask application called .env.

Inside .env set up environmental variables for your postgres db.

# .env
export POSTGRES_URL="127.0.0.1:5432"
export POSTGRES_USER="postgres"
export POSTGRES_PW="mysecretpassword"
export POSTGRES_DB="testdb"

Then in your root directory of your flask application create a file called config.py which will house the configurations for the postgres database. We'll load the environmental variables for postgres and create SQLAlchemy configurations for the app.

# config.py
import os
from dotenv import load_dotenv

# Sets the base directory path
basedir = os.path.abspath(os.path.dirname(__file__))

# loads the environment variable file
load_dotenv()

# Function to get the environmental variables
def get_env_variable(name):
    try:
        return os.environ[name]
    except KeyError:
        message = "Expected env variable '{}' not set.".format(name)
        raise Exception(message)

# Set the variables
POSTGRES_URL = get_env_variable("POSTGRES_URL")
POSTGRES_USER = get_env_variable("POSTGRES_USER")
POSTGRES_PW = get_env_variable("POSTGRES_PW")
POSTGRES_DB = get_env_variable("POSTGRES_DB")

DB_URL = 'postgresql+psycopg2://{user}:{pw}@{url}/{db}'.format(user=POSTGRES_USER,pw=POSTGRES_PW,url=POSTGRES_URL,db=POSTGRES_DB)

# Sets the database
class Config(object):
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'butter'
    SQLALCHEMY_DATABASE_URI = DB_URL or \
            'psql:///' + os.path.join(basedir)
    SQLALCHEMY_TRACK_MODIFICATIONS = False

Then you'll need to change your __init__.py script to migrate the database instance.

# app/__init__py
from flask import Flask
from config import Config
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
app.config.from_object(Config)

db = SQLAlchemy(app)
migrate = Migrate(app, db)

from app import routes, models

The next step is to create a database model. For this example, I create a user database model, where the table contains an ID, a string username, string email, and a string password hash. Create a models.py in the app directory.

# app/models.py
from app import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))

    def __repr__(self):
        return '<User {}>'.format(self.username)

The User class inherits from db.Model, which is a base class for all models from Flask-SQLAlchemy. The following lines create the instances of the db.Column class

The __repr__ method tells python how to print the objects of this class.

You can test the models out by loading the python interpereter and running:

>>> from app.models import User
>>> u = User(username='bob', email='bob@email.com')
>>> u
<User bob>

Database migration

To migrate the database run flask db init.

Then run flask db migrate -m "users table"

And to apply changes to the data base run: flask db upgrade

At this point you can check the postgres container to see if any changes were made.

Login to the postgres instance again with: docker exec -it some-postgres bash then into postgres with psql -U postgres

Next connect to the test database with: \c testdb and finally view the tables with \l

Now if you see the user table, you'll know that postgres was succesfully connected to the flask app.