Automating APIs with Node.js, MySQL, Express and Postman

Introduction

As the amount of data produced every day grows exponentially, automating repetitive tasks with APIs becomes crucial for both developers and organizations. By automating API interactions with Node.js, MySQL, Express, and Postman, we can streamline business processes, increase efficiency, and enhance our overall software development workflow. In this tutorial, we'll explore a step-by-step guide to automating APIs with the mentioned technologies, focusing on real-world examples and applications.

Tutorial: Automating APIs with Node.js, MySQL, Express, and Postman

Objective: By the end of this tutorial, you'll learn how to design, build, and deploy an API that integrates with MySQL and automate interactions using Node.js, Express, and Postman.

System Requirements

• Node.js (install Node.js using the installer on the official website or update your npm version with the command: npm install -g npm@latest)
• MySQL (download and install the community server edition on your machine)
• Express.js (install using: npm install express)
• Postman (install the latest version from the official website or use Postman from Chrome)
• A text editor or Integrated Development Environment (IDE)
• Familiarity with basic HTML, CSS, and JavaScript

Section 1: Setting Up the Development Environment

Step 1: Creating a MySQL Database

Create a new MySQL database, let's name it api_demo. You can use a MySQL client, such as Sequel Ace, or phpMyAdmin, to interact with the database. Run the following SQL query to create a new database:

CREATE DATABASE api_demo;

Step 2: Creating Database Tables

In the newly created api_demo database, create two tables: users and tasks.

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE tasks (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  user_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Section 2: Building the API with Express.js and Node.js

Step 1: Creating the Project Structure

Create a new directory for your project, and add the following folders and files:

project-folder/
    controllers/
        userController.js
    models/
        user.js
        task.js
    routes/
        api.js
    app.js
package.json
README.md

Step 2: Building the Models

In the models folder, create the following files:

• user.js

const sequelize = require('sequelize');

class User extends sequelize.Model {}

module.exports = User.init({
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: Sequelize.STRING
  },
  email: {
    type: Sequelize.STRING,
    unique: true
  }
}, { sequelize });

• task.js

const sequelize = require('sequelize');

class Task extends sequelize.Model {}

module.exports = Task.init({
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  title: {
    type: Sequelize.STRING
  },
  description: {
    type: Sequelize.STRING
  },
  userId: {
    type: Sequelize.INTEGER,
    references: 'users'
  }
}, { sequelize });

Step 3: Creating the API Endpoints with Express.js

In the routes/api.js file, create the following endpoint:

const express = require('express');
const { User } = require('../models/User');
const { Task } = require('../models/Task');
const router = express.Router();

router.post('/register', async (req, res) => {
  const { body } = req;
  try {
    const existingUser = await User.findOne({ where: { email: body.email } });
    if (existingUser) throw new Error('User with this email already exists.');

    const user = await User.create(body);
    res.send(user);
  } catch (error) {
    console.error(error);
    res.status(500).send({ error: error.message });
  }
});

router.get('/tasks', async (req, res) => {
  try {
    const tasks = await Task.findAll({
      attributes: [[sequelize.fn('CONCAT', sequelize.col('title'), ': ', sequelize.col('description')), 'fullName']],
      include: [{
        model: User,
        as: 'author',
        attributes: ['name']
      }]
    });
    res.send(tasks);
  } catch (error) {
    console.error(error);
    res.status(500).send({ error: error.message });
  }
});

router.post('/create-task', async (req, res) => {
  try {
    const { title, description } = req.body;
    const { id: userId } = req.body;

    await Task.create({ title, description, userId });
    res.send({ message: 'Task created successfully!' });
  } catch (error) {
    console.error(error);
    res.status(500).send({ error: error.message });
  }
});

router.put('/update-task', async (req, res) => {
  const { id } = req.body;

  try {
    const task = await Task.findOne({ where: { id } });
    if (!task) {
      return res.status(404).send({ message: 'Task not found.' });
    }

    await task.update(req.body);
    res.send({ message: 'Task updated successfully!' });
  } catch (error) {
    console.error(error);
    res.status(500).send({ error: error.message });
  }
});

module.exports = router;

Step 4: Starting the API

Create a app.js file to start the Express.js app:

const express = require('express');
const router = require('./routes/api');

const app = express();

app.use(express.json());
app.use(router);

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

app.listen(PORT, () => {
  console.log('Server is running on port ' + PORT);
});

Section 3: Testing the API with Postman

1. Download and install Postman on your computer.
2. Go to http://localhost:3000/api/docs to see the API endpoints and descriptions.
3. You can use these endpoints to interact with your API:
   • /register - Post a user object to register a new user.
   • /tasks - Get all tasks. You can add filters based on author.name, title, or description for a detailed query.
   • /create-task - Post a new task including title, description, and userId. The userId corresponds to a registered user in the MySQL database.
   • /update-task - Put an update request including title, description, or any other updates to modify the task in the MySQL database.

Here's how to test an API request with Postman:

• Create a new tab and enter http://localhost:3000/api/create-task
• Change the request body to include "title," "description," and a valid user id
• Press Send to verify the data stored in MySQL database
• After that you should see: {"message":"Task created successfully!"}

These examples illustrate using Postman with the established API using a local, Node.js-powered Express web server running.