Build a Simple CRUD Application with Node and MySQL
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 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
anddocker-compose
(A light layer of virtualized Linux machine you’ll be using to run theMySQL
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 ofJavaScript
)TypeORM
(Object Relational Mapper forTypeScript
andJavaScript
)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/jwt-verifier@1.0.0 @okta/oidc-middleware@2.0.0 body-parser@1.19.0 dotenv@8.0.0 express@4.17.1 express-session@1.16.2 express-with-json@0.0.6 glob@7.1.4 mysql@2.17.1 reflect-metadata@0.1.13 ts-node@8.3.0 typeorm@0.2.18 typescript@3.5.3
npx ts-typie # This will add TypeScript types for all the dependencies that manage their typings separately in `https://github.com/DefinitelyTyped/DefinitelyTyped` 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:
DB_PORT=3389
DB_USERNAME=user
DB_PASSWORD=password
DB_DATABASE=okta-feed-me-well-db
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
const { DB_PORT, DB_USERNAME, DB_PASSWORD, DB_DATABASE } = process.env;
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('dotenv/config');
require('reflect-metadata');
require('ts-node/register');
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:
OKTA_ORG_URL=https://{yourOktaDomain}
OKTA_CLIENT_ID={yourClientId}
OKTA_CLIENT_SECRET={yourClientSecret}
APP_SECRET=secret
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({
issuer,
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'
});
app.use(session({
secret: process.env.APP_SECRET,
resave: true,
saveUninitialized: false
}));
app.use(oidc.router);
app.get('/', oidc.ensureAuthenticated(), (req: any, res) => {
res.send(req.userContext.tokens.access_token);
});
return oidc;
}
const oktaJwtVerifier = new OktaJwtVerifier({
issuer,
clientId: process.env.OKTA_CLIENT_ID
});
export async function authenticateUser(req: express.Request) {
const { authorization } = req.headers;
if (!authorization) {
return;
}
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.status(500);
res.json({ error: error.message });
}
console.error(error);
}
export function entityNotFoundErrorHandler(error, req, res, next) {
if (!(error instanceof EntityNotFoundError)) {
return next(error);
}
res.status(401);
res.json({ error: 'Not Found' });
}
export async function bootstrap() {
await createConnection();
const app = withJson(express());
app.useAsync(authenticateUser);
app.use(bodyParser.json());
initializeAuthentication(app, port);
findAllControllers().map(applyController => applyController(app));
app.use(entityNotFoundErrorHandler);
app.use(errorHandler);
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';
@Entity()
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';
@Entity()
export class Restaurant {
@PrimaryColumn({ generated: 'increment' })
id: number;
@Column({ unique: true })
name: string;
@Column()
description: string;
@Column()
address: string;
@OneToMany(() => FoodDish, foodDish => foodDish.restaurant)
foodDishes: Promise<Array<FoodDish>>;
@Column()
creatorId: number;
@ManyToOne(() => User, user => user.restaurants)
creator: Promise<User>;
@OneToMany(() => Rating, rating => rating.restaurant)
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 manager.save(user);
}
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 === user.id;
}
export async function createRestaurant(req: express.Request) {
const { address, description, name, } = req.body;
const restaurant = new Restaurant();
restaurant.creatorId = req.user.id;
restaurant.address = address;
restaurant.description = description;
restaurant.name = name;
const manager = getManager();
return await manager.save(restaurant);
}
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;
restaurant.name = name;
return await manager.save(restaurant);
}
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;
foodDish.name = name;
foodDish.priceInCents = parseInt(priceInCents);
foodDish.restaurantId = parseInt(restaurantId);
return manager.save(foodDish);
}
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 = req.user.id;
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 \
http://localhost:3000/restaurants/2/ratings \
-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 {
EntityManager,
EntitySubscriberInterface,
EventSubscriber,
InsertEvent,
RemoveEvent,
UpdateEvent
} 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 manager.save(restaurant);
}
@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 \
http://localhost:3000/restaurants/2/ratings \
-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
.
Learn More About Node.js, MySQL, Express and User Auth!
If you’ve stuck with me to this point, you have successfully setup a virtual MySQL environment, connected it to a bootstrapped Express app, built out restaurant rating functionality, and made it all secure with user auth! Nice job.
Now that you’re on a roll learning new stuff, keep it going with these other posts related to the topics and technologies we used in this article:
- MySQL vs PostgreSQL – Choose the Right Database for Your Project
- Build a REST API with Node and Postgres
- Modern Token Authentication in Node with Express
- Build a Simple REST API with Node and OAuth 2.0
Questions? Requests for a future post? Drop them in the comments! And don’t forget to follow @oktadev on Twitter and subscribe on Youtube.
Okta Developer Blog Comment Policy
We welcome relevant and respectful comments. Off-topic comments may be removed.