NodeJS and ExpressJS API with MySQL

In this blog, we are going to learn the following...

Explaining NodeJS and ExpressJS
Create an API with NodeJS and ExpressJS
- Requires NodeJS to be installed
Create a MySQL database and example table
Connecting the MySQL database to the API
Send a test CRUD op to the API via Thunderclient in VS Code

💡

Complete Repo Can Be Found Here NoahFlowaBlog/NodeJSExpressAPI (github.com )

Explaining NodeJS and ExpressJS

First off, let's dive into what exactly NodeJS is, what Express is and how the two work together to allow us to make an API. These are 1,000-foot views of both, for more info I highly recommend their respective documentation pages or Wikipedia if you know about Primary/Secondary sources.

NodeJS is the environment that allows developers to deploy desktop-class applications using web technologies such as HTML, CSS and JavaScript to any platform where the Chrome V8 Engine can run. Essentially, if Google Chrome or other flavors of Chromium can run on that platform, we can make an app for it.

Express or sometimes referred to as ExpressJS takes it a bit further and builds upon NodeJS applications to allow them to work as a regular web application. Kind of a circle-of-life conundrum if you ask me. But why do we need to use Express for NodeJS?

NodeJS can work in a couple of different ways. It can display a GUI like a regular application such as VS Code or Discord which uses NodeJS to power the behind-the-scenes or we can use it to build console applications. For an API, since we don't need to have a GUI to do CRUD ops, we just want to run it headless or in the console. However, because we are using NodeJS we cant port it back to the web because again, it isn't practical to do so. So to add it back to the web, we use Express which allows us to listen on ports and URIs for specific actions and then handle said actions.

Now, let's jump in!

Create An API with NodeJS and Express

Go ahead and create a folder/directory anywhere on your PC. I'm going to make one on my Desktop in Windows Terminal

PS C:\Users\oster> cd .\Desktop\
PS C:\Users\oster\Desktop> mkdir HashAPI; cd HashAPI

    Directory: C:\Users\oster\Desktop


Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
d-----          8/9/2023   4:10 PM                HashAPI

PS C:\Users\oster\Desktop\HashAPI>

From here we can go ahead and do code . if you have Visual Studio Code installed and mapped the PATH env variable to it. If not or you are using a different editor, go ahead and open the HashAPI folder. Going forward I'm going to use VS Code as my editor and refer back to it. It's not required but it is what I prefer.

Inside VS Code, let's open up the integrated terminal with CTRL ~ and run npm init. This will initialize a basic NodeJS project inside our folder to help us get started. Go ahead and press enter a few times or customize the file as you see fit. Then let's go ahead and run the following:

PS: C:\Users\oster\Desktop\HashAPI> npm i express body-parser cors sequelize mysql2

This will tell NPM or Node Package Manager to install the following packages from the NPMJS website:

We install Express and Sequelize along with some other middleware and helper libraries as well such as CORS or Cross Origin Resource Handling which should honestly be another topic, Body Parser which allows us to parse a FETCH request body in JSON. Lastly, we install MySQL package for Sequelize since we are using a MySQL database.

Once installed, let's open up app.js which should have been created when we executed node init, if not, go ahead and make that and assign it in the package.json file which should always be created as your entry file. Here is my package.json for reference:

{
  "name": "hashapi",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.20.2",
    "cors": "^2.8.5",
    "express": "^4.18.2",
    "mysql2": "^3.6.0",
    "sequelize": "^6.32.1"
  }
}

Inside of app.js go ahead and add the following imports to the top of the file:

const express = require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const { Sequelize } = require('sequelize');

Then initialize Express, CORS and bodyParser:

const app = express();

app.use(cors());
app.use(bodyParser.json());

Add in a test route to make sure the API is working and the listen object to have it start listening on the URI:

// Test route
app.get('/', (req, res) => {
    res.json({ message: 'API is working' });
});

const PORT = 3000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`)
});

With this all completed, your file should look similar to this:

const express = require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const { Sequelize } = require('sequelize');

const app = express();

app.use(cors());
app.use(bodyParser.json());

// Test route
app.get('/', (req, res) => {
    res.json({ message: 'API is working' });
});

const PORT = 3000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`)
});

Again, just like with all of my entries in this B.L.O.G. project, this is super beginner friendly and made from a 1,000-foot view. I will be diving in deeper to show you how to set up custom routes but this should help you get the gears turning and started.

