Bootcamp
Search…
3.4.2: PostgreSQL Node App

Database Setup Outside of Application

Our applications will not set up the DB or the tables. DBs and tables need to be created before our applications run. The code in this module assumes that we have set up Postgres as per Module 3.4, which should create a Postgres user and DB named after the current Unix username. We can retrieve the current Unix username with the whoami command in Terminal.
When running the below code, replace <MY_UNIX_USERNAME> with your Unix username.

pg NPM Library

To use Postgres in Node, we need to install the Postgres client library for node, pg. This enables our application to connect with our database.
npm install pg

DB Queries in Node

SELECT

The following Node application runs a SQL query on our cats table. What does this code do?

index.js

import pg from 'pg';
const { Client } = pg;
​
// set the way we will connect to the server
const pgConnectionConfigs = {
user: '<MY_UNIX_USERNAME>',
host: 'localhost',
database: '<MY_UNIX_USERNAME>',
port: 5432, // Postgres server always runs on this port
};
​
// create the var we'll use
const client = new Client(pgConnectionConfigs);
​
// make the connection to the server
client.connect();
​
// create the query done callback
const whenQueryDone = (error, result) => {
// this error is anything that goes wrong with the query
if (error) {
console.log('error', error);
} else {
// rows key has the data
console.log(result.rows);
}
​
// close the connection
client.end();
};
​
// write the SQL query
const sqlQuery = 'SELECT * from cats';
​
// run the SQL query
client.query(sqlQuery, whenQueryDone);
client.end() terminates the connection with the SQL client, which is necessary for our Node script to exit. If we do not terminate the SQL connection, the Node script will hang until the connection is terminated.

Command Line

node index.js

INSERT

When we replace the SQL query in index.js with the following, what does this do?
const sqlQuery =
"INSERT INTO cats (name, type, weight) VALUES ('Mr. Snuggles', 'Calico', 327)";

INSERT with value params

We can give structured data to the query. The syntax is slightly different.
Confusingly, SQL syntax here uses an array to pass in the data: ['brianc', '[email protected]'] then references those array values in the query, INSERT INTO users(name, email) VALUES($1, $2) but the value $1 references array index 0. This is because SQL is a 1-indexed language, and most other programming languages including JS are 0-indexed.
const inputData = ['Mr. Snuggles', 'Calico', 327];
​
// in this example, $1 is going to be replaced with 'Mr. Snuggles'
const sqlQuery = 'INSERT INTO cats (name, type, weight) VALUES ($1, $2, $3)';
​
client.query(sqlQuery, inputData, whenQueryDone);

SQL Entity Naming and Casing

  1. 1.
    Database names should match the relevant code repo name and be in snake case (i.e. lowercase with underscores between words). For example, ufo_express.
  2. 2.
    Table names are pluralised snake case. For example, ufo_sightings.
  3. 3.
    Column names are singular snake case. For example, ufo_shape.
  4. 4.
    We use snake case for SQL because SQL entities are case-insensitive, and - is a special character in some SQL implementations, while _ is not.

Exercise

We'll write a command line app that mirrors the todo app we built in 2.PCE.6, except with dogs.
Begin by cloning the base node app. Use the create table example from the previous section 3.4.1 to create a new table, dogs, with the columns name, type, and weight.
The following command should run an INSERT query on the database.
node index.js fluffy terrier 654
Check your work by looking in the database using psql and querying using SQL commands.
Add functionality to gets all dogs and output the dog info on the command line.
node index.js all-dogs