Application Architecture – Database Layer and Models

The database layer leverages Flask-SQLAlchemy to map Python classes to database tables. It centralizes connection setup, model definitions, and persistence helpers. All models import a shared db instance, ensuring consistency and simplified session management.

Database Initialization

This section shows how the db instance is defined and integrated into the Flask app.

db.py

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
  • Purpose: Defines a single SQLAlchemy instance.
  • Usage: Imported by all model modules to share the same database session.

Integrating db in app.py

if __name__ == '__main__':
    from db import db
    db.init_app(app)
    app.run(port=5000, debug=True)
  • db.init_app(app): Binds the Flask application to the db instance.
  • @app.before_first_request: Calls db.create_all() to create tables automatically.

Models Overview

All models inherit from db.Model. Each class maps to a table via __tablename__, declares columns, and provides helper methods for JSON serialization, lookups, and persistence.

BookModel 📚

Represents the books table and encapsulates book-specific data and operations.

Column Type Description
id Integer, primary key Unique identifier
title String(80) Book title
author String(80) Book author
isbn String(40) International Standard Book Number
release_date String(10) Publication date in YYYY-MM-DD
price Float(precision=2) Price with two-decimal precision
store_id Integer, foreign key References stores.id 
class BookModel(db.Model):
    __tablename__ = 'books'

    id           = db.Column(db.Integer, primary_key=True)
    title        = db.Column(db.String(80))
    author       = db.Column(db.String(80))
    isbn         = db.Column(db.String(40))
    release_date = db.Column(db.String(10))
    price        = db.Column(db.Float(precision=2))
    store_id     = db.Column(db.Integer, db.ForeignKey('stores.id'))
    store        = db.relationship('StoreModel')

    def __init__(self, title, price, store_id, author, isbn, release_date):
        self.title        = title
        self.price        = price
        self.store_id     = store_id
        self.author       = author
        self.isbn         = isbn
        self.release_date = release_date

    def json(self):
        return {
            'title': self.title,
            'price': self.price,
            'author': self.author,
            'isbn': self.isbn,
            'release_date': self.release_date
        }

    @classmethod
    def find_by_title(cls, title):
        return cls.query.filter_by(title=title).first()

    def save_to_db(self):
        db.session.add(self)
        db.session.commit()

    def delete_from_db(self):
        db.session.delete(self)
        db.session.commit()
  • Relationship: Each book links to one store via store_id.
  • Serialization: json() returns API-ready dict.
  • Lookup: find_by_title() for quick searches.
  • Persistence: save_to_db() and delete_from_db() manage db.session.

StoreModel 🏪

Encapsulates the stores table and manages one-to-many relations with books.

Column Type Description
id Integer, primary key Unique store identifier
name String(80) Store name 
class StoreModel(db.Model):
    __tablename__ = 'stores'

    id    = db.Column(db.Integer, primary_key=True)
    name  = db.Column(db.String(80))
    books = db.relationship('BookModel', lazy='dynamic')

    def __init__(self, name):
        self.name = name

    def json(self):
        return {
            'name':  self.name,
            'books': [book.json() for book in self.books.all()]
        }

    @classmethod
    def find_by_name(cls, name):
        return cls.query.filter_by(name=name).first()

    def save_to_db(self):
        db.session.add(self)
        db.session.commit()

    def delete_from_db(self):
        db.session.delete(self)
        db.session.commit()
  • Dynamic Relationship: books loads associated books on demand.
  • Nested Serialization: json() includes all related books.
  • Lookup & Persistence: Similar helpers as BookModel.

UserModel 👤

Manages the users table for authentication and registration.

Column Type Description
id Integer, primary key Unique user identifier
username String(80) User’s login name
password String(80) Hashed password (plain text here) 
class UserModel(db.Model):
    __tablename__ = 'users'

    id       = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80))
    password = db.Column(db.String(80))

    def __init__(self, username, password):
        self.username = username
        self.password = password

    def save_to_db(self):
        db.session.add(self)
        db.session.commit()

    @classmethod
    def find_by_username(cls, username):
        return cls.query.filter_by(username=username).first()

    @classmethod
    def find_by_id(cls, _id):
        return cls.query.filter_by(id=_id).first()
  • Authentication Hooks: Used by authenticate and identity in security.py.
  • Persistence: save_to_db() for account creation.

Entity Relationship Diagram

Below is a simplified ER diagram illustrating the one-to-many relation between stores and books.

erDiagram
    STORES {
        Integer id PK
        String name
    }
    BOOKS {
        Integer id PK
        String title
        String author
        String isbn
        String release_date
        Float price
        Integer store_id FK
    }
    STORES ||--o{ BOOKS : contains
  • Stores can contain multiple Books.
  • Books belong to one Store via store_id.

Persistence & Query Patterns

  • Session Management: All models call db.session.commit() to persist changes.
  • Error Handling: Resources wrap save_to_db() in try/except to catch DB errors.
  • Class Methods: Provide clean, reusable lookup utilities (find_by_*).
  • JSON Serialization: Models expose a json() method for API responses without extra mapping.

📑 Key Takeaway
Models centralize data structure, relationships, and CRUD helpers. This design promotes clear separation between business logic and data access layers.

Updated