Banking Web App Stories — Part 2
Continuing from part 1, part 2 aims to touch on creating a local database for user account data storage and validation during account creation and user login. We will be using SQLAlchemy from flask_sqlalchemy to create our SQLite database.
Database relationship
My initial idea was to have 3 tables: roles, users and transactions; The roles table will contain the id as primary key and the role name denoting the types of roles present for users in the database (e.g. Admin, moderator, users).
The users tables contains id as the primary key and also as the foreign key to owner in accounts_table. It also contains the user’s first and last name, email, password hash, role_id as the foreign key to the id in roles table and account balance.
The accounts table contains account number as the primary key and foreign key to either the sender or receiver in transactions table.
The transactions table contains id as the primary key, receiver, sender, amount involved and datetime of the transaction.
Notice that by having user’s account number in the receiver and sender field in transactions_table, transactions are restricted to happen only within the bank. I will assume that no interbank transactions are happening.
Suppose if I were to implement interbank transactions, I’d imagine transaction details from the other bank in the form of a JSON object to be parsed via an exposed API, and the receiver or sender details corresponding to the foreign bank account be kept as a standalone data in transactions_table or to have a separate database within the bank keeping tabs of accounts of the foreign bank, similar to the accounts_table.
app/__init__.py
Let’s initialize the db object from the SQLAlchemy class:
...
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
db = SQLAlchemy(app, db)models.py
The database instance we initialized from SQLAlchemy provides a base class for models and helper functions to define their struture. We will define the structure of our tables in models.py as Python classes with attributes that match the columns.
from app import db
from werkzeug.security import generate_password_hash, check_password_hash
class Role(db.Model):
"""Role SQlite ORM model
Columns:
- id (SQLite int): primary key
- name (SQLite str64): role name in system (e.g. Admin, Moderator, User)
"""
__tablename__ = "roles_table"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True)
users = db.relationship('User', backref='role') # Adds role attribute to User model
def __repr__(self):
return '<Role %r>' % self.name
class User(db.Model):
"""User SQlite ORM model
Columns:
- id (SQLite int): primary key
- first_name (SQLite str64): user first name
- last_name (SQLite str64): user last name
- email (SQLite str120): user email
- password_hash (SQLite str128): user hashed password
- role_id (SQLite int): user's role, mapped to Role table
"""
__tablename__ = "users_table"
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(64), index=True)
last_name = db.Column(db.String(64), index=True)
email = db.Column(db.String(120), index=True, unique=True)
password_hash = db.Column(db.String(128))
role_id = db.Column(db.Integer, db.ForeignKey('roles_table.id'))
accounts = db.relationship("Accounts", backref="account_owner")
def set_password(self, password: str) -> None:
"""Stores user's password as a hashed value
Reduces risk of compromising user information safety if we store password hash instead.
Uses Werkzeug's security moduyle hashing. Default hashing method 'pbkdf2:sha256', salt length = 8
Args:
password (str): user input in the password field
"""
self.password_hash = generate_password_hash(password)
def check_password(self, password: str) -> bool:
"""Checks if input password matches the one stored in database as a hashed value.
Args:
password (str): user input in the password field
Returns:
bool: True if the input password matches the one stored in database as a hashed value.
"""
return check_password_hash(self.password_hash, password)
def __repr__(self):
return '<User {} {}>'.format(self.first_name, self.last_name)
class TransactionType(db.Model):
"""Transaction Type SQlite ORM model
Columns:
- id (SQLite int): primary key
- name (SQLite str64): transaction type in system (e.g. Deposit, Withdrawal, Transfer, Other)
"""
__tablename__ = "transaction_type_table"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True)
txn = db.relationship('Transactions', backref='transaction_type') # Adds txn attribute to Transactions model
def __repr__(self):
return '<Transaction Types %r>' % self.name
class Transactions(db.Model):
"""Transactions SQlite ORM model
Columns:
- id (SQLite bigint): primary key
- receiver (SQLite bigint): account number of receiver
- sender (SQLite bigint): account number of sender
- amount (SQLite bigint): amount involved in the transaction
- date_time (SQLite DateTime): date time of the transaction
"""
__tablename__ = "transactions_table"
id = db.Column(db.Integer, primary_key=True)
receiver = db.Column(db.Integer, db.ForeignKey("accounts_table.account_num"), nullable=False)
sender = db.Column(db.Integer, db.ForeignKey("accounts_table.account_num"), nullable=False)
amount = db.Column(db.Integer)
date_time = db.Column(db.DateTime, index=True)
transaction_type_id = db.Column(db.Integer, db.ForeignKey('transaction_type_table.id'))
def __repr__(self):
return '< {} Txn {}: {} - {}, {}>'.format(self.date_time, self.id, self.sender, self.receiver, self.amount)
class Accounts(db.Model):
"""Bank account SQlite ORM model
Columns:
account_num (SQLite int): bank account number, primary key
owner (SQLite int): bank account owner, mapped to users_table id
balance (SQLite bigint): account balance
"""
__tablename__ = "accounts_table"
account_num = db.Column(db.Integer, primary_key=True, autoincrement=True)
owner = db.Column(db.Integer, db.ForeignKey('users_table.id'))
balance = db.Column(db.Float, default=0.00)
receiver_acc = db.relationship("Transactions", foreign_keys="Transactions.receiver", backref="receiver_account", lazy="dynamic")
sender_acc = db.relationship("Transactions", foreign_keys="Transactions.sender", backref="sender_account", lazy="dynamic")
def update_balance(self, amount):
self.balance += amount
def __repr__(self):
return '<{} account {}: {}, {}>'.format(self.owner, self.account_num, self.balance)I’ve included two methods in our User model: set_password and check_password. The former function generates the password hash given a password, which we will call the method after validating on submit during account registration. The function to generate hash comes from werkzeug.security package. The latter function is responsible for checking if the login password matches the password hash stored in the database during login.
Database migration repository
The only way to update changes to tables in Flask-SQLAlchemy is to destroy the old tables and create a new one, but this destroys all data previously stored. Using a database migration framework function similar to code source control — it keeps track of changes to the database schema, allowing incremental changes to be applied. We will be using the framework written by the developer of SQLAlchemy — Alembic through the Flask-Migrate wrapper.
Let’s initialize the migrate framework in app/__init__.py:
...
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
app = Flask(__name__)
db = SQLAlchemy(app, db)
migrate = Migrate(app, db)We can add support for database migrations with the init subcommand:
flask db initAfter making changes to the tables through model classes, we create a migration script using the flask db migrate command:
flask db migrate -m "summary of changes"After reviewing and accepting the changes, we apply it using the flask db upgrade command:
flask db upgradeWe’re almost ready to test user registration and login. In the next part, we will look at how to do just that. Stay tuned!
Link to part 3
