Creating APIs using Typescript and MySQL
Connecting MySQL to Nodejs with Typescript
In this article, we will be seeing how to connect your MySQL database to nodejs in typescript. Now you may be wondering why not javascript. Well, typescript is the widely used language and is much stricter than javascript. It is the superset of javascript. You can mention various data types to variables thereby reducing the error. Moreover, it also provides an ES6 module. Typescript can easily be compiled into javascript.
Basic setup
Now let us start building this. Create a folder and open the same in Visual Studio Code.
Initializing npm
npm init -y
You can see that the package.json file has been created
Installing Typescript and ts-node
npm install -g typescript
npm install -D ts-node
Installing required dependencies
npm install express mysql nodemon dotenv axios cors
Installing with the types extension
npm install -D @types/express @types/mysql @types/nodemon @types/dotenv @types/axios @types/cors
Initializing the Typescript config file
tsc --init
After running the above command you can see the tsconfig.json file in the root folder. In this file, assign the value of "target" to "ES6".
Now it's time to create the database. In your MySQL server create a new schema named bookdb. In this schema, we will be creating a table called books.
CREATE TABLE books (
id INT PRIMARY KEY NOT NULL,
title VARCHAR(45) NOT NULL,
description VARCHAR(45) NOT NULL,
cover VARCHAR(45),
price INT NOT NULL
);
Creating Node js server
Create a file named index.ts in the root folder
import express, { Application } from 'express';
import cors from 'cors';
const app: Application = express();
app.use(express.urlencoded({ extended: false }));
app.use(express.json());
app.use(cors());
app.listen(5000, () => {
console.log('Server is running on http://localhost:5000');
});
We are using 5000 as the port for our localhost
Connecting MySQL database
Create a .env file in the root folder. In this folder, you will store all the required credentials to connect to your MySQL server. You can ignore this file if you are pushing your project to GitHub.
HOST = 'localhost'
USER = 'root'
PASSWORD = 'your_password'
DATABASE = 'bookdb'
Now create a folder in the root named database. In this folder create a file called database.ts
import mysql from "mysql";
import dotenv from 'dotenv';
dotenv.config();
export const db = mysql.createConnection({
host: process.env.HOST,
user: process.env.USER,
password: process.env.PASSWORD,
database: process.env.DATABASE
});
We have to connect this db to our main server. So in the index.ts execute the following changes
import express, { Application } from 'express';
import cors from 'cors';
import { db } from './database/database';
db.connect((error) => {
if (error) {
console.error('Error connecting to the database:', error);
return;
}
console.log('Connected to the database.');
});
const app: Application = express();
app.use(express.urlencoded({ extended: false }));
app.use(express.json());
app.use(cors());
app.listen(5000, () => {
console.log('Server is running on http://localhost:5000');
});
Finally, to run our server several changes need to be done in our package.json file
"scripts": {
"dev": "nodemon index.ts"
}
Add this in the place of scripts in the package.json file
And then run the following command in the terminal to run the server
npm run dev
You should get the following output in your terminal
Creating APIs
Now let us begin to create APIs. Create 2 folders in the root named controllers and routers respectively.
In controllers create a file named helper.ts file. In this file, we will be creating all the required functions that will be called whenever we call the API.
import { Response, Request } from 'express';
import { db } from '../database/database';
export const getAllBooks = (req: Request, res: Response) => {
const query = `SELECT * FROM books;`;
db.query(query, (err, data) => {
if (err) {
return res.status(404).json({
success: false,
message: err.sqlMessage
});
};
return res.status(200).json(data);
});
};
export const getBookByTitle = (colName: string, req: Request, res: Response) => {
const title = req.params.btitle;
const query = `SELECT * FROM books WHERE ${db.escapeId(colName)} LIKE ${db.escape(`%${title}%`)}`;
db.query(query, (err, data) => {
if (err) {
return res.status(404).send("No book found");
}
return res.status(200).json(data);
});
};
export const addNewBook = (req: Request, res: Response) => {
const query = 'INSERT INTO books(`title`,`desc`,`cover`,`price`) VALUES (?)';
const values = [
req.body.title,
req.body.desc,
req.body.cover,
req.body.price,
];
db.query(query, [values], (err, data) => {
if (err) {
return res.status(404).json({
success: false,
message: err.sqlMessage
});
}
return res.status(200).json(data);
});
};
export const updateBook = (req: Request, res: Response) => {
const bookId = req.params.id;
const {
title, desc, cover, price
} = req.body;
const query = `UPDATE books SET title = ${db.escape(`${title}`)}, books.desc = ${db.escape(`${desc}`)}, cover = ${db.escape(`${cover}`)}, price = ${db.escape(price)} WHERE id = ${bookId}`;
db.query(query, (err, data) => {
if (err) {
return res.status(404).json({
success: false,
message: err.sqlMessage
});
}
return res.status(200).json(data);
});
};
export const deleteBook = (req: Request, res: Response) => {
const bookId = req.params.id;
const query = `DELETE from books WHERE id = ${bookId}`;
db.query(query, (err, data) => {
if (err) {
return res.status(404).json({
success: false,
message: err.sqlMessage
});
}
return res.status(200).json(data);
});
};
In this file we have created functions to Get all the books, get the book using its title, create a new book, update a book and delete a book.
In the routers folder create a file named bookRouter.ts. In this file we will be importing the helper function and will create routes using express routers.
import { Request, Response, Router } from "express";
import {
addNewBook,
deleteBook,
getAllBooks,
getBookByTitle,
updateBook
} from "../controllers/helper";
const router = Router();
router.get('/books', async (req: Request, res: Response) => {
try {
await getAllBooks(req, res);
} catch (error) {
res.status(500).json({ error: 'Internal server error' });
}
});
router.get('/getbook/:btitle', async (req: Request, res: Response) => {
try {
await getBookByTitle('title', req, res);
} catch (error) {
res.status(500).json({ error: 'Internal server error' });
}
});
router.post('/addbook', async (req: Request, res: Response) => {
try {
await addNewBook(req, res);
} catch (error) {
res.status(500).json({ error: 'Internal server error' });
}
});
router.put('/book/:id', async (req: Request, res: Response) => {
try {
await updateBook(req, res);
} catch (error) {
res.status(500).json({ error: 'Internal server error' });
}
});
router.delete('/book/:id', async (req: Request, res: Response) => {
try {
await deleteBook(req, res);
} catch (error) {
res.status(500).json({ error: 'Internal server error' });
}
});
export default router;
After this, we will be initializing the routes in our index.ts file.
import express, { Application } from 'express';
import cors from 'cors';
import { db } from './database/database';
db.connect((error) => {
if (error) {
console.error('Error connecting to the database:', error);
return;
}
console.log('Connected to the database.');
});
const app: Application = express();
app.use(express.urlencoded({ extended: false }));
app.use(express.json());
app.use(cors());
import bookRouter from './routers/bookRoutes';
app.use('/api/bk', bookRouter);
app.listen(5000, () => {
console.log('Server is running on http://localhost:5000');
});
Testing the APIs
We will be testing our APIs. For this, you have to install the Postman API testing software. Otherwise, there is an extension available in VSCode named Thunder Client. Install any one of these. I will be showing the results in Thunder Client.
Add a new Book
Add another book of your choice if you want.
Get all the books
Getting books using the title
Updating a book
[
{
"id": 1,
"title": "Atomic habits by James Clear",
"desc": "Explore the power of small habits and incremental changes",
"cover": "cover.png",
"price": 150
}
]
This is what you get as the updated value of 1st book.
Deleting a book
As you can see the second book record got deleted
This was my effort to make you know how can we use MySQL and nodejs with Typescript for creating backend APIs. If there are any changes or questions please leave a comment below, and share this with those who want to learn this.