Setup DB (Postgres)

During my programming career I used many different DBs. It was relational and nosql DBs. I liked nosql due to schema free approach. It is perfect for fast prototypes development and in small projects, but in long term it causes many hidden problems with project management. With JSONB field type Postgres becomes a real Grail in DB world. With all power of regular relational DB it has option to store and process schema-free data. It is perfect alliance. Let's add Postgres to our DB. Moreover this DB has free plan in Heroku (https://elements.heroku.com/addons/heroku-postgresql), so we can start with our small Production easily.

One more thing related to DB in our project will be ORM - SQLAlchemy it the best solution I used in different python projects and this time not an exception.

SQLAlchemy
https://www.sqlalchemy.org/
Flask-SQLAlchemy
https://flask-sqlalchemy.palletsprojects.com/en/2.x/

Life-Hack for setup and run different types of DB or any other tools required for your app in local environment. I'm crazy about Docker Containers. It is greatest tools I used in my local dev machine. I don't need how to install and setup each particular tool. What I need to know is Docker. As a real example here it bash script that allow run Postgres DB (I don't describe the full Docker setup for your System. It can be tricky and you should learn this tool by yourself):

#!/bin/bash

echo run postgres container

docker run --rm \
  -e 'POSTGRES_PASSWORD=1234' \
  -e 'POSTGRES_USER=postgres' \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:10.3

You can play around with option in this script or you can simply use standard Postgres setup, but the main idea is to have working DB instance for our project. 

Let's prepare our first DB Model:

./backend/models/user.py

from models import db

class User(db.Model):

    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(255), unique=True, nullable=False)
    email = db.Column(db.String(255), unique=True, nullable=False)
    password = db.Column(db.String(255), nullable=False)

And to make it works we need to add

./backend/models/__init__.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy(session_options={"expire_on_commit": False})

from .user import User  # NOQA

Update in ./backend/app.py

...
from flask_migrate import Migrate
...

...
def create_app():
    app = Flask(__name__)

    app.config['SQLALCHEMY_DATABASE_URI'] = os.environ['DATABASE_URL']
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    app.config['SECRET_KEY'] = os.environ.get('SECRET_KEY')
    app.config['SQLALCHEMY_ECHO'] = bool(os.environ.get('SQLALCHEMY_ECHO', False))

    CORS(app)

    from models import db  # NOQA
    db.app = app
    db.init_app(app)

    Migrate(app, db, directory="backend/migrations")

    ...

Create empty DB for your application (in my case it will be DB with name "b2")

Setup Flask-SQLAlchemy and Flask-Migrate. We are going to use Alembic, which is part of Flask-Migrate, to manage database migrations to update a database’s schema. The source of truth for our DB are models and we should prevent all direct manual updated in DB setup.

https://flask-migrate.readthedocs.io/

flask db init
flask db migrate -m "Initial migration."
flask db upgrade

Open your DB in any Postgres client (I prefer PSequel for Mac). You should see two tables: alembic_version and users. So it means all works as expected and your migration is good.

Create one test user in users table. Don't care about password in this step, we will encrypt it latter.

Let's test it. Add this test code to your Home controller

users = User.query.all()
print('Users: ', users)

and reload home page in the browser. In console you should see:

Users:  [<User 1>]

127.0.0.1 - - [13/Mar/2020 11:54:06] "GET / HTTP/1.1" 200 -

DB setup for out app is finished!

To downgrade DB to previous revision use this command
flask db downgrade