How to build a basic CRUD Application with Node and MySQL

How to build a basic CRUD Application with Node and MySQL

In this post, you’ll learn how to build a basic CRUD (Create, Read, Update, Delete) application and secure the application with Node and MySQL using Okta

Originally published by Ivo Katunaric at

NodeJS + Express is a popular technology stack for building APIs and backend services. Often times a backend database is required. There are several popular relational databases used in both enterprise and hobby projects. MySQL’s spike in popularity came with the rise of PHP during the early 2000s and today - more than 20 years after the initial release - it’s used on a wide array of technology stacks.

In this post, you’ll learn how to build a basic CRUD (Create, Read, Update, Delete) application and secure the application with Node and MySQL using Okta. You’ll be making a simple Yelp-style backend to rate restaurants called “FeedMeWell.” Every restaurant will have a list of dishes it offers, and all registered users will be able to rate the restaurants. The system will then calculate the average rating for each restaurant based on the ratings.

Please note that you shouldn’t read this blog post on an empty stomach!

  • NodeJS version 8+ (although these instructions should work with any version)
  • docker and docker-compose (A light layer of virtualized Linux machine you’ll be using to run the MySQL server)
  • A free Okta developer account for easy authentication

That’s everything you’ll need to set up and run the project!

During the npm install phase, you will install the following dependencies:

  • TypeScript (Typed superset of JavaScript)
  • TypeORM (Object Relational Mapper for TypeScript and JavaScript)
  • Express (Fast, unopinionated, minimalist web framework for Node)

Initialize the Node + Express Project and Add Dependencies

Open a terminal and cd into a directory where you want your project created.

For example:

mkdir feed-me-well
cd feed-me-well
npm init --yes # This will create a package.json file used for dependency management.
npm install @okta/[email protected] @okta/[email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected]
npx ts-typie # This will add TypeScript types for all the dependencies that manage their typings separately in `` repo)

Create a Virtual MySQL Docker Machine

To avoid polluting your development machine with all the dependencies, use docker-compose to set up a database for development purposes.

To do this, create a docker-compose.yml file that defines the kinds of virtual machines your project requires. This project only requires one docker container for MySQL.

version: '3.1'

services: okta-feed-me-well-db:   container_name: okta-feed-me-well-db   image: mysql   command: --default-authentication-plugin=mysql_native_password   restart: always   ports:     - 3389:3306   environment:     MYSQL_ROOT_PASSWORD: example     MYSQL_DATABASE: okta-feed-me-well-db     MYSQL_USER: user     MYSQL_PASSWORD: password

Please note that you should use a more secure password if you create a production MySQL environment from this docker file.

After creating that docker-compose.yml file, you can start the virtual machine by running this command in the project root directory:

docker-compose up -d 

Depending on your internet connection speed, the first run might take some time to download the mysql docker image.

All later re-runs will be much faster.

Define the run environment for your app

Even though all the info on the database already exists in the docker-compose.yml file, you now need to provide that information to the server.

To do this, create a .env file in the root of the project:


You now need to tell TypeORM how to connect to the MySQL database you just created. To do this, create an ormconfig.js file:

require('dotenv/config'); // load everything from .env file into the process.env variable


module.exports = [{  name: 'default',  type: 'mysql',  host: 'localhost',  port: DB_PORT,  username: DB_USERNAME,  password: DB_PASSWORD,  database: DB_DATABASE,  synchronize: true,  entities: [   "src/models/.ts"  ],  subscribers: [   "src/subscribers/*.ts"  ],  migrations: [   "src/migrations/.ts"  ],  cli: {   entitiesDir: "src/models",   migrationsDir: "src/migrations",   subscribersDir: "src/subscribers"  } }];

Create a Node.js Entry File

The entry point for the backend is server.js so you can run the server using the npm start command.

The file will load all the environmental variables from .env into process.env and set up the project for dynamic transpilation.

This is what the server.js should look like:


require('./src/bootstrap.ts')  .bootstrap()  .catch(console.error);

File ./src/bootstrap.ts boots the server. To make this process possible, you must create tsconfig.json in the project’s root:

  "compilerOptions": {
    "module": "commonjs",
    "esModuleInterop": true,
    "target": "es6",
    "noImplicitAny": false,
    "moduleResolution": "node",
    "sourceMap": true,
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true

Add User Authentication to Your Node.js app with Okta

Okta is a free API service that allows you to create users, handle user authentication, authorization, multi-factor authentication and more quickly and easily. By using Okta, you avoid writing time-consuming and potentially insecure authentication logic yourself.

Sign up for a forever-free developer account and then continue below.

Once you create your Okta account and log into your Okta dashboard, click on the Applications menu item. Then click Add Application. From the app creation wizard, select Web, and then click Next.

On the Application Settings screen, give your application a name and copy in the following app settings:

Once complete, scroll down and take a look at the Client Credentials. You’re going to need this information shortly to integrate your web app with Okta. These settings (your Client ID and Secret) are your application’s OpenID Connect credentials.

Reopen your .env file and append these values:


Interface with Okta’s authentication service

To provide information about the user that’s making a request and to protect the server from unwanted requests, we’ll create two Express middlewares to delegate that job to Okta’s authentication service.

Export those middlewares (initializeAuthentication and authenticateUser) from the src/services/okta.ts file:

import session from 'express-session';
import express from 'express';
import { ExpressOIDC } from '@okta/oidc-middleware';
import OktaJwtVerifier from '@okta/jwt-verifier';
import { JsonErrorResponse } from 'express-with-json';
// import { assertUser } from './user'; // we're going to need this import later
const issuer = ${process.env.OKTA_ORG_URL}/oauth2/default;
export function initializeAuthentication(app: express.Application, port: number) {
const oidc = new ExpressOIDC({
   client_id: process.env.OKTA_CLIENT_ID,
   client_secret: process.env.OKTA_CLIENT_SECRET,
   appBaseUrl: process.env.APP_BASE_URL || http://localhost:${port},
   scope: 'openid profile'
   secret: process.env.APP_SECRET,
   resave: true,
   saveUninitialized: false
app.get('/', oidc.ensureAuthenticated(), (req: any, res) => {
return oidc;
const oktaJwtVerifier = new OktaJwtVerifier({
clientId: process.env.OKTA_CLIENT_ID
export async function authenticateUser(req: express.Request) {
const { authorization } = req.headers;
if (!authorization) {
const [authType, token] = authorization.split(' ');
if (authType !== 'Bearer') {
   throw new JsonErrorResponse({ error: 'Expected a Bearer token' }, { statusCode: 400 });
const { claims: { sub } } = await oktaJwtVerifier.verifyAccessToken(token, 'api://default');
// req.user = await assertUser(sub); // we're going to use this line as soon as we define User model
export async function requireUser(req: express.Request) {
if (!req.user) {
   throw new JsonErrorResponse({ error: 'You must send an Authorization header' }, { statusCode: 400 });

Bootstrap Your Express Server

With authentication ready, you can now bootstrap the server. The bootstrap logic contains:

  • a function to load all of the project’s controllers
  • a generic error handler
  • a persistent connection to the MySQL database
  • an Express server instance
  • a handler for TypeORM’s “EntityNotFound” exception

That last one is useful to avoid having to handle an EntityNotFound situation in every request handler.

To make it all happen, create a new file called src/bootstrap.ts:

import { createConnection } from 'typeorm';
import express from 'express';
import withJson from 'express-with-json'
import glob from 'glob';
import path from 'path';
import bodyParser from 'body-parser';
import { EntityNotFoundError } from 'typeorm/error/EntityNotFoundError';
import { authenticateUser, initializeAuthentication } from './services/okta';
const port = 3000;
function findAllControllers() {
return glob
   .sync(path.join(__dirname, 'controllers/*'), { absolute: true })
   .map(controllerPath => require(controllerPath).default)
   .filter(applyController => applyController);
function errorHandler(error, req, res, next) {
if (!error) {
   return next();
if (error) {
   res.json({ error: error.message });
export function entityNotFoundErrorHandler(error, req, res, next) {
if (!(error instanceof EntityNotFoundError)) {
   return next(error);
res.json({ error: 'Not Found' });
export async function bootstrap() {
await createConnection();
const app = withJson(express());
initializeAuthentication(app, port);
findAllControllers().map(applyController => applyController(app));
app.listen(port, () => console.log('Listening on port', port));
return app;

 After creating the bootstrap.ts file, you can now run the server like so:

npm start 

Open http://localhost:3000 in your browser, authenticate with Okta, and receive a JWT token to authenticate with the still non-existent API for the “FeedMeWell” app.

Define Database Model Relationships

Every good app must have a clear database model. According to the requirements for the FeedMeWell app I gave at the beginning of this post, these are the relations between the entities of this project:

User has many Restaurants <-> Restaurant has one User owner

User has many Ratings <-> Rating has one User author

Restaurant has many Ratings <-> Rating has one Restaurant

Restaurant has many FoodDishes <-> FoodDish has one Restaurant

Create models for MySQL database based on the entities’ relations

You need a model for the User table. It will contain only a minimum amount of data because all the user info is stored by Okta.

Create a src/models/user.ts file:

import { Column, Entity, OneToMany, PrimaryColumn } from 'typeorm';
import { Restaurant } from './restaurant';
import { Rating } from './rating';
export class User {
  @PrimaryColumn({ generated: 'increment' })
  id: number;
  @Column({ unique: true })
  oktaUserId: string;
  @OneToMany(() => Restaurant, restaurant => restaurant.creator)
  restaurants: Promise<Array<Restaurant>>;
  @OneToMany(() => Rating, rating => rating.creator)
  ratings: Promise<Array<Rating>>;

Notice the references for the still-missing ./restaurant and ./rating models? You should create those, too.

This is the content of your new Restaurant model called src/models/restaurant.ts:

import { Entity, Column, OneToMany, PrimaryColumn, ManyToOne } from 'typeorm';
import { FoodDish } from './food-dish';
import { User } from './user';
import { Rating } from './rating';
export class Restaurant {
  @PrimaryColumn({ generated: 'increment' })
  id: number;
  @Column({ unique: true })
  name: string;
  description: string;
  address: string;
  @OneToMany(() => FoodDish, foodDish =>
 foodDishes: Promise<Array<FoodDish>>;
  creatorId: number;
  @ManyToOne(() => User, user => user.restaurants)
  creator: Promise<User>;
  @OneToMany(() => Rating, rating =>
  ratings: Promise<Array<Restaurant>>;
  @Column({ nullable: true })
  averageRating: number;

And this is what the model for Rating table should look like in the src/models/rating.ts file:

import { Column, Entity, ManyToOne, OneToMany, PrimaryColumn, Unique } from 'typeorm';
import { Restaurant } from './restaurant';
import { User } from './user';

@Entity() @Unique(['restaurantId', 'creatorId']) export class Rating {  @PrimaryColumn({ generated: 'increment' })  id: number;

 @Column({ type: 'integer' })  rating: number;

 @Column()  text: string;

 @Column()  restaurantId: number;

 @ManyToOne(() => Restaurant, restaurant => restaurant.ratings)  restaurant: Promise<Restaurant>;

 @Column()  creatorId: number;

 @ManyToOne(() => User, user => user.ratings)  creator: Promise<User>; }

The last model persists the information about all those delicacies served in the restaurants.

Create the src/models/food-dish.ts file:

import {Entity, Column, ManyToOne, PrimaryColumn, Unique} from 'typeorm';
import { Restaurant } from './restaurant';

@Entity() @Unique(['restaurantId', 'name']) export class FoodDish {  @PrimaryColumn({ generated: 'increment' })  id: number;

 @Column()  name: string;

 @Column()  description: string;

 @Column({ type: 'integer' })  priceInCents: number;

 @Column()  restaurantId: number;

 @ManyToOne(() => Restaurant, restaurant => restaurant.foodDishes)  restaurant: Promise<Restaurant>; }

That’s it as far as the MySQL is concerned!

Now you just need to implement the missing assertUser function. Do this by creating a src/services/user.ts file:

import { getManager } from 'typeorm';
import { User } from '../models/user';

export async function assertUser(oktaUserId: string) {  const manager = getManager();  const existingUser = await manager.findOne(User, { where: { oktaUserId } });  if (existingUser) {    return existingUser;  }

 const user = new User();  user.oktaUserId = oktaUserId;  return await; }

This function is used to synchronize user data between Okta and the MySQL database. Once the file is created, make sure to uncomment those two lines including the assertUser function in the src/services/user.ts file:

import { assertUser } from './user';
req.user = await assertUser(sub);

This change attaches the authenticated user’s information to the request object.

After creating the models and defining the relations between them, TypeORM will make sure all those MySQL tables are created on the next run.

Finally, terminate the running server and restart it using the npm start command.

Connect Node App to MySQL Database

After restarting the server, check the database by running mysql commands in the docker container:

docker-compose exec okta-feed-me-well-db mysql -u user -p okta-feed-me-well-db -p -e "select * from food_dish"
docker-compose exec okta-feed-me-well-db mysql -u user -p okta-feed-me-well-db -p -e "select * from rating"
docker-compose exec okta-feed-me-well-db mysql -u user -p okta-feed-me-well-db -p -e "select * from restaurant"
docker-compose exec okta-feed-me-well-db mysql -u user -p okta-feed-me-well-db -p -e "select * from user"

Don’t forget to type in “password” when the program asks you for the password.

Add CRUD Functionality to Your Node + MySQL App

(C)reating, (R)eading, (U)pdating and (D)eleting are the most basic functions of every web app. Let’s add all that functionality to the restaurant resource.

Create a file called src/controllers/restaurants.ts:

import express from 'express';
import { getManager } from 'typeorm';

import { Restaurant } from '../models/restaurant'; import { requireUser } from '../services/okta'; import { IExpressWithJson, JsonErrorResponse } from 'express-with-json/dist'; import { User } from '../models/user';

function isRestaurantCreatedBy(restaurant: Restaurant, user: User) {  return restaurant.creatorId ===; }

export async function createRestaurant(req: express.Request) {  const { address, description, name, } = req.body;

 const restaurant = new Restaurant();  restaurant.creatorId =;  restaurant.address = address;  restaurant.description = description; = name;

 const manager = getManager();  return await; }

export async function removeRestaurant(req: express.Request) {  const { id } = req.params;  const manager = getManager();  const restaurant = await manager.findOneOrFail(Restaurant, id);

 if (!isRestaurantCreatedBy(restaurant, req.user)) {    throw new JsonErrorResponse({ error: 'Forbidden' }, { statusCode: 403 });  }  await manager.remove(restaurant);  return { ok: true }; }

export async function getAllRestaurants() {  const manager = getManager();

 return await manager.find(Restaurant); }

export async function getRestaurant(req: express.Request) {  const { id } = req.params;  const manager = getManager();

 return await manager.findOneOrFail(Restaurant, id); }

export async function updateRestaurant(req: express.Request) {  const { id } = req.params;  const { address, description, name, } = req.body;  const manager = getManager();

 const restaurant = await manager.findOneOrFail(Restaurant, id);  if (!isRestaurantCreatedBy(restaurant, req.user)) {    throw new JsonErrorResponse({ error: 'Forbidden' }, { statusCode: 403 });  }

 restaurant.address = address;  restaurant.description = description; = name;

 return await; }

export default (app: IExpressWithJson) => {  app.postJson('/restaurants', requireUser, createRestaurant);  app.deleteJson('/restaurants/:id', requireUser, removeRestaurant);  app.getJson('/restaurants', getAllRestaurants);  app.getJson('/restaurants/:id', getRestaurant);  app.patchJson('/restaurants/:id', requireUser, updateRestaurant); }

After adding these functions, you should be able to create a restaurant.

Launch the application, browse to http://localhost:3000 and login. After logging in, the security token returned from Okta will be displayed in the browser window. Copy this token.

To create a restaurant, execute this cURL request (replace TOKEN with the actual token):

curl -X POST http://localhost:3000/restaurants 
 -H 'Authorization: Bearer TOKEN'
 -H 'Content-Type: application/json'
 -d '{   "address": "Test Address 125",   "description": "The best restaurant to test your API",   "name": "Testing Food" }'

And to view all the restaurants in the database, execute this cURL request:

curl -X GET http://localhost:3000/restaurants

To delete the created restaurant:

curl -X DELETE http://localhost:3000/restaurants/1 -H 'Authorization: Bearer TOKEN' 

Now re-run the POST action to recreate the restaurant. You’ll need it in the next section.

There are an additional two endpoints I didn’t go throughs, one for fetching a single restaurant and one for changing an existing restaurant. Try calling them yourself using cURL or Postman!

Add Functionality to Create and Fetch Dishes

Every (Ok, most) restaurant has multiple dishes. Create a new controller src/controllers/food-dishes.ts so you can enter the dishes into the restaurant

import express from 'express';
import { getManager } from 'typeorm';
import { IExpressWithJson } from 'express-with-json';
import { FoodDish } from '../models/food-dish';
import { requireUser } from '../services/okta';
import { Restaurant } from '../models/restaurant';

export async function createFoodDish(req: express.Request) {  const { restaurantId } = req.params;  const manager = getManager();  await manager.findOneOrFail(Restaurant, restaurantId);

 const { description, name, priceInCents } = req.body;

 const foodDish = new FoodDish();  foodDish.description = description; = name;  foodDish.priceInCents = parseInt(priceInCents);  foodDish.restaurantId = parseInt(restaurantId);

 return; }

export async function getRestaurantFoodDishes(req: express.Request) {  const { restaurantId } = req.params;

 return await getManager().find(FoodDish, { where: { restaurantId } }); }

export default (app: IExpressWithJson) => {  app.postJson('/restaurants/:restaurantId/food-dishes', requireUser, createFoodDish);  app.getJson('/restaurants/:restaurantId/food-dishes', getRestaurantFoodDishes); }

Create a food dish for your new favorite restaurant:

curl -X POST 
 http://localhost:3000/restaurants/2/food-dishes  -H 'Authorization: Bearer TOKEN'
 -H 'Content-Type: application/json'
 -d '{   "description": "Exclusive dish made entirely of cURL",   "name": "testing food dish",   "priceInCents": 5000 }'

Check out all the dishes of your favorite restaurant:

curl -X GET http://localhost:3000/restaurants/2/food-dishes

Add Rating Functionality

To allow users to rate restaurants, you must implement a controller for rating functionality src/controllers/ratings.ts:

import { getManager } from 'typeorm';
import express from 'express';
import { IExpressWithJson, JsonErrorResponse } from 'express-with-json';
import { Rating } from '../models/rating';
import { requireUser } from '../services/okta';

export async function createRating(req: express.Request) {  const { restaurantId } = req.params;  const { rating: ratingString, text } = req.body;

 const ratingNumber = parseInt(ratingString);  if (ratingNumber < 0 || ratingNumber > 5) {    throw new JsonErrorResponse({ error: 'Rating must be between 1 and 5' }, { statusCode: 400 });  }

 const rating = new Rating();  rating.creatorId =;  rating.rating = ratingNumber;  rating.restaurantId = parseInt(restaurantId);  rating.text = text;

 return await getManager().save(rating); }

export async function getRestaurantRatings(req: express.Request) {  const { restaurantId } = req.params;

 return await getManager().find(Rating, { where: { restaurantId } }); }

export default function(app: IExpressWithJson) {  app.postJson('/restaurants/:restaurantId/ratings', requireUser, createRating);  app.getJson('/restaurants/:restaurantId/ratings', getRestaurantRatings); }

Again, use cURL to create a rating for an existing restaurant:

curl -X POST 
 -H 'Authorization: Bearer TOKEN'
 -H 'Content-Type: application/json'
 -d '{   "rating": 5,   "text": "This is the best restaurant I'''ve ever POSTed" }'

You can now view all the ratings for any given restaurant:

curl -X GET http://localhost:3000/restaurants/2/ratings

If you try fetching the restaurants, you’ll notice that they are all missing the averageRating field. That’s because it hasn’t been set yet. It must be set and then updated with every rating change.

Create a TypeORM subscriber src/subscribers/restaurant-rating-subscriber.ts for this purpose:

import { Rating } from '../models/rating';
import {
} from 'typeorm';
import { Restaurant } from '../models/restaurant';

async function getAverageRating(manager: EntityManager, restaurantId: number): Promise<number> {  const response = await manager.query(    select AVG(rating) as averageRating from rating where rating.restaurantId = ${restaurantId}  );

 return response[0].averageRating; }

async function recalculateAverageRating(manager: EntityManager, restaurantId: number) {  const restaurant = await manager.findOneOrFail(Restaurant, restaurantId);  restaurant.averageRating = await getAverageRating(manager, restaurantId);  await; }

@EventSubscriber() export class RestaurantRatingSubscriber implements EntitySubscriberInterface<Rating> {  listenTo() {    return Rating;  }

 async afterInsert(event: InsertEvent<Rating>) {    await recalculateAverageRating(event.manager, event.entity.restaurantId);  }

 async afterUpdate(event: UpdateEvent<Rating>) {    await recalculateAverageRating(event.manager, event.entity.restaurantId);  }

 async afterRemove(event: RemoveEvent<Rating>) {    await recalculateAverageRating(event.manager, event.entity.restaurantId);  } }

Now try deleting all the ratings:

docker-compose exec okta-feed-me-well-db mysql -u user -p okta-feed-me-well-db -p -e "delete from rating"

And then recreate the ratings from before:

curl -X POST 
 -H 'Authorization: Bearer TOKEN'
 -H 'Content-Type: application/json'
 -d '{   "rating": 5,   "text": "This is the best restaurant I'''ve ever POSTed" }'

Restaurant with ID 2 now contains a valid averageRating.

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Further reading

Best 50 Nodejs interview questions from Beginners to Advanced in 2019

Laravel 6 CRUD Application Tutorial

Angular 8 + Spring Boot 2.2: Build a CRUD App Today!

Angular 8 Tutorial - Understanding Directory Structure & Creating CRUD App

node-js mysql web-development

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Hire Node.JS Developers | Skenix Infotech

We are providing robust Node.JS Development Services with expert Node.js Developers. Get affordable Node.JS Web Development services from Skenix Infotech.

How to Hire Node.js Developers And How Much Does It Cost?

A Guide to Hire Node.js Developers who can help you create fast and efficient web applications. Also, know how much does it cost to hire Node.js Developers.

Top Node.js Development Companies and Expert NodeJS Developers

A thoroughly researched list of top NodeJS development companies with ratings & reviews to help hire the best Node.JS developers who provide development services and solutions across the world. List of Leading Node.js development Service Providers...

Hire Web Developer

Looking for an attractive & user-friendly web developer?, a leading web, and mobile app development company, offers web developers for hire through flexible engagement models. You can **[Hire Web...

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

Compare ScaleGrid MySQL vs. DigitalOcean Managed Databases - See which offers the best MySQL throughput, latency, and pricing on DigitalOcean across workloads.