Writing directly to the database

dev
1

Has anyone here already done work to read/write from the database directly? At first I tried to use the API to create threads and posts, but this was too unreliable, so now I’m looking at trying to write directly to the database.

The data I want to put in is quite simple: (thread title, author, category, body text) and for posts (author, body text).

However, I suspect various additional fields need to be also completed and perhaps lookups made to the user database.

I will be starting from zero so looking to see if anyone has done it before or has any pointers on the table structures or any things to watch out for.

2

i forgot i also need to add the date/time to each (i am unable to edit my original post to update).

3

Could you share the issues you encountered?

2 Likes
4

You can see my other posts. They were regarding:

  • Rate limit issues
  • Validation issues
  • Validation bypass not working consistently for threads vs topics

In the end, I figure it would be easier to go directly to the database.

Instead of hours of frustration trying to deal with the vagaries of the API, in a few minutes I managed to get something working and hopefully more performant:

For those wanting to do the same, notes from my exploration so far:

First enter the container:

sudo ./launcher enter app

Then connect to the database:

sudo -u postgres psql discourse

To insert a topic:
insert into topics (title, user_id, archetype, fancy_title, category_id, created_at, updated_at, last_post_user_id, bumped_at) values ('psql test', 1, 'regular', 'psql test',8, NOW(), NOW(), 1, NOW());

Get the new id, in my case 886.

Then insert posts:

insert into posts (user_id, topic_id, post_number, raw, cooked, created_at, updated_at, last_version_at) values (1,886,1,'this is the raw text','this is the cooked test',NOW(),NOW(),NOW());

Then update posts_count (if not already done at topic insertion). Note it seems that the topic body needs an initial post. Below changes number of posts of the topic to be 1:

update topics set posts_count=2 where id=886;

5

Likely because you’re on a different account?

3 Likes
6

I recommend that you figure out how to use the API. Lots of magic gets handled by rails. The likelihood that you’ll do something that will make your database unusable is high.

5 Likes
7

But do you see anything that can go wrong if you are just adding to topics and post tables and they are correctly formed?

8

This is a terrible idea.

Why do you think this is easier then either using the API or running Rails commands to create posts?

5 Likes
9

I wasn’t aware of rails commands to create posts. Do you have further details of this?

10

Yes, the details are: Discourse is a Rails app!

1 Like
11

I’m aware that Discourse is a Rails app. But you said:

So implying that there’s another way of generating threads by issuing ‘rails commands’ unless by rails command you mean manually creating accounts and typing them into discourse web front end?

12

It’s good practice to use the API especially if you are making a call from outside the app, because it takes care of all the authentication and authorisation as well as a lot of business logic that you can’t always assume.

2 Likes
13

Well, one of the things I want to do is bypass all authorisation and get the post into the database without having it trip up because a user doesn’t have permission to post into a category, or the topic is too short, or there’s not enough entropy etc. etc.

It would be nice if there was a ‘superuser’ API call which bypassed all these checks and just creates the post or topic.

For example, if you want to use the API to create a topic under a user which currently has no permission to post in a certain category, you can use the bypass_validations parameter to do this. But when you then call the API to create a reply by the same user, the validation checks are not skipped and the thread creation fails. (this is a bug which was reported 6 years ago with a pull request for a fix which never made it into the codebase).

Also, in this case, unlike with directly writing into the database, there’s no support for transactions to roll back the creation of the original thread and you have to manually find it to clean it up and fix it.

14

For now, just inserting the posts/topics seems to work OK. I was a bit worried about the ‘cooked’ column as it was not possible to have this null, but I’m just filling it in with the same text as raw for now and leaving baked_at and baked_version NULL.

On view, the baking process seems to be triggered quite quickly when the post is viewed.

OK. I found a way to trigger the rebake:

rake posts:rebake

15

Use specs and import scripts as a guide to manipulate discourse data structures via ruby commands.

Use the rails console for experiments.

3 Likes
16

It’s worth pointing out that if your client is written in Ruby you can use the Ruby API gem:

https://rubygems.org/gems/discourse_api

1 Like
17

If you create the record with Rails, it’ll do the rebake automatically when the post is saved as well as send notifications and a bunch of other stuff.

18

When the post is created with raw database access, Discourse also seems to rebake it immediately.

1 Like