Category: PostgreSQL

Setting up a Free Postgres Database in Heroku w/ PgAdmin

Step 1: Log into Heroku

Step 2: Create a New App

Create a new Heroku app by either clicking “Create a new app” in the center of the screen or “New” at the top right of the screen.

Choose an App Name and the Region (either the US or Europe) for your new app. The name can be anything you want. Then, just click the Create app button.

Step 3: Add a PostgreSQL Database

To attach a PostgreSQL database to the blank app you just made, you need to go to the Resources tab in the header of the new app’s dahsboard. Then type Heroku Postgres into the Add-ons search field. When shown, select the suggested Heroku Postgres add-on from the dropdown:

The next popup asks you to choose a pricing plan for the database. Select the Hobby Dev – Free plan and click Provision.

Now your PostgreSQL database is up !

Step 4: Get Heroku DB Information

To find the credentials and the connection URL for the PostgreSQL database, make sure you’re still in the Resources tab and click on the Heroku Postgres resource we just added.

That should bring you to this screen:

Select the Settings tab in the header of that screen. You will be navigated to a page where Here, you can click the View Credentials button to see the credentials of your PostgreSQL database.

You will need these values to input into your PgAdmin interface

Step 5: Configure PgAdmin w/ Heroku DB Credentials

Open up PgAdmin

Right-click on Servers at the very top of the left-side panel

Choose Create > Serve

Fill out the new server form with all of the corresponding information from the Heroku View Credentials page.

After you save the new server, it will become populated with a huge list of databases. You will not have access to any of them except the one with your Database name from Heroku. Scroll through and find this one.

Then, as you would with any other PgAdmin database, go to Schemas > public > Create new Table. Populate this table with the fields you want.

You’re all set.

Sequelize with PostgreSQL with Node

Import the Sequelize module into your Node project

npm i sequelize

Add the specific database driver to coincides with the SQL DBMS that you are using:

npm i --save pg pg-hstore

Connect to the database in your Node application

Option 1: Passing a connection URI

const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname') 

module.exports = sequelize;

Option 2: Passing parameters separately (other dialects)

const sequelize = new Sequelize('database', 'username', 'password', {    
   host: 'localhost', 
   dialect: 'postgres'
});

module.exports = sequelize;

Test the connection

try { 
   await sequelize.authenticate(); 
   console.log('Connection has been established successfully.'); 
} catch (error) { 
   console.error('Unable to connect to the database:', error); 
}

Create a new model

The db variable is imported from the Sequelize connection above (Opt 1 or 2) which we are assuming is in a separate file. 

const Sequelize = require('sequelize');
const db = require('../config/database');

const Person = db.define('gig', {
   name: {
      type: Sequelize.STRING
   },
   age: {
      type: Sequelize.INTEGER
   },
   description: {
      type: Sequelize.STRING
   }
});

module.exports = Person;

Start making Sequelize queries in server routes

The beauty of Sequelize is that it allows you to use object-oriented syntax to make queries to relational/SQL databases.

app.get('/', (req, res) =>
   Person.findAll()
      .then(people => console.log(people))
      .catch(err => console.log(err)));