Your Guide to Building a NodeJS, TypeScript Rest API with MySQL
MySQL is undoubtedly one of the top picks for a relational database in every Node developer’s technology stack. Node’s ease of creating backend APIs paired with MySQL’s ability to support complex query operations provides a simple way for developers to build advanced web backends.
In this tutorial, we are going to develop a simple REST API for an online store with Express framework. MySQL is our choice of database. Instead of using simple Javascript for the implementation, we decided to build this API using Typescript.
Typescript type support leaves little room for developers to misuse types. It helps us write cleaner, reusable code. If you are a beginner to Typescript or want to brush up your memory of the language, read our guide to Typescript for Javascript developers before going to the next step.
With the initial introduction out of the way, let’s get started now.
Before We Begin…
Before we begin the tutorial, make sure you have all the tools we need set up. Assuming you already have Node.js installed, install MySQL on your device before continuing.
Set up the database
As I mentioned before, we are creating an API for a simple online store that stores a list of products and registered customers in its database. When customers place product orders, their details are also stored in this database.
In total, our database schema has 3 tables: Product, Customer, and ProductOrder.
Data Model
I am going to create them using regular SQL queries. If you want, you can go ahead and use a tool GUI tool to create the database schema.
Make sure your MySQL server is running and then run this command on the command-line. (You’d have to add MySQL to environment variables to directly use the mysql command).
mysql -u <username> -p <password>
It will take you to the MySQL shell where you can directly run SQL queries on the database.
Now, we can create a new database for our project.
create database OnlineStore
Use the following command to switch to the newly created database.
use OnlineStore;
Then, run the following queries to create the tables we need.
CREATE TABLE Product (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
description VARCHAR(255),
instock_quantity INT,
price DECIMAL(8, 2)
);
CREATE TABLE Customer (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
password VARCHAR(255),
email VARCHAR(255) UNIQUE
);
CREATE TABLE ProductOrder (
order_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
customer_id INT,
product_quantity INT,
FOREIGN KEY (product_id) REFERENCES Product(id),
FOREIGN KEY (customer_id) REFERENCES Customer(id)
);
Use queries similar to the ones below to enter some data into the created tables.
INSERT INTO Product VALUES (1, "Apple MacBook Pro", "15 inch, i7, 16GB RAM", 5, 667.00);
INSERT INTO Customer VALUES (1, "Anjalee", "2w33he94yg4mx88j9j2hy4uhd32w", "anjalee@gmail.com");
INSERT INTO ProductOrder VALUES (1, 1, 1, 1);
Great! Our database schema is now complete. We can head over to Node.js and start with the implementation of the API in the next step.
Set Up Node.js Project Environment
As usual, we use the npm init command to initialize our Node.js project as the first step of setup.
Next, we have to install the npm packages we’ll be using in this project. There are quite a few. We’ll install the project dependencies first.
npm install express body-parser mysql2 dotenv
Here, we use dotenv to import environment variables to the project and mysql2 to manage the database connection.
Then, install Typescript as a development dependency.
npm install typescript --save-dev
We also have to install the Typescript type definitions for the packages we are using in the project. Since most of these packages do not have type definitions, we use the @types npm namespace, where relevant type definitions are hosted in the Definitely Typed project.
npm install @types/node @types/express @types/body-parser @types/mysql @types/dotenv --save-dev
Next, we should initialize our project as a Typescript project. For this, run the following command.
npx tsc --init
This will add the tsconfig.json file to your project. We use it to configure Typescript options related to the project.
When you open the tsconfig.json file, you will see a bunch of commented code. For our project, we need to uncomment the following options and set them to the values shown below.
"compilerOptions": {
"target": "es6",
"module": "commonjs",
"outDir": "dist",
"strict": true,
"esModuleInterop": true,
"experimentalDecorators": true,
"emitDecoratorMetadata": true,
}
These options will be considered at the time Typescript is compiled into Javascript. The outDir we have provided here is where the compiled .js files are going to be stored.
As the final step, we have to modify the start script in the package.json file to compile Typescript before starting the Node app.
"scripts": {
"start": "tsc && node dist/app.js",
},
The dist/app.js file is the compiled version of the app.ts file we are using to write our code.
The Project Directory Structure
Our project has a simple directory structure similar to the following chart.
|-.env
|-package.json
|-tsconfig.json
|-dist/
|-app.ts
|-db.ts
|-models/
|-routes/
|-types/
Create the .Env File
We use the .env file store the environment variables of the app.
PORT=3000
DB_HOST="localhost"
DB_USER="username"
DB_PWD="****"
DB_NAME="OnlineStore"
As you can see, most of the environment variables are related to the database we previously created.
Define New Types for the API
We have to define new Typescript types for Product, Customer, and Order objects. We are storing all the type files in the types directory.
//file types/customer.ts
export interface BasicProduct {
id: number,
}
export interface Product extends BasicProduct {
name: string,
description: string,
instockQuantity: number,
price: number
}
Here, we have created two product types. The first type, BasicProduct, contains only the product ID in its fields. The second type, Product, extends the first interface and creates a type with elaborate details.
In our app sometimes we want to process only the ID of a product. Some other times we want to process a detailed product object. For this reason, we use two product types and one extends the other. You’ll see a similar behavior when defining Customer and Order types.
//file types/customer.ts
export interface BasicCustomer {
id: number,
}
export interface Customer extends BasicCustomer{
name: string,
email?: string,
password?: string
}
When defining the order types, we can use previously created customer and product types as the types of customer and product fields respectively. In the three order types we define, the relevant types are used for customer and product fields.
//file types/order.ts
import {BasicProduct, Product} from "./product";
import {BasicCustomer, Customer} from "./customer";
export interface BasicOrder {
product: BasicProduct,
customer: BasicCustomer,
productQuantity: number
}
export interface Order extends BasicOrder {
orderId: number
}
export interface OrderWithDetails extends Order{
product: Product,
customer: Customer,
}
The BasicOrder type which is defined without the id is useful when creating an order for the first time (because the new order doesn’t have an ID yet).
Connect to the Database
With the help of mysql2 package, it’s an easy step to connect to the database we created before.
import mysql from "mysql2";
import * as dotenv from "dotenv";
dotenv.config();
export const db = mysql.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PWD,
database: process.env.DB_NAME
});
We export the established connection object to make it easy to define database operations for different types separately.
Define Database Operations
Next, let’s create functions for create, findOne, findAll, and update operations for the database. I’m only implementing the operations related to order type. But you can implement these operations for other data types as well.
Note that we are going to write plain SQL statements for this task. If you want to use an ORM instead of manually writing the statenments, Node offers several ORMs compatible with Typescript like TypeORM and Sequelize.
First, import the objects we will need for the implementation.
//file models/order.ts
import {BasicOrder, Order, OrderWithDetails} from "../types/order";
import {db} from "../db";
import { OkPacket, RowDataPacket } from "mysql2";
Next, let’s implement the create function. It is used to insert a new record of an order into the ProductRecord table.
export const create = (order: BasicOrder, callback: Function) => {
const queryString = "INSERT INTO ProductOrder (product_id, customer_id, product_quantity) VALUES (?, ?, ?)"
db.query(
queryString,
[order.product.id, order.customer.id, order.productQuantity],
(err, result) => {
if (err) {callback(err)};
const insertId = (<OkPacket> result).insertId;
callback(null, insertId);
}
);
};
We use the imported db object to query the database and return the insertId of the order record through a callback. Since the returned result is a union of several types, we do a simple casting to convert it to OkPacket type, the type returned for insert operations.
When passing a SQL statements to the query function, always be careful to not use the user-provided inputs directly in the string. It’s a practice that makes your system vulnerable to SQL injection attacks. Instead, use the ? symbol in places where variables should be added, and pass the variables as an array to the query function.
If you are unfamiliar with SQL statements, refer to the official MySQL documentation to understand the basic insert, select, update statements we have used in this tutorial.
Next, let’s implement the findOne function which selects a record from the ProductOrder table based on the order ID.
export const findOne = (orderId: number, callback: Function) => {
const queryString = `
SELECT
o.*,
p.*,
c.name AS customer_name,
c.email
FROM ProductOrder AS o
INNER JOIN Customer AS c ON c.id=o.customer_id
INNER JOIN Product AS p ON p.id=o.product_id
WHERE o.order_id=?`
db.query(queryString, orderId, (err, result) => {
if (err) {callback(err)}
const row = (<RowDataPacket> result)[0];
const order: OrderWithDetails = {
orderId: row.order_id,
customer: {
id: row.cusomer_id,
name: row.customer_name,
email: row.email
},
product: {
id: row.product_id,
name: row.name,
description: row.description,
instockQuantity: row.instock_quantity,
price: row.price
},
productQuantity: row.product_quantity
}
callback(null, order);
});
}
Here too, we follow a similar process to the create function. In the SQL statement, we have to join ProductRecord, Customer, Product tables to retrieve complete records of the customer and the product included in the order. We use the foreign keys defined in the OrderProduct table to do the joining.
After retrieving the data, we have to create an object of the Order type. Since we are retrieving details of both product and customer objects, we choose OrderWithDetails as our type from the 3 order types we defined before.
We can now implement the other two database operations, findAll and update, following the same pattern.
export const findAll = (callback: Function) => {
const queryString = `
SELECT
o.*,
p.*,
c.name AS customer_name,
c.email
FROM ProductOrder AS o
INNER JOIN Customer AS c ON c.id=o.customer_id
INNER JOIN Product AS p ON p.id=o.product_id`
db.query(queryString, (err, result) => {
if (err) {callback(err)}
const rows = <RowDataPacket[]> result;
const orders: Order[] = [];
rows.forEach(row => {
const order: OrderWithDetails = {
orderId: row.order_id,
customer: {
id: row.customer_id,
name: row.customer_name,
email: row.email
},
product: {
id: row.product_id,
name: row.name,
description: row.description,
instockQuantity: row.instock_quantity,
price: row.price
},
productQuantity: row.product_quantity
}
orders.push(order);
});
callback(null, orders);
});
}
export const update = (order: Order, callback: Function) => {
const queryString = `UPDATE ProductOrder SET product_id=?, product_quantity=? WHERE order_id=?`;
db.query(
queryString,
[order.product.id, order.productQuantity, order.orderId],
(err, result) => {
if (err) {callback(err)}
callback(null);
}
);
}
With that, we have completed the functions for order-related database operations.
Implement the Routes Handlers
As the next step, we are going to implement the route handler for the /orders endpoint. You can follow the pattern we use here to implement the /customer and /product endpoints later.
For our REST API, we are going to define 4 endpoints to send requests from the client-side.
//get all order objects
GET orders/
//create a new order
POST orders/
//get order by order ID
GET orders/:id
//update the order given by order ID
PUT orders/:id
Since we are using the express Router to define the routes, we can use the paths relative to the /orders route in the following implementation.
Since we implemented the data retrieving logic in the database model, in the route handler, we only have to get that data using the relevant functions and pass them to the client side.
Let’s add the route handling logic to the orderRouter.ts file.
import express, {Request, Response} from "express";
import * as orderModel from "../models/order";
import {Order, BasicOrder} from "../types/order";
const orderRouter = express.Router();
orderRouter.get("/", async (req: Request, res: Response) => {
orderModel.findAll((err: Error, orders: Order[]) => {
if (err) {
return res.status(500).json({"errorMessage": err.message});
}
res.status(200).json({"data": orders});
});
});
orderRouter.post("/", async (req: Request, res: Response) => {
const newOrder: BasicOrder = req.body;
orderModel.create(newOrder, (err: Error, orderId: number) => {
if (err) {
return res.status(500).json({"message": err.message});
}
res.status(200).json({"orderId": orderId});
});
});
orderRouter.get("/:id", async (req: Request, res: Response) => {
const orderId: number = Number(req.params.id);
orderModel.findOne(orderId, (err: Error, order: Order) => {
if (err) {
return res.status(500).json({"message": err.message});
}
res.status(200).json({"data": order});
})
});
orderRouter.put("/:id", async (req: Request, res: Response) => {
const order: Order = req.body;
orderModel.update(order, (err: Error) => {
if (err) {
return res.status(500).json({"message": err.message});
}
res.status(200).send();
})
});
export {orderRouter};
Put everything together in app.ts
We have now completed adding the internal logic of our API. The only thing left to do is putting everything together in the app.ts file, the entry point to our API, and creating the server that listens to and responds to requests.
import * as dotenv from "dotenv";
import express from "express";
import * as bodyParser from "body-parser";
import {orderRouter} from "./routes/orderRouter";
const app = express();
dotenv.config();
app.use(bodyParser.json());
app.use("/orders", orderRouter);
app.listen(process.env.PORT, () => {
console.log("Node server started running");
});
That’s it! We have created our simple Node.js REST API with MYSQL and Typescript in no time.
Summary
In this tutorial, we learned how to create a REST API with Node.js and MySQL with type support from Typescript. These 3 technologies make a perfect combination for creating APIs quickly and easily. So, I hope this tutorial will prove to be invaluable to you to write good backend APIs in the future. To get more experience on the subject, you can try your hand at implementing the suggestions I have made in this tutorial as your next step.
If you liked what you saw, please support my work!
Anjalee Sudasinghe
I’m a software engineering student who loves web development. I also have a habit of automating everyday stuff with code. I’ve discovered I love writing about programming as much as actual programming.
I’m extremely lucky to join the Live Code Stream community as a writer and share my love for programming with others one article at a time.
