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.

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

[image] Isambard: but this was too unreliable Could you share the issues you encountered?

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;

[image] Isambard: i am unable to edit my original post to update Likely because you’re on a different account?

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.

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

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

[image] Michael Brown: running Rails commands I wasn’t aware of rails commands to create posts. Do you have further details of this?

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

[image] Robert: Discourse is a Rails app! I’m aware that Discourse is a Rails app. But you said: [image] Michael Brown: easier then either using the API or running Rails commands to create posts 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?

Writing directly to the database

Development

thoka (Thomas Kalka) March 24, 2024, 6:01am 15

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

Use the rails console for experiments.

Topic		Replies	Views
Can we safely insert users directly into separate Discourse databases? Product Support	3	147	April 22, 2025
How to batch post Development rest-api	3	834	October 14, 2023
How to create post with ruby api? Product Support	2	990	April 26, 2017
Is It Possible to Bulk Create Threads for Many WordPress Posts? SQL Maybe? WordPress	6	1122	August 23, 2021
How can I directly edit Discourse database from a GUI? Self-hosting	20	4094	May 14, 2020

Writing directly to the database

Related topics