Here is an example of how you could save data to a MySQL database using Express.js, MySQL, and TypeScript with the Model-View-Controller (MVC) pattern:
First, you would need to install the necessary dependencies and configure your MySQL database:
// Install the MySQL and TypeScript dependencies
npm install mysql2 @types/mysql2
// Configure your MySQL database connection in a separate module
import { createConnection, Connection } from 'mysql2';
export class MySQLDatabase {
static connection: Connection;
static connect() {
this.connection = createConnection({
host: 'localhost',
user: 'username',
password: 'password',
database: 'database',
});
}
}
Next, you would create a model file to represent your database entities and handle the database queries:
import { MySQLDatabase } from './database';
export class Product {
id: number;
name: string;
price: number;
static async find(): Promise<Product[]> {
return new Promise((resolve, reject) => {
MySQLDatabase.connection.query(
'SELECT * FROM products',
(err, results) => {
if (err) {
return reject(err);
}
resolve(results as Product[]);
}
);
});
}
static async findById(id: number): Promise<Product | null> {
return new Promise((resolve, reject) => {
MySQLDatabase.connection.query(
'SELECT * FROM products WHERE id = ?',
[id],
(err, results) => {
if (err) {
return reject(err);
}
resolve(results[0] as Product | null);
}
);
});
}
save(): Promise<void> {
return new Promise((resolve, reject) => {
MySQLDatabase.connection.query(
'INSERT INTO products SET ?',
this,
(err, result) => {
if (err) {
return reject(err);
}
this.id = result.insertId;
resolve();
}
);
});
}
update(): Promise<void> {
return new Promise((resolve, reject) => {
MySQLDatabase.connection.query(
'UPDATE products SET ? WHERE id = ?',
[this, this.id],
(err, result) => {
if (err) {
return reject(err);
}
resolve();
}
);
});
}
delete(): Promise<void> {
return new Promise((resolve, reject) => {
MySQLDatabase.connection.query(
'DELETE FROM products WHERE id = ?',
[this.id],
(err, result) => {
if (err) {
return reject(err);
}
resolve();
}
);
});
}
Then, in your controller file, you could use the methods from the Product model to save, update, and delete data from the database:
import { Request, Response } from 'express';
import { Product } from '../models/product';
export class ProductsController {
// A method to handle GET requests to the '/products' endpoint
static async getAll(req: Request, res: Response) {
try {
// Get the list of products from the database
const products = await Product.find();
res.json(products);
} catch (err) {
res.status(500).send(err);
}
}
// A method to handle GET requests to the '/products/:id' endpoint
static async getById(req: Request, res: Response) {
try {
// Get the product with the matching id from the database
const product = await Product.findById(req.params.id);
if (product) {
res.json(product);
} else {
res.sendStatus(404);
}
} catch (err) {
res.status(500).send(err);
}
}
// A method to handle POST requests to the '/products' endpoint
static async create(req: Request, res: Response) {
try {
// Create a new product in the database
const product = new Product(req.body);
await product.save();
res.sendStatus(201);
} catch (err) {
res.status(500).send(err);
}
}
// A method to handle PUT requests to the '/products/:id' endpoint
static async update(req: Request, res: Response) {
try {
// Update the product with the matching id in the database
const product = await Product.findById(req.params.id);
if (product) {
Object.assign(product, req.body);
await product.update();
res.sendStatus(200);
} else {
res.sendStatus(404);
}
} catch (err) {
res.status(500).send(err);
}
}
// A method to handle DELETE requests to the '/products/:id' endpoint
static async delete(req: Request, res: Response) {
try {
// Delete the product with the matching id from the database
const product = await Product.findById(req.params.id);
if (product) {
await product.delete();
res.sendStatus(200);
} else {
res.sendStatus(404);
}
} catch (err) {
res.status(500).send(err);
}
}
}
In this example, the Product model is assumed to have methods find, findById, save, update, and delete for fetching, saving, updating, and deleting data from the database, respectively. The controller defines methods for handling GET, POST, PUT, and DELETE requests to the relevant API endpoints, and uses the Product model methods to perform the corresponding database operations.
I hope this helps! Let me know if you have any questions or need further assistance.
Top comments (0)