How to install PostgreSQL for Mac for Node

07 Sep 2018

Installing PostgreSQL for Mac is very simple when you use Postgres.app.

  1. Download the screencast.
  2. Download Postgres.app.
  3. Install on your Mac by double clicking the dmg file.
  4. Make sure to drag the icon to Applications.
  5. Open /Applications/Postgres.app or use finder.
  6. Click Initialize

Now you have a PostgreSQL database server running on your Mac. As of September 2018, this will install PostgreSQL version 10.5.

Running version() is how you tell which version of PostgreSQL server you are running.

/Applications/Postgres.app/Contents/Versions/latest/bin/psql -c 'select version()'

Creating a PostgreSQL database for Node apps

To create a postgres database we need to have the PostgreSQL command line tools in our path. The way Postgres.app recommends to add the latest bin directory to all users on your Mac. I personally do not like this method. But if you want all users to have to have the command line tools in your path then it is good.

sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp

My personal preference is to add the latest symlink directory to my path in my zsh dotfiles. If you use bash then your bashrc file works great.

PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin

Now you should have the command to create a database on your freshly installed PostgreSQL server.

The latest symlink directory links to the 10.5 directory. Currently Postgres.app is shipping with PostgreSQL server 10, 9.5, and 9.6.

Now that we have the command line tools in our path we can try createdb node_example.

Connecting to Postgres.app database for Node Applications

Now that we have a our PostgreSQL database server running and an example database running we can connect and issue queries.

Postgres.app configures our PostgreSQL server to run on the standard port of 5432, your mac username, and no password.

After unzipping the project contents for this example, run npm install to install the required dependencies.

'use strict';
const { Client } = require('pg');

let connectionString = 'postgresql://localhost/node_example';

const client = new Client({
  connectionString: connectionString
});

client.connect();

async function run() {
  let createUsersTable = `
    create table if not exists users(
      email varchar,
      id serial PRIMARY KEY
    )`
  try {
    await client.query(createUsersTable);
  } catch (e) {
    console.error('problem creating users table', e);
    process.exit(1);
  }

  let insertNewUser = `insert into users values ($1) returning id`;

  try {
    let {rows} = await client.query(insertNewUser, [`${new Date()/1000}@gmail.com`]);
    console.log('created new user with id', rows[0].id);
  } catch (e) {
    console.error('problem inserting new user', e);
    process.exit(1);
  }

  let findAllUsers = `select * from users`;

  try {
    let {rows: users} = await client.query(findAllUsers);
    console.log(users);
  } catch (e) {
    console.error('problem finding all users', e);
    process.exit(1);
  }
  client.end();
}


run();

This is a simple example that connects to the database, creates the users table if not exists, inserts a new user, and finds them all.

This code takes advantage of my new favorite feature of JavaScript async/await. I've got a fun async await express passport post if you are interested in more async await.