Blog Engine Example

A complete blog engine using sqbooster with posts, tags, and comments.

Full Source

  1"""
  2Blog Engine Example - sqbooster
  3
  4A complete blog engine with multiple related tables,
  5complex queries, pagination, and JSON metadata.
  6"""
  7
  8import sqbooster
  9from sqbooster.schema import Column
 10from sqbooster.types import Integer, Text, JSON
 11
 12
 13def create_schema(db):
 14    db.create_table("posts", [
 15        Column("id", Integer(), primary_key=True, autoincrement=True),
 16        Column("title", Text(), nullable=False),
 17        Column("slug", Text(), unique=True, nullable=False),
 18        Column("body", Text()),
 19        Column("author", Text(), nullable=False),
 20        Column("status", Text(), default="draft"),
 21        Column("tags", JSON(), default=[]),
 22        Column("views", Integer(), default=0),
 23    ])
 24
 25    db.create_table("comments", [
 26        Column("id", Integer(), primary_key=True, autoincrement=True),
 27        Column("post_id", Integer()),
 28        Column("author", Text(), nullable=False),
 29        Column("email", Text()),
 30        Column("body", Text(), nullable=False),
 31    ])
 32
 33
 34def seed_data(db):
 35    db.insert_many("posts", [
 36        {
 37            "title": "Getting Started with sqbooster",
 38            "slug": "getting-started-sqbooster",
 39            "body": "sqbooster makes database access easy...",
 40            "author": "Alice",
 41            "status": "published",
 42            "tags": ["python", "database", "tutorial"],
 43            "views": 150,
 44        },
 45        {
 46            "title": "Advanced Query Techniques",
 47            "slug": "advanced-query-techniques",
 48            "body": "Learn how to chain filters, sort, and paginate...",
 49            "author": "Bob",
 50            "status": "published",
 51            "tags": ["python", "advanced"],
 52            "views": 89,
 53        },
 54        {
 55            "title": "Draft: Future Features",
 56            "slug": "draft-future-features",
 57            "body": "Coming soon...",
 58            "author": "Alice",
 59            "status": "draft",
 60            "tags": ["roadmap"],
 61            "views": 0,
 62        },
 63    ])
 64
 65    db.insert_many("comments", [
 66        {"post_id": 1, "author": "Charlie", "email": "c@test.com",
 67         "body": "Great tutorial!"},
 68        {"post_id": 1, "author": "Diana", "email": "d@test.com",
 69         "body": "Very helpful, thanks!"},
 70        {"post_id": 2, "author": "Eve", "email": "e@test.com",
 71         "body": "The filter chaining is really clean."},
 72    ])
 73
 74
 75def main():
 76    db = sqbooster.sqlite()
 77
 78    print("=== Blog Engine Example ===\n")
 79
 80    create_schema(db)
 81    seed_data(db)
 82
 83    print("--- Published Posts ---")
 84    published = (db.query("posts")
 85        .filter(status="published")
 86        .order_by("-views")
 87        .all())
 88    for post in published:
 89        tags = ", ".join(post["tags"]) if post["tags"] else "none"
 90        print(f"  [{post['views']} views] {post['title']} ({tags})")
 91
 92    print("\n--- Search: 'query' in title ---")
 93    results = db.query("posts").filter(title__contains="query").all()
 94    for post in results:
 95        print(f"  {post['title']} by {post['author']}")
 96
 97    print("\n--- All Posts (page 1, limit 2) ---")
 98    page = db.query("posts").order_by("id").limit(2).all()
 99    for post in page:
100        print(f"  #{post['id']}: {post['title']}")
101
102    print(f"\nPublished: {db.query('posts').filter(status='published').count()}")
103    print(f"Drafts: {db.query('posts').filter(status='draft').count()}")
104
105    print("\n--- Comments for Post #1 ---")
106    comments = db.query("comments").filter(post_id=1).all()
107    for c in comments:
108        print(f"  {c['author']}: {c['body']}")
109
110    db.update("posts", {"views": 151}, slug="getting-started-sqbooster")
111    updated = db.query("posts").filter(slug="getting-started-sqbooster").one()
112    print(f"\nUpdated views: {updated['views']}")
113
114    deleted = db.delete("posts", status="draft")
115    print(f"\nDeleted {deleted} draft(s)")
116    print(f"Remaining posts: {db.query('posts').count()}")
117
118    post = db.query("posts").filter(slug="getting-started-sqbooster").one()
119    new_tags = post["tags"] + ["beginner"]
120    db.update("posts", {"tags": new_tags}, slug="getting-started-sqbooster")
121    post = db.query("posts").filter(slug="getting-started-sqbooster").one()
122    print(f"\nUpdated tags: {post['tags']}")
123
124    db.close()
125    print("\nDone!")
126
127
128if __name__ == "__main__":
129    main()

What It Demonstrates

  • Multiple related tables with foreign-key-like references

  • Complex filtering across columns

  • Pagination for listing posts

  • Updating and deleting records

  • JSON columns for flexible metadata

Running the Example

python examples/blog.py