Teague
Teague Stockwell
Software Engineer
Seattle, WA

Threaded Comments with React Query

Teague Stockwell
Teague Stockwell2021-12-21
Comment Thread

Table of Contents

  1. About
  2. The Data Layer
  3. The API Layer
  4. The React Client
  5. Results

About

These past 6 months, I've been building a social platform. As I'm scoping out some of the features I would like this app to have, I ran across one that I could not find many resources on. The feature in question was a commenting system that implemented:

  • Threaded Comments
  • Infinite scroll
  • Sorting
  • Incremental Static Regeneration + Client Side Revalidation
  • Optimistic Updates
  • Ability to like comments

After studying the features of some other commenting systems that have been implemented at scale, I was excited to jump in and try my hand at it. Lucky for me, I have all the best tooling to work with. Together with Typescript, these tools have created the best developer experience I've yet to work with.

  • Prisma an ORM for creating a type-safe API, and pragmatic database schema
  • React Query a client library for caching, querying, and mutating server state
  • Nextjs a React framework to implement server-side rendering
  • Vercel a platform to implement CI while hosting static content and serverless functions

The Data Layer

  • What schema can a relational database use to store a tree of comments?
  • How can the client lazily load a tree of comments?
  • How can the client mutate a nested model without creating an expensive query to find it?

My first thought was to use a JSON field to store comments within each other, but after some consideration, I found this would significantly reduce how effectively I can query for a comment. A better implementation of this is to have a field that denotes the primary key of a comments parent, where an empty string would refer to a root level comment:

model Comment {
  commentId String @id @default(cuid())

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  parentCommentId String
  email           String
  postId          String
  numLikes        Int     @default(0)
  text            String

  user           User  @relation("commented", fields: [email], references: [email])
  post           Post   @relation(fields: [postId], references: [postId])
  commentLikedBy User[] @relation("likedComments")
}
  • parentCommentId preserves the hierarchy of the comment thread
  • postId, numLikes, and createdAt allows the comments to be filtered
  • commentId is a sequential GUID to allow pagination
  • email is used server side to control which authenticated users can modify a comment
  • commentLikedBy is used to make sure each user can only like a comment once

This generates the following SQL

CREATE TABLE "Comment" (
    "commentId" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "parentCommentId" TEXT NOT NULL,
    "email" TEXT,
    "postId" TEXT NOT NULL,
    "numLikes" INTEGER NOT NULL DEFAULT 0,
    "text" TEXT NOT NULL,

    PRIMARY KEY ("commentId")
);

CREATE TABLE "_likedComments" (
    "A" TEXT NOT NULL,
    "B" TEXT NOT NULL
);

ALTER TABLE "Comment" ADD FOREIGN KEY ("email") REFERENCES "User"("email") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "Comment" ADD FOREIGN KEY ("postId") REFERENCES "Post"("postId") ON DELETE CASCADE ON UPDATE CASCADE;

CREATE UNIQUE INDEX "_likedComments_AB_unique" ON "_likedComments"("A", "B");
CREATE INDEX "_likedComments_B_index" ON "_likedComments"("B");
ALTER TABLE "_likedComments" ADD FOREIGN KEY ("A") REFERENCES "Comment"("commentId") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "_likedComments" ADD FOREIGN KEY ("B") REFERENCES "User"("email") ON DELETE CASCADE ON UPDATE CASCADE;

With the schema of a comment figured out, the next task would be figuring out how to host the database so that it can support all the connections my serverless functions would throw at it. I tried a handful of cloud providers and some of their hosted solutions for connection pooling including AWS RDS with RDS Proxy, AWS Aurora, and Digital Ocean with PgBouncer. I settled with a new platform called Planet Scale because they made the MySQL sharding middleware used by YouTube: Vitess. For a few other projects, Planet Scale has been working great with Vercel's functions, and theoretically, this architecture could be used all the way up to planet scale, but I'm not sure how well it would hold up if you are trying to serve web apps to galaxies 😂!

The PostgreSQL DB is currently hosted on Digital Ocean (DO) using PgBouncer to pool connections, however, I plan to migrate this to PlanetScale (PS) and MySQL soon because:

  • PS auto-scales to the moon with Vitess 🚀
  • PS has DB branching
  • PS has better vitals
  • PS has better logging
  • PS has a much greater connection limit than PSQL and PGBouncer
  • DO has no support for traffic spikes
  • My other DBs are on PS

Lee Robinson also has an excellent write up on this topic From Firebase/Redis to MySQL with PlanetScale

The API Layer

Using the above DB schema allowed me to expose an endpoint that will allow the client to lazily load a tree of comments with the following route:

GET /api/comment?params

  • Passing parentCommentId as an empty string in the query params returns a page of root level comments
  • Passing parentCommentId as a comment's primary key would return all of that comment's replies

