Patrick

writing, drawing, surfing, painting

Express Sequelize Heroku Postgres Configuration Success

Posted on Author theptrkPosted in express

You are in flow developing on your local database and you decide to upload everything to heroku and share it with the world.

How do you configure your production database?

It’s easier than you think. Let’s assume you have already set up your site.

If you used the sequelize init command, you will have a file db/models/index.js that looks like this

Usually, your sequelize database will initialize with the usual database, username password, etc but in the special case that your config has use_env_variable, sequelize will recognize that an environment variable (usually representing a URI) can be used in place of the usual credentials.

What is your env/config variable?

Find your heroku config variables in the heroku website or by running $ heroku config. Note, config and env variables are used to reference the same settings here.

When you create a database on your heroku account, it will default a new configuration setting called “DATABASE_URL”

You can access this with process.env.DATABASE_URL in any of your node.js code (on production). Your local machine will have different environment variables set.

Step 1: Set use_env_variable

But set it to what? Set it what heroku designates as your database url, DATABASE_URL!

Thats it!

You may need to migrate your database if you aren’t using sync

Step 2: Migrate your database

Simply run the migrate command in the heroku environment with heroku run. This avoids any configuration errors of running from your local environment

$ heroku run ./node_modules/.bin/sequelize db:migrate --debug

Final config

Step 3: Check your database

Run the heroku psql command and run the “list tables” command.

$ heroku psql

$<app_name>::DATABASE=> \dt

Possible Errors

Error: no pg_hba.conf entry for host…, SSL OFF

Add the ssl setting into dialectOptions of your config.json

Error: The server does not support SSL connections

This may be an outdated error. But previously, users who tried to migrate on their local computer would run into this error.

$ NODE_ENV=production DATABASEURL=$(heroku config:get DATABASE_URL) ./node_modules/.bin/sequelize db:migrate

This will not work correctly unless you set up your local computer to support ssl in the migration. You could get a certificate and configure your sequelize config to use that BUT theres an easier way.

ERROR: SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:5432

It’s quite possible you forgot to push your code to heroku. Make sure you git push heroku master!!!