Setting up the Database

Flask Unchained comes integrated with the SQLAlchemy ORM.

Install Dependencies

First we need to install SQLAlchemy and related dependencies:

pip install "flask-unchained[sqlalchemy]" py-yaml-fixtures

We also need to update our tests so that they load the pytest fixtures from the SQLAlchemy Bundle:

touch tests/conftest.py

# tests/conftest.py

from flask_unchained.bundles.sqlalchemy.pytest import *

There are two fixtures that it includes: db and db_session. They’re both automatically used; db is scoped session and will create/drop all necessary tables once per test session while db_session is function scoped, and thus will run for every test. Its responsibility is to create an isolated session of transactions for each individual test, to make sure that every test starts with a clean slate database without needing to drop and recreate all the tables for each and every test.

Next, enable the SQLAlchemy and Py YAML Fixtures bundles so we can begin using them:

# unchained_config.py

BUNDLES = [
    # ...
    'flask_unchained.bundles.sqlalchemy',
    'py_yaml_fixtures',
    'app',
]

Configuration

By default, the SQLAlchemy Bundle is configured to use an SQLite database. For the sake of simplicity, we’ll leave the defaults as-is. The SQLite file will be stored at db/<env>.sqlite.

If you’d like to change the path, it would look like this:

# app/config.py

from flask_unchained import BundleConfig

class Config(BundleConfig):
   # ...
   SQLALCHEMY_DATABASE_URI = 'sqlite:///db/hello-flask-unchained.sqlite'

If you’re fine using SQLite, continue to Initialize Migrations.

If instead you’d like to use MariaDB/MySQL or PostgreSQL, now would be the time to configure it. For example, to use PostgreSQL with psycopg2:

# app/config.py

from flask_unchained import BundleConfig

class Config(BundleConfig):
    # ...
    SQLALCHEMY_DATABASE_URI = '{engine}://{user}:{pw}@{host}:{port}/{db}'.format(
        engine=os.getenv('FLASK_DATABASE_ENGINE', 'postgresql+psycopg2'),
        user=os.getenv('FLASK_DATABASE_USER', 'hello_fun'),
        pw=os.getenv('FLASK_DATABASE_PASSWORD', 'hello_fun'),
        host=os.getenv('FLASK_DATABASE_HOST', '127.0.0.1'),
        port=os.getenv('FLASK_DATABASE_PORT', 5432),
        db=os.getenv('FLASK_DATABASE_NAME', 'hello_fun'))

class TestConfig:
    # ...
    SQLALCHEMY_DATABASE_URI = '{engine}://{user}:{pw}@{host}:{port}/{db}'.format(
        engine=os.getenv('FLASK_DATABASE_ENGINE', 'postgresql+psycopg2'),
        user=os.getenv('FLASK_DATABASE_USER', 'hello_fun_test'),
        pw=os.getenv('FLASK_DATABASE_PASSWORD', 'hello_fun_test'),
        host=os.getenv('FLASK_DATABASE_HOST', '127.0.0.1'),
        port=os.getenv('FLASK_DATABASE_PORT', 5432),
        db=os.getenv('FLASK_DATABASE_NAME', 'hello_fun_test'))

Or for MariaDB/MySQL, replace the engine parameter with mysql+mysqldb and the port parameter with 3306.

Note that you’ll probably need to install the relevant driver package, eg:

# for psycopg2
pip install psycopg2-binary

# for mysql
pip install mysqlclient

See the upstream docs on SQLAlchemy dialects for details.

Initialize Migrations

The last step is to initialize the database migrations folder:

flask db init

We should commit our changes before continuing:

git add .
git status
git commit -m 'install sqlalchemy and py-yaml-fixtures bundles'

Next, in order to demonstrate using migrations, and also as preparation for installing the Security Bundle, let’s continue to setting up Server Side Sessions using the Session Bundle.