If we go ahead and run this in our VS Code Terminal or Windows Terminal:

PS C:\Users\oster\Desktop\HashAPI> node app.js

We should get this returned:

PS C:\Users\oster\Desktop\HashAPI> node app.js
Server is running on port 3000

We should then be able to open up localhost:3000 and see the message:

{
    "message": "API is working"
}

Perfect! This means our simple API is listening on the localhost:3000 URI and our simple GET / route is responding! We can also call this from Thunderbird or Postman Client like so:

VS Code image showing off ThunderClient and Directory

Create a MySQL database and example table

For the sake of simplicity, I'm going to post screenshots of my steps of setting up a MySQL database on Caprover using my Virtual Machine. Caprover is a PaaS self host-able software where you can do one-click installs or use Docker Containers. I'll be doing another B.L.O.G. on that in more detail.

Choose One-Click Apps/Databases button on the bottom:

Choose MySQL from the list:

Fill in the text fields with your choice:

Wait for it to install:

Choose it from your app list:

Uncheck Do Not Expose As Web-App option (optional encrypt):

Head over to the Deployment tab and find in the App Logs what port it self-assigned to:

Head back to the App Configs tab and assign the port number found in the Deployment tab to the Container Port field then choose a port for Server Port (I use 1009) and hit Save & Update:

Now use whatever SQL program you want, or if you don't have one, consider installing the official MySQL Workbench application, and connect to the database. I'm going to take a large assumption and say you know how to do that. If not, you just take in the URL provided at the top, the port number you entered, use the username and password you defined and you should be good to go.

Let's finally add a dummy table where we can do some CRUD ops and seed it with a couple of test entries.

🖋

Editors Note: I use both camelCase and CamelCaps. However, when dealing with databases and MySQL I was taught to always use CamelCaps.

-- Create a new database
CREATE DATABASE HashAPI;

-- Use the new database
USE HashAPI;

-- Create a users table that stores basic info
CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(15) NOT NULL,
    LastName VARCHAR(20) NOT NULL,
    Birthday DATE NOT NULL,
    FavoriteGame VARCHAR(30) NOT NULL,
    DateCreated DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Seed the data
INSERT INTO Users (FirstName, LastName, Birthday, FavoriteGame)
VALUES ('Noah', 'Osterhout', '1999-06-05', 'Star Citizen'),
       ('John', 'Halo', '1999-11-15', 'Halo: Combat Evolved');

-- Select the data to view
SELECT *
FROM Users;

Connecting the MySQL database to the NodeJS Express API

Now that we got both the beginning of the API and the bare minimum of a MySQL database, let's go ahead and connect them and define some routes for users.

First, we are going to add in our imports, define our database connection object and then connect to the database:

const express = require('express');
const { Sequelize, Model, DataTypes } = require('sequelize');
const router = express.Router();

// Define your variables
const DB_NAME = 'HashAPI';
const DB_USER = 'root';
const DB_PASSWORD = 'rootpassword';
const DB_HOST = 'your-mysql-host.com';
const DB_PORT = '1009';

// Database connection
const sequelize = new Sequelize(DB_NAME, DB_USER, DB_PASSWORD, {
  host: DB_HOST,
  port: DB_PORT,
  dialect: 'mysql'
});

Then we should add in our Users model, which mimics our table schema that we defined above so Sequelize knows how to handle each column and the table itself:

class User extends Model {}

User.init({
  UserID: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  FirstName: DataTypes.STRING,
  LastName: DataTypes.STRING,
  Birthday: DataTypes.DATE,
  FavoriteGame: DataTypes.STRING,
  DateCreated: { type: DataTypes.DATE, defaultValue: Sequelize.NOW },
}, { 
  sequelize, 
  modelName: 'User',
  tableName: 'Users',
  timestamps: false
});

Lastly, let's define our CRUD operations using simple logic and then add in our module export so we can tie it back to our app.js:

// Get all users (optional)
router.get('/', async (req, res) => {
try {
    const users = await User.findAll();
    res.json(users);
} catch (error) {
    console.error(error);  // Log the error details to the console
    res.status(500).json({ error: 'Failed to retrieve users', message: error.message });
}
});

// Create a new user
router.post('/', async (req, res) => {
    const {
      FirstName,
      LastName,
      Birthday,
      FavoriteGame
    } = req.body;

    try {
      const newUser = await User.create({
        FirstName,
        LastName,
        Birthday,
        FavoriteGame
      });

      res.json(newUser);
    } catch (error) {
      res.status(500).json({ error: 'Failed to create a new user' });
    }
});

