A complete explanation of the method of using mysql2 to operate a database in Nodejs

Advantages of mysql2 compared to mysql

mysql2 is a MySQL client library based on . Compared with the mysql library, it has the following advantages:

Better performance: The mysql2 library is optimized in terms of performance and uses a more efficient underlying implementation. It uses faster connection pool management and query execution mechanisms, which can handle higher concurrent requests and provide better performance.
Supports Promise and async/await: the mysql2 library natively supports Promise and async/await, making writing asynchronous code more convenient and intuitive. You can usepromise().query()Methods execute query and useawaitKeywords are waiting for query results.
Support streaming queries: The mysql2 library supports streaming queries, which can handle large query result sets by creating readable streams. This is useful for handling large amounts of data or results that require line-by-line processing, reducing memory footprint and improving performance.
Better error handling: The mysql2 library provides a better error handling mechanism that can capture and handle errors in database operations in more detail. The error object it returns contains more useful information, such as SQL statements, error codes, and error stacks, which helps better debug and troubleshoot problems.
Support for preprocessing statements: The mysql2 library supports preprocessing statements, which can use placeholders to safely build and execute SQL queries. This prevents SQL injection attacks and improves application security.

Install

npm install mysql2

Connect to the database

To connect to a MySQL database using mysql2, you need to install the mysql2 package and create the connection with the appropriate connection parameters. Here is an example showing how to connect to a MySQL database using mysql2:

const mysql = require("mysql2");

// Create a database connectionconst connection = ({
  host: "localhost", // Host address  user: "root", // username  password: "password", // password  port: 3306, // Port number, default is 3306  database: "mydatabase", // Database name  charset: "UTF8_GENERAL_CI", // Connect character set, default is UTF8_GENERAL_CI  connectTimeout: 10000, // Connection timeout, unit in milliseconds  multipleStatements: false // Whether to allow multiple MySQL statements in a query, the default is false});

// Connect to the database((err) =&gt; {
  if (err) {
    ("Error connecting to database:", err);
    return;
  }
  ("Connected to database");
  
  // Perform database operations here});

// Triggered when the database connection is closed("end", () =&gt; {
  ("Database connection closed");
});

// Triggered when an error occurs("error", (err) =&gt; {
  ("Database error:", err);
});

// Close the database connection();

In this example, we use()The method creates a database connection and passes the connection parameters (host name, user name, password and database name). Then, we use()Method to connect to the database.

After the connection is successful, you can perform database operations in the callback function. In this example, we simply output the message of the connection success, but you can do any database operations here, such as query, insert, update, etc.

Please note that in order to ensure the correct shutdown of the database connection, we listenedendEvent, and the connection is closed in the event handler. In addition, we also monitorederrorEvents to handle possible database errors.

Finally, we use()Method to close the database connection.

Please modify the connection parameters according to your actual situation to adapt to the configuration of your MySQL database.

Execute SQL statements through query() method

After connecting to the database, you can usequery()Methods: Add, delete, modify and check the database.

Specific syntax format:(sql,params,callback),The following is correctquery()The parameters of the method are explained in detail:

sql: The SQL query statement to be executed can be SELECT, INSERT, UPDATE, DELETE, etc.
params(Optional): An array containing parameters to be passed to the SQL query. These parameters can be used for placeholders in preprocessing statements.
callback: A callback function that handles query results or errors. The callback function receives two parameters:errandresults。erris an error object (if an error occurs),resultsIt is the query result.

Here is an example showing how to usequery()Methods execute SQL query and process results:

const mysql = require("mysql2");

// Create a database connectionconst connection = ({
  host: "localhost",
  user: "root",
  password: "password",
  database: "mydatabase"
});

// Connect to the database((err) =&gt; {
  if (err) {
    ("Error connecting to database:", err);
    return;
  }
  ("Connected to database");

  // When executing SQL query, you can use ?? instead of table name, field, and index name; use ? instead of data.  const sql = "SELECT * FROM users WHERE age &gt; ?";
  const params = [18];
  (sql, params, (err, results) =&gt; {
    if (err) {
      ("Error executing query:", err);
      return;
    }
    ("Query results:", results);
  });
});

// Triggered when the database connection is closed("end", () =&gt; {
  ("Database connection closed");
});

// Triggered when an error occurs("error", (err) =&gt; {
  ("Database error:", err);
});

// Close the database connection();

In this example, we execute a SELECT query to find users older than 18. We used preprocessing statements and put the parameters[18]Pass toquery()method.

In the callback function, we handled the query result or error. If the query is successful, we print out the query results.results。

Please modify SQL query statements, parameters and processing logic according to your actual needs. You can execute any valid SQL query and process the query results or errors as needed.

Execute SQL statements through execute() method

Here is an example of using the mysql2 module to execute SQL queries:

const mysql = require('mysql2');

// Create a database connectionconst connection = ({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase'
});

// Execute SQL query(
  'SELECT * FROM users WHERE age &gt; ?',
  [18],
  function(err, results, fields) {
    if (err) {
      ('[SELECT ERROR] - ', );
      return;
    }
    (results);
    // (fields);
  }
);

// Close the database connection();

In this example, we create a name calledmydatabaseThe database connection is performed and a query statement is executed to find users older than 18 years old. If the query is successful, the result will be printed on the console. You can uncomment it(fields)This line of code is used to view the field information.

Please make sure tolocalhost、root、passwordandmydatabaseReplace with your own database connection information.

