In this task, you will extend the Posts API to manage relational data by adding support for Comments. Comments are associated with Posts, creating a one-to-many relationship between the two entities. You will update the database schema, seed the database with sample data, implement CRUD operations for Comments, and handle relational data operations.
In a relational database, entities are often related to each other through relationships. In this task, we will model a one-to-many relationship between Posts and Comments. Each Post can have multiple Comments associated with it, but each Comment belongs to only one Post.
There are three main types of relationships in relational databases:
In our case, the relationship between Posts and Comments is a one-to-many relationship. An example of a one-to-one relationship could be a User and their Profile, where each User has one Profile. A many-to-many relationship could be between Posts and Tags, where each Post can have multiple Tags, and each Tag can be associated with multiple Posts.
When modeling relational data, it’s essential to consider the cardinality of the relationship (one-to-one, one-to-many, many-to-many) and the referential integrity constraints (e.g., foreign keys) to maintain data consistency.
To model the one-to-many relationship between Posts and Comments, we need to update the database schema (src/db/schema.ts) to include the Comments table and establish the relationship between the two tables using foreign keys.
export const comments = sqliteTable("comments", {
id: integer("id").primaryKey({ autoIncrement: true }),
content: text("content").notNull(),
date: integer("date", { mode: "timestamp" }).notNull(),
postId: integer("post_id")
.notNull()
.references(() => posts.id),
});In the comments table definition:
id column is the primary key for the Comments table.content column stores the text content of the Comment.date column stores the timestamp when the Comment was created.postId column is a foreign key that references the id column of the Posts table.By defining the postId column as a foreign key that references the id column of the Posts table, we establish the one-to-many relationship between Posts and Comments. This relationship ensures that each Comment is associated with a specific Post.
After updating the schema, you need to run the migration to apply the changes to the database. Since we are at early stages of development, you can use pnpm run db:push to recreate the database with the updated schema instead of running a migration.
To test the app, we need to seed the database with sample data. Let’s update src/db/seed.ts to insert sample Posts and Comments into the database.
import { db, connection } from "./index";
import { comments, posts } from "./schema";
import { faker } from "@faker-js/faker";
import { sql } from "drizzle-orm";
async function seed() {
console.log("Seeding the database...");
// Clean the tables
console.log("Cleaning existing data...");
await db.delete(comments);
await db.delete(posts);
// Reset the auto-increment counters
await db.run(
sql`DELETE FROM sqlite_sequence WHERE name IN ('posts', 'comments')`,
);
console.log("Inserting new seed data...");
const sampleKeywords = [
"technology",
"innovation",
"design",
"development",
"programming",
"software",
"hardware",
"AI",
"machine learning",
"data science",
"cloud computing",
"cybersecurity",
];
// Insert 100 sample posts
for (let i = 0; i < 100; i++) {
const randomKeywords = faker.helpers.arrayElements(sampleKeywords, {
min: 1,
max: 3,
});
const content = `${faker.lorem.sentence({ min: 10, max: 200 })} ${randomKeywords.join(" ")}`;
const post = await db
.insert(posts)
.values({
content,
date: faker.date.recent({
days: 5, // The range of days the date may be in the past.
}), // Generates a random date in the recent past.
})
.returning()
.get();
// Insert 1-20 comments for each post
const numComments = faker.number.int({ min: 0, max: 10 });
for (let j = 0; j < numComments; j++) {
const randomKeywords = faker.helpers.arrayElements(sampleKeywords, {
min: 1,
max: 3,
});
await db.insert(comments).values({
content: `${faker.lorem.sentence({ min: 5, max: 100 })} ${randomKeywords.join(" ")}`,
date: faker.date.recent({
days: 3,
}),
postId: post.id,
});
}
}
console.log("Seeding completed successfully.");
}
seed()
.catch((e) => {
console.error("Seeding failed:");
console.error(e);
})
.finally(() => {
connection.close();
});Make sure to run npm run db:seed to populate the database with sample data.
Before implementing routes for Comments, update the input validation schemas for creating, updating, and retrieving Comments. Add the following schemas to src/validators/schemas.ts:
export const createCommentSchema = z.object({
content: z
.string()
.min(1, "Content is required")
.max(120, "Content must be 120 characters or less"),
});
export const updateCommentSchema = createCommentSchema.partial();
export const getCommentsSchema = z.object({
postId: z.coerce.number().int().positive(),
});
export const getCommentSchema = z.object({
postId: z.coerce.number().int().positive(),
commentId: z.coerce.number().int().positive(),
});These schemas define the structure and constraints for creating, updating, and retrieving Comments. The createCommentSchema schema specifies that the content field is required and must be between 1 and 120 characters long. The updateCommentSchema schema is a partial schema that allows updating only the content field. The getCommentsSchema schema defines the structure for retrieving all Comments for a specific Post. The getCommentSchema schema defines the structure for retrieving a single Comment by ID for a specific Post.
Now that you have updated the database schema, seeded the database with sample data, and defined the input validation schemas for Comments, you can implement CRUD operations for Comments. Add the following routes to
src/routes/comments.ts.
Add a route to retrieve all Comments for a specific Post:
// Get all comments for a post
commentRoutes.get(
"/posts/:postId/comments",
zValidator("param", getCommentsSchema),
async (c) => {
const { postId } = c.req.valid("param");
const allComments = await db
.select()
.from(comments)
.where(eq(comments.postId, postId));
return c.json({allComments});
},
);Notice the API endpoint is /posts/:postId/comments, where :postId is the ID of the Post for which you want to retrieve the Comments. We could have used /comments?postId=:postId as well, but the former is more RESTful. The route handler fetches all Comments associated with the specified Post and returns them as a JSON response.
Add a route to retrieve a single Comment by ID for a specific Post:
// Get a single comment by id for a post
commentRoutes.get(
"/posts/:postId/comments/:commentId",
zValidator("param", getCommentSchema),
async (c) => {
const { postId, commentId } = c.req.valid("param");
const comment = await db
.select()
.from(comments)
.where(and(eq(comments.id, commentId), eq(comments.postId, postId)))
.get();
if (!comment) {
throw new HTTPException(404, { message: "Comment not found" });
}
return c.json(comment);
},
);In this route, the API endpoint is /posts/:postId/comments/:commentId, where :postId is the ID of the Post and :commentId is the ID of the Comment you want to retrieve. The route handler fetches the Comment with the specified ID for the specified Post and returns it as a JSON response. If the Comment is not found, it throws a 404 Not Found error.
Add a route to delete a Comment by ID for a specific Post:
// Delete a comment by id for a post
commentRoutes.delete(
"/posts/:postId/comments/:commentId",
zValidator("param", getCommentSchema),
async (c) => {
const { postId, commentId } = c.req.valid("param");
const deletedComment = await db
.delete(comments)
.where(and(eq(comments.id, commentId), eq(comments.postId, postId)))
.returning()
.get();
if (!deletedComment) {
throw new HTTPException(404, { message: "Comment not found" });
}
return c.json(deletedComment);
},
);In this route, the API endpoint is /posts/:postId/comments/:commentId, where :postId is the ID of the Post and :commentId is the ID of the Comment you want to delete. The route handler deletes the Comment with the specified ID for the specified Post and returns the deleted Comment as a JSON response. If the Comment is not found, it throws a 404 Not Found error.
Add a route to create a new Comment for a specific Post:
// Create a new comment for a post
commentRoutes.post(
"/posts/:postId/comments",
zValidator("param", getCommentsSchema),
zValidator("json", createCommentSchema),
async (c) => {
const { postId } = c.req.valid("param");
const { content } = c.req.valid("json");
const newComment = await db
.insert(comments)
.values({
content,
date: new Date(),
postId,
})
.returning()
.get();
return c.json(newComment);
},
);In this route, the API endpoint is /posts/:postId/comments, where :postId is the ID of the Post for which you want to create a new Comment. The route handler extracts the postId from the request parameters and the content from the request body. It then creates a new Comment with the specified content, current date, and Post ID and returns the newly created Comment as a JSON response.
Add a route to update an existing Comment by ID for a specific Post:
// Update a comment by id for a post
commentRoutes.patch(
"/posts/:postId/comments/:commentId",
zValidator("param", getCommentSchema),
zValidator("json", updateCommentSchema),
async (c) => {
const { postId, commentId } = c.req.valid("param");
const { content } = c.req.valid("json");
const updatedComment = await db
.update(comments)
.set({ content })
.where(and(eq(comments.id, commentId), eq(comments.postId, postId)))
.returning()
.get();
if (!updatedComment) {
throw new HTTPException(404, { message: "Comment not found" });
}
return c.json(updatedComment);
},
);In this route, the API endpoint is /posts/:postId/comments/:commentId, where :postId is the ID of the Post and :commentId is the ID of the Comment you want to update. The route handler extracts the postId and commentId from the request parameters and the content from the request body. It then updates the Comment with the specified ID for the specified Post with the new content and returns the updated Comment as a JSON response. If the Comment is not found, it throws a 404 Not Found error.
Similar to the previous tasks, you can implement search, sort, filter, and pagination functionality for the Comments resource. This will allow clients to query the Comments data based on specific criteria and retrieve the results in a structured manner. This is left as an exercise for you to complete.
When a Post is deleted, you should also delete all associated Comments. This is known as a cascade delete operation. You can implement this functionality by updating the delete operation for Posts to also delete all associated Comments.
Alternatively, you can configure the foreign key constraint in the database schema to perform a cascade delete automatically when a Post is deleted. Update the foreign key definition in the comments table to specify the onDelete: "cascade" option:
export const comments = sqliteTable("comments", {
id: integer("id").primaryKey({ autoIncrement: true }),
content: text("content").notNull(),
date: integer("date", { mode: "timestamp" }).notNull(),
postId: integer("post_id")
.notNull()
- .references(() => posts.id),
+ .references(() => posts.id, { onDelete: "cascade" }),
});With this configuration, when a Post is deleted, all associated Comments will be automatically deleted by the database engine.
In this task, you extended the Posts API to manage relational data by adding support for Comments. You updated the database schema to model the one-to-many relationship between Posts and Comments, seeded the database with sample data, implemented CRUD operations for Comments, and handled relational data operations. By managing relational data effectively, you can build more complex and feature-rich applications that leverage the power of relational databases.