Working with Node.js and Postgres in Heroku
Published: 2020-07-29 23:06:10

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](https://devcenter.heroku.com/articles/heroku-postgres-plans).
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,
    },
```

4. You run the migrations that you wrote in your code with `npx heroku run knex migrate:latest --app NAME_OF_YOUR_APP`
5. 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](https://devcenter.heroku.com/articles/heroku-cli) on how to get it installed, etc. You will also need `psql` in your computer, you can get started [here](https://devcenter.heroku.com/articles/heroku-postgresql#local-setup).

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](https://help.heroku.com/DR0TTWWD/seeing-fatal-no-pg_hba-conf-entry-errors-in-postgres#:~:text=Resolution-,%22FATAL%3A%20no%20pg_hba.,connection%20couldn't%20be%20established.&text=All%20Heroku%20Postgres%20production%20databases,and%20the%20database%20remain%20secure.) 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](https://devcenter.heroku.com/articles/heroku-postgresql#heroku-postgres-ssl) which brought me [here](https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-node-js). For now I have ommited the `ssl`configuration 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!