In this task, you will implement CRUD operations for the Post model in the API. You will update the route handlers we created in Task 2 to interact with the database using the Drizzle ORM.
Since we are now using a database, we no longer need the sample posts data. Remove the PostType and posts array from the src/routes/posts.ts file. Instead import the following at the top of the file:
import { db } from "../db";
import { posts } from "../db/schema";
import { eq } from "drizzle-orm";db object is an instance of the Drizzle ORM.posts object is a reference to the posts table in the database.eq function is used to create a query condition.Update the GET route handler for fetching all posts:
// GET all posts
postsRouter.get("/posts", async (c) => {
try {
const allPosts = await db.select().from(posts);
return c.json(allPosts);
} catch (error) {
console.error("Error fetching posts:", error);
return c.json({ error: "Failed to fetch posts" }, 500);
}
});db.select().from(posts) method to fetch all posts from the database.
select() method specifies the columns to select (all columns in this case).from(posts) method specifies the table to select from.SELECT * FROM posts. Drizzle ORM tries to mimic the SQL syntax.return c.json(allPosts) statement sends the list of posts as a JSON response.Try fetching all posts using Postman and verify that the API returns the list of posts (which should be our seed data from Task 3).
Update the GET route handler for fetching a specific post by ID:
// GET a specific post by ID
postsRouter.get("/posts/:id", async (c) => {
const id = parseInt(c.req.param("id"));
try {
const post = await db.select().from(posts).where(eq(posts.id, id)).get();
if (!post) {
return c.json({ error: "Post not found" }, 404);
}
return c.json(post);
} catch (error) {
console.error(`Error fetching post ${id}:`, error);
return c.json({ error: "Failed to fetch post" }, 500);
}
});db.select().from(posts).where(eq(posts.id, id)).get() method to fetch a specific post by ID.
where() method specifies the condition to filter the posts.eq(posts.id, id) condition filters the posts based on the id column.get() method returns a single post object.SELECT * FROM posts WHERE id = :id.Try fetching a specific post by ID using Postman and verify that the API returns the correct post.
Update the DELETE route handler for deleting a post by ID:
// DELETE a post
postsRouter.delete("/posts/:id", async (c) => {
const id = parseInt(c.req.param("id"));
try {
const deletedPost = await db
.delete(posts)
.where(eq(posts.id, id))
.returning()
.get();
if (!deletedPost) {
return c.json({ error: "Post not found" }, 404);
}
return c.json(deletedPost);
} catch (error) {
console.error(`Error deleting post ${id}:`, error);
return c.json({ error: "Failed to delete post" }, 500);
}
});db.delete(posts).where(eq(posts.id, id)).returning().get() method to delete a post by ID.
delete() method specifies the table to delete from.where() method specifies the condition to filter the posts.eq(posts.id, id) condition filters the posts based on the id column.returning() method specifies that we want to return the deleted post(s).get() method returns the deleted post object.DELETE FROM posts WHERE id = :id.Try deleting a post by ID using Postman and verify that the API returns the deleted post.
Update the POST route handler for creating a new post:
// POST a new post
postsRouter.post("/posts", async (c) => {
const { content } = await c.req.json();
try {
const newPost = await db
.insert(posts)
.values({
content,
date: new Date(),
})
.returning()
.get();
return c.json(newPost, 201);
} catch (error) {
console.error("Error creating post:", error);
return c.json({ error: "Failed to create post" }, 500);
}
});db.insert(posts).values({ content, date: new Date() }).returning().get() method to create a new post.
insert() method specifies the table to insert into.values() method specifies the values to insert.returning() method specifies that we want to return the newly created post.get() method returns the newly created post object.INSERT INTO posts (content, date) VALUES (:content, :date).Try creating a new post using Postman and verify that the API returns the newly created post.
Update the PATCH route handler for updating an existing post:
// PATCH (update) a post
postsRouter.patch("/posts/:id", async (c) => {
const id = parseInt(c.req.param("id"));
const { content } = await c.req.json();
try {
const updatedPost = await db
.update(posts)
.set({ content })
.where(eq(posts.id, id))
.returning()
.get();
if (!updatedPost) {
return c.json({ error: "Post not found" }, 404);
}
return c.json(updatedPost);
} catch (error) {
console.error(`Error updating post ${id}:`, error);
return c.json({ error: "Failed to update post" }, 500);
}
});db.update(posts).set({ content }).where(eq(posts.id, id)).returning().get() method to update an existing post.
update() method specifies the table to update.set() method specifies the columns to update.where() method specifies the condition to filter the posts.eq(posts.id, id) condition filters the posts based on the id column.returning() method specifies that we want to return the updated post.get() method returns the updated post object.UPDATE posts SET content = :content WHERE id = :id.Try updating an existing post using Postman and verify that the API returns the updated post.
In this task, you implemented CRUD operations for the Post model in the API. You updated the route handlers to interact with the database using the Drizzle ORM. You can now perform all CRUD operations on the Post model through the API endpoints.