Reddit Style Comments

Building Threaded Comments with SQLite and Flask: A Recursive Approach

Threaded (or nested) comment systems, like those used on Reddit or Hacker News, allow users to reply to replies, creating a tree of conversation. In this post, we’ll explore how to build a fully functional, recursive comment system using:

SQLite as the database (lightweight, no extra setup)
Flask as the backend web framework
Jinja templates for rendering the thread structure recursively

This setup powers the comments across [YottaRock.ca] and supports nested replies of any depth.

Database Structure

We use a single comments table. Each comment knows: - Which post it belongs to - Which user wrote it - Which comment (if any) it's replying to - Its depth in the tree

CREATE TABLE comments (
    id INTEGER PRIMARY KEY,
    post_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    comment TEXT NOT NULL,
    parent_id INTEGER,
    depth INTEGER DEFAULT 0,
    date_created TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(parent_id) REFERENCES comments(id)
);

This structure lets each comment link to a parent, forming a tree.

Recursive Logic (Python)

To render this tree, we recursively fetch child comments using a simple depth-first traversal. Here’s the core get_comments() function in Flask:

def get_comments(post_id, parent_id=None):
    db = get_db()

    if parent_id is None:
        parent_condition = 'c.parent_id IS NULL'
    else:
        parent_condition = 'c.parent_id = ?'

    query = f'''
        SELECT c.id, c.comment, c.date_created, c.user_id, u.username, c.parent_id, c.depth
        FROM comments c
        JOIN users u ON c.user_id = u.id
        WHERE c.post_id = ? AND {parent_condition}
        ORDER BY c.date_created ASC
    '''

    args = (post_id,) if parent_id is None else (post_id, parent_id)
    comments = db.execute(query, args).fetchall()

    comment_list = []
    for comment in comments:
        comment_dict = dict(comment)
        comment_dict['children'] = get_comments(post_id, comment['id'])
        comment_list.append(comment_dict)

    return comment_list

Rendering in Jinja (Recursively)

Using Jinja’s {% include %} inside the comment template, we render the tree with indentation based on depth:

<!-- comment.html -->
<div class="comment" style="margin-left: {{ 20 * comment['depth'] }}px;">
  <p><strong>{{ comment['username'] }}</strong> said:</p>
  <p>{{ comment['comment'] }}</p>

  {% if g.user %}
  <form method="POST" action="{{ url_for('blog.view', id=post['id']) }}">
    <input type="hidden" name="parent_id" value="{{ comment['id'] }}">
    <textarea name="comment" required placeholder="Reply..."></textarea>
    <button type="submit">Reply</button>
  </form>
  {% endif %}

  {% for child_comment in comment['children'] %}
    {% include 'comment.html' with context %}
  {% endfor %}
</div>

Each comment includes its children recursively — no front-end JS required!

Submitting a New Comment

When a user submits a comment, we: - Check if it's a reply (parent_id) - Lookup the parent's depth - Save the new comment with depth + 1

if request.method == 'POST':
    comment_text = request.form['comment']
    parent_id = request.form.get('parent_id', None)
    depth = 0

    if parent_id:
        parent = db.execute('SELECT depth FROM comments WHERE id = ?', (parent_id,)).fetchone()
        if parent:
            depth = parent['depth'] + 1

    db.execute(
        'INSERT INTO comments (post_id, user_id, comment, parent_id, depth) VALUES (?, ?, ?, ?, ?)',
        (id, g.user['id'], comment_text, parent_id, depth)
    )
    db.commit()

Features This Supports

Infinite nesting (well, limited by screen space)
Replies to replies
Simple layout control via CSS
Efficient recursive loading

YottaRock Industries