Best way to run queries to change user profile data?

I’m looking to change the join date on a few users after an import.

What’s the best way to run queries and do this?

I saw some command line suggestions in this topic:

But I wonder if that’s out of date, since I get this error when running rails c in the container:

/usr/local/lib/ruby/gems/2.6.0/gems/activerecord- rescue in postgresql_connection’: FATAL: Peer authentication failed for user “discourse” (ActiveRecord::NoDatabaseError)`

Does the Data Explorer plugin work to run update and insert queries, or is it just read only?

What’s the best way to connect to the database and run some queries?


The “best way” often depends more on your own knowledge, comfort and skill level. There is no single “best way” for every type and level of system admin.

Here is one way, which works for people who prefer direct DB access:

# cd /var/discourse
# ./launcher enter app
# su discourse -c psql 
psql (10.12 (Debian 10.12-1.pgdg100+1))
Type "help" for help.
discourse=> \c discourse
You are now connected to database "discourse" as user "discourse".


For those more familiar with Rails, they consider Rails the “best way”, so you might try many of the documented Rails console approaches to DB access.

In the end, regardless of method, you should understand the basics of SQL and the discourse DB structure.

PS: We installed and looked at the Data Explorer plugin. Because we have more than 20 years SQL direct DB experience, Data Explorer plugin is not for us; as we long-in-the-tooth sys admins prefer command line and not GUI based. Others however, might and do often prefer GUI based. We only install plugins if absolutely required because there are no other alternatives (for performance reasons).


Something like

cd /var/discourse
./launcher enter app
rails c

I don’t promise that will work, but it should be close.

There should be plenty of examples. It’s often the case that making changes with Rails protects you from being able to break things in a horrible way.



I have no idea about that @pfaffman and have never read anything by DB experts where they say it is safer to use Rails to query the DB over the CLI, PHP, Javascript or other DB query clients. I have seen a few people post this comment here at meta (including your good self, more than once), but outside meta, frankly speaking, I cannot find a discussion which says “Rails saves you from bad SQL queries”. (I searched before posting today and searched a few weeks ago).

DB speaking, I have been working with DB driven web forums for close to 20 years, always use SQL on the command line, and have never broken “things in a horrible way” using the basic CLI, never in two decades. Anyone can break the DB with any query client if they have GRANT permissions.

This means, honestly, I am slightly confused as to the origin of this “Rails saves you from yourself” idea; because we can issue a Rails query which will delete every user in the DB just as easy as a similar query can happen using the DB CLI directly. We can issue a PHP query to change every post in the DB just as easy as we can do it with a CLI or Rails or phpmyadmin or shell or Javascript or … any DB query client. It is possible to destroy an entire DB with a GUI client like phpmyadmin, if you have GRANT permissions. Even thought I am not an expert in Rails, I would guess you can drop an entire DB with Rails in perhaps single command with GRANT permissions.

After you posted, curious as to why some here like to say this “Rails saves you from yourself” I have been reading here, I tried to further educate myself on this, and so curious I happily spent about 15 minute searching the net for things like “is rails safer than CLI for DB queries”, “rails v cli” etc. and frankly, I did not find a single discussion where Rails was promoted as the CLI to save yourself from a bad SQL statement if you have GRANT permissions.

My closing thoughts is that anyone can do damage with DB queries, with a CLI, Rails, Javascript, PHP or whatever client the are using if they do not know what you are doing. Heck Fire, Jethro! It is possible any admin can do serious damage even if they know what they are doing if not careful (fat finger, an unexpected kiss from their girlfriend while making a query, etc ).

Rails, PHP, GUI clients, none of these query clients will save you from that if you have GRANT permissions to access the DB. The only thing which will save anyone from a serious mistake in the DB is a recent, working backup.

Of course, by all means use Rails instead of direct CLI if you like. I am sure Rails is the cat’s meow, the best thing on Earth since the discovery of buttered bread and french fries. Use the tools you like and are comfortable with, for sure. Stay in your comfort zone.

As an example of my comfort zone:

Yesterday, I ran a this CLI query to fix a markdown issue (after testing it on a small sample completely first) to add a hard line break after a closing LIST BBCODE tag ; because without it, this BBCODE would not work with markdown (without the hard line break, markdown has a lot of these types of migration gotchas). I did not want to rewrite a BBCODE plugin for this.

Worked perfectly. Of course, I tested first before running this against one million posts and had a full backup before doing so.

After that query, I ran:

rake posts:rebake_match["[/LIST]"]  

and problem solved (we also fixed this in another instance using Ruby and gsub in preprocessing).

It’s all the same (for a sys admin who knows his salt) …

Sometimes is is good to use the CLI directly, sometimes it is good to use a cool rake task. Sometimes Rails is da bomb. It’s all good.

What is important with DB UPDATES in my view, is to always test your query with SELECT first. Then, before doing a huge UPDATE, tests on one or two rows in the table only. Make sure you always have a recent working backup when making DB changes and even if you drop the entire DB by mistake, it is simple to recover.

What separates the expert from the novice is that the expert always makes a solid backup first :slight_smile:

My view has always been never get “religious” about any single technical approach or tool. Tools are tools, not rules. Use the best tool for the task at hand, that is how I do tech - “the zen tech approach”. Fun, easy, no “tech religion”.


A trivial example is that if you use User.find(123).destroy! you will also delete the UserCustomFields associated with that user. If you delete the user from the user table with SQL, you’ll leave behind those unassociated UserCustomFields. This is also true of tables with their email addresses and profile info, just to name two more tables that I can think of offhand.

Rails does a bunch of magic stuff like that.

But sure, you can still do something that will, say, delete all the users instead of just one.

1 Like