'How to change DATABASE_URL for a heroku application

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.

ruby-on-railspostgresqlherokuheroku-postgres


Solution 1:[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:[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:[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:[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:[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

Solution 6:[6]

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

Solution 7:[7]

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

Solution 8:[8]

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...

Solution 9:[9]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ronan Boiteau
Solution 2 andrewjt
Solution 3
Solution 4 Tan Duong
Solution 5 Andrei Erdoss
Solution 6 Hatim Alattas
Solution 7 Ethan
Solution 8
Solution 9 Irfan wani

Related Questions