Setting up a Free Postgres Database in Heroku w/ PgAdmin

Step 1: Log into Heroku

Step 2: Create a New App

Create a new Heroku app by either clicking “Create a new app” in the center of the screen or “New” at the top right of the screen.

Choose an App Name and the Region (either the US or Europe) for your new app. The name can be anything you want. Then, just click the Create app button.

Step 3: Add a PostgreSQL Database

To attach a PostgreSQL database to the blank app you just made, you need to go to the Resources tab in the header of the new app’s dahsboard. Then type Heroku Postgres into the Add-ons search field. When shown, select the suggested Heroku Postgres add-on from the dropdown:

The next popup asks you to choose a pricing plan for the database. Select the Hobby Dev – Free plan and click Provision.

Now your PostgreSQL database is up !

Step 4: Get Heroku DB Information

To find the credentials and the connection URL for the PostgreSQL database, make sure you’re still in the Resources tab and click on the Heroku Postgres resource we just added.

That should bring you to this screen:

Select the Settings tab in the header of that screen. You will be navigated to a page where Here, you can click the View Credentials button to see the credentials of your PostgreSQL database.

You will need these values to input into your PgAdmin interface

Step 5: Configure PgAdmin w/ Heroku DB Credentials

Open up PgAdmin

Right-click on Servers at the very top of the left-side panel

Choose Create > Serve

Fill out the new server form with all of the corresponding information from the Heroku View Credentials page.

After you save the new server, it will become populated with a huge list of databases. You will not have access to any of them except the one with your Database name from Heroku. Scroll through and find this one.

Then, as you would with any other PgAdmin database, go to Schemas > public > Create new Table. Populate this table with the fields you want.

You’re all set.

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;