How to change DATABASE_URL for a heroku application

Question

I wanted to use an external Database with my heroku application. But I'm unable to edit the configuration cariables. I tried using GUI, Which says, Cannot overwrite attachment values DATABASE_URL. While I tried using CLI as well. I used the command: heroku config:addDATABASE_URL="postgresql://username:password@IP:PORT". However, this throws an error ... is not a heroku command.

Solution 1

After trying out most these answers, I came across an update in 2016, here: the database needs to be detached first, then update the variable of the DATABASE_URL.

heroku addons:attach heroku-postgresql -a <app_name> --as HEROKU_DATABASE
heroku addons:detach DATABASE -a <app_name>
heroku config:add DATABASE_URL=

Solution 2

An alternative method which does not require detaching (which may not be a desired outcome of the switch) is to simply attach the new database and then promote it, which the Heroku Documents explicitly states as a way to set the DATABASE_URL.

heroku addons:attach heroku-postgresql -a <app_name>
heroku pg:promote heroku-postgresql -a <app_name>

Solution 3

As explained in this article, the correct syntax to set/add a configuration variable is

$ heroku config:set DATABASE_URL="postgresql://username:password@IP:PORT"

However, it looks like (see the comments) the DATABASE_URL has been deprecated and trying to update it will trigger an error.

Solution 4

I got the very same situation today when I need to change postgres to postgis. Detach doesn't work for me so I done this to database.yml:

production:

  url: <%= ENV['DATABASE_URL'].sub(/^postgres/, "postgis") %>

https://github.com/rgeo/activerecord-postgis-adapter/issues/214.

Solution 5

Based on the Heroku docs this is how you would share a database with multiple apps.

heroku addons:attach my-originating-app::DATABASE --app sushi

View more solutions

Comments

[Ninja Boy]

Hey, throws me an error Cannot overwrite attachment values DATABASE_URL.

[Ninja Boy]

Heroku depreciated the update database_url is what some stackoverflow posts say.

[Simone Carletti]

Thanks. I've updated the answer, in case someone else will stumble upon this thread.

[Ninja Boy]

Thanks. I've added an answer with the workaround which worked for me. Feel free to share it.

[jcstritt]

The link no more works, can you give us an example of database.yml and where to depose this file ?

[Ninja Boy]

Just go to heroku dashboard > databases > delete the database; Come back to config/database.yml enter the details of the database. Like this gist.github.com/pbssubhash/705dfb538aea75ee9ab3 However, it's not advised to hardcode the values, until we find a better one you can follow this. Comment here if you still have issues, I'll be happy to help @jcstritt

[Anthony To]

So you pushed your database.yml into source control?

[Ninja Boy]

Yes @AnthonyTo. I did.

[Pattay]

+1 Had to detach the current database before setting DATABASE_URL. Did this when migrating from Heroku Postgres to RDS on AWS.

[port5432]

You could always put the actual url into an environment variable and push to Heroku using something like the figaro gem.

[Matt]

I had to use this version instead of the detach then add config.

[Matt]

I ran into an error "Cannot destroy last attachment to billing app for resource" using this method and had to destroy my Heroku database before it detached (see: stackoverflow.com/questions/30611801/…). On the plus side, you can create a new Heroku db, promote it as listed below, and then destroy it to also release this hold. After that, you can set an external URL (e.g. RDS) with the config:add or config:set.

[AfromanJ]

This worked for me. Note that you need to replace heroku-postgresql with the name of the new database you want to attach to. This can be found on the overview page for your app under Heroku Postgres.

[snakecharmerb]

This question is specifically about Ruby, so a Python answer is not useful.

[ToonAlfrink]

It was useful for me