DML Changes directly in PostgreSQL for Ruby on Rails App

Question

What are the risks of performing DML changes directly to a PostgreSQL db (using SQL scripts or even pgAdmin) created by a Rails App? Particularly using UPSERT or MERGE to keep data from a 3rd party up-to-date.

Does this have the potential to break the app?

As a bonus question, does anyone know if Heroku will give you access to manipulate your production db (without using "rake db:migrate")?

Answer 1

The possible risks I can think off: if you have a lot of validations in your model, which are not backed by constrains in the database, it would be possible to insert data your rails models will not accept. Now what does that mean in the worst case:

• your rails models will always be able to retrieve the data, but maybe if content is not as expected, this could cause exceptions (remember: worst case). E.g. a validate which checks if a certain number is in some range, and then using that number to index an array, which could cause a nil exception.
• there is no validation when retrieving, but it could be possible when saving, since the data did not pass the validation in the first place, the data cannot be saved. This would be extremely confusing for the user (the data is in the database, was saved, she saves something completely unrelated, and suddenly the data no longer saves).

Those are the risks. In general there is no problem at all inserting data directly, as long as you make sure the data is consistent :)

Note that rails is even smart enough to handle DDL changes. You are not forced to used migrations. But it helps tremendously :)

Heroku offers the ability to run a rails console remotely. You can use that to change data.