Working with Node.js and Postgres in Heroku

Once you have your project configured with Postgres in your local environment, it is time to host your server. I usually work with Heroku, hence why the post will outline the steps with that tool:

1. You have to first deploy your server to Heroku.
2. In the Resources tab search for Heroku Postgres add on. They have a free plan, this is the one that I have used so far. You can checkout their plans here.
3. In Settings > Config Vars you will see a new env variable: DATABASE_URL. That is the one you need to reference in your server code. If you are using Knex, your knexfile.js would have something similar to:

    production: {
        client: 'pg',
        connection: process.env.DATABASE_URL,
        migrations: {
            directory: './db/migrations/dev',
        },
        seeds: {
            directory: './db/seeds/dev',
        },
        useNullAsDefault: true,
    },

1. You run the migrations that you wrote in your code with npx heroku run knex migrate:latest --app NAME_OF_YOUR_APP
2. If you need to run the seeds, you type npx heroku run knex seed:run --app NAME_OF_YOUR_APP

How to see the data saved in your production database?

I found 2 options:

1. data.heroku.com, and selecting your database from the list.

2. You connect to the Heroku's psgl (PostgreSQL interactive terminal) typing heroku pg:psql --app NAME_OF_YOUR_APP. You have to be logged in to Heroku's CLI before, you can do so by typing heroku login. Check this article on how to get it installed, etc. You will also need psql in your computer, you can get started here.

Once you are connected to the psql, you can run psql commands like \dt (displays available tables in the DB) or run SQL queries, like select * from NAME_OF_TABLE;, delete from NAME_OF_TABLE where WHATEVER = WHATEVER;

Please leave a comment if something is wrong, if I am missing anything important or you want to elaborate.

Edit April 11th 2021

When running npx heroku run knex migrate:latest --app NAME_OF_YOUR_APP I got message error: no pg_hba.conf entry for host "{{there is an ip address }}", user "{{long string of characters}}", database "{{long id string}}", SSL off. Hmm, I did a bit of researched and I found that All Heroku Postgres production databases require using SSL connections to ensure that communications between applications and the database remain secure. If your client is not using SSL to connect to your database, you would see these errors even if you're using the right credentials to connect to it.. This brought me here which brought me here. For now I have ommited the sslconfiguration and run heroku config:set PGSSLMODE=no-verify -a NAME_OF_YOUR_APP. Not recommended but I still need to research how to secure it with SSL - more to come!