Building a forum backend in Go with SQLite is a great way to keep the architecture simple without sacrificing clarity. The main challenge is designing a schema that stays normalized, mapping it cleanly into Go structs, and configuring database access so concurrent posts do not trigger lock errors. A good design also makes tagging easy to query and maintain, instead of turning tags into an awkward text blob.
Forum data model
A forum usually revolves around a few core entities: users, categories, posts, comments, likes, and tags. Each entity should have its own table, while relationships should be represented with foreign keys or junction tables. This keeps the data consistent, avoids duplication, and makes querying easier over time.
A practical structure is:
users for account identity.
categories for grouping posts into topics.
posts for the main discussion content.
comments for replies and threaded conversations.
likes for user reactions.
tags for flexible labels.
post_tags for the many-to-many relationship between posts and tags.
This structure reflects standard relational design principles and keeps the schema flexible as the forum grows.
SQLite schema design
SQLite works well for this kind of application because it is lightweight, embedded, and easy to ship. The key is to use the database the way it wants to be used: normalized tables, foreign keys, and carefully chosen indexes. For a forum, the schema should preserve referential integrity so that deleting a post can cascade to its comments and tag links if you want that behavior.
A solid starting point looks like this:
sql
PRAGMA foreign_keys = ON;
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
slug TEXT NOT NULL UNIQUE
);
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
title TEXT NOT NULL,
body TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);
CREATE TABLE comments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
parent_comment_id INTEGER,
body TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (parent_comment_id) REFERENCES comments(id) ON DELETE CASCADE
);
CREATE TABLE likes (
user_id INTEGER NOT NULL,
post_id INTEGER NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
PRIMARY KEY (user_id, post_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);
CREATE TABLE tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
slug TEXT NOT NULL UNIQUE
);
CREATE TABLE post_tags (
post_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
PRIMARY KEY (post_id, tag_id),
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
The likes and post_tags tables both use composite primary keys because each pair should only appear once. That avoids duplicate likes and duplicate tag links while keeping the schema compact. This is a classic use case for a junction table in relational databases.
Indexes and integrity
Foreign keys alone are not enough for performance. You also want indexes on the columns used most often in joins and filters, especially posts.user_id, posts.category_id, comments.post_id, and post_tags.tag_id. Without those indexes, even a modest forum can become slow when listing posts by category or loading tags for a page.
A few useful indexes are:
sql
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_category_id ON posts(category_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);
CREATE INDEX idx_post_tags_tag_id ON post_tags(tag_id);
If you plan to search by tag name often, the unique index created by UNIQUE(name) on tags already helps. Keeping PRAGMA foreign_keys = ON enabled is also important because SQLite does not enforce foreign keys unless explicitly told to do so. That makes the difference between a resilient schema and one that quietly accumulates broken references.
Mapping to Go structs
Once the schema is settled, the next step is matching it with Go types. The cleanest approach is to keep one struct per table and then add optional nested fields for joined data. You generally want to keep raw foreign key IDs in the struct so that inserts and updates stay simple.
A straightforward model might look like this:
go
type User struct {
ID int64
Username string
Email string
CreatedAt time.Time
}
type Category struct {
ID int64
Name string
Slug string
}
type Tag struct {
ID int64
Name string
Slug string
}
type Post struct {
ID int64
UserID int64
CategoryID int64
Title string
Body string
CreatedAt time.Time
UpdatedAt *time.Time
Author *User
Category *Category
Tags []Tag
}
type Comment struct {
ID int64
PostID int64
UserID int64
ParentCommentID *int64
Body string
CreatedAt time.Time
Author *User
Replies []Comment
}
This pattern gives you the best of both worlds. The base struct mirrors the table and is easy to persist, while nested fields are filled when you load joined data for views or API responses. It also avoids overcomplicating the database layer with an ORM-style object graph that does not match the actual schema.
Loading data cleanly
For most forum pages, you will query data in layers. For example, when loading a post page, you may fetch the post row first, then load comments, tags, author details, and category details. This approach keeps the SQL readable and gives you control over performance.
For tags, the usual query looks like this:
sql
SELECT t.id, t.name, t.slug
FROM tags t
JOIN post_tags pt ON pt.tag_id = t.id
WHERE pt.post_id = ?;
For comments, you may want one query for the top-level comments and another for replies, or you may load all comments for a post and build the tree in Go. The latter is often easier if you want nested threads. A forum backend usually benefits from explicit SQL and explicit mapping rather than trying to hide all of the relationships behind magic.
Concurrency in Go
SQLite can handle many readers, but it allows only one writer at a time. That is why forum applications can hit database is locked errors when several users post simultaneously. The solution is not to fight SQLite with lots of connections; it is to configure the connection pool in a way that fits SQLite’s locking model.
Go’s sql.DB is designed as a concurrency-safe handle with its own internal connection pool. The handle itself is safe to share among goroutines, and the package creates and reuses connections as needed. For SQLite, however, you usually want to keep the number of open connections low, often to one, because multiple concurrent writers create contention rather than throughput.
A common configuration looks like this:
go
db, err := sql.Open("sqlite3", "file:forum.db?_busy_timeout=5000&_foreign_keys=on")
if err != nil {
return err
}
db.SetMaxOpenConns(1)
db.SetMaxIdleConns(1)
db.SetConnMaxLifetime(0)
db.SetConnMaxIdleTime(0)
That setup reduces write contention and makes the application behave more predictably under load. WAL mode is also important because it lets readers continue while a write transaction is active. Together, WAL and a sensible busy timeout make SQLite much more usable for concurrent forum traffic.
You should also keep write transactions short. Do not hold a transaction open while doing extra computation, network calls, or template rendering. Start the transaction, write the rows, commit, and release the lock as quickly as possible.
Preventing lock errors
Lock errors usually happen when the application performs overlapping writes without enough timeout or too many concurrent connections. In SQLite, a writer temporarily needs exclusive access, so a burst of simultaneous post submissions can trigger failures if the database is not configured correctly. The practical fix is a combination of WAL, busy timeout, short transactions, and a low connection count.
A good operational pattern is:
Enable WAL mode at startup.
Enable foreign keys on every connection.
Set a busy timeout.
Keep writes small and fast.
Use a single shared sql.DB.
Avoid unnecessary parallel writes.
This is especially relevant in a forum where users may create posts, comments, or likes at the same moment. SQLite can still work well here if the app treats writes carefully rather than assuming the database behaves like a high-concurrency server engine.
Tagging with many-to-many links
Tags are the best example of a many-to-many relationship in a forum. A post can have multiple tags, and the same tag can belong to many posts. In relational design, this should not be stored as an array column or a comma-separated string. The proper solution is a junction table, which keeps each association as its own row.
In Go, the user may submit tags as a slice of strings such as:
go
tags := []string{"go", "sqlite", "backend"}
That slice is useful at the application boundary, but the database should store normalized records. The usual process is:
Trim and lowercase each tag.
Ignore empty values.
Insert the tag into tags if it does not already exist.
Insert the post-tag pair into post_tags.
Use INSERT OR IGNORE or a unique constraint to avoid duplicates.
A simple implementation might look like this:
go
func SavePostTags(tx *sql.Tx, postID int64, tags []string) error {
for _, raw := range tags {
name := strings.ToLower(strings.TrimSpace(raw))
if name == "" {
continue
}
var tagID int64
err := tx.QueryRow(`SELECT id FROM tags WHERE name = ?`, name).Scan(&tagID)
if err == sql.ErrNoRows {
res, err := tx.Exec(`INSERT INTO tags(name, slug) VALUES(?, ?)`, name, name)
if err != nil {
return err
}
tagID, err = res.LastInsertId()
if err != nil {
return err
}
} else if err != nil {
return err
}
_, err = tx.Exec(`INSERT OR IGNORE INTO post_tags(post_id, tag_id) VALUES(?, ?)`, postID, tagID)
if err != nil {
return err
}
}
return nil
}
That method is simple, readable, and close to the database model. It also makes tag queries efficient because the join table is indexed and normalized.
Querying tags back into Go
When loading a post, you may want the tags as a Go slice again. This is where array manipulation happens on the application side, not inside SQLite. Query the joined rows, scan each row into a Tag, and append it to post.Tags.
That keeps the storage layer relational and the application layer ergonomic. It also makes it easy to render tags in templates or return them in JSON responses. In practice, this separation is healthier than trying to store “arrays” in the database and unpack them later.
Putting it together
A good forum backend in Go and SQLite is mostly about respecting the strengths of each tool. SQLite gives you a compact relational store that works well for small and medium workloads, while Go gives you explicit control over struct mapping, transactions, and concurrency. If the schema is normalized and the connection pool is configured carefully, the system stays simple without becoming fragile.
The final design should aim for these outcomes: clear tables, explicit relationships, safe concurrent access, and a tagging system that scales gracefully. If you follow that structure, you get a forum backend that is easy to maintain, easy to query, and realistic for production use within SQLite’s limits.
Top comments (0)