// Get a user by ID
router.get('/:id', async (req, res) => {
const { id } = req.params;

try {
    const user = await User.findOne({ where: { UserID: id } });
    if (user) {
    res.json(user);
    } else {
    res.status(404).json({ error: 'User not found' });
    }
} catch (error) {
    console.error(error);  // Log the error details to the console
    res.status(500).json({ error: 'Failed to retrieve the user', message: error.message });
}
});

// Update a user by ID
router.put('/:id', async (req, res) => {
const { id } = req.params;
const {
    FirstName,
    LastName,
    Birthday,
    FavoriteGame
} = req.body;

try {
    const user = await User.findOne({ where: { UserID: id } });
    if (user) {
    user.FirstName = FirstName || user.FirstName;
    user.LastName = LastName || user.LastName;
    user.Birthday = Birthday || user.Birthday;
    user.FavoriteGame = FavoriteGame || user.FavoriteGame;

    await user.save();
    res.json(user);
    } else {
    res.status(404).json({ error: 'User not found' });
    }
} catch (error) {
    res.status(500).json({ error: 'Failed to update the user' });
}
});

// Delete a user by ID
router.delete('/:id', async (req, res) => {
const { id } = req.params;

try {
    const user = await User.findOne({ where: { UserID: id } });
    if (user) {
    await user.destroy();
    res.status(204).send();
    } else {
    res.status(404).json({ error: 'User not found' });
    }
} catch (error) {
    res.status(500).json({ error: 'Failed to delete the user' });
}
});

module.exports = router;

All together now:

const express = require('express');
const { Sequelize, Model, DataTypes } = require('sequelize');
const router = express.Router();

// Define your variables
const DB_NAME = 'HashAPI';
const DB_USER = 'root';
const DB_PASSWORD = 'rootpassword';
const DB_HOST = 'your-mysql-host.com';
const DB_PORT = '1009';

// Database connection
const sequelize = new Sequelize(DB_NAME, DB_USER, DB_PASSWORD, {
  host: DB_HOST,
  port: DB_PORT,
  dialect: 'mysql'
});

class User extends Model {}

User.init({
  UserID: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  FirstName: DataTypes.STRING,
  LastName: DataTypes.STRING,
  Birthday: DataTypes.DATE,
  FavoriteGame: DataTypes.STRING,
  DateCreated: { type: DataTypes.DATE, defaultValue: Sequelize.NOW },
}, { 
  sequelize, 
  modelName: 'User',
  tableName: 'Users',
  timestamps: false
});

// Get all users
router.get('/', async (req, res) => {
try {
    const users = await User.findAll();
    res.json(users);
} catch (error) {
    console.error(error);  // Log the error details to the console
    res.status(500).json({ error: 'Failed to retrieve users', message: error.message });
}
});

// Create a new user
router.post('/', async (req, res) => {
    const {
      FirstName,
      LastName,
      Birthday,
      FavoriteGame
    } = req.body;

    try {
      const newUser = await User.create({
        FirstName,
        LastName,
        Birthday,
        FavoriteGame
      });

      res.json(newUser);
    } catch (error) {
      res.status(500).json({ error: 'Failed to create a new user' });
    }
});

// Get a user by ID
router.get('/:id', async (req, res) => {
const { id } = req.params;

try {
    const user = await User.findOne({ where: { UserID: id } });
    if (user) {
    res.json(user);
    } else {
    res.status(404).json({ error: 'User not found' });
    }
} catch (error) {
    console.error(error);  // Log the error details to the console
    res.status(500).json({ error: 'Failed to retrieve the user', message: error.message });
}
});

// Update a user by ID
router.put('/:id', async (req, res) => {
const { id } = req.params;
const {
    FirstName,
    LastName,
    Birthday,
    FavoriteGame
} = req.body;

try {
    const user = await User.findOne({ where: { UserID: id } });
    if (user) {
    user.FirstName = FirstName || user.FirstName;
    user.LastName = LastName || user.LastName;
    user.Birthday = Birthday || user.Birthday;
    user.FavoriteGame = FavoriteGame || user.FavoriteGame;

    await user.save();
    res.json(user);
    } else {
    res.status(404).json({ error: 'User not found' });
    }
} catch (error) {
    res.status(500).json({ error: 'Failed to update the user' });
}
});

