by: PooyaPosted on:

Flask App Postgres Database Initialization: Step-by-Step Guide with Models

Most applications we develop need Relational Databaes(s) to store different types of application data such as user data and application states. No matter which framework or programming language we use, the general steps for initiating a relational database are pretty much the same:

  • Create a database
  • Design the database schema and relations
  • Use the framework data abstraction layer to implement the database model and needed queries
  • Run database Migration to apply the designed schema to the target database.

In the last episode, I wrote about how to initiate a Flask app. Here I will show how to create a database schema in Flask.

Note: I suggest looking at the last episode since we are using the same source code.

Let’s start!

Create a Database and config the connection

In this setup, we use Postgres as our database engine. I do not go into the Postgres installation, but if you want to install it, you can look at it here. (Debian)

After the installation, first, we need to create a database user. Log in to Postgres and run this:

:$ sudo -u postgres psql 
> CREATE USER cool_user WITH PASSWORD '1234';

The database user is cool_user and the password is 1234.

Then we need to create the database. Inside the Postgres shell, run:

> CREATE DATABASE cool_db;

The last step is to grant the needed permission to the user cool_user for the database cool_db:

> GRANT ALL PRIVILEGES ON DATABASE cool_db TO cool_user;

Note: This grants all the permissions. In a real setup, just give the needed permissions for security reasons.

After setting up the database, we need to config our Flask app.

In the config.py script, we have to introduce two new variables:

  • SQLALCHEMY_DATABASE_URI: This is the database connection setting. The format is: postgresql://DATABASE_USER:PASSWORD@DATABASE_HOST_NAME:DATABASE_PORT/DATABASE_NAME
  • SQLALCHEMY_TRACK_MODIFICATIONS: SQLAlchemy tracks modifications to database objects and emits signals to notify the application of changes. Enabling this may have performance overhead for large applications. Choose carefully.

This is the config.py

class CoolConfig(object):
    SQLALCHEMY_DATABASE_URI = "postgresql://cool_user:1234@localhost:5432/cool_db"
    SQLALCHEMY_TRACK_MODIFICATIONS = False

Note: Our database is running on localhost. 5432 is the Postgres desfault port.

Before we implement our database schema, we need to import the database object into our app to use it. To do this, create a new script name databse.py in your app directly and create the object:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

This way we make the database object accessible globally in our app.

Implement the Schema: Flask Model

Before we implement our schema, we need to model it in our mind. In this example, we want to model the cars that are purchased by users:

  • We need two tables: users and cars
  • users table columns are: id (primary key), username, created_at (user registration date), role (user roles in our system that are student, teacher, and employee)
  • cars table columns are: id (primary key), model (car name), and owner_id (who bought it, a user id)
  • Each user can buy one-to-many cars

With this knowledge, we create our Flask app models. Models are:

  • Python Classes
  • Data abstraction layer
  • Save us from writing complex SQL queries
  • We use them to define database schema
  • We also use them for all the functions that interact with the data layer.

In the app directory, create a new directory name models. We put our models here.

Then inside this directory create a script name user.py. Put this code in it:

from best_app.database import db

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(), unique=True, nullable=False)    
    created_at = db.Column(db.Date, nullable=False)
    role = db.Column(db.String(), default="employee")    

    __table_args__ = (
        db.CheckConstraint(role.in_(['student', 'teacher', 'employee']), name='role_types'),      
    )


    def __init__(self, username, created_at, role):
        self.username = username        
        self.created_at = created_at        
        self.role = role
    
    def register_user_if_not_exist(self):        
        db_user = User.query.filter(User.username == self.username).all()
        if not db_user:
            db.session.add(self)
            db.session.commit()
        
        return True
    
    def get_by_username(username):        
        db_user = User.query.filter(User.username == username).first()
        return db_user

    def __repr__(self):
        return f"<User {self.username}>"
  • This is our model class for the users’ table
  • Pay attention that we first imported the db object that we created before in the database.py
  • First, we define our table name using __tablename__
  • Then, we introduce our table columns which are class properties
  • We also create special constraints for the column role using __table_args__. (The constrain’s name is arbitrary)
  • After that is the class constructor
  • Then, we implement two functions for user registration and selecting a user based on the given username. Notice that you do not need any SQL query.
  • In the end, is the model string representation if we want to print a user object for instance.

