3.2.5: SQL - Express

Database Setup

Our node applications will not set up the Database or the tables. Databases and tables need to be created before our applications run. The code in this section assumes that we have set up Postgres, at this point you should have created a Postgres user and be able to enter the DataBase 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. We will connected to the users database that we developed in a previous section.

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. The command below should be run within an npm initialised directory:

npm install pg

DB Queries in Node

SELECT

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

Replace <MY_UNIX_USERNAME>

index.js

const pg = require('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 students';

// 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

Note

When working in companies you may be asked to mask your database credentials with .env.

INSERT

When we replace the SQL query in index.js with the following, what does this do?

const sqlQuery =
  "INSERT INTO students (first_name, last_name, mobile, gender) VALUES ('Eric', 'Marsh', 874480753, true)";

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: ['Eric', 'Marsh', 874480753, true] then references those array values in the query, INSERT INTO students(first_name, last_name, mobile, gender) VALUES($1, $2, $3, $4) but the value $1 references array index 0. This is because SQL is a [1-indexed language](https://stackoverflow.com/questions/53631015/why-sql-primary-key-index-begin-at-1-and-not-at-0#:~:text=3 Answers&text=Counting in SQL generally starts,1 %28and not 0%29.&text=We don't start counting at zero until we learn programming.), and most other programming languages including JS are 0-indexed.

const inputData = ['Eric', 'Marsh', 874480753, true];

// in this example, $1 is going to be replaced with 'Eric'
const sqlQuery = 'INSERT INTO students (first_name, last_name, mobile, gender VALUES ($1, $2, $3, $4)';

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.

Postgres and Express

When developing full stack applications we will need to interface our Postgres database with the Express Application such that we can fire off functions that query the database through API's.

To run the code below you will need to install Express into the same npm initialised directory that you created for the previous code block. Run this command:

npm install express

Alter the current index.js to reflect the code below, changing the value of<MY_UNIX_USERNAME>.

index.js

const express = require('express');
const pg = require('pg');

// Initialise DB connection
const { Pool } = pg;
const pgConnectionConfigs = {
  user: '<MY_UNIX_USERNAME>',
  host: 'localhost',
  database: '<MY_UNIX_USERNAME>',
  port: 5432, // Postgres server always runs on this port by default
};
const pool = new Pool(pgConnectionConfigs);

const app = express();
app.use(express.json());
app.use(express.urlencoded({ extended: false }));

// Code to retireve all rows from the students table
app.get('/', (request, response) => {
  console.log('request came in');

  const whenDoneWithQuery = (error, result) => {
    if (error) {
      console.log('Error executing query', error.stack);
      response.status(503).send(result.rows);
      return;
    }
    console.log(result.rows[0].name);
    response.send(result.rows);
  };

  // Query using pg.Pool instead of pg.Client
  pool.query('SELECT * FROM students', whenDoneWithQuery);
});

// Code to insert a student into the students table
app.post('/', (request, response) => {
  console.log('request came in');
  console.log(request.body)
  
  let first_name = request.body.first_name;
  let last_name = request.body.last_name;
  let mobile = request.body.mobile;
  let gender = request.body.gender;  

  const whenDoneWithQuery = (error, result) => {
    if (error) {
      console.log('Error executing query', error.stack);
      response.status(503).send(result.rows);
      return;
    }
    console.log(result.rows);
    response.send(result.rows);
  };

  // Query using pg.Pool instead of pg.Client
  pool.query(`INSERT INTO students (first_name, last_name, mobile, gender) VALUES ('${first_name}', '${last_name}', ${mobile}, ${gender})`, whenDoneWithQuery);
});


app.listen(3004);

Note: There is no need to run pool.end like how we ran client.end with the pg Client class in our Node CLI apps. This is because in server applications we expect the DB connection to persist beyond individual requests.

Command Line

node index.js

Exercise

Develop a put and delete handler within this Express application.

  • The put request should alter an existing student stored within your tables, you may need to use request parameters here.

  • The delete request should remove a student from your tables, again, consider using request parameters.

  • Test your API calls using Thunder Client.

Previous3.2.4: Advanced SQL ConceptsNext3.2.6: DBeaver