Moving from Cove to Ghost Comments

I've replaced the Cove comments system I was using with the native Ghost comments system that was introduced in August. Similar to Cove, Ghost comments are only available to logged-in users.

There's no native way—that I'm aware of—to migrate comments from Cove to Ghost. As such, getting comments to display required an export of the comments stored with Cove and then some mysql shenanigans. Here's a brief walkthrough.

Get The Data

Download either the json or csv extract from Cove. The json is structured as follows:

{
  "total": 129892,
  "comments": [
    {
      "id": "9cc061fd-5fd0-4721-bdfd-be6c20f3c390",
      "is_hidden": false,
      "parent_id": null,
      "date": 1649473135,
      "body": "I am considering open-sourcing Singapore Rail. It would be unsupported, but a good resource for creating cross-platform SwiftUI apps.",
      "topic": {
        "title": null,
        "url": null
      },
      "member": {
        "id": "3eb7c928-566d-471d-af85-b9bc57a4e6ff",
        "name": "Stuart",
        "email": "[email protected]"
      }
    },
  ]
}

Excluding  the body, date and member.email fields, there isn't much you can use. However, this data is required when adding comments to your Ghost database.

Fill in the Blanks

In Ghost, the comments table is structured as below:

+------------+-------------+------+-----+-----------+-------+
| Field      | Type        | Null | Key | Default   | Extra |
+------------+-------------+------+-----+-----------+-------+
| id         | varchar(24) | NO   | PRI | NULL      |       |
| post_id    | varchar(24) | NO   | MUL | NULL      |       |
| member_id  | varchar(24) | YES  | MUL | NULL      |       |
| parent_id  | varchar(24) | YES  | MUL | NULL      |       |
| status     | varchar(50) | NO   |     | published |       |
| html       | longtext    | YES  |     | NULL      |       |
| edited_at  | datetime    | YES  |     | NULL      |       |
| created_at | datetime    | NO   |     | NULL      |       |
| updated_at | datetime    | NO   |     | NULL      |       |
+------------+-------------+------+-----+-----------+-------+

To create your insert statement, you need to be aware of the following:

• For id — you need to create a unique 24-character alphanumeric string for each comment you add
• For post_id — you need to identify the correct id from the posts table: select id, title from posts; (It's possible that the title could be detailed in the topic.title field in the Cove json, but this has never worked for me.)
• For member_id — do not use member.id from Cove, instead, use the member.email field to get the correct id from the members table in Ghost select id from members where email = '[email protected]';
• For parent_id — if this comment is in reply to a previous comment, use the id from the previous comment (you need to keep track!)
• status can be left as default
• For html — use the value of the Cove body field
• edited_at doesn't need a value
• For created_at — you need to convert the Unix timestamp from the Cove date field to a format of YYYY-MM-DD HH:mm:ss
• For updated_at — keep it simple and use the same value as created_at

Insert the Data

With all that cleared up, my insert statement for the sample post from Cove looks like this:

insert into comments (id, post_id, member_id, html, created_at, updated_at) values ('9910abaca5b1ff266d03ffe2', '98b82bea2b907e7308689bfc', '42p061d72b907e7308689c3b', 'I am considering open-sourcing Singapore Rail. It would be unsupported, but a good resource for creating cross-platform SwiftUI apps.', '2022-04-09 02:58:08', '2022-04-09 02:58:08');

...which results in this.