request.params schema

{
  "commentId": "yup.string().max(30)",
  "numLikesGTE": "yup.number()",
  "numLikesLTE": "yup.number()",
  "parentCommentId": "yup.string().max(30)",
  "postId": "yup.string().max(30)",
  "userId": "yup.string().max(30)",

  "cursor": "yup.string().max(30)",
  "orderBy": "yup.string().max(30).oneOf(['top', 'new', 'old'])",
  "pageSize": "yup.number().max(100)"
}

200 response.body json

[
  {
    "commentId": "ckx77wugz003509l0lxe91ztt",
    "parentCommentId": "ckx77o7w1034709lfqjn24m76",
    "createdAt": "2021-12-15T07:34:12.035Z",
    "updatedAt": "2021-12-15T07:34:47.488Z",
    "postId": "ckuxu0kk202941ic9mw03btou",
    "numLikes": 0,
    "text": "Comment Deleted",
    "user": {
      "profileSrc": "",
      "name": "deleted",
      "userId": "deleted",
      "createdAt": "1970-01-01T00:00:00.000Z",
      "updatedAt": "2021-12-15T02:37:03.883Z"
    }
  }
]

When a PUT or DELETE request is handled, the API will verify the request's JWT is legitimate and that the user making the request has permission to perform the operation. For example:

const comment = await prisma.comment.findUnique({
  where: {commentId: req.query.commentId},
})
const isOwner = jwt.sub === comment.email

if (req.method === 'PUT' && isOwner) {
  // create or update
}

if (
  req.method === 'DELETE' &&
  (isOwner || (await isModerator(jwt.sub, comment)))
) {
  // delete
}

This is great because the API can normally verify a user owns a comment using the JWT to avoid making a separate query for the user's model. However, if a user does not own the model, the API can make a query to see if the user is a moderator.

PUT /api/comment/[commentId]

  • Authenticated users may CREATE
  • Authenticated users who own the comment may UPDATE

request.body

{
  "commentId": ""
  "parentCommentId": "",
  "postId": "",
  "text": ""
}

200 || 201 response.body

{
  "commentId": ""
  "parentCommentId": "",
  "postId": "",
  "text": "",
  "createdAt": "",
  "updatedAt": "",
  "numLikes": 123,
  "user": {
    "email": ""
    ...user
  }
}

Unlike a normal DELETE operation, calling this will only soft delete a comment to preserve a deleted comment's replies. To preserve the relationship where every comment must have a user, I created a new user that is assigned all the deleted comments. This way when a comment is "deleted" its text is updated to say it has been deleted, and then its user is changed to the deleted user.

Soft Delete

DELETE /api/comment/[commentId]

  • Topic moderators may DELETE a comment
  • Comment owner may DELETE a comment

200 response.body

{
  "commentId": ""
  "parentCommentId": "",
  "postId": "",
  "text": "Comment Deleted",
  "createdAt": "",
  "updatedAt": "",
  "numLikes": 123,
  "user": {
    "email": "deleted"
    ...deletedUser
  }
}

The React Client

To have great SEO, the first few root comments are ordered by top and statically generated during build time for each post. Once the static page is served to the client, React hydrates the page, and React Query can take over to lazily load the tree of comments:

  1. Make a request with parentCommentId as an empty string to return a page of root level comments

As the user scrolls to the bottom of the root level comments, a component with an intersection observer will enter the viewport and fire a callback to automatically load more root level comments (infinite scroll).

export const CommentList = React.memo(
  ({
    initialComments,
    postId,
  }: {
    initialComments: Types.Comment.PostComment[]
    postId: string
  }) => {
    const orderBy = useCommentOrderStore((s) => s.order)
    const q = useInfiniteQuery({
      notifyOnChangeProps: ['data'],
      queryKey: ['comments', postId, orderBy],
      queryFn: async ({pageParam: cursor = ''}) => {
        const params = new URLSearchParams({
          pageSize: '50',
          postId,
          cursor,
          // only get root comments
          parentCommentId: '',
          orderBy,
        }).toString()

        const {data} = await axios('/api/comment?' + params)

        return data as Types.Comment.PostComment[]
      },
      getNextPageParam: (lastPage: any) => {
        return lastPage[lastPage.length - 1]?.commentId
      },
      cacheTime: Infinity,
    })

    const listStyle: React.CSSProperties = {
      marginTop: 30,
      marginRight: 20,
      marginLeft: 20,
    }

    // SSR and 1st CSR
    if (!q.data) {
      return (
        <>
          <Nav order={'top'} />
          {initialComments.length ? (
            initialComments.map((c) => {
              return (
                <div key={c.commentId} style={listStyle}>
                  <CommentView comment={c} />
                </div>
              )
            })
          ) : (
            <LoadingText text="There are no comments here yet" />
          )}
        </>
      )
    }

    const flatComments = q.data.pages.flatMap((p) => p.map((c) => c))

    if (!flatComments.length) {
      return (
        <>
          <Nav order={orderBy} />
          <LoadingText text="There are no comments here yet" />
        </>
      )
    }

    return (
      <>
        <Nav order={orderBy} />

        <div style={listStyle}>
          {flatComments.map((c) => {
            return (
              <Lazy key={c.commentId} unmountIfInvisible offset={1000}>
                <CommentView comment={c} />
              </Lazy>
            )
          })}
        </div>

        <UseInViewport
          cb={(inView) => (inView && q.hasNextPage ? q.fetchNextPage() : null)}
        />

        <LoadingText
          text={
            q.hasNextPage
              ? 'Hang on one second we are loading more comments'
              : 'Congrats! You made it through all the comments'
          }
        />
      </>
    )
  },
  (s0, s1) => s0.postId === s1.postId
)

