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.

1. Add a new Book

   

   Add another book of your choice if you want.

2. Get all the books

1. Getting books using the title

   

2. 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.

1. 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.