😄Node.js Typescript with MySQL example (ok)

https://www.bezkoder.com/node-js-typescript-mysql/

Chú ý: Sử dụng

app.use câu lệnh mới cho xem kêt quả connection.query

Minh họa:

package.json

{
  "name": "app",
  "version": "1.0.0",
  "main": "dist/app.js",
  "scripts": {
    "test": "npx ts-node app.ts",
    "build": "npx tsc",
    "dev": "nodemon app.ts",
    "start": "npx tsc && node dist/app.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "description": "",
  "devDependencies": {
    "@types/express": "^5.0.0",
    "@types/mysql": "^2.15.26",
    "@types/node": "^22.10.1",
    "@types/typescript": "^2.0.0",
    "express": "^4.21.1",
    "nodemon": "^3.1.7",
    "ts-node": "^10.9.2",
    "typescript": "^5.7.2"
  },
  "dependencies": {
    "mysql2": "^3.11.5"
  }
}

tsconfig.json

{
  "compilerOptions": {
    "target": "es2016",
    "module": "commonjs",    
    "esModuleInterop": true, 
    "forceConsistentCasingInFileNames": true,          
    "strict": true,    
    "skipLibCheck": true
  }
}

app.ts

import express from "express";
import mysql from "mysql2";
const port = 3000;
const app = express();
// Create the connection to database
// Get the client

// Create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test',
});
app.get("/", function(rqe,res) {
  res.send("res...");
});
// A simple SELECT query
app.use("/api", function() {
  connection.query(
    'SELECT * FROM `tutorials`',
    function (err, results, fields) {
      console.log(fields); // fields contains extra meta data about results, if available
      console.log(results); // results contains rows returned by server
    }
  );
});
app.listen(port, () => {
  console.log(`[server]: Server is running at http://localhost:${port}`);
});

Và đây là cách sử dụng với bất đồng bộ

app.ts

import express from "express";
import mysql from 'mysql2/promise';
const port = 3000;
const app = express();
// Create the connection to database
// Get the client

// Create the connection to database

app.get("/", function(rqe,res) {
  res.send("res...");
});
// A simple SELECT query
app.use("/api",async function() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'test',
  });
  try {
    const [results, fields] = await connection.query(
      'SELECT * FROM `tutorials`'
    );
    console.log(results); // results contains rows returned by server
    console.log(fields); // fields contains extra meta data about results, if available
  } catch (err) {
    console.log(err);
  }
});
app.listen(port, () => {
  console.log(`[server]: Server is running at http://localhost:${port}`);
});

Node.js Typescript with MySQL example

Last modified: July 12, 2023 bezkoder Node.js, Typescript

In this tutorial, I will show you step by step to implement CRUD operations in Node.js Typescript with MySQL example using mysql2.

You should install MySQL in your machine first. The installation instructions can be found at Official MySQL installation manual.

Working with following front-end: – Angular 8 / Angular 10 / Angular 11 / Angular 12 / Angular 13 / Angular 14 / Angular 15 / Angular 16 – Vue 2 Typescript / Vue 3 Typescript – React Typescript Component / React Typescript Hooks

Contents [hide]

Typescript

TypeScript is an open-source programming language developed and maintained by Microsoft. It is a superset of JavaScript, which means that any valid JavaScript code is also valid TypeScript code. However, TypeScript adds static typing and other features to JavaScript to enhance its development experience and help catch errors at compile-time rather than runtime.

Here are some key features and concepts of TypeScript:

