Looking at the database on heroku

Question

I am attempting to take a look at the data on my heroku instance. I'd like to be able to view the database on their server..

I don't want to pull it down to my local system.. More to the point I don't want wait that long, to take a look at the data.

Lets assume I push my current database of 8 million books(and all the meta data associated) up to heroku. Then in two months I add another 12 million. (I'm still in development mode) If I have a problem with one batch of data, I don't think it's feasible to pull down the whole database.

I suppose I could do crunches while I wait on the download of the gigs of data. I hear that's what old programmers did during compiles.

---

I've looked at Viewing database in Heroku and it's just a "Can't do it."
Is that true? We can't look at the live data?

Answer 1

You can use JackDB (http://www.jackdb.com).

JackDB is a database client in your web browser that has OAuth integration with Heroku. It lets you list your Heroku apps and their respective datasources and then connect to them to run SQL queries. See the docs for details of how to use it with Heroku.

Here's what it looks like:

---

There's also the JackDB Heroku plugin for the Heroku CLI. The plugin let's you run heroku jackdb from your command line to connect to your default database. There are additional options if you have more than one database and want to explicitly select which one you'd like to connect to.

To install it:

$ heroku plugins:install https://github.com/jackdb/jackdb-heroku-plugin.git

To connect to your default database:

$ heroku jackdb

---

Full disclosure: I'm the founder of JackDB.

Answer 2

From https://devcenter.heroku.com/articles/heroku-postgresql

Heroku Postgres can be attached to a Heroku application via the CLI: $ heroku addons:add heroku-postgresql:dev

To establish a psql session with your remote database use heroku pg:psql.

Answer 3

Try

heroku pg:psql

You'll get to know next steps automatically

Answer 4

(untested but seem it is what you are looking for.. )

https://github.com/ddollar/heroku-sql-console

Answer 5

To view a database directly from Heroku, I just create a 'Dataclip' of the entire database, at https://dataclips.heroku.com/ . You can alternatively get there from the postgreSQL app. From this page, do a SQL query to view the desired data.

If you are unsure which tables are in the database, view them with:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

Answer 6

Depends on what you mean by "view the database". If you're looking for a bona fide SQL console, you're probably out of luck; however, there's always heroku console which gets you access to an IRB session attached to your app and its Heroku database. From there, you can use the usual commands like Book.where("title = ?", @title) or Book.count or whatever you need.

Answer 7

Well, this isn't a perfect answer, but the best way to get in is with the console IMO.

To make that a little easier I've put a class method on some of my models to print them. One example from my app is:

class User

  def self.list
    puts "ID. Name  -  Email\n"
    self.all.each do |user|
      puts "#{user.id}.  #{user.name}  -  #{user.email}\n"
    end
  end

end

In console you can just call User.list to get the list.

Now this method is not very well optimized, and it would be better if it was written so it could be chained to the end of a query, but you get the basic idea.

It's a little bit cludgy but making a display helper method like this is probably the quickest way to introspect your data on Heroku.

Otherwise my advice would be contact them and ask for recommendations, in my experience the Heroku staff are very responsive and helpful with such requests.

I hope this gives you an idea or two.

Answer 8

You can't connect directly to Shared Heroku instances - if you use a dedicated Postgres Heroku addon then you are able to connect directly with PG tools from your local system. However, there is a significant cost jump to do this.

Answer 9

I know this is an old question but thought I'd answer in case anyone was interested. I have been using PG Commander to view my databases both locally and on heroku. It's not free (£28 at the moment) but it is very good.

Go to your Heroku databases and all of the login details will be there.

Answer 10

Try DBHawk from Datasparc. We asked Datasparc to create an online demo for us and it worked.