// Delete a user by ID
router.delete('/:id', async (req, res) => {
const { id } = req.params;

try {
    const user = await User.findOne({ where: { UserID: id } });
    if (user) {
    await user.destroy();
    res.status(204).send();
    } else {
    res.status(404).json({ error: 'User not found' });
    }
} catch (error) {
    res.status(500).json({ error: 'Failed to delete the user' });
}
});

module.exports = router;

With this userRoutes.js file completed, we only have to do one more thing; edit the app.js file to handle our URI user routes and authenticate our database connection. We can do so by adding the following lines to the file:

const userRoutes = require('./API/users/userRoutes.js');

// Define your variables
const DB_NAME = 'HashAPI';
const DB_USER = 'root';
const DB_PASSWORD = 'rootpassword';
const DB_HOST = 'your-mysql-host.com';
const DB_PORT = '1009';

// Database connection
const sequelize = new Sequelize(DB_NAME, DB_USER, DB_PASSWORD, {
  host: DB_HOST,
  port: DB_PORT,
  dialect: 'mysql'
});

sequelize.authenticate()
  .then(() => console.log('Database connected...'))
  .catch(err => console.log('Error: ' + err));

app.use('/api/users', userRoutes);

All together now:

const express = require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const { Sequelize } = require('sequelize');

const userRoutes = require('./API/users/userRoutes.js');

const app = express();

// Define your variables
const DB_NAME = 'HashAPI';
const DB_USER = 'root';
const DB_PASSWORD = 'rootpassword';
const DB_HOST = 'your-mysql-host.com';
const DB_PORT = '1009';

// Database connection
const sequelize = new Sequelize(DB_NAME, DB_USER, DB_PASSWORD, {
  host: DB_HOST,
  port: DB_PORT,
  dialect: 'mysql'
});

sequelize.authenticate()
  .then(() => console.log('Database connected...'))
  .catch(err => console.log('Error: ' + err));


app.use(cors());
app.use(bodyParser.json());

app.use('/api/users', userRoutes);

// Test route
app.get('/', (req, res) => {
    res.json({ message: 'API is working' });
});

const PORT = 3000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`)
});

Sweet! Now let's start it back up. CTRL C a few times to kill the process and then type in node app.js and enter. We should see this:

PS C:\Users\oster\Desktop\HashAPI> node app.js  
Server is running on port 3000
Executing (default): SELECT 1+1 AS result
Database connected...

Send a test CRUD op to the API via Thunderclient in VS Code

We finally did it! Take a breather, get some water, stretch whatever. We are in the home run now!

Good? Let's finish this project off with a simple CRUD op request. I will be using Thunderclient which is an extension for VS Code but you can do this in Postman or even in the Terminal with CURL command.

Get All Users:

Create User:

💡

You can see it returns the record we just created! Make sure to send a POST request!

Read Specific User:

💡

To get specific user info, just append the UserID integer to the end of the call! Make sure to send a GET request!

Update Specific User:

💡

To update specific user info, just append the UserID to the end of the call and send in the new values in the Body! Make sure to send it as a PUT request!

Delete Specific User:

💡

To delete a specific user, just append the UserID to the end of the call and submit it as a DELETE request! You can see we get a 204 which is the same as a 200 but the server is just telling us nothing was sent back. We could add in logic but for an app, we would handle it locally and return to login/signup!

Well, that took longer than expected to write out! However, we did just create our API using NodeJS, ExpressJS, Sequelize and MySQL! That is something to add to our toolbelt!

That's about it. Just like with all entries in my B.L.O.G. series, I post this to not only share with everyone else who might want to use it but also as a backup and a way for myself to step through solutions I've come up with for different types of problems.

If you enjoyed it, let me know on any of my socials or even just a comment below! Thanks!

Create a NodeJS and ExpressJS API with Sequelize

Comments

More from this blog

Finding locations using User Location and Haversine Formula

Explaining NodeJS and ExpressJS

Create An API with NodeJS and Express

Create a MySQL database and example table

Connecting the MySQL database to the NodeJS Express API

Send a test CRUD op to the API via Thunderclient in VS Code

Command Palette

Comments

More from this blog

Explaining NodeJS and ExpressJS

Create An API with NodeJS and Express

Create a MySQL database and example table

Connecting the MySQL database to the NodeJS Express API

Send a test CRUD op to the API via Thunderclient in VS Code