Static Typing: TypeScript introduces static typing, allowing you to explicitly declare the types of variables, function parameters, and return types. This helps catch type-related errors during development and provides better code documentation and editor support.
Type Inference: TypeScript has a powerful type inference system that can automatically infer the types of variables based on their initial values. This reduces the need for explicit type annotations while still providing the benefits of static typing.
Interfaces: TypeScript supports the definition of interfaces, which are used to define contracts for object structures. Interfaces specify the names and types of properties or methods that an object must have to conform to the interface.
Classes: TypeScript introduces classes, allowing you to use object-oriented programming concepts such as inheritance, encapsulation, and polymorphism. Classes in TypeScript can have properties, methods, constructors, and support for access modifiers like public, private, and protected.
Modules: TypeScript provides a module system that helps organize and encapsulate code into reusable units. Modules allow you to define public and private members and provide a way to structure larger applications.
Generics: TypeScript supports generics, which enable the creation of reusable components that can work with different types. Generics allow you to write code that is more flexible and type-safe by parameterizing types and functions.
Decorators: TypeScript supports decorators, which are a way to add metadata or modify the behavior of classes, methods, or properties at design time. Decorators are heavily used in frameworks like Angular for features like dependency injection, component declaration, and more.
Tooling and Integration: TypeScript integrates well with modern development tools and workflows. It provides excellent editor support with features like autocompletion, type checking, and refactoring. TypeScript code is transpiled to JavaScript, allowing it to run in any JavaScript environment.

mysql2

mysql2 is a Node.js library that provides a fast and efficient way to connect and interact with MySQL databases. It is a successor to the original mysql library and offers several improvements in terms of performance and features.

Here are some key features and benefits of using mysql2:

Performance: fast and efficient. It supports streaming result sets, which allows for handling large result sets with lower memory consumption. It also provides improved performance through prepared statements, connection pooling, and support for multiple statements in a single query.
Promises and Async/Await Support: allows you to work with the library using modern JavaScript syntax. It provides a promise-based API, making it easier to write asynchronous code using promises or utilizing the async/await syntax for more readable and concise code.
Connection Pooling: offers built-in connection pooling functionality, allowing you to reuse database connections instead of creating new connections for each request. Connection pooling helps improve performance by reducing the overhead of establishing a new connection every time.
Prepared Statements: enabling you to execute parameterized queries. Prepared statements offer performance benefits by allowing the database server to optimize query execution and help prevent SQL injection attacks by properly escaping input values.
Support for Multiple Statements: allows you to execute multiple SQL statements in a single query, separated by semicolons. This feature can be useful when you need to perform multiple related operations in a single database round trip, reducing the overall latency.
Support for Named Placeholders: supports named placeholders in addition to the traditional question mark placeholders. Named placeholders provide more readability and clarity when working with complex queries by allowing you to bind values using their names instead of relying on the order.
SSL/TLS Support: supports SSL/TLS encryption for secure connections to the MySQL database server. It provides options for configuring SSL/TLS certificates and keys to establish encrypted connections, ensuring data privacy and security.

Import Typescript and mysql2

We need to install necessary modules: typescript, ts-node, @types/node and mysql2.

Run the command:

npm install typescript ts-node @types/node --save-dev
npm install mysql2

Create MySQL table

Before connecting Node.js Application with MySQL, we need a table first. So run the SQL script below to create tutorials table:

CREATE TABLE IF NOT EXISTS `tutorials` (
  id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  description varchar(255),
  published boolean DEFAULT false
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Configure & Connect to MySQL database

We’re gonna have a separate folder for configuration. Let’s create config folder in the src folder, then create db.config.ts file inside that config folder with content like this:

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

Now create a database connection that uses configuration above. The file for connection is index.ts, we put it in src/db folder:

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
});

Define the Model

In models folder, create a file called tutorial.model.ts. We’re gonna define Tutorial interface that extends RowDataPacket type (from the mysql npm package) here:

This is the content inside tutorial.model.ts:

import { RowDataPacket } from "mysql2"

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

Tutorial model is simple, it contains fields: id, title, description & published.

Create the Repository

Inside srx/repositories folder, let’s create tutorial.repository.ts with these CRUD functions:

