Notes

  • Technologies: JavaScript, Node.js, Express, PostgreSQL, Git/GitHub, jQuery, AJAX
  • If you want to try out this project, you will need to have your machine set up correctly. Checkout the Code Fellows 301 Pre-work for instructions on how to do this [https://github.com/codefellows/code-301-prework]
  • Do not put any sensitive information in your code or through this app.

Background

Recently we were given labs or exercises where the application was already built but missing some key code to make it work. It was a good exercise on reading and understanding someone else’s code but I knew I needed more practice building an app from the ground up to really understand what was going on. To do this, I and other classmates requested a project and our instructor was generous and created a new extra credit assignment that required us to build a Full Stack CRUD application from scratch. Below are some of the requirements we were given:

Requirements:

  • Create an Express app that exposes routes to Create, Read, Update and Delete records in database with single table, or multiple related tables.
  • Express app should create (and perhaps populate) table(s) as needed.
  • Create a simple front end web page that allows user to perform CRUD operations via the Express app.
  • Make sure to include database name in your connection string so that others (especially graders) can build out the app.

The Project Repository:

  • Simple Contact Form [https://github.com/amgranad/simple-contact-form]
  • Please note, I also used this project to test out Heroku so there are some extra code you don’t need to build this app which should be labeled.
  • More about the app: [https://github.com/amgranad/simple-contact-form/blob/master/README.md]

Let’s Start the Work

Initalize

First get your database ready. Create your database. You should find information on how to do this in the Code Fellows 301 Prework in this section: [https://github.com/codefellows/code-301-prework#all-users-startup-and-create-some-databases]

Next, Create a simple table in the database: 1. Enter this command in your PostgreSQL shell to create a new table called “contacts” with “id” as the serial, primary key, columns called “name” and email that are limited to 255 characters, and a column called “message” that takes texts with no character limit. Don’t forget the semi-colon at the end!

CREATE TABLE IF NOT EXISTS
    contacts (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255),
      email VARCHAR(255),
      message TEXT
    );
  1. Add in dummy information to your table

    INSERT INTO contacts
    (name, email, message)
    VALUES (‘‘, ‘‘, ‘‘);

  2. Create your repository in Github [https://help.github.com/articles/create-a-repo/] clone it down to your local machine. Don’t forget the .gitignore file, license, and README.md!

  3. Initialize your repository with NPM by entering this in your command line:

    npm init

  4. Install npm packages (express, pg, optional: body-parser). After the installations have been finished, there should be new package.json and package-lock.json and a node_modules folder files in your repository.

    npm install

Also install Nodemon globally.

npm install -g nodemon
  1. Add the node_modules folder to your .gitignore file.

    node_modules

  2. Create your public folder and the files inside. In your repository folder with the command line:

    mkdir public

and in the public folder:

touch index.html app.js account.html

Set Up Your Server

This will be responsible for interacting with your database. Make sure you have a server.js file in your repository.

In your server.js file: 1. Require your middleware/npm packages:

const express = require('express');
const pg = require('pg');
const pg = require('body-parser');
  1. Set your port

    const PORT = process.env.PORT || 3000;

  2. Store the express function in a const so you can work with it:

    const app = express();

  3. Store your connection string to use for later.

    const conString = ‘postgres://:@localhost:5432/‘;

Make sure your password here is a password you’re not using in other important accounts. Make sure it’s a password you don’t mind going public. 5. Store and create a new PostgreSQL client and pass your connection string into it.

const client = new pg.Client(conString);
  1. Connect to your client

    client.connect();

  2. If you want to use body-parser:

    app.use(bp.json());
    app.use(bp.urlencoded({extended:true}));

  3. To help us serve static files in our public folder:

    app.use(express.static(‘./public’));

  4. Have your server listen in at your port:

    app.listen(PORT, function() {
    console.log(server started on: ${PORT});
    });

  5. Start your server by typing this in your terminal while in your local repository:

    nodemon server.js

You should see a message in your terminal saying your server has been started on your port 3000.

Create Your CRUD API’s

  1. To serve your index.html and account.html files:

    app.get(‘/’, (request, response) => {
    response.sendFile(‘index.html’, {root: ‘./public’});
    });

    app.get(‘/account’, (request, response) => {
    response.sendFile(‘account.html’, {root: ‘./public’});
    });

Now when you type these in your local host, you should be taken to your index.html and account.html files.

To see which files you are in, you should create a basic html scaffold in each of your html files, connect your app.js file, and add in a header with a message that wil let you know which file you’re in.

  1. READ. This method will grab all the information from the database. In particular, this method selects and returns all the rows in the ‘contacts’ table from my database. It also has a catch() method to catch errors.

    app.get(‘/contacts’, (request, response) => {
    client.query(SELECT * FROM contacts;
    ).then(function(results){
    response.send(results.rows);
    }).catch(err => {
    console.error(err);
    });
    });

When you type this in your browser while your server is online, you should see the information from your database returned to you:

localhost:3000/contacts
  1. CREATE. This method will insert new rows into the table

    app.post(‘/contacts’, function(request, response) {
    const contact = request.body;
    client.query(
    INSERT INTO contacts
    (name, email, message)
    VALUES ('${contact.name}', '${contact.email}', '${contact.message}');

    ).then(function() {
    response.send(‘insert complete’);
    }).catch(err => {
    console.error(err);
    });
    });

You won’t really be able to test this until you use this API in your app.js file.

  1. UPDATE. This method will update a row in your database table with the matching id:

    app.put(‘/contacts/:id’, (request, response) => {
    client.query(
    UPDATE contacts
    SET name=$1, email=$2, message=$3
    WHERE id=$4;
    ,
    [
    request.body.name,
    request.body.email,
    request.body.message,
    request.params.id
    ]
    )
    .then(() => {
    response.send(‘update complete’)
    })
    .catch(err => {
    console.error(err);
    });
    });

When you use this later in the app.js file, ‘:id’ will need to be replaced by a number.

Inside the query, the $1 – $4 each refer to the items in the array that follows the SQL string in order. So $1 refers to request.body.name and $4 refers to the request.params.id.

request.params.id pulls the id number from the URL

  1. DELETE. This will delete a row by ID. It works similarly to the “put” method above but just deletes the row instead of updating it.

    app.delete(‘/contacts/:id’, (request, response) => {
    client.query(
    DELETE FROM contacts WHERE id=$1;,
    [request.params.id]
    )
    .then(() => {
    response.send(‘Delete complete’)
    })
    .catch(err => {
    console.error(err);
    });
    });

  2. OPTIONAL. This function will create your table if you haven’t yet created your table in your dtabase or if you deleted it.

    function loadDB() {
    client.query(CREATE TABLE IF NOT EXISTS
    contacts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    message TEXT
    );

    )
    .catch(err => {
    console.error(err)
    });
    }

Runs the loadDB function.

loadDB();

Now Build Your Front-End App.

Build out your html files some more.

  1. index.html. I created a simple contact form:

    Send me a message:





  2. account.html has a few parts/sections.

A button that triggers loading in the contact information from the database:

<button id="load-contacts">Load/Refresh Contacts</button>

A section that will display the information from above.

<section id="contacts"></section>

A form for updating the contact information or a row in your database table

 <form id="update-contacts">
    <fieldset>
      <legend>Update/correct an entry</legend>
      <input id="id" type="number" placeholder="id" required>
      <input id="name" type="text" placeholder="name" required>
      <input id="email" type="email" placeholder="email" required>
      <textarea id="message" placeholder="message" required></textarea>
    </fieldset>
  <button type="submit">Submit</button>
</form>

A form that will let you delete rows from your table

<form id="delete-contact">
  <fieldset>
    <legend>Delete Contact</legend>
    <input id="delete-id" type="number" placeholder="id" required>
  </fieldset>
  <button type="submit">Submit</button>
</form>

Create your function and event listeners

  1. This code attaches an event listener to the contact form in the index.html file and the information submitted is then sent and saved into the contacts table in the database using the .post() jQuery method.

    $(‘#contact-form’).on(‘submit’, function(e) {
    e.preventDefault();
    console.log(‘submission detected’);
    let name = $(‘#name’).val();
    let email = $(‘#email’).val();
    let message = $(‘#message’).val();
    $.post(‘/contacts’, {
    name,
    email,
    message
    }).then(results => {
    console.log(‘post results’, results);
    });
    });

  2. This code grabs all the contact information from the contacts table in the database using the jQuery .get() method and then appends all the information into the account.html DOM.

    function loadContacts() {
    $(‘#contacts’).empty();
    $.get(‘/contacts’).then(results => {
    $.each(results, function(index, value) {
    $.each(value, function(key, item) {
    $(‘#contacts’).append(‘

    <

    div>’).append(item);
    });
    });
    });
    }

Note that the ‘#contacts’ section is also emptied before it runs the get request to make sure that the database information is not appended/displayed more than once.

Attach the event listener that will run the loadContacts function to the #load-contacts button

$('#load-contacts').on('click', loadContacts);
  1. This function will update the information for an entry in the database using the jQuery .ajax() method by performing a ‘put’

    function updateContacts(id, name, email, message) {
    $.ajax({
    url: /contacts/${id},
    method: ‘PUT’,
    data: {
    name,
    email,
    message
    } //condensed since the key and variables have the same names.
    }).then(data => {
    console.log(‘put data: ‘ + data);
    });
    }

The event listener that grabs the information from the update form:

$('#update-contacts').on('submit', function(e) {
  e.preventDefault();
  updateContacts($('#id').val(), $('#name').val(), $('#email').val(), $('#message').val());
});
  1. For deleting a contact form entry from the database using the .ajax() jQuery method:

    function deleteContact(id) {
    $.ajax({
    url: /contacts/${id},
    method: ‘DELETE’
    }).then(data => {
    console.log(data);
    });
    }

The event listener:

$('#delete-contact').on('submit', function(e) {
  e.preventDefault();
  deleteContact($('#delete-id').val());
});

Final Notes

Creating this simple full stack app really helped me understand how each piece of the puzzle works with each other and struggling on my own help me to learn not just how to write the code but also to troubleshoot it

I hope this will be helpful to someone else who just started learning node.js, CRUD applications, AJAX, PostgreSQL, and API’s.

If you find any bugs, errors, or typos, please let me know!

Get Started on Growing the Online Side of Your Business!

Download my Essential Tools to Expand Your Business Online.

You have Successfully Subscribed!