The difference between execute() and query():

Below isexecute()andquery()Differences between:

Parameterized query:execute()Methods support parameterized queries, and placeholders can be used (e.g.?) instead of the parameters in the query, andquery()The method does not support parameterized queries, and parameters need to be passed through string splicing.
Callback function:execute()andquery()Methods all accept a callback function as a parameter to process the query results. but,execute()The callback function of the method has three parameters (err, results, fields), respectively representing error information, query results and field information;query()The callback function of the method has two parameters (err, results), which only represents error information and query results.
Error handling:execute()In the method, the error needs to be handled manually and judged byerrWhether the parameter exists to determine if an error occurs. andquery()The method will automatically handle the error. If an error occurs, the error message will be passed to the callback function.errparameter.
Return result: No matter howexecute()stillquery()Methods will return a result set object. but,execute()The result set object returned by the method contains more information, such as the number of affected rows, etc.
Precompiled:execute()The method supports precompilation, and SQL statements can be compiled in advance to improve execution efficiency. andquery()Methods do not support precompilation.

Connection pool

By reusing the previous connection, the connection pool can be left open, reducing the time to connect to the MySQL server. In()Method to create a connection pool. Its syntax format is as follows:

const mysql = require('mysql2');

// Create a connection poolconst pool = ({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase',
    connectionLimit: 10, // Maximum number of connections    queueLimit: 0, // The maximum number of requests queued for connection, 0 means unlimited    waitForConnections: true // When the connection pool reaches the maximum number of connections, whether to wait for available connections});

Use connection pool forDatabase operationsAvailable()and()Method, you can also manually obtain the connection for operation. Here is a sample code for database operations using a connection pool:

Use the() method:

const mysql = require('mysql2');

// Create a connection poolconst pool = ({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase',
    connectionLimit: 10
});

// Use the () method to execute the query('SELECT * FROM users', function(err, results, fields) {
    if (err) {
        ('[QUERY ERROR] - ', );
        return;
    }

    (results);
});

// Close the connection pool();

Use the() method:

const mysql = require('mysql2');

// Create a connection poolconst pool = ({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase',
    connectionLimit: 10
});

// Use the () method to execute the query('SELECT * FROM users', function(err, results, fields) {
    if (err) {
        ('[EXECUTE ERROR] - ', );
        return;
    }

    (results);
});

// Close the connection pool();

Manually obtain the connection for database operations:

const mysql = require('mysql2');

// Create a connection poolconst pool = ({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase',
    connectionLimit: 10
});

// Get the connection from the connection pool(function(err, connection) {
    if (err) {
        ('[CONNECTION ERROR] - ', );
        return;
    }

    // Perform query operations    ('SELECT * FROM users', function(err, results, fields) {
        (); // Release the connection back to the connection pool
        if (err) {
            ('[QUERY ERROR] - ', );
            return;
        }

        (results);
    });
});

// Close the connection pool();

Using Promise

When the database was operated through query() and execute(), the returned data was obtained through callback functions. We can use Promise to convert asynchronously into synchronously to implement asynchronous operations through two methods: async/await and promise() functions.

One is achieved through async\await.
This is achieved through the promise() function.

The following is async/await, createConnection(), and createPool() to create database operations:

How to use async/await and createConnection():

const mysql = require('mysql2');

// Create a connectionconst connection = ({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase'
});

// Encapsulate query functionfunction query(sql) {
    return new Promise((resolve, reject) =&gt; {
        (sql, function(err, results, fields) {
            if (err) {
                reject(err);
            } else {
                resolve(results);
            }
        });
    });
}

// Use async/await for database operationsasync function fetchData() {
    try {
        const results = await query('SELECT * FROM users');
        (results);
    } catch (err) {
        ('[QUERY ERROR] - ', );
    } finally {
        ();
    }
}

fetchData();

How to use async/await and createPool():

const mysql = require('mysql2');

// Create a connection poolconst pool = ({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase',
    connectionLimit: 10
});

// Encapsulate query functionfunction query(sql) {
    return new Promise((resolve, reject) =&gt; {
        (sql, function(err, results, fields) {
            if (err) {
                reject(err);
            } else {
                resolve(results);
            }
        });
    });
}

// Use async/await for database operationsasync function fetchData() {
    let connection;
    try {
        connection = await ().getConnection();
        const results = await ('SELECT * FROM users');
        (results);
    } catch (err) {
        ('[QUERY ERROR] - ', );
    } finally {
        if (connection) {
            ();
        }
        ();
    }
}

fetchData();

How to use promise() function and createConnection():

const mysql = require('mysql2');

// Create a connectionconst connection = ({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase'
});

// Encapsulate query functionfunction query(sql) {
    return new Promise((resolve, reject) =&gt; {
        (sql, function(err, results, fields) {
            if (err) {
                reject(err);
            } else {
                resolve(results);
            }
        });
    });
}

// Use promise() function to perform database operationsquery('SELECT * FROM users')
    .then(results =&gt; {
        (results);
    })
    .catch(err =&gt; {
        ('[QUERY ERROR] - ', );
    })
    .finally(() =&gt; {
        ();
    });

Whether using async/await or promise() function, asynchronous operations can be converted into synchronous methods to complete the database operations. Choose the right way to use Promise according to your personal preferences and project needs.

Summarize

This is the article about Nodejs using mysql2 to operate databases. For more related content on Nodejs using mysql2 to operate databases, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!