Sequelize with PostgreSQL with Node

Import the Sequelize module into your Node project

npm i sequelize

Add the specific database driver to coincides with the SQL DBMS that you are using:

npm i --save pg pg-hstore   

Connect to the database in your Node application

Option 1: Passing a connection URI

const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname') 

module.exports = sequelize;

Option 2: Passing parameters separately (other dialects)

const sequelize = new Sequelize('database', 'username', 'password', {    
   host: 'localhost', 
   dialect: 'postgres'
});

module.exports = sequelize;

Test the connection

try { 
   await sequelize.authenticate(); 
   console.log('Connection has been established successfully.'); 
} catch (error) { 
   console.error('Unable to connect to the database:', error); 
}

Create a new model

The db variable is imported from the Sequelize connection above (Opt 1 or 2) which we are assuming is in a separate file.

const Sequelize = require('sequelize');
const db = require('../config/database');

const Person = db.define('gig', {
   name: {
      type: Sequelize.STRING
   },
   age: {
      type: Sequelize.INTEGER
   },
   description: {
      type: Sequelize.STRING
   }
});

module.exports = Person;

Start making Sequelize queries in server routes

The beauty of Sequelize is that it allows you to use object-oriented syntax to make queries to relational/SQL databases.

app.get('/', (req, res) =>
   Person.findAll()
      .then(people => console.log(people))
      .catch(err => console.log(err)));

Upload Images to AWS S3 from Node Server w/ Multer

  1. Create a bucket
    1. (Sign in to console) Sign in to the AWS Management Console and open the Amazon S3 console at https://console.aws.amazon.com/s3/.
    2. Choose Create Bucket
    3. In Bucket name,  enter a DNS-compliant name for your bucket.
    4. In Region, choose the AWS Region where you want the bucket to reside.
    5. In Bucket settings for Block Public Access, keep the values set to the defaults. They will be private for now. We will change them to public later.
    6. Choose Create bucket.

2. Get security credentials

3. Install dependencies to Node project

npm i --save aws-sdk multer multer-s3

4. Set up the file upload middleware with multer and AWS-SDK

const aws = require('aws-sdk');
const multer = require('multer');
const multerS3 = require('multer-s3');

const config = require('../config');

aws.config.update({
   secretAccessKey: config.get('AWS_SECRET_ACCESS'),
   accessKeyId: config.get('AWS_ACCESS_KEY'),
   region: 'us-east-1'
});

const s3 = new aws.S3();

const upload = multer({
   storage: multerS3({
      s3,
      bucket: 'bucket-name',
      acl: 'public-read',
      key: function (req, file, cb) {
         cb(null, Date.now().toString())
      }
   })
}).single('photo');

module.exports = upload;

In the upload middleware, we define a key value. This key serves essentially as the image’s name in AWS, differentiating images in the list in the AWS Console. We are setting the key to the date at which the image is saved.

5. Set up route to handle image requests

router.post('/image-upload', function(req, res) {
   upload(req, res, (err) => {
      if (err) {
         return res.status(422).send({errors: [{title: 'File Upload Error', detail: err.message}] });
      }

      return res.json({'imageUrl': req.file.location});
   });
});

6. Set permissions to public

7. Send request in Postman to test it

8. Check the AWS S3 console to see if a new entry is there

The Date string that we set as the key value is in the Name field.

Uploading Images in Node with Multer

Multer is one of the most popular libraries for uploading files to a Node.js server. Multer is a Node.js middleware for handling multipart/form-data, which is primarily used for uploading files.

Install Multer package in your project

npm i multer

Create a form in your HTML file that will post to your server. Notice the enctype is set to multipart/form-data. Also, make sure to give your inputs names. That is how Multer finds them.

<form action="/" method="POST" enctype="multipart/form-data">
   <input type="text" name="name">
   <input type="file" name="image">
   <button type="submit">Submit</button>
</form>

Below is a very basic application of Multer. When our form makes a post request to the root, the Multer middleware (upload.single('image')) will give us access to the inputs in the request. The middleware takes in the name of the file input from your form. In our case, we called the input image. req.file holds the file uploaded to the form. req.body holds the text inputs.

When we defined our upload middleware, we set the destination in which we want to store our file uploads from Multer. In this case, files from our form will be stored in the uploads folder. If the folder doesn’t exist, it will be created automatically.

