3.4.2: PostgreSQL Node App

Search…
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.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.Table names are pluralised snake case. For example, ufo_sightings.
3.Column names are singular snake case. For example, ufo_shape.
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
3.4.1: PostgreSQL, psql
3.4.3: Nested SQL Queries
Last modified 2mo ago
Copy link
Outline
Database Setup Outside of Application
INSERT with value params
SQL Entity Naming and Casing
Exercise