save
retrieveAll
retrieveById
update
delete
deleteAll

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 { }

  retrieveAll(searchParams: {title?: string, published?: boolean}): Promise<Tutorial[]> { }

  retrieveById(tutorialId: number): Promise<Tutorial> { }

  update(tutorial: Tutorial): Promise<number> { }

  delete(tutorialId: number): Promise<number> { }

  deleteAll(): Promise<number> { }
}

export default new TutorialRepository();

Let’s implement each CRUD operation.

Create new object

import { OkPacket } from "mysql2";

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);
      }
    );
  });
}

Retrieve objects (with conditions)

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);
    });
  });
}

Retrieve object by Id

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 an object

import { OkPacket } from "mysql2";

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 an object

import { OkPacket } from "mysql2";

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);
      }
    );
  });
}

Delete all objects

import { OkPacket } from "mysql2";

deleteAll(): Promise<number> {
  return new Promise((resolve, reject) => {
    connection.query<OkPacket>("DELETE FROM tutorials", (err, res) => {
      if (err) reject(err);
      else resolve(res.affectedRows);
    });
  });
}

Create the Controller and Routes

Inside src/controllers folder, we create tutorial.controller.ts with these CRUD functions:

create
findAll
findOne
update
delete
deleteAll
findAllPublished

When a client sends request for an endpoint using HTTP request (GET, POST, PUT, DELETE), we need to determine how the server will response by setting up the routes.

These are our routes:

/api/tutorials: GET, POST, DELETE
/api/tutorials/:id: GET, PUT, DELETE
/api/tutorials/published: GET

Inside src/routes folder, create index.ts file.

import { Application } from "express";
import tutorialRoutes from "./tutorial.routes";

export default class Routes {
  constructor(app: Application) {
    app.use("/api/tutorials", tutorialRoutes);
  }
}

We import TutorialRoutes for handling HTTP Requests with /api/tutorials endpoint. The tutorial.routes.ts will look like this:

So this is our project structure:

For more details about implementing the Controller and Routes, kindly visit: Express Typescript example

Run and Check

First you need to run the SQL script below to create tutorials table:

CREATE TABLE IF NOT EXISTS `tutorials` (
  id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  description varchar(255),
  published boolean DEFAULT false
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Run the Node.js Typescript MySQL Rest APIs with command: npm run start

Using Postman, we’re gonna test all the APIs above.

– Create a new Tutorial: POST http://localhost:8080/api/tutorials

After creating some new Tutorials, you can check MySQL table:

– Retrieve all Tutorials: GET http://localhost:8080/api/tutorials

– Retrieve a single Tutorial by id: GET http://localhost:8080/api/tutorials/[id]

– Update a Tutorial: PUT http://localhost:8080/api/tutorials/[id]

Check tutorials table after some rows were updated:

– Find all Tutorials which title contains ‘sql’: GET http://localhost:8080/api/tutorials?title=sql

– Find all published Tutorials: GET http://localhost:8080/api/tutorials/published

– Delete a Tutorial: DELETE http://localhost:8080/api/tutorials/[id]

Tutorial with id=4 was removed from tutorials table.

– Delete all Tutorials: DELETE http://localhost:8080/api/tutorials

Now there are no rows in tutorials table.

You can use the Simple HTTP Client using Axios to check it.

Or: Simple HTTP Client using Fetch API

Conclusion

Today, we’ve learned how to implement Node.js Typescript with MySQL database using mysql2. We also know way to add configuration for MySQL database, create Model, write a controller and define routes for handling all CRUD operations with Express Typescript Rest API.

Happy learning! See you again.

Source code

You can find the complete source code for this example on Github.

With Sequelize ORM: Typescript ORM with MySQL example

If you want to add Comments for each Tutorial. It is the One-to-Many Association, there is a tutorial for that Relationship: Sequelize Associations: One-to-Many example – Node.js, MySQL

Or you can add Tags for each Tutorial and add Tutorials to Tag (Many-to-Many Relationship): Sequelize Many-to-Many Association example with Node.js & MySQL