Thanks.
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
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”.
Cheers.