44

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.

Ninja Boy
  • 1,112
  • 3
  • 14
  • 28
  • my bad, typo during editing. there was an '=' in the command btw, but it didn't work – Ninja Boy Jan 28 '16 at 12:56
  • Please post the entire output of the error message. The `heroku config:add VAR="VAL"` should definitely work. – eugen Jan 28 '16 at 12:59
  • Setting config vars and restarting *app-name*... done VAR: VAL – Ninja Boy Jan 28 '16 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 https://devcenter.heroku.com/articles/config-vars – eugen Jan 28 '16 at 13:03

9 Answers9

64

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=
Ronan Boiteau
  • 9,608
  • 6
  • 34
  • 56
0bserver07
  • 3,390
  • 1
  • 28
  • 56
  • 7
    +1 Had to detach the current database before setting `DATABASE_URL`. Did this when migrating from Heroku Postgres to RDS on AWS. – Pattay Jul 27 '16 at 00:28
  • 7
    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: https://stackoverflow.com/questions/30611801/how-to-change-database-url-on-heroku). 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. – Matt Sep 19 '18 at 22:40
  • 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 – equivalent8 Mar 30 '23 at 10:55
22

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>
andrewjt
  • 231
  • 2
  • 7
  • I had to use this version instead of the detach then add config. – Matt Sep 19 '18 at 19:10
  • 5
    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*. – AfromanJ Jan 10 '19 at 14:05
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.

Tan Duong
  • 1,473
  • 2
  • 17
  • 29
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

Hatim Alattas
  • 61
  • 1
  • 1
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.

Simone Carletti
  • 173,507
  • 49
  • 363
  • 364
2

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

Ethan
  • 876
  • 8
  • 18
  • 34
Ninja Boy
  • 1,112
  • 3
  • 14
  • 28
  • 1
    The link no more works, can you give us an example of database.yml and where to depose this file ? – jcstritt Mar 20 '16 at 16:10
  • 1
    Just go to heroku dashboard > databases > delete the database; Come back to config/database.yml enter the details of the database. Like this https://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 – Ninja Boy Mar 22 '16 at 14:01
  • 1
    So you pushed your database.yml into source control? – Anthony To Apr 23 '16 at 23:43
  • Yes @AnthonyTo. I did. – Ninja Boy Apr 24 '16 at 02:14
  • You could always put the actual url into an environment variable and push to Heroku using something like the figaro gem. – port5432 Jun 10 '17 at 17:04
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
Andrei Erdoss
  • 1,573
  • 1
  • 13
  • 14
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...

Alex T
  • 1
  • 2
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.

Irfan wani
  • 4,084
  • 2
  • 19
  • 34