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.

Search this space