var express = require('express')
var multer  = require('multer')
var upload = multer({ dest: 'uploads/' })

var app = express()

app.post('/', upload.single('image'), function (req, res, next) {
  console.log(req.file, req.body);
})

Multer also allows us to customize the way that we handle file uploads.

The disk storage engine gives you full control on storing files to disk. There are two options available, destination and filename. They are both functions that determine where the file should be stored.

const storage = multer.diskStorage({
    destination: function (req, file, cb) {
       cb(null, './public/uploads');
    },
    filename: function (req, file, cb) {
        cb(null, file.fieldname + '-' + Date.now() + path.extname(file.originalname));
    }
});

You append this storage property to your upload middleware

const upload = multer({
    storage: storage,
});

You can also create a filter function to define which types of files you want to accept and which types of files you don’t. For example, you can set your filter to only accept .png, .jpeg., and .jpg file types.

function checkFileType(file, cb) {
    // Allowed ext
    const filetypes = /jpeg|jpg|png/;
    // Check ext
    const extname = filetypes.test(path.extname(file.originalname).toLowerCase());
    // Check mime
    const mimetype = filetypes.test(file.mimetype);

    if (mimetype && extname) {
        return cb(null, true);
    } else {
        cb('Error: Images Only');
    }
}

Set this to the fileFilter property of the upload object:

const upload = multer({
    storage: storage,
    fileFilter: function (req, file, cb) {
        checkFileType(file, cb);
    }
}));

Insert A Record Into MySQL Database from Server POST Request (Node + MySQL)

There are 2 ways to insert data from the req.body of a route:

Method #1

router.post('/', (req, res) => {
    const userInfo = req.body;

    const sql = "INSERT INTO users SET ?";

    mysqlConnection.query(sql, userInfo, (err, rows, fields) => {
        if (!err) {
            res.send(rows);
        } else {
            console.log(err.message);
            res.send(err);
        }
    })
});

Method #2

router.post('/', (req, res) => {
    const { name, username } = req.body;

    const sql = "INSERT INTO users (name, username) VALUES ('${name}', '${username}')";

    mysqlConnection.query(sql, (err, rows, fields) => {
        if (!err) {
            res.send(rows);
        } else {
            console.log(err.message);
            res.send(err);
        }
    })
});

Node.js MySQL Tutorial

This tutorial assumes you have Node.js, MySQL, and MySQL Workbench installed

  1. Create a new Node application

Create the package.json and install the necessary dependencies

cd project-folder
npm init
npm i express express-handlebars mysql

2. Create a new MySQL Database

Create a new Schema. I will call mine Practice

3. Set up your server file

Quickly set up your server file and initiate your MySQL Connection in order to make sure the connection is running successfully before we go any further.

const express = require("express");
const mysql = require("mysql");

const app = express();

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

const mysqlConnection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "password",
    database: "Practice",
    multipleStatements: true
})

mysqlConnection.connect((err) => {
    if (!err) {
        console.log("Connection Successful");
    } else {
        console.log("Connection Failed");
        console.log(err.message);
    }
})

app.listen(3000);

Run your Node app

node index.js OR nodemon index.js

If your Terminal logs “Connection Successful”, move to Step 4

4. Move MySQL Connection to separate file

This is not required; it’s simply for organization’s sake

At the same level as the server file, make a new file. I’ll call mine connection.js

This will be the contents of the file:

const mysql = require("mysql");

const mysqlConnection = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "josh-mysql",
   database: "Practice",
   multipleStatements: true
});

mysqlConnection.connect((err) => {
   if (!err) {
      console.log("Connection Successful");
   } else {
      console.log(err.message);
   }
});

module.exports = mysqlConnection;

In the server file, import the MySQL connection from the connection.js file:

const mysqlConnection = require("./connection");

5. Create a table in the database

I’m going to open up my Practice Schema and find the Tables. Then, I’m going to right-click on Tables and create a new Table by clicking “Create Table.” Give your table a name and whatever properties you would like. Then, press Apply.

My projects table have properties of id and name

6. Create a route and query the database

In my server file, I’m going to handle GET requests to the root route. When a user requests the root route, I will query the MySQL database for all of the items in our projects table and send back the items in the response.

app.get("/", function(req, res){
   mysqlConnection.query("SELECT * from projects", (err, rows, fields) => {
      if (!err) {
          res.send(rows);
      } else {
         console.log(err);
      }
   })
});

