How to view tables on a PostgreSQL database hosted on Heroku

To view tables on a PostgreSQL database hosted on Heroku, you can use either the Heroku CLI or a database client. Here’s how to do it with both methods:

Method 1: Using Heroku CLI and psql

1. Install the Heroku CLI (if not already installed) by following the instructions on Heroku's website.
2. Log in to Heroku:

   heroku login

1. Access the PostgreSQL Database:

Use the following command to connect to your database:

   heroku pg:psql -a <your-app-name>

Replace <your-app-name> with the name of your Heroku application.

1. List Tables:

Once connected to the database, you can list all tables with:

   \dt

This command will display all the tables in the current schema.

1. Exit psql:

When you're done, exit the psql interface by typing:

   \q

Method 2: Using a Database Client (e.g., pgAdmin, DataGrip)

1. Retrieve Database Credentials:

Run the following command to get your database URL:

   heroku config:get DATABASE_URL -a <your-app-name>

This will display a URL in the format: postgres://<user>:<password>@<host>:<port>/<database>

1. Connect with a Client:

Open your database client, such as pgAdmin or DataGrip, and enter the details from the DATABASE_URL:

• Host: Found in the URL after @
• Database: Found after the last /
• User and Password: Found between // and @
• Port: Default is 5432 (or whatever is in the URL)

1. List Tables:

After connecting, you can explore the database tables in the client’s interface. Most clients allow you to view tables directly and run SQL queries to list tables with:

   SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Using either method will allow you to view and interact with your PostgreSQL tables on Heroku.