Models & Relationships

The previous lesson gave Bookshelf a single, standalone Book model. A real reading tracker is really four connected ideas — a User owns Shelves, each holding several Books, each of which can carry several Tags. This lesson defines all four models together and wires up the relationships that connect them, which is where SQLAlchemy's ORM genuinely starts to shine.

All Four Bookshelf Models

Here is the complete set, with every field and relationship in place. We'll unpack each relationship line by line right after:

# models.py
from extensions import db

book_tags = db.Table(
    'book_tags',
    db.Column('book_id', db.Integer, db.ForeignKey('book.id'), primary_key=True),
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True)
)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password_hash = db.Column(db.String(255), nullable=False)

    shelves = db.relationship('Shelf', backref='owner', lazy=True)


class Shelf(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.String(300))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    books = db.relationship('Book', backref='shelf', lazy=True,
                             cascade='all, delete-orphan')


class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    author = db.Column(db.String(120), nullable=False)
    pages = db.Column(db.Integer)
    status = db.Column(db.String(20), default='want_to_read')
    rating = db.Column(db.Integer)
    shelf_id = db.Column(db.Integer, db.ForeignKey('shelf.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    tags = db.relationship('Tag', secondary=book_tags, backref='books', lazy=True)


class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)

One-to-Many with ForeignKey: Shelf and Book

A Shelf can hold many Book rows, but each Book sits on exactly one Shelf — a classic one-to-many relationship. The SQL-level link lives entirely on the "many" side, as a foreign key column on Book:

shelf_id = db.Column(db.Integer, db.ForeignKey('shelf.id'), nullable=False)

db.ForeignKey('shelf.id') tells the database that every value in Book's shelf_id column must match an existing id value in the shelf table — the same constraint a hand-written SQL schema would enforce, now declared as part of the model itself.

db.relationship() and backref

A foreign key column alone only gets you a raw ID — useful for the database, clumsy in Python. db.relationship() is the layer on top that gives you a convenient, object-level way to navigate the connection without writing a single join yourself:

books = db.relationship('Book', backref='shelf', lazy=True,
                         cascade='all, delete-orphan')

Declared on Shelf, this single line sets up navigation in both directions at once. books = db.relationship('Book', ...) is the forward direction — given a Shelf instance, shelf.books returns the list of every Book whose shelf_id points at it. The backref='shelf' argument is what adds the reverse direction automatically: it attaches a .shelf attribute directly onto the Book class, so some_book.shelf gives you back the owning Shelf instance — without ever having to declare a second, separate relationship on Book to make that work.

Many-to-Many with an Association Table: Book and Tag

Tags are different from shelves: one Book can carry several Tags ("sci-fi", "favorites"), and one Tag is shared across many Books. Neither side can hold a single foreign key column, because neither side has just one of the other — this needs a many-to-many relationship, which SQL implements with a separate table in between:

book_tags = db.Table(
    'book_tags',
    db.Column('book_id', db.Integer, db.ForeignKey('book.id'), primary_key=True),
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True)
)

# on Book:
tags = db.relationship('Tag', secondary=book_tags, backref='books', lazy=True)

book_tags is db.Table() rather than a db.Model class — it's a plain association table, existing purely as an SQL-level implementation detail with two foreign key columns and no model of its own, since it represents a pairing rather than a standalone entity. The secondary=book_tags argument on Book.tags is how you tell SQLAlchemy "this relationship isn't a direct foreign key — route it through this table instead." Just like before, backref='books' gives you the reverse direction for free: some_tag.books returns every Book carrying that tag.

The cascade Option

Look back at the Shelf.books relationship: cascade='all, delete-orphan' tells SQLAlchemy what should happen to a Shelf's books if that shelf itself is deleted. With this setting, deleting a Shelf automatically deletes every Book that belonged to it — which makes sense here, because a book with no shelf isn't a state Bookshelf's design allows (shelf_id is nullable=False), so an orphaned book is meaningless and should go too.

That is very much not always the right default. The Book.tags relationship deliberately has no cascade delete: removing a Book should not delete the Tag rows it was using, since "sci-fi" almost certainly still labels other books on other shelves. Whether cascading makes sense comes down to one question: does the "child" record mean anything without its "parent"? A Book without a Shelf — no. A Tag without one particular Book — obviously yes.

Creating and Navigating the Full Graph

With all four models in place, here's a full worked example: creating a user, a shelf, a few books, and some tags, then walking the relationships in both directions. We flush after adding the user so it gets a real id to stamp onto each book's user_id column:

user = User(username='sonu', email='sonu@example.com', password_hash='...')
db.session.add(user)
db.session.flush()  # assigns user.id without ending the transaction

shelf = Shelf(name='Sci-Fi Favorites', description='My favorite science fiction', owner=user)

sci_fi = Tag(name='sci-fi')
favorites = Tag(name='favorites')

book1 = Book(title='Dune', author='Frank Herbert', pages=412,
             status='finished', rating=5, shelf=shelf, user_id=user.id)
book1.tags.append(sci_fi)
book1.tags.append(favorites)

book2 = Book(title='Foundation', author='Isaac Asimov', pages=255,
             status='reading', shelf=shelf, user_id=user.id)
book2.tags.append(sci_fi)

db.session.add_all([shelf, book1, book2])
db.session.commit()

# Forward: from the shelf, to its books
print(shelf.books)          # [<Book Dune>, <Book Foundation>]

# Reverse: from a book, back to its shelf (via backref)
print(book1.shelf.name)    # 'Sci-Fi Favorites'

# Many-to-many, forward: from a book, to its tags
print(book1.tags)          # [<Tag sci-fi>, <Tag favorites>]

# Many-to-many, reverse: from a tag, to every book carrying it (via backref)
print(sci_fi.books)        # [<Book Dune>, <Book Foundation>]

# One-to-many, reverse: from the user, to every shelf they own (via backref)
print(user.shelves)        # [<Shelf Sci-Fi Favorites>]

Notice that nothing above wrote a single line of SQL, and nothing manually managed the book_tags association rows either — appending to book1.tags and committing is enough for SQLAlchemy to insert the correct rows into book_tags behind the scenes.