Replies are... well, comments too!

#javascript #programming #node #database

Last week, I did a bit of refactoring on Picashot's web service, and went through a rethink of how to store comments and replies in the database. I initially had 2 tables for this, a Comment table and Reply table. These tables had very similar columns, and the only thing that made them distinct in any way was that the Comment table had a comment column that holds the actual text of the comment, while the Reply table had a reply column for the reply text, and a columnID column that references the comment row it replies to.
Here's how the Comment model looks like:

class Comment extends Model { }

Comment.init(
  {
    userId: {
      type: DataTypes.UUID,
      allowNull: false,
    },
    id: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true,
      unique: true,
    },
    comment: {
      type: DataTypes.TEXT,
      allowNull: false,
    },
    postId: {
      type: DataTypes.UUID,
      allowNull: false,
    },
  },
  {
    sequelize: instance,
    modelName: "comments",
    tableName: "comments",
    timestamps: true,
    createdAt: true,
    updatedAt: true,
  }
);

and now the Reply model:

class Reply extends Model {}

Reply.init(
  {
    userId: {
      type: DataTypes.UUID,
      allowNull: false,
    },
    id: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true,
      unique: true,
    },
    reply: {
      type: DataTypes.TEXT,
      allowNull: false,
    },
    commentId: {
        type: DataTypes.UUID,
        allowNull: false,
      },
  },
  {
    sequelize: instance,
    modelName: "replies",
    tableName: "replies",
    timestamps: true,
    createdAt: true,
    updatedAt: true,
  }
);

What's even more wild is that I had two controllers that performed the same read/write operations for both tables and really wasn't comfortable with how this was designed. Besides the regular controllers for the table, there was a CommentLike model that stores comments liked by users using the commentId and userId column, however this table was only meant for the Comment table. Replies could not be liked, or even replied to because of the rigid design nature I adopted for it months back🤮. The final bit of crazy was where I had two notification models for both tables, CommentNotification and ReplyNotification, each referencing rows in both Comment and Reply table. Yes, this also means writing different controller classes that literally did the same thing, besides a few columns that were distinct🤦

I finally decided to rethink this design approach, and I think what really helped me out here was the way tweets work on twitter. I spent some time using the twitter api for evaluating tweet acitivities, and noticed how tweet response are usually structured. Essentially every comment, or quote made on twitter is a tweet, which means they all have the same common attributes (columns) that describes them. What could probably distinguish a tweet from another is if one tweet is a standalone tweet, or a reply to another tweet, but generally they are all a single entity - A Tweet

Having this in mind, I realized that every reply to a comment is still essentially a comment, so I could actually store all replies within the comments table. But how would I be able to distinguish a standalone comment from a reply to a comment, since querying for all comments related to a particular post would include the standalone comments and replies, which isn't what we want? For this, I created a junction table called CommentReply with two columns commentId, and referenceId like this:

class CommentReply extends Model { }
CommentReply.init(
    {
        commentId: {
            type: DataTypes.UUID,
            allowNull: false,
            primaryKey: true,
            unique: true,
        },
        referenceId: {
            type: DataTypes.UUID,
            allowNull: true,
        },
    },
    {
        sequelize: instance,
        modelName: "comment_replies",
        tableName: "comment_replies",
        timestamps: true,
        createdAt: true,
        updatedAt: true,
    }
);

The commentId column references the comment that is supposed to be a reply, while the referenceId column references the comment being replied to. So, whenever a reply is made to a certain comment, the reply is stored in the database as a comment, and the id of this comment (reply) along, with the id of the comment that's being replied to is stored in a CommentReply.

Now how do I query for comments that are originally standalone comments, not replies. The idea is to query for every comment within the database, and perform a left join with the CommentReply table. A "left join" would include null columns in the query results for comments that do not have references in the commentId column of the CommentReply table. So rows with null commentId column represents original standalone comments, while the non-null columnID rows are replies. So based on this ideology, we can query for standalone comments, or just comments that are replies to a certain comment Here's how I performed the query in the comment controller class:

  async getComments({ postId, limit, offset, userId, commentId }: GetShotParams & { shotId: string, commentId: string | null }) {

    /* if query contains commentId, fetch all comments referenced by that id
    else, fetch comments that do not have a row in the replies. Note that: Standalone
    comment ids can never have a row in the columnId for commentReplies, but references so this works!
    */

    const where: WhereOptions = commentId ? { shotId, '$comment_reply."referenceId"$': commentId } : { postId, '$comment_reply."commentId"$': null }

    const comments = (await Comment.findAll({ where, order: [['createdAt', "DESC"]], limit, offset, include: COMMENT_INCLUDE_OPTIONS }))
    return comments
  }

What makes this even better is that replies also get to have likes, or even nested replies just like "original comments" which wasn't available in the first design. This recursive sequence just keeps going on and on without adding an extra line of code to the controller, making it more flexible than the initial approach.

After implementing this, I was able to migrate every reply from the Reply table to the Comment table, and severe all ties to it😁 Now, I get to delete the reply table, its controllers, and including the notification model that is associated with this table. Damnn that's really a lot of code😵 I think the best part of refactoring code is where you get to delete a number of lines of code that really are just garbage😅