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)));

MySQL in the Terminal

The default path to MySQL on Mac is /usr/local/mysql/bin.

If you type mysql --version, you may get an error saying “command not found.” This is because you don’t have the MySQL command in your path.

To solve this, you can do one of two things:

# This will add MySQL to your current session (will be lost when you close Terminal)
export PATH=${PATH}:/usr/local/mysql/bin
# Permanantly
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> ~/.bash_profile

Copy and paste whichever command you prefer into Terminal. Close & reopen your Terminal window after you run the command.

Now, if you type mysql --verison, it should show you your version of MySQL.

Log in and use MySQL in the Terminal using this command: mysql -u root -p. It will ask for your password; make sure you use your MySQL password, not your system password

Ctrl + L to clear the prompt

Show users:

SELECT User, Host FROM mysql.user;

Create user: When you create a new database for a new application, you will probably want to have a specific user for that database rather than using the root user

CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'somepassword';

Leave the MySQL Terminal

quit

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);
        }
    })
});

Error: MySQL Workbench Hangs When Connecting to Database

Problem: Whenever I click on my Local Connection to get access to my MySQL Database, Workbench attempts to open it then freezes.

Solution: It’s not ideal, but I have to delete the Local Instance and create a new one. It works for me after that.

Right-click on the Local Instance and select “Delete Connection.” Then Press the “+” button next to “MySQL Connections.”

Fill in the Connection Name (any name you want) and Password (the password we created earlier during the MySQL installation). Then, test the connection using the Test Connection button at the bottom. If the connection is successful, press OK.

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;