How to view tables on a PostgreSQL database hosted on Heroku

5 min read

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](https://devcenter.heroku.com/articles/heroku-cli).
  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](https://www.pgadmin.org/) 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.

Discussion (0)

Please sign in to join the discussion.

No comments yet. Be the first to share your thoughts!