Revamping 1klb comments part two: the database

Published
2021-02-21
Tagged
Part of a series: Revamping 1klb comments
  1. Overview
  2. Database
  3. Display
  4. Submitting

We’re building a commenting system for a static site blog! Or at least, I’m taking you on a guided tour of my blog commenting system.

In my last blog post I outlined how the commenting system of the blog works, at least at the big picture level. In this post, I’ll go through how we set up the database.

Building the backend

We’re always going to need somewhere to store our comments, and that means a database of some sort. For my blog, I’m building my commenting backend in Fauna,1 which is a web-based database which allows access both through its own API (usually using one of their libraries), and through the GraphQL API spec. GraphQL is a bit trickier to use, but also doesn’t require you to run the custom library. That custom library is about 100kb of download, so we’re going to be chosing between “download more” and “make our job easier” as we proceed through this framework.

One of the big advantages of Fauna is that as a document-based database it’s pretty flexible. We don’t need to set up the schema exactly right ahead of time, although it’s handy to plan things just so you can plan indices, functions, and how you use the results. (And also you do need to plan things a bit when you set up the GraphQL API.)

Previously, we recorded a bare minimum of data for each comment. We used Fauna’s ts field to record when the comment was made, for example, rather than storing the creation time as a created_at field or similar. It turns out it’s not great to use admin data for this purpose, as when we edit a comment we’ll change the apparent date it was posted at. In addition, we’re going to add a few more fields to these comments to provide some “quality of life” improvements.

We’re going to assume each comment has the following fields:

  • submitter: the name the commenter provides when they submit the comment
  • body: the comment itself
  • created_at: the time the comment was posted. We’ll automatically generate this in Fauna
  • post_slug: the URL of the blog post on which this comment was made2
  • post_title: the title of the blog post on which this comment was made (we’ll use this when sending emails about the comment)
  • status: whether this comment has been approved or rejected by the admin (i.e. me)

Some of these fields are pretty obvious, while some I’ve arrived at over a bit of experimentation. You’ll see how it all fits together as we go through the system.

Create the Comment collection

As mentioned, we don’t actually need to specify the structure of our comment collection when we create it. Once we’ve created a new database, we can create the collection either by selecting Collections > New Collection, or entering code in the Shell tab.

The Fauna sidebar gives us access to all the parts of our database

The relevant code in the Shell is:

1
CreateCollection({name: "Comment"})

We’re going to do a decent amount of work in the shell as we continue: it’s generally easier than trying to replicate things by pointing and clicking through the various menus.

Indices and functions

Alongside the structure of the database, we’re going to want to design some indices and functions. In particular, we’re going to want to be able to:

  • Create new comments (for when a visitor makes a comment on a blog post)
  • View all the comments for a specific blog post (for when I want to show the comments on a post)
  • View all the comments with a specific status (for administering the comments on the backend)

Create new comments

We’re going to wrap the creation of comments in a useful convenience function. This makes it a bit easier to call the code from our server-side function, and also lets us set some default values (here the creation time of the post and the post’s moderation status).

1
CreateFunction({

2
  name: "add_comment",

3
  role: "server",

4
  body: Query(

5
    Lambda(

6
      "comment",

7
      Create(Collection("Comment"), {

8
        data: Merge(Var("comment"), { status: 0, created_at: Now() })

9
      })

10
    )

11
  )

12
)}

View comments per blog post and per status

Each blog post is represented by its post_slug. We’re going to need to set up an index of comments by post. In addition, we’re going to want to do the same for moderation status.

1
CreateIndex({

2
  name: "commentsByPostSlug",

3
  unique: false,

4
  serialized: true,

5
  source: Collection("Comment"),

6
  terms: [

7
    {

8
      field: ["data", "post_slug"]

9
    }

10
  ]

11
})

12
13
CreateIndex({

14
  name: "commentsByStatus",

15
  unique: false,

16
  serialized: true,

17
  source: "Comment",

18
  terms: [

19
    {

20
      field: ["data", "status"]

21
    }

22
  ]

23
})

24
25
CreateFunction({

26
  name: "get_comments_by_status",

27
  role: "server",

28
  body: Query(

29
    Lambda(

30
      "status",

31
      Map(

32
        Paginate(Match("commentsByStatus", Var("status"))),

33
        Lambda(

34
          "commentRef",

35
          Let(

36
            { comment: Get(Var("commentRef")) },

37
            {

38
              submitter: Select(["data", "submitter"], Var("comment")),

39
              post_slug: Select(["data", "post_slug"], Var("comment")),

40
              post_title: Select(["data", "post_title"], Var("comment")),

41
              created_at: Select(["data", "created_at"], Var("comment")),

42
              ref: Select(["ref", "id"], Var("comment")),

43
              body: Select(["data", "body"], Var("comment"))

44
            }

45
          )

46
        )

47
      )

48
    )

49
  )

50
})



Create users



It might surprise you that we’re going to set up users, but there’s one important user for this database: me, the moderator. Fauna’s attribute-based access control is basically a way for us to tell the database that logged-in users can do things (like edit comments, for example), while logged-out users cannot. Once you’ve created the User database, you can create a login function as follows:


1
CreateIndex({

2
  name: "usersByUsername",

3
  unique: true,

4
  serialized: true,

5
  source: "User",

6
  terms: [

7
  {

8
    field: ["data", "username"]

9
  }]

10
})

11
12
CreateFunction({

13
  name: "login",

14
  role: "server",

15
  body: Query(

16
    Lambda(

17
      ["username", "password"],

18
      Login(Match("usersByUsername", Var("username")), {

19
        password: Var("password")

20
      })

21
    )

22
  )

23
})



You can then create a new user in the shell:


1
Create(

2
  Collection("User"),

3
  {

4
    data: {username: "yourUsername"},

5
    credentials: {password: "yourPassword"}

6
  }

7
)



We store the password in the credentials field, which means it’s not just stored in the database in plaintext for anyone to retrieve. In fact, we only interact with this field through the login function above.



Set permissions



At this point, we’ve set up our collection of posts, the various indices and functions associated with it, and our users. The only thing left is to set up permissions, so that we can access the bits of our database that we need to.



Fauna allows you to set up permissions either based on API keys, or through attribute-based access control (ABAC, as mentioned above). If you want to use API keys, you can set up the appropriate permissions, download an API key, and embed that into your app. ABAC works on a per-object basis: you assign permissions to database objects, and then later on you can log in as those objects and receive the equivalent permissions.



We want to set up a mix of permissions:


  • The website should be able to read all comments, as well as the commentsByPostSlug index. This will be an API key permission.
    • 

  • The server-side function will need to be able to create new comments. This will also be an API key permission.
    • 

  • We’ll need to be able to log in as a user - so we need to be able to call the login function. This will also be an API key permission.
    • 

  • Finally, we’ll need to ensure that logged in users can basically do anything to comments. This will be the only ABAC permission we need.
    • 

1
CreateRole({

2
  name: "ReadComments",

3
  privileges: [

4
    {

5
      resource: Collection("Comment"),

6
      actions: {read: true}

7
    },

8
    {

9
      resource: Index("commentsByPostSlug"),

10
      actions: {read: true}

11
    }

12
  ]

13
})

14
15
CreateRole({

16
  name: "CreateComments",

17
  privileges: [

18
    {

19
      resource: Collection("Comment"),

20
      actions: {create: true}

21
    },

22
    {

23
      resource: Function("add_comment"),

24
      actions: {call: true}

25
    }

26
  ]

27
})

28
29
CreateRole({

30
  name: "Login",

31
  privileges: [

32
    {

33
      resource: Function("login"),

34
      actions: {call: true}

35
    }

36
  ]

37
})

38
39
CreateRole({

40
  name: "EditComments",

41
  privileges: [

42
     {

43
      resource: Collection("Comment"),

44
      actions: {

45
        read: true,

46
        write: true,

47
        create: false,

48
        delete: false,

49
        history_read: false,

50
        history_write: false,

51
        unrestricted_read: false

52
      }

53
    },

54
    {

55
      resource: Index("commentsByStatus"),

56
      actions: {

57
        unrestricted_read: false,

58
        read: true

59
      }

60
    },

61
    {

62
      resource: Ref(Ref("functions"), "get_comments_by_status"),

63
      actions: {

64
        call: true

65
      }

66
    }

67
  ],

68
  membership: [

69
    {

70
      resource: Collection("User")

71
    }

72
  ]

73
})



Now we’ll be able to grab API keys for the first three of these later. Any logged in user will gain EditComments permissions, because they are a member of the role.



Setting up GraphQL



Like I said, Fauna is a pretty low-prep database: we’re pretty ready to go at this stage. In fact, if we were happy using Fauna’s javascript libraries for everything, we wouldn’t need to do anything else. But I do want to set up GraphQL for a couple of operations in the future.



GraphQL is a pretty relaxed API spec, and Fauna makes it pretty easy to hook up the various bits. It does this through a GraphQL “schema” document, which outlines what objects exist, what fields we can query, and what mutations we can perform.



Here’s the GraphQL schema that we need to use with our commenting framework:


1
type Comment {

2
  body: String!

3
  post_title: String!

4
  post_slug: String!

5
  status: Long!

6
  created_at: Time!

7
  submitter: String!

8
}

9
10
type Query {

11
  commentsByPostSlug(post_slug: String!): [Comment]!

12
}



This is pretty short, because all we want to do is fetch the comments we’re going to show on a particular page. We’ll handle new comments via server-side functions, and all the admin is complex enough that we benefit by using the javascript driver. If we wanted to do anything else through this API (and we could try!) we would need to spec out the GraphQL schema more.



To load this schema into Fauna:


  1. Save it to a file.
    2. 

  2. Go to the GraphQL tab in your Fauna dashboard.
    3. 

  3. Click update schema.
    4. 

  4. Load the file.
    5. 



And you should be good to go!



What’s next?



At this point, we’ve got all the backend done. This is the complex and, frankly, unrewarding work that we need to go through to make the rest of this work. Next, we’ll try displaying comments on the blog via javascript.







    


  1. 

    Why Fauna? To be honest, because it’s there. There’s plenty of other online databases you could use! ↩
    

    2. 


  2. 

    From the journalism term “slug” meaning the heading for an article - this is a uniquely identifying string which also, when attached to your blog’s URL, should point the reader to the post itself. ↩
    

    3.