Let’s test out our route. Run your server with node index.js, open up Postman and send a GET request to the root route.

Since we haven’t created any records into our projects table, this request should return an empty array.

That’s it! You’ve set up a basic Node server with a MySQL database.

Errors Connecting to MySQL in Node

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

In MySQL Workbench, open a blank Query page and execute these commands (Query > Execute (All or Selection)):

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
flush privileges;

This is what it will look like in Workbench:

ER_ACCESS_DENIED_ERROR: Access denied for user ‘root’@’localhost’ (using password: YES)

In the part of your Node application where you define the connection to the MySQL database, set password to an empty string:

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: ''
});

After you re-run your server, the error will go away, but you may be left with a new error– the error below:

ER_MUST_CHANGE_PASSWORD_LOGIN: Your password has expired. To log in you must change it using a client that supports expired passwords.

Execute the following query in MYSQL Workbench (change new-password to your own new password):

ALTER USER `root`@`localhost` IDENTIFIED BY 'new-password',
       `root`@`localhost` PASSWORD EXPIRE NEVER;
flush privileges;

How to use the express-validator package

The express-validator package is an npm package that makes it much easier to validate input fields (for user authentication).

First, at the top of our server file, we need to get access to the check and validationResult methods from express-validator:

const { check, validationResult } = require('express-validator/check');

Then, when you are handling the post route of a form, you can implement validation. First, you add a new parameter to the post method; it should be an empty JS array:

app.post('/', [], (req, res) => {
});

Within the array, run the check function on each field and give it the requirements you want to check for. For example, you can check if the input is an email or has a minimum length:

app.post('/', [
   // username must be an email
   check('username').isEmail(),
   // password must be at least 5 chars long
   check('password').isLength({ min: 5 })
], (req, res) => {
});

Then, in the callback, you create a variable called errors to hold any errors from the validation checks in the above array:

app.post('/', [
   check('username').isEmail(),
   check('password').isLength({ min: 5 })
], (req, res) => {
   // Finds the validation errors in this request and puts them in an array called errors
   const errors = validationResult(req);
});

Then, we check if the errors object holds an errors. If so, we can do stuff with the errors. If not, we can do stuff with the inputs:

app.post('/', [
   check('username').isEmail(),
   check('password').isLength({ min: 5 })
], (req, res) => {
   const errors = validationResult(req);

   if (!errors.isEmpty()) {
      return res.status(400).json({ errors: errors.array() });
   }

   User.create({
      username: req.body.username,
      password: req.body.password
   }).then(user => res.json(user));
});

In the event of an error, we send a 400 Bad Request using res.status(400). This simply means that things didn’t go as planned. Then, we get an array of error messages that correspond with the input validations that failed. The json response object will look something like this:

{
   "errors": [
      {
         "location": "body",
         "params": "username",
         "msg": "Insert custom or default error message here",
      }, 
      {
         "location": "body",
         "params": "password",
         "msg": "Please enter a correct password",
      }
   ]
}

More on the check function

We can pass a second optional parameter into the check function which will serve as a custom error message. If we don’t pass this parameter, it will give some generic error message.

check('name', 'Name is required')

Check if a field is empty

check('name', 'Name is required').not().isEmpty()

Setup React Front-End in Existing Project

Let’s say you’re building with the MERN stack, for example. You have an existing project folder in which you’ve set up your entire backend server with Express and connecting your MongoDB database. Now you want to append a React frontend to the project. How do you setup a new React project within your existing overall project folder?

cd into your existing project folder and run this command:

npx create-react-app [your-folder-name]

This line basically creates a React application within a folder with whatever name you choose (ex. client) within your current project directory. npx is a tool that comes with Node.js that allows us to run create-react-app without having to install it globally on our machine.

To run your React project to make sure everything installed successfully, run these commands:

cd [your-folder-name]
yarn start

Run Frontend and Backend at the Same Time

We can run our frontend React app and backend Node.js server at the same time using the development tool concurrently.

Install concurrently as a development dependency:

npm i -D concurrently

Define the necessary scripts within your package.json:

"scripts": {
    "start": "node server",
    "server": "nodemon server",
    "client": "npm start --prefix client",
    "dev": "concurrently \"npm run server\" \"npm run client\""
},

