47

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.

Improve this question
4
  • my bad, typo during editing. there was an '=' in the command btw, but it didn't work Commented Jan 28, 2016 at 12:56
  • Please post the entire output of the error message. The heroku config:add VAR="VAL" should definitely work. Commented Jan 28, 2016 at 12:59
  • Setting config vars and restarting app-name... done VAR: VAL Commented Jan 28, 2016 at 13:00
  • I meant your error message, don't try my example, that's just to show what the syntax should look like. Anyway, the Heroku documentation is at devcenter.heroku.com/articles/config-vars Commented Jan 28, 2016 at 13:03

9 Answers 9

Reset to default
68

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=
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

+1 Had to detach the current database before setting DATABASE_URL. Did this when migrating from Heroku Postgres to RDS on AWS.
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.
in heroku review apps you may get an error "unable to detach ..." when trying to detach original DB. The way to solve it is to delete it first, then just set DATABASE_URL
I had to do heroku config:remove DATABASE_URL then heroku config:add DATABASE_URL="blahblahblah" WITH THE QUOTE MARKS for it to work.
23

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>
Improve this answer

3 Comments

I had to use this version instead of the detach then add config.
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.
But what if you want to migrate to a different hosted database, like Azure postgres or AWS?
6

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.

Improve this answer

Comments

5

SQLAlchemy 1.4.x has removed support for the postgres:// URI scheme, which is used by Heroku Postgres (https://github.com/sqlalchemy/sqlalchemy/issues/6083). To maintain compatibility, perform the following before setting up connections with SQLAlchemy:

import os
import re

uri = os.getenv("DATABASE_URL")  # or other relevant config var
if uri.startswith("postgres://"):
    uri = uri.replace("postgres://", "postgresql://", 1)
# rest of connection code using the connection string `uri`

This will allow you to connect to Heroku Postgres services using SQLAlchemy >= 1.4.x

Improve this answer

3 Comments

This question is specifically about Ruby, so a Python answer is not useful.
It was useful for me
Same. I needed a python solution after I found out the this is maintained by Heroku
4

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.

Improve this answer

4 Comments

Hey, throws me an error Cannot overwrite attachment values DATABASE_URL.
Heroku depreciated the update database_url is what some stackoverflow posts say.
Thanks. I've updated the answer, in case someone else will stumble upon this thread.
Thanks. I've added an answer with the workaround which worked for me. Feel free to share it.
2

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
Improve this answer

Comments

1

Solved it. Just for the reference of the users who have the same issue or want to have a similar implementation. Here's the workaround which worked for me.

Heroku no more overwrites databse.yml, so I just modified the DATBASE_URL in the database.yml and pushed it :)

It worked too!

Source

Improve this answer

5 Comments

The link no more works, can you give us an example of database.yml and where to depose this file ?
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
So you pushed your database.yml into source control?
Yes @AnthonyTo. I did.
You could always put the actual url into an environment variable and push to Heroku using something like the figaro gem.
0

In my case, I needed to launch an java spring boot application with my personal data base (postgres). I have an instance on AWS, and when loading the app, an error was occurring because it would connect without ssl.

Considering this documentation (https://devcenter.heroku.com/articles/connecting-to-relational-databases-on-heroku-with-java#using-ssl-with-postgresql), it says:

We used to suggest adding the URL parameter sslmode=disable to JDBC URLs. We now require use of SSL for all new Heroku Postgres databases. We will be enforcing use of SSL on all Heroku Postgres databases from March 2018. Please do not disable SSL for your database or your applications may break.

So, resuming, step 1, I deleted my addon Heroku Postgres on Resources tab. Step 2, I changed my application.yml from:

datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://<url>:<port>/<dataBaseName>?createDatabaseIfNotExist=true&useSSL=false
    username: <user>
    password: <pass>

to

datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://<url>:<port>/<dataBaseName>?createDatabaseIfNotExist=true&useSSL=false&sslmode=disable
    username: <user>
    password: <pass>

I added "&sslmode=disable" at the end of url string.

And finally, rebuild/deploy (which in my case is automatic after pushing into my repo on github).

I hope this would help someone.

Peace...

Improve this answer

Comments

0

One way to edit the DATABASE_URL will be to create another app and add the heroku_postgres add-on there and then grab the url of that database and use that in your main app by configuring the environment variables and set the value of DATABASE_URL to that url of the database.

Now you can easily change the DATABASE_URL as that is not attached with the app.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.