Access Update stagingcoyote and livecoyote Databases

Access Update stagingcoyote and livecoyote Databases.md

With Heroku pg:psql

Details here: https://devcenter.heroku.com/articles/heroku-postgresql#pg-psql

This is the process to update Scavenger Hunt Location Colors on staging and production:

heroku pg:info -—app livecoyote (or stagingcoyote) to get the identifying characteristics of each (such as database size, status, number of tables, and PG version).

heroku pg:psql --app livecoyote (or stagingcoyote) to establish a psql session with your remote database. 

• psql is the native PostgreSQL interactive terminal and is used to execute queries and issue commands to the connected database.

This is what you should see once you establish a psql session:

heroku pg:psql --app stagingcoyote
--> Connecting to postgresql-objective-32850
psql (10.3, server 10.4 (Ubuntu 10.4-2.pgdg14.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

stagingcoyote::DATABASE=> enter your SQL queries here 

To see all organizations:

stagingcoyote::DATABASE=> SELECT * FROM organizations;
 id |        title        |         created_at         |         updated_at         
----+---------------------+----------------------------+----------------------------
  1 | MCA Chicago         | 2018-07-24 15:11:12.772039 | 2018-07-24 15:11:12.772039
  3 | Smart Museum of Art | 2018-09-28 17:38:58.558372 | 2018-09-28 17:38:58.558372

To see all Scavenger Hunt locations:

stagingcoyote::DATABASE=> SELECT * FROM scavenger_hunt_locations;
 id | organization_id | position |  tint   
----+-----------------+----------+---------
  1 |               1 |        0 | #ff00ff

To update a table use this SQL update syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE scavenger_hunt_locations SET tint='#0000FF' WHERE id=1;

Double check the updates:

SELECT tint FROM scavenger_hunt_locations WHERE id = 1;

---

With Heroku Shell + Rails Console

To test out your changes, run: heroku run rails c --sandbox --app stagingcoyote

• If you ever want to test out different code, queries, or manipulate data without permanently writing to the database use the sandbox option.
• This will give you a Rails console where you can test out your code and manipulate data in realtime to see what the effects are. Once you exit the console, all changes are rolledback so it's a really safe way to play with production data before you manipulate anything permanently.
• Notes about sandbox from a The Practical Dev article:

Yes, --sandbox is very handy, but I cringe every time I see someone use it on a production system. Sandbox mode doesn't just rollback, it creates an enclosing transaction for all of the commands you do while in that instance of the console. If you change database rows in that transaction, updates to the tables and rows affected can block for other users. This can cause lockups for other users if used in production. Any rails console actions on production should be done carefully, and should (IMHO) never use --sandbox. Sandbox mode is really useful for development and other non-critical systems.

After you confirm the changes on staging/sandbox, heroku run rails c - --app your_app