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;

Connecting to MongoDB Atlas with Mongoose

In MongoDB Atlas on your Cluster page, click Connect > Connect your application > Copy the connection string

Create config folder. Inside, create default.json. With the config package, we can create global values that we can use throughout our application

In default.json:

{
   "mongoURI": "mongodb+srv://username:<password>@devconnector.cc6yr.mongodb.net/<dbname>?retryWrites=true&w=majority"
}

We use curly braces because this is json formatting

Replace <password> with your password. Replace <dbname> with your database’s name.

We could put our connection logic in the server file, but in order to not clutter it up, we can put it in a separate file within the config folder

We’ll call the file db.js (you can call it whatever you want) and this is what goes inside of it:

const mongoose = require('mongoose');
const config = require('config');
const db = config.get('mongoURI');

const connectDB = async () => {
    try {
        await mongoose.connect(db, { useNewUrlParser: true });
        console.log("MongoDB connected...");
    } catch(err) {
        console.log(err.message);
        process.exit(1);
    }
}

module.exports = connectDB;

We add { useNewUrlParser: true } to our connect function in order to get rid of this error:

“(node:58034) DeprecationWarning: current URL string parser is deprecated, and will be removed in a future version. To use the new parser, pass option { useNewUrlParser: true } to MongoClient.connect.”

Then, in our server file we can just import and call the connectDB() function:

const express = require("express");
const connectDB = require('./config/db');

const app = express();

connectDB();

app.get('/', (req, res) => res.send('API Running'));

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

app.listen(PORT, () => console.log(`Server started on port ${PORT}`));