Rocket Academy Bootcamp
  • 🚀Welcome to Bootcamp!
  • 🛠️Logistics
    • Course Schedules
    • Course Methodology
    • Required Software
    • LinkedIn Education Badge
  • 📚General Reference
    • Naming, Casing, and Commenting Conventions
    • VS Code Tips
    • Recommended Resources
  • 🪨0: Foundations
    • 0.1: Command Line
    • 0.2: Git
      • 0.2.1: Branches
    • 0.3: GitHub
      • 0.3.1: Pull Requests
    • 0.4: JavaScript
      • 0.4.1: ES6
      • 0.4.2: Common Syntax
      • 0.4.3: Reference vs Value
      • 0.4.4: Classes
      • 0.4.5: Destructuring and Spread Operator
      • 0.4.6: Promises
        • 0.4.6.1: Async Await
    • 0.5: Node.js
      • 0.5.1: Node Modules
      • 0.5.2: NPM
      • 0.5.3: Nodemon
  • 🖼️1: Frontend
    • 1.1: HTML
    • 1.2: CSS
      • 1.2.1: Layout
    • 1.3: React
      • Styling in ReactJs
      • Using Styling Libraries with React
      • React Deployment
    • 1.E: Exercises
      • 1.E.1: Recipe Site
      • 1.E.2: Portfolio Page
      • 1.E.3: World Clock
      • 1.E.4: High Card
      • 1.E.5: Guess The Word
    • 1.P: Frontend App
  • 🏭2: Full Stack
    • 2.1: Internet 101
      • 2.1.1: Chrome DevTools Network Panel
      • 2.1.2: HTTP Requests and Responses
    • 2.2: Advanced React
      • 2.2.1: AJAX
      • 2.2.2: React Router
      • 2.2.3: useContext
      • 2.2.4: useReducer
      • 2.2.5: Environmental Variables
      • 2.2.6: React useMemo - useCallback
    • 2.3: Firebase
      • 2.3.1: Firebase Realtime Database
      • 2.3.2: Firebase Storage
      • 2.3.3: Firebase Authentication
      • 2.3.4: Firebase Hosting
      • 2.3.5: Firebase Techniques
    • 2.E: Exercises
      • 2.E.1: Weather App
      • 2.E.2: Instagram Chat
      • 2.E.3: Instagram Posts
      • 2.E.4: Instagram Auth
      • 2.E.5: Instagram Routes
    • 2.P: Full-Stack App (Firebase)
  • 🤖3: Backend
    • 3.1: Express.js
      • 3.1.1 : MVC
    • 3.2: SQL
      • 3.2.1: SQL 1-M Relationships
      • 3.2.2: SQL M-M Relationships
      • 3.2.3: SQL Schema Design
      • 3.2.4: Advanced SQL Concepts
      • 3.2.5: SQL - Express
      • 3.2.6: DBeaver
    • 3.3: Sequelize
      • 3.3.1: Sequelize One-To-Many (1-M) Relationships
      • 3.3.2: Sequelize Many-To-Many (M-M) Relationships
      • 3.3.3: Advanced Sequelize Concepts
      • 3.3.4 Database Design
    • 3.4: Authentication
      • 3.4.1: JWT App
    • 3.5: Application Deployment
    • 3.E: Exercises
      • 3.E.1: Bigfoot JSON
      • 3.E.2: Bigfoot SQL
      • 3.E.3: Bigfoot SQL 1-M
      • 3.E.4: Bigfoot SQL M-M
      • 3.E.5: Carousell Schema Design
      • 3.E.6: Carousell Auth
    • 3.P: Full-Stack App (Express)
  • 🏞️4: Capstone
    • 4.1: Testing
      • 4.1.1: Frontend React Testing
      • 4.1.2: Backend Expressjs Testing
    • 4.2: Continuous Integration
      • 4.2.1 Continuous Deployment (Fly.io)
      • 4.2.2: Circle Ci
    • 4.3: TypeScript
    • 4.4: Security
    • 4.5: ChatGPT for SWE
    • 4.6: Soft Skills for SWE
    • 4.P: Capstone
  • 🧮Algorithms
    • A.1: Data Structures
      • A.1.1: Arrays
        • A.1.1.1: Binary Search
        • A.1.1.2: Sliding Windows
      • A.1.2: Hash Tables
      • A.1.3: Stacks
      • A.1.4: Queues
      • A.1.5: Linked Lists
      • A.1.6: Trees
      • A.1.7: Graphs
      • A.1.8: Heaps
    • A.2: Complexity Analysis
    • A.3: Object-Oriented Programming
    • A.4: Recursion
    • A.5: Dynamic Programming
    • A.6: Bit Manipulation
    • A.7: Python
  • 💼Interview Prep
    • IP.1: Job Application Strategy
    • IP.2: Resume
    • IP.3: Portfolio
Powered by GitBook
On this page
  • Database Setup
  • pg NPM Library
  • DB Queries in Node
  • SQL Entity Naming and Casing
  • Postgres and Express
  • Exercise
  1. 3: Backend
  2. 3.2: SQL

3.2.5: SQL - Express

Previous3.2.4: Advanced SQL ConceptsNext3.2.6: DBeaver

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 .

pg NPM Library

To use Postgres in Node, we need to install the Postgres client library for node,. 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.

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.

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](.), and most other programming languages including JS are 0-indexed.

Test your API calls using .

🤖
previous section
pg
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
Thunder Client