Installing PostgreSQL for Mac is very simple when you use Postgres.app.
Please download the screencast and example code to follow along.
- Download Postgres.app.
- Install on your Mac by double clicking the dmg file.
- Make sure to drag the icon to Applications.
- Open
/Applications/Postgres.app
or use finder. - 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.