😍Nodejs typescript mysql rest api Full (ok)

testdb.sql

CREATE TABLE `tutorials` (
  `id` int(50) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `published` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `tutorials` ADD PRIMARY KEY (`id`);
ALTER TABLE `tutorials` MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; COMMIT;
INSERT INTO `tutorials` (`id`, `title`, `description`, `published`) VALUES
(1, 'Test 1', 'Test 1', 1),
(2, 'Test 2', 'Test 2', 0);

tsconfig.json

{
  "compilerOptions": {
    "target": "es2016",                                 
    "experimentalDecorators": true,                   
    "emitDecoratorMetadata": true,                   
    "module": "commonjs",                               
    "resolveJsonModule": true,                      
    "outDir": "./build",                                  
    "esModuleInterop": true,                            
    "forceConsistentCasingInFileNames": true,           
    "strict": true,                                    
    "skipLibCheck": true                                 
  }
}

server.ts

import express, { Application } from "express";
import Server from "./src/index";
const app: Application = express();
const server: Server = new Server(app);
const PORT: number = process.env.PORT ? parseInt(process.env.PORT, 10) : 8000;
app
.listen(PORT, "localhost", function () {
  console.log(`Server is running on port 1 ${PORT}.`);
})
.on("error", (err: any) => {
  if (err.code === "EADDRINUSE") {
    console.log("Error: address already in use");
  } else {
    console.log(err);
  }
});

package.json

{
  "name": "rest",
  "version": "1.0.0",
  "description": "Building Node.js Typescript Express and MySQL: CRUD Rest API example.",
  "main": "server.ts",
  "scripts": {
    "test": "nodemon server.ts",
    "build": "tsc",
    "dev": "node ./build/server.js",
    "start": "tsc && npm run dev"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "cors": "^2.8.5",
    "express": "^4.21.1",
    "mysql2": "^3.11.5"
  },
  "devDependencies": {
    "@types/cors": "^2.8.17",
    "@types/express": "^5.0.0",
    "@types/node": "^22.10.1",
    "@types/nodemon": "^1.19.6",
    "nodemon": "^3.1.7",
    "ts-node": "^10.9.2",
    "typescript": "^5.7.2"
  }
}

src\config\db.config.ts

export default {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "",
  DB: "testdb"
};

src\controllers\home.controller.ts

import { Request, Response } from "express";
export function welcome(req: Request, res: Response): void {
  res.send("Welcome to bezkoder application check.");
}

src\controllers\tutorial.controller.ts

import { Request, Response } from "express";
import Tutorial from "../models/tutorial.model";
import tutorialRepository from "../repositories/tutorial.repository";
export default class TutorialController {
  async create(req: Request, res: Response) {
    if (!req.body.title) {
      res.status(400).send({
        message: "Content can not be empty!"
      });
      return;
    }
    try {
      const tutorial: Tutorial = req.body;
      const savedTutorial = await tutorialRepository.save(tutorial);
      res.status(201).send(savedTutorial);
    } catch (err) {
      res.status(500).send({
        message: "Some error occurred while retrieving tutorials."
      });
    }
  }
  async findAll(req: Request, res: Response) {
    const title = typeof req.query.title === "string" ? req.query.title : "";
    try {
      const tutorials = await tutorialRepository.retrieveAll({ title: title });
      res.status(200).send(tutorials);
    } catch (err) {
      res.status(500).send({
        message: "Some error occurred while retrieving tutorials."
      });
    }
  }
  async findOne(req: Request, res: Response) {
    const id: number = parseInt(req.params.id);
    try {
      const tutorial = await tutorialRepository.retrieveById(id);
      if (tutorial) res.status(200).send(tutorial);
      else
        res.status(404).send({
          message: `Cannot find Tutorial with id=${id}.`
        });
    } catch (err) {
      res.status(500).send({
        message: `Error retrieving Tutorial with id=${id}.`
      });
    }
  }
  async update(req: Request, res: Response) {
    let tutorial: Tutorial = req.body;
    tutorial.id = parseInt(req.params.id);
    try {
      const num = await tutorialRepository.update(tutorial);
      if (num == 1) {
        res.send({
          message: "Tutorial was updated successfully."
        });
      } else {
        res.send({
          message: `Cannot update Tutorial with id=${tutorial.id}. Maybe Tutorial was not found or req.body is empty!`
        });
      }
    } catch (err) {
      res.status(500).send({
        message: `Error updating Tutorial with id=${tutorial.id}.`
      });
    }
  }
  async delete(req: Request, res: Response) {
    const id: number = parseInt(req.params.id);
    try {
      const num = await tutorialRepository.delete(id);
      if (num == 1) {
        res.send({
          message: "Tutorial was deleted successfully!"
        });
      } else {
        res.send({
          message: `Cannot delete Tutorial with id=${id}. Maybe Tutorial was not found!`,
        });
      }
    } catch (err) {
      res.status(500).send({
        message: `Could not delete Tutorial with id==${id}.`
      });
    }
  }
  async deleteAll(req: Request, res: Response) {
    try {
      const num = await tutorialRepository.deleteAll();
      res.send({ message: `${num} Tutorials were deleted successfully!` });
    } catch (err) {
      res.status(500).send({
        message: "Some error occurred while removing all tutorials."
      });
    }
  }
  async findAllPublished(req: Request, res: Response) {
    try {
      const tutorials = await tutorialRepository.retrieveAll({ published: true });
      res.status(200).send(tutorials);
    } catch (err) {
      res.status(500).send({
        message: "Some error occurred while retrieving tutorials."
      });
    }
  }
}

src\db\index.ts

import mysql from "mysql2";
import dbConfig from "../config/db.config";
export default mysql.createConnection({
  host: dbConfig.HOST,
  user: dbConfig.USER,
  password: dbConfig.PASSWORD,
  database: dbConfig.DB
});

src\models\tutorial.model.ts

import { RowDataPacket } from "mysql2"
export default interface Tutorial extends RowDataPacket {
  id?: number;
  title?: string;
  description?: string;
  published?: boolean;
}

src\repositories\tutorial.repository.ts

import { OkPacket } from "mysql2";
import connection from "../db";
import Tutorial from "../models/tutorial.model";
interface ITutorialRepository {
  save(tutorial: Tutorial): Promise<Tutorial>;
  retrieveAll(searchParams: { title: string, published: boolean }): Promise<Tutorial[]>;
  retrieveById(tutorialId: number): Promise<Tutorial | undefined>;
  update(tutorial: Tutorial): Promise<number>;
  delete(tutorialId: number): Promise<number>;
  deleteAll(): Promise<number>;
}
class TutorialRepository implements ITutorialRepository {
  save(tutorial: Tutorial): Promise<Tutorial> {
    return new Promise((resolve, reject) => {
      connection.query<OkPacket>(
        "INSERT INTO tutorials (title, description, published) VALUES(?,?,?)",
        [tutorial.title, tutorial.description, tutorial.published ? tutorial.published : false],
        (err, res) => {
          if (err) reject(err);
          else
            this.retrieveById(res.insertId)
              .then((tutorial) => resolve(tutorial!))
              .catch(reject);
        }
      );
    });
  }
  retrieveAll(searchParams: { title?: string, published?: boolean }): Promise<Tutorial[]> {
    let query: string = "SELECT * FROM tutorials";
    let condition: string = "";
    if (searchParams?.published)
      condition += "published = TRUE"
    if (searchParams?.title)
      condition += `LOWER(title) LIKE '%${searchParams.title}%'`
    if (condition.length)
      query += " WHERE " + condition;
    return new Promise((resolve, reject) => {
      connection.query<Tutorial[]>(query, (err, res) => {
        if (err) reject(err);
        else resolve(res);
      });
    });
  }
  retrieveById(tutorialId: number): Promise<Tutorial> {
    return new Promise((resolve, reject) => {
      connection.query<Tutorial[]>(
        "SELECT * FROM tutorials WHERE id = ?",
        [tutorialId],
        (err, res) => {
          if (err) reject(err);
          else resolve(res?.[0]);
        }
      );
    });
  }
  update(tutorial: Tutorial): Promise<number> {
    return new Promise((resolve, reject) => {
      connection.query<OkPacket>(
        "UPDATE tutorials SET title = ?, description = ?, published = ? WHERE id = ?",
        [tutorial.title, tutorial.description, tutorial.published, tutorial.id],
        (err, res) => {
          if (err) reject(err);
          else resolve(res.affectedRows);
        }
      );
    });
  }
  delete(tutorialId: number): Promise<number> {
    return new Promise((resolve, reject) => {
      connection.query<OkPacket>(
        "DELETE FROM tutorials WHERE id = ?",
        [tutorialId],
        (err, res) => {
          if (err) reject(err);
          else resolve(res.affectedRows);
        }
      );
    });
  }
  deleteAll(): Promise<number> {
    return new Promise((resolve, reject) => {
      connection.query<OkPacket>("DELETE FROM tutorials", (err, res) => {
        if (err) reject(err);
        else resolve(res.affectedRows);
      });
    });
  }
}
export default new TutorialRepository();

src\routes\home.routes.ts

import { Router, Request, Response } from "express";
import { welcome } from './../controllers/home.controller';
class HomeRoutes {
  router = Router();
  constructor() {
    this.intializeRoutes();
  }
  intializeRoutes() {
    this.router.get("/", welcome);
  }
}
export default new HomeRoutes().router;

src\routes\index.ts

import { Application } from "express";
import tutorialRoutes from "./tutorial.routes";
import homeRoutes from "./home.routes";
export default class Routes {
  constructor(app: Application) {
    app.use("/api", homeRoutes);
    app.use("/api/tutorials", tutorialRoutes);
  }
}

src\routes\tutorial.routes.ts

import { Router } from "express";
import TutorialController from "../controllers/tutorial.controller";
class TutorialRoutes {
  router = Router();
  controller = new TutorialController();
  constructor() {
    this.intializeRoutes();
  }
  intializeRoutes() {
    // Create a new Tutorial
    this.router.post("/", this.controller.create);
    // Retrieve all Tutorials
    this.router.get("/", this.controller.findAll);
    // Retrieve all published Tutorials
    this.router.get("/published", this.controller.findAllPublished);
    // Retrieve a single Tutorial with id
    this.router.get("/:id", this.controller.findOne);
    // Update a Tutorial with id
    this.router.put("/:id", this.controller.update);
    // Delete a Tutorial with id
    this.router.delete("/:id", this.controller.delete);
    // Delete all Tutorials
    this.router.delete("/", this.controller.deleteAll);
  }
}
export default new TutorialRoutes().router;

src\index.ts

import express, { Application } from "express";
import cors, { CorsOptions } from "cors";
import Routes from './routes';
export default class Server {
  constructor(app: Application) {
    this.config(app);
    new Routes(app);
  }
  private config(app: Application): void {
    const corsOptions: CorsOptions = {
      origin: "http://localhost:8000"
    };
    app.use(cors(corsOptions));
    app.use(express.json());
    app.use(express.urlencoded({ extended: true }));
  }
}

Part 1. constructor Server (cors, express.json, express.urlencoded) app.use & app.listen

src\index.ts

import express from "express";
import cors from "cors";
export default class Server {
  constructor(app: any) {
    this.config(app);
  }
  private config(app: any): void {
    const corsOptions = {
      origin: "http://localhost:8000"
    };
    app.use(cors(corsOptions));
    app.use(express.json());
    app.use(express.urlencoded({ extended: true }));
  }
}

server.ts

import express, {Express } from "express";
import Server from "./src/index";
const app: Express = express();
const server: Server = new Server(app);
const PORT: number = process.env.PORT ? parseInt(process.env.PORT, 10) : 8000;
app.use("/",function(req,res){
  res.send("Nodejs typescript mysql rest api 😍");
});
app.listen(PORT, "localhost", function () {
  console.log(`Server is running on port 1 ${PORT}.`);
});

Part 2 Router Home

src\index.ts

import express from "express";
import cors from "cors";
import Routes from './routes';
export default class Server {
  constructor(app: any) {
    this.config(app);
    new Routes(app);
  }
  private config(app: any): void {
    const corsOptions = {
      origin: "http://localhost:8000"
    };
    app.use(cors(corsOptions));
    app.use(express.json());
    app.use(express.urlencoded({ extended: true }));
  }
}

src\routes\index.ts

import homeRoutes from "./home.routes";
export default class Routes {
  constructor(app: any) {
    app.use("/api", homeRoutes);
  }
}

src\routes\home.routes.ts

import { Router} from "express";
import { welcome } from './../controllers/home.controller';
class HomeRoutes {
  public router = Router();
  constructor() {
    this.intializeRoutes();
  }
  public intializeRoutes() {
    this.router.get("/", welcome);
  }
}
export default new HomeRoutes().router;

Part 3 Router Tutorials

src\routes\index.ts

import homeRoutes from "./home.routes";
import tutorialRoutes from "./tutorial.routes";
export default class Routes {
  constructor(app: any) {
    app.use("/api", homeRoutes);
    app.use("/api/tutorials", tutorialRoutes);
  }
}

src\routes\tutorial.routes.ts

import { Router } from "express";
import TutorialController from "../controllers/tutorial.controller";
class TutorialRoutes {
  public router = Router();
  public controller = new TutorialController();
  constructor() {
    this.intializeRoutes();
  }
  intializeRoutes() {
    this.router.get("/", this.controller.findAll);
  }
}
export default new TutorialRoutes().router;

src\controllers\tutorial.controller.ts

import Tutorial from "../models/tutorial.model";
import tutorialRepository from "../repositories/tutorial.repository";
export default class TutorialController {
  async findAll(req: any, res: any) {
    const title = typeof req.query.title === "string" ? req.query.title : "";
    try {
      const tutorials = await tutorialRepository.retrieveAll({ title: title });
      res.status(200).send(tutorials);
    } catch (error) {
      res.status(500).send({
        message: "Some error occurred while retrieving tutorials."
      });
    }
  };
}

src\repositories\tutorial.repository.ts

import connection from "../db";
import Tutorial from "../models/tutorial.model";
interface ITutorialRepository {
  retrieveAll(searchParams: { title: string, published: boolean }):any;
}
class TutorialRepository implements ITutorialRepository {
  retrieveAll(searchParams: { title?: string, published?: boolean }): any {
    let query: string = "SELECT * FROM tutorials";
    let condition: string = "";
    if (searchParams?.published) condition += "published = TRUE";
    if (searchParams?.title) condition += `LOWER(title) LIKE '%${searchParams.title}%'`;
    if (condition.length) query += " WHERE " + condition;
    return new Promise((resolve, reject) => {
      connection.query<any>(query, (err, res) => {
        if (err) reject(err);
        else resolve(res);
      });
    });
  }
}
export default new TutorialRepository();

23KB

rest.zip

archive