CODEWITHSUNDEEP
ruby-on-railspostgresqlherokuheroku-postgres
Ninja Boy
Ninja Boy

Reputation: 1170

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.

Upvotes: 47

Views: 31345

Answers (9)

Ninja Boy
Ninja Boy

Reputation: 1170

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

Upvotes: 1

Hatim Alattas
Hatim Alattas

Reputation: 61

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

Upvotes: 5

Irfan wani
Irfan wani

Reputation: 5075

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.

Upvotes: 0

0bserver07
0bserver07

Reputation: 3471

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=

Upvotes: 68

Alex T
Alex T

Reputation: 1

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

Upvotes: 0

Andrei Erdoss
Andrei Erdoss

Reputation: 1643

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

Upvotes: 2

andrewjt
andrewjt

Reputation: 241

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>

Upvotes: 23

Tan Duong
Tan Duong

Reputation: 1561

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.

Upvotes: 6

Simone Carletti
Simone Carletti

Reputation: 176372

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.

Upvotes: 4

Related Questions