Now we implement the cars table model. In the same models directory, create a new script name car.py and put this in that:

from best_app.database import db
from best_app.models.user import User
from sqlalchemy import ForeignKeyConstraint

class Car(db.Model):
    __tablename__ = 'cars'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)     
    model = db.Column(db.String(), nullable=False)
    owner_id = db.Column(db.Integer, nullable=False)    
    
    __table_args__ = (        
        ForeignKeyConstraint([owner_id], [User.id], ondelete='NO ACTION'),        
    )


    def __init__(self, model, owner_id):
        self.model = model
        self.owner_id = owner_id      
    
    def to_dict(self):
        return {
            'model': self.model,
            'owner': self.owner_id            
        }
    
    def buy_car(self):
        record = Car.query.filter(Car.id == self.id).first()
        if not record:
            db.session.add(self)
            db.session.commit()
        
        return True

    def get_user_cars(user_id):
        records = Car.query.filter(Car.owner_id == user_id).all()
        return [record.to_dict() for record in records] 

    def __repr__(self):
        return f"<Car {self.model}>"

The section is almost exactly like the UserModel. Except:

  • We have a new constraint here: ForeignKey. This is the user id that indicated who bought this car. The ondelete policy tells the database what should happen when a user is deleted. Here we tell it to do nothing. But we can also change to other policies such as cascade that also deletes all the cars related to the user that got deleted. Read more
  • We also implement a to_dict function. This function transforms a SQLAlchemy object into a dictionary. Why? because SQLAlchemy objects are not JSON serializable. Therefore, we run into problems if we want to return a list of cars to the application client in JSON format. Besides, we can control what to expose from this table.
  • After this, we implement two functions to buy a car and return the list of all purchased cars by a user.

Now, our models are ready. However, we still have no tables in our Postgres database. We need some final steps.

Database Initiation and Migration

The last step is to use our models and initiate the database tables. To do this we use the Flask Migration library. We also need the Postgres Database Adopter for Python. Activate the Flask app Python virtual environment and run:

(.venv) > pip install Flask-Migrate
(.venv) > pip install psycopg2

After this, we need to do three important things:

  • initiate the db object for our app
  • Register our models (User and Car)
  • Load the migration object for Flask

We do all these steps inside our application factory:

from flask import Flask
from best_app.modules import hello, goodbye
from best_app.config import CoolConfig
from flask_migrate import Migrate
from best_app.database import db


def create_app():    
    app = Flask(__name__)        
    app.config.from_mapping(
        SECRET_KEY = "My_Secret_Key"
    )     
    
    app.config.from_object(CoolConfig)    
    
    # Database related part
    db.init_app(app)
    from best_app.models.user import User
    from best_app.models.car import Car
    migrate = Migrate(app, db)

    app.register_blueprint(hello.blueprint)
    app.register_blueprint(goodbye.blueprint)

    return app

The last step is to create our tables. Run this in the command line (venv enabled)

(.venv) > export FLASK_APP=best_app
(.venv) > flask db init
(.venv) > flask db migrate
(.venv) > flask db upgrade

Notes:

  • You need to run db init only the first time initiating your database.
  • the migrate command creates a migration script (SQL version of your model) inside your application.
  • The upgrade command is the one that actually runs the migrations on your database.
  • Every time you change your schema, you need to run the migrate and upgrade commands.

And that’s it! Our database is ready to use!

You can find the source code here: https://github.com/Pooya-Oladazimi/flask-cool-app

Hope it will be useful for you!

The End.

fb-share-icon
Tweet