Node.js: how to create a generic model for MySQL

Adopting a generic model in a Node.js application can greatly simplify the management of CRUD (Create, Read, Update, Delete) operations on a MySQL database. In this article, we will explore how to create a generic model from scratch using the mysql2 package, a popular tool for interfacing with MySQL in Node.js.

Before we begin, make sure you have Node.js installed on your system. Next, you will need the mysql2 package, which you can install using npm:

npm install mysql2

The first step is to set up the connection to the MySQL database. Let's create a db.js file to manage this connection:

// db.js
const mysql = require('mysql2');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'your_database'
});

connection.connect((err) => {
    if (err) {
        console.error('Error connecting to the database:', err.stack);
        return;
    }
    console.log('Connected to the database');
});

module.exports = connection;

In this file, we configure the connection details like host, user, password, and database name. The connect method handles the connection and returns an error if something goes wrong.

Now we can create a generic model that includes methods for CRUD operations. Let's create a GenericModel.js file:

// GenericModel.js
const db = require('./db');

class GenericModel {
    constructor(tableName) {
        this.tableName = tableName;
    }

    findAll() {
        const query = `SELECT * FROM ${this.tableName}`;
        return new Promise((resolve, reject) => {
            db.query(query, (err, results) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(results);
                }
            });
        });
    }

    findById(id) {
        const query = `SELECT * FROM ${this.tableName} WHERE id = ${id}`;
        return new Promise((resolve, reject) => {
            db.query(query, (err, results) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(results[0]);
                }
            });
        });
    }

    create(data) {
        const columns = Object.keys(data).join(', ');
        const values = Object.values(data).map(value => `'${value}'`).join(', ');
        const query = `INSERT INTO ${this.tableName} (${columns}) VALUES (${values})`;
        return new Promise((resolve, reject) => {
            db.query(query, (err, results) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(results.insertId);
                }
            });
        });
    }

    update(id, data) {
        const updates = Object.entries(data).map(([key, value]) => `${key} = '${value}'`).join(', ');
        const query = `UPDATE ${this.tableName} SET ${updates} WHERE id = ${id}`;
        return new Promise((resolve, reject) => {
            db.query(query, (err, results) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(results.affectedRows);
                }
            });
        });
    }

    delete(id) {
        const query = `DELETE FROM ${this.tableName} WHERE id = ${id}`;
        return new Promise((resolve, reject) => {
            db.query(query, (err, results) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(results.affectedRows);
                }
            });
        });
    }
}

module.exports = GenericModel;

This generic model uses the GenericModel class to perform basic operations such as findAll, findById, create, update, and delete. Each method generates a chained SQL query and executes it using db.query.

Once the generic model is created, we can create specific models for each table in the database. For example, if we have a users table, we can create a UserModel.js file:

// UserModel.js
const GenericModel = require('./GenericModel');

class UserModel extends GenericModel {
    constructor() {
        super('users'); // Passes the table name to the parent's constructor
    }

    // Here you can add additional methods
}

module.exports = new UserModel();

Here, UserModel extends GenericModel, passing the table name users to the constructor of the parent class. This allows us to inherit all CRUD methods without having to rewrite the code.

One of the main problems with using string concatenation to construct SQL queries is the vulnerability to SQL injection. For example, if a malicious user enters a malformed value as input, it could compromise the database. A better practice is to use parameterized queries, as shown below for the findById method:

findById(id) {
    const query = `SELECT * FROM ${this.tableName} WHERE id = ?`;
    return new Promise((resolve, reject) => {
        db.query(query, [id], (err, results) => {
            if (err) {
                reject(err);
            } else {
                resolve(results[0]);
            }
        });
    });
}

In this example, the ID value is passed as a separate parameter, preventing the risk of SQL injection. Using parameterized queries is highly recommended in any production-oriented application.

Conclusion

In this article, we explored how to create a generic model in Node.js using the mysql2 package to interact with a MySQL database. We saw how to implement basic CRUD methods and how to extend this generic model to create specific models for different tables. Finally, we discussed the importance of security and parameterized queries to prevent common vulnerabilities such as SQL injection.

This modular and reusable approach can make your code cleaner, easier to maintain, and more secure, allowing you to focus more on the logic of your application rather than the complexities of database interaction.