Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 37 ยท Projects and Practice

Project: A Blog Backend with SQLAlchemy

Build the data layer of a blog: users, posts, comments and tags with relationships, a service layer, tag queries, pagination, counts and tests. Fully runnable.

Time to combine everything from this slice into one program. You will build the data layer of a blog: users who write posts, comments on posts, tags shared between posts, and a small set of service functions (publish, comment, search by tag, list a page of posts). There is no web framework here on purpose; these functions could sit behind Flask, FastAPI or a command-line tool without changes.

Ideas you will reuse: one-to-many and many-to-many relationships, selectinload, cascades, a request-scoped session, aggregates, keyset pagination and a few assertions that act as tests.

The design

erDiagram
    USER ||--o{ POST : writes
    USER ||--o{ COMMENT : writes
    POST ||--o{ COMMENT : has
    POST }o--o{ TAG : tagged
    USER { int id PK
           string name
           string email }
    POST { int id PK
           string title
           string body
           bool published
           int author_id FK }
    COMMENT { int id PK
              string body
              int post_id FK
              int author_id FK }
    TAG { int id PK
          string name }

Posts belong to an author, comments belong to a post and an author, and posts and tags form a many-to-many pair.

Decisions worth stating:

  • Comments are owned by a post, so they use cascade="all, delete-orphan". Deleting a post removes its comments.
  • A post's tags go through a plain secondary table because the link has no data.
  • Tag names are unique; the get_or_create_tag helper keeps us from inserting duplicates.
  • Publishing sets published=True and a published_at timestamp, in one place.

Step 1: models

Python Models and session helper

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Step 2: service functions

Each function takes the session as an argument. That keeps them testable and lets the caller decide where the transaction starts and ends.

Python
from sqlalchemy import func, select
from sqlalchemy.orm import selectinload

def get_or_create_tag(session, name):
    name = name.strip().lower()
    tag = session.scalar(select(Tag).where(Tag.name == name))
    return tag or Tag(name=name)

def register_user(session, name, email):
    user = User(name=name, email=email.lower())
    session.add(user)
    session.flush()                      # assigns user.id, raises on duplicate email
    return user

def write_post(session, author, title, body, tag_names=()):
    tags = [get_or_create_tag(session, n) for n in tag_names]
    post = Post(title=title, body=body, author=author, tags=tags)
    session.add(post)
    return post

def publish(session, post):
    if post.published:
        raise ValueError("already published")
    post.published = True
    post.published_at = datetime.now()

def add_comment(session, post, author, body):
    if not post.published:
        raise ValueError("cannot comment on a draft")
    post.comments.append(Comment(body=body, author=author))

def feed(session, after_id=0, limit=3, tag=None):
    stmt = (select(Post).where(Post.published, Post.id > after_id)
            .options(selectinload(Post.author), selectinload(Post.tags))
            .order_by(Post.id).limit(limit))
    if tag:
        stmt = stmt.where(Post.tags.any(Tag.name == tag))
    return list(session.scalars(stmt))

def comment_counts(session):
    return session.execute(
        select(Post.title, func.count(Comment.id))
        .outerjoin(Post.comments).group_by(Post.id).order_by(Post.id)).all()

def top_authors(session):
    return session.execute(
        select(User.name, func.count(Post.id).label("posts"))
        .join(User.posts).where(Post.published).group_by(User.id)
        .order_by(func.count(Post.id).desc(), User.name)).all()

Two small details to notice. get_or_create_tag reuses an existing Tag row when there is one, which is what prevents the unique constraint from firing. And feed() uses keyset pagination: you pass the last id you have seen instead of an offset.

Step 3: run the whole story

Python The blog in action: users, posts, tags, comments, feed

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the output against the story: the draft never reaches the feed, the second page continues where the first stopped, the duplicate email is rejected by the unique constraint, and deleting post 1 removes its two comments through the cascade but leaves the tags alone.

Step 4: where to take it next

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why do the service functions receive the session as a parameter?

    Passing the session in keeps transaction boundaries outside the business functions.
  2. 2. What does feed(after_id=...) implement?

    The caller passes the last id they saw, and the query seeks straight past it.
  3. 3. Why does get_or_create_tag look the tag up before creating one?

    Creating a second tag with the same name would raise an IntegrityError.
  4. 4. What happens to a post's tags when the post is deleted?

    The ORM deletes the association rows for a secondary relationship; tags are independent objects.

Summary

  • A blog data layer uses one-to-many (author-posts, post-comments) and many-to-many (post-tags) relationships.
  • Owned children get delete-orphan cascades; shared things like tags do not.
  • Service functions take the session, and a session_scope() helper commits or rolls back.
  • selectinload keeps the feed query count constant; keyset pagination keeps deep pages fast.
  • Unique constraints are the final guard against duplicates; handle IntegrityError at the edge.

Next: Project: Library Loans with Business Rules