CRUD API with Express
Published in CRUD
Prasomsak Khunmuen

Create a CRUD API with Express

The CRUD operations (create, read, update, delete) are the basic functionality of any web application when working with a database. This example will show you how to create the CRUD API with Express and using MySQL as a database.

Prerequisites

  • Node.js
  • MySQL

Setup project

Setting up the Node.js project dependencies.

npm install express mysql2 sequelize

Create a testing database named "example" and run the database.sql file to import the table and data.

Project structure

├─ controllers
│  └─ ProductController.js
├─ models
│  └─ Product.js
├─ public
│  └─ index.html
├─ config.js
├─ db.js
├─ index.js
└─ router.js

Project files

config.js

This file contains the database connection information.

module.exports = {
  host: 'localhost',
  port: 3306,
  user: 'root',
  password: '',
  database: 'example',
  dialect: 'mysql'
}

db.js

This file will be used to create and export the Sequelize instance. Sequelize is an ORM library that makes working with the database easier.

const Sequelize = require('sequelize')
const config = require('./config')

module.exports = new Sequelize(config.database, config.user, config.password, {
  host: config.host,
  port: config.port,
  dialect: config.dialect,
  define: {
    timestamps: false,
    freezeTableName: true
  }
})
  • timestamps: false does not utilize the auto-generate timestamp feature (createdAt, updateAt columns).
  • freezeTableName: true use the model name as the table name, without any modifications.

router.js

This file will define the URL routes of our web application to handle the incoming requests.

const express = require('express')
const product = require('./controllers/ProductController.js')

module.exports = express.Router().use('/products', express.Router()
  .get('/', product.getProducts)
  .get('/:id', product.getProduct)
  .post('/', product.createProduct)
  .put('/:id', product.updateProduct)
  .delete('/:id', product.deleteProduct)
)
  • We create the base URL route by use express.Router().use('/products', ...)
  • Then create each CRUD route inside it and use the appropriate HTTP Method (GET, POST, PUT, DELETE) for each operation.

index.js

This file is the main entry point of our application. It will create and setting up the Express server.

const express = require('express')
const router = require('./router.js')

const app = express()
app.use(express.json())
app.use(express.static('public'))
app.use('/api', router)
app.listen(8000)
  • express.json() parses the request body as a JSON object, so we can access it later by using request.body in our Controller.
  • express.static('public') will serve the static resource inside the public folder. (We used to serve index.html as a default page)

models/Product.js

This file defines the Model information that mapping to our database table named "Product". This model will be used when working with CRUD operations later.

const Sequelize = require('sequelize')
const db = require('../db')

module.exports = db.define('Product', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: Sequelize.STRING,
  price: Sequelize.DECIMAL
})

controllers/ProductController.js

This file defines all functions required to handle incoming requests and perform any CRUD operations.

const Product = require('../models/Product')

exports.getProducts = async (req, res) => {
  let products = await Product.findAll()
  res.send(products)
}

exports.getProduct = async (req, res) => {
  let product = await Product.findByPk(req.params.id)
  res.send(product)
}

exports.createProduct = async (req, res) => {
  let product = { ...req.body }
  let created = await Product.create(product)
  res.send(created)
}

exports.updateProduct = async (req, res) => {
  let product = { ...req.body }
  await Product.update(product, { where: { id: req.params.id } })
  let updated = await Product.findByPk(req.params.id)
  res.send(updated)
}

exports.deleteProduct = async (req, res) => {
  await Product.destroy({ where: { id: req.params.id } })
  res.end()
}
  • We utilize req.body as the input data.
  • And use the Product model to perform any CRUD operations on the database by use the basic methods such as findAll, create, update and destroy.

public/index.html

This file will be used to create a basic user interface for testing our API.

<!DOCTYPE html>
<head>
    <style>
        li {
            margin-bottom: 5px;
        }
        textarea {
            width: 100%;
        }
    </style>
</head>
<body>
    <h1>Example CRUD</h1>
    <ul>
        <li><button onclick="getProducts()">Get Products</button></li>
        <li><button onclick="getProduct()">Get Product</button></li>
        <li><button onclick="createProduct()">Create Product</button></li>
        <li><button onclick="updateProduct()">Update Product</button></li>
        <li><button onclick="deleteProduct()">Delete Product</button></li>
    </ul>
    <textarea id="text_response" rows="20"></textarea>
    <script>
        function showResponse(res) {
            res.text().then(text => {
                let contentType = res.headers.get('content-type')
                if (contentType && contentType.startsWith('application/json')) {
                    text = JSON.stringify(JSON.parse(text), null, 4)
                }
                document.getElementById('text_response').innerHTML = text
            })
        }
        function getProducts() {
            fetch('/api/products').then(showResponse)
        }
        function getProduct() {
            let id = prompt('Input product id')
            fetch('/api/products/' + id).then(showResponse)
        }
        function createProduct() {
            let name = prompt('Input product name')
            let price = prompt('Input product price')
            fetch('/api/products', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: JSON.stringify({ name, price })
            }).then(showResponse)
        }
        function updateProduct() {
            let id = prompt('Input product id to update')
            let name = prompt('Input new product name')
            let price = prompt('Input new product price')
            fetch('/api/products/' + id, {
                method: 'PUT',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: JSON.stringify({ name, price })
            }).then(showResponse)
        }
        function deleteProduct() {
            let id = prompt('Input product id to delete')
            fetch('/api/products/' + id, {
                method: 'DELETE'
            }).then(showResponse)
        }
    </script>
</body>
</html>
  • Many other articles will use Postman as the HTTP client to test the API, but this article I will use JavaScript instead. This will help you understand more detail when working with HTTP request on the client-side.
  • To keep this file is clean and readable, we will only use basic HTML and JavaScript. There are no additional libraries such as the CSS Framework or Axios here.
  • All CRUD functions will use the appropriate HTTP Method to invoke the API.
  • showResponse(res) will formatting the JSON object to makes it easier to read.

Run project

node index.js

Open the web browser and goto http://localhost:8000

Testing

Get All Products

Click the "Get Products" button. The API will return all products data.

Get All Products

Get Product By Id

Click the "Get Product" button and enter "1" for the product id. The API will return a product data.

Get Product By Id

Create Product

Click the "Create Product" button and enter "test-create" for the product name and "100" for the price. The API will return a newly created product.

Create Product

Update Product

Click the "Update Product" button and enter "101" for the product id and "test-update" for the name and "200" for the price. The API will return an updated product.

Update Product

Delete Product

Click the "Delete Product" button and enter "101" for the product id. The API will return nothing, which is acceptable as we do not return anything from our API.

Delete Product

Conclusion

In this article, you have learned how to create and settings up the Express server in order to create a CRUD API. Utilize Sequelize as an ORM to perform the CRUD operations on the database. Test our API using JavaScript. I hope you will enjoy the article.

Source code: https://github.com/StackPuz/Example-CRUD-Express

Create a CRUD Web App: https://stackpuz.com

Related post

Recommended Topics

StackPuz is the Web App Code Generator. It will help you bootstrap a new project FASTER. Please check out it on stackpuz.com