Search code examples
sqlpostgresqlherokuheroku-postgres

How to manually update a record in Heroku Postgres database?

I have a project on Heroku. I have installed Heroku Postgres. I have a users table, and in it the role field, if the value is 1, then the user has more powers, if 0, then they are limited. The default is 0, now I want to update this field for a specific user, what should I do? And is there any phpMyAdmin analog for Heroku Postgres?

I tried heroku pg:sql in console and wrote queries like UPDATE users SET role = "1" WHERE id = "1", but nothing happened.

Solution

  • You can connect your heroku-postgresql to a SQL Editor using the credentials provided by the Heroku in the postgres dashboard. After connecting to the SQL Editor you can update records in your database.

    Where heroku-postgresql credentials can be found?

    • Login to your Heroku account and click on the menu on the left of your profile.
    • After that choose Data option from the dropdown menu.
    • In a new window you will see your database running in the Heroku account.
    • Choose the data resource you want to connect.
    • Now go to the settings and there you will see the option view credentials.
    • Click on view credentials and get your database credentials to connect it to a SQL Editor

    Please note that these credentials are not permanent. Heroku rotates credentials periodically and updates applications where this database is attached. But you have enough time to update your changes.

    How to connect heroku-postgresql to a SQL Editor?

    I personally use PopSQL to remotely work with databases. You can use it for free**

    • In this case, I am using PopSQL
    • Open your app and click on connect to the database. There you can choose PostgresSQL from the list.
    • Fill important fields like hostname, database, username, password etc.
    • Click on Test. It will show you output like Looks Good and then you can connect to your database. If any error occurred then you can see it in the output.

    If you are using Private or Shield Heroku Postgres Database then you have to whitelist the IP address from which you are accessing the resources. Read more here

    To know about analog to phpMyAdmin for PostgresSQL check this answer on Stackoverflow