The “dev” script uses concurrently to run the “server” and “client” scripts at the same time– with one single command.

In the root folder, run the “dev” script:

npm run dev

Installing Dependencies Within the React Project

You install dependencies within a submerged React project the same way you would within an non-submerged React project.

First, make sure you’re inside the React project folder. My folder name is client so:

cd client

Then, install dependencies with npm

npm i react-redux axios ...

Get Ride of Default Git Repository Within React App

Within the React app, a git repository is automatically initialized on the creation of the React app. If you already have a Git repository within your root project, you don’t necessarily need another one for your React project. If you wanted to delete the React repository, you would

  1. Delete the .gitignore and README.md files within the React folder
  2. Delete the Git folder

This is the command to delete the git folder. Make sure you cd into your React folder before running this command or you might delete your root repository:

rm -rf .git

Node.js User Authentication with Bcrypt

In this lesson, we will be allowing users to create accounts, disguising their passwords to prevent hackers from stealing them, saving the accounts in a MongoDB database, and allowing users to log in with their account info.

In your project directory in Terminal, use this command to create a package.json file for your project

npm init -y

Any project that is using Node.js needs a package.json file. The file contains the project’s dependencies, source control information, and specific metadata.

Now install all the packages that our project will need using npm.

npm i express body-parser ejs mongoose bcrypt

I will show you how to set up the app.js file, but you’ll have to design the ejs (or html) and css files on your own.

In general, though, you’ll want to include something like this in your login & signup files:

<form action="/login" method="POST">
   <div class="form-group">
       <label for="email">Email</label>
       <input type="email" class="form-control" name="username">
   </div>
   <div class="form-group">
        <label for="password">Password</label>
        <input type="password" class="form-control" name="password">
   </div>
   <button type="submit" class="btn btn-dark">Login</button>
</form>

You will need a form that has an action parameter that posts to a route. You will take the user’s input using named input tags that can be referenced in the server file.

In your app.js file, require all the packages we installed earlier. Also create a saltRounds variable and set it to 10. We will use it later to salt our passwords.

const express = require("express");
const bodyParser = require("body-parser");
const ejs = require("ejs");
const mongoose = require("mongoose");
const bcrypt = require("bcrypt");
const saltRounds = 10;

More standard setup:

const app = express();

app.use(express.static("public"));
app.set('view engine', 'ejs');
app.use(bodyParser.urlencoded({
  extended: true
}));

mongoose.connect("mongodb://localhost:27017/authtestDB", {useNewUrlParser: true});

Create the User Model in MongoDB:

const userSchema = new mongoose.Schema ({
  email: String,
  password: String
});


const User = new mongoose.model("User", userSchema);

Set up the get routes so users can get to the different pages:

app.get("/", function(req, res){
  res.render("home");
});

app.get("/login", function(req, res){
  res.render("login");
});

app.get("/register", function(req, res){
  res.render("register");
});

Handle users entering their information into the login and register forms:

Posting to the Register Route

As you can see, we are using the bcrypt hash function. The function takes in the password given by the user in the request, the number of salt rounds (defined above), or times we want our password to be salted , and a callback function that can either return an error or the hashed (encrypted) password.

A new user based on our Mongo User Model is created using the username from the request and the encrypted password. Then, that user is saved into the MongoDB. If successful, the server will navigate the user to pages that require authentication.

app.post("/register", function(req, res){
  bcrypt.hash(req.body.password, saltRounds, function(err, hash) {
    const newUser =  new User({
      email: req.body.username,
      password: hash
    });
    newUser.save(function(err){
      if (err) {
        console.log(err);
      } else {
        res.render("secrets");
      }
    });
  });

});
Posting to the Login Route

First, using the findOne function, we see if there are any emails in our database that match the one inputted by the user. This findOne function can return either an error or a found user/ matching user. If a user is found for the inputted email, bcrypt will proceed to compare the inputted password with the password for the email in the database. If it’s another match (result === true), the server will navigate the user to pages that require authentication.

app.post("/login", function(req, res){
  const username = req.body.username;
  const password = req.body.password;

  User.findOne({email: username}, function(err, foundUser){
    if (err) {
      console.log(err);
    } else {
      if (foundUser) {
        bcrypt.compare(password, foundUser.password, function(err, result) {
          if (result === true) {
            res.render("secrets");
          }
        });
      }
    }
  });
});