CommentList.displayName = 'CommentList'
  1. For each root level comment, make a request with parentCommentId as the comments key to return a page of comments replies
  2. For each visible reply repeat step two

When each <CommentView/> enters the viewport, it uses React Query to GET /api/comment?params. Instead of passing an empty string to parentCommentId to fetch the root levels comments, <CommentView/> passes its own id to fetch a page it's replies. If a <CommentView/> has replies, the user may click to show more replies. Showing the replies will recursively render the replies and repeat the cycle to fetch the replies of the replies.

Unlike root-level comments, replies do not implement an infinite scroll. Instead, the user may press a button and choose to fetch more comments.

Load more
export const CommentView = React.memo(
  ({comment}: {comment: Types.Comment.PostComment}) => {
    const [ref, visible] = useVisible((vi: number) => vi > 0.1)
    const [open, setOpen] = useState(false)
    const orderBy = useCommentOrderStore().order
    const pageSize = 10
    const q = useInfiniteQuery({
      notifyOnChangeProps: ['data'],
      enabled: visible,
      queryKey: ['comment', 'replies', comment.commentId, orderBy],
      queryFn: async ({pageParam: cursor = ''}) => {
        const params = new URLSearchParams({
          pageSize: pageSize.toString(),
          postId: comment.postId,
          cursor,
          parentCommentId: comment.commentId,
          orderBy,
        }).toString()

        const {data} = await axios('/api/comment?' + params)

        return data as Types.Comment.PostComment[]
      },
      getNextPageParam: (lastPage: any) => {
        if (!lastPage) return undefined
        return lastPage[lastPage.length - 1]?.commentId
      },
    })

    const ReplyToggleAction = () => {
      if (q.data?.pages.length === 0 || q.data?.pages[0].length === 0) {
        return null
      }

      const action = open ? 'Hide' : 'Show'

      return (
        <CommentActionButton
          onClick={() => setOpen((o) => !o)}
          text={`${action} replies`}
        />
      )
    }

    return (
      <>
        <CommentContent comment={comment} />

        <div
          style={{display: 'flex', marginLeft: 50, marginTop: 4}}
          ref={ref as any}
        >
          <CommentLike comment={comment} />
          <CommentReply comment={comment} />
          <CommentEdit comment={comment} />
          <CommentDelete comment={comment} />
          <ReplyToggleAction />
        </div>

        {open && (
          <>
            {(q as any).data.pages.map((page: any) =>
              page.map((c: Types.Comment.PostComment) => {
                return (
                  <Lazy key={c.commentId} unmountIfInvisible offset={1000}>
                    {/* thread the comments with the margin left */}
                    <div style={{marginLeft: 20}}>
                      <CommentView key={c.commentId} comment={c} />
                    </div>
                  </Lazy>
                )
              })
            )}

            {/* 
            This does not have auto fetch, because it's nested inside of an infinite query
            that already has auto fetch.
          */}
            {q.hasNextPage &&
              q.data?.pages[q.data.pages.length - 1].length === pageSize && (
                <CommentActionButton
                  text="Load more"
                  onClick={() => q.fetchNextPage()}
                />
              )}
          </>
        )}
      </>
    )
  },
  isEqual
)

CommentView.displayName = 'CommentView'

Results

Overall, I'm happy with how this design works. I seeded a post with 10k threaded comments, then scrolled through and posted a few comments of my own. The performance is great because React can lazily render only the tree of comments that are currently in the viewport. Using React Query and Nextjs to implement this was not only a great experience, but I got the best of both worlds because initially, the top comments use incremental static regeneration, so pages can be served from a CDN, then after React hydrates the page, it can lazily refetch for the most up to date comments.