Thread Continuation: How to Create a Personal Finance Application Integrating Google Drive, MySQL and Node.js

Step 6: Connection with the Database**

In this step, we explore how to establish a connection between our Node.js application and a MySQL database to efficiently persist data. You will learn to:

• Configure your database credentials in Node.js.
• Use the mysql2 library to manage the connection.
• Create basic queries to insert, update and retrieve data.

This step is essential to optimize the performance of the application, avoiding continuous readings from files in Google Drive and allowing more efficient analysis of the stored data. ?

Documentation of the Code for Connecting to a Database with MySQL in Node.js

This code establishes a connection to a MySQL database using mysql2 and handles connection errors and queries using promises. Each part of the process is detailed below to document its use.

Code Settings

1. Environment Requirements and Configuration:

   • The mysql2 package is required to interact with the MySQL database.
   • dotenv is used to manage environment variables, allowing sensitive information such as database credentials to be securely configured.
   • The connection configuration is obtained from a configuration file or from environment variables if dotenv is used.

   const mysql = require('mysql2');
   const { promisify } = require('util');
   require('dotenv').config();  // Cargar variables de entorno
   const localhost = process.env.DATABASE_CONFIG || require('../config');  // Recuperar configuración desde dotenv o archivo de configuración
   

   Copy after login

   Copy after login

2. Creation of the Connection Pool:

   • A connection pool is created using the recovered configuration. This helps maintain persistent and reusable connections, optimizing performance.

   const pool = mysql.createPool(localhost);
   

   Copy after login

3. Promisification of Queries:

   • Queries are promissified to handle them asynchronously using promisify. This simplifies the management of query results and errors.

   pool.query = promisify(pool.query);
   

   Copy after login

4. Error Management:

   • Possible connection errors and queries are handled. For example, errors such as access denied, connection lost, or maximum connections reached are handled specifically.

   pool.getConnection()
       .then(connection => {
           connection.release();
           console.log("Conexión establecida correctamente.");
       })
       .catch(err => {
           if (err.code === 'ER_NOT_SUPPORTED_AUTH_MODE' || err.code === 'ER_ACCESS_DENIED_ERROR') {
               console.error('Error de acceso denegado. Revise las credenciales.');
           } else if (err.code === 'PROTOCOL_CONNECTION_LOST') {
               console.error('La conexión con la base de datos se perdió.');
           } else if (err.code === 'ER_CON_COUNT_ERROR') {
               console.error('Demasiadas conexiones activas.');
           } else if (err.code === 'ECONNREFUSED') {
               console.error('La conexión con la base de datos fue rechazada.');
           } else {
               console.error(`Error desconocido: ${err.message}`);
           }
       });
   

   Copy after login

Detailed Explanation of the Code

1. Requirements and Configuration:

   • mysql2 provides an API to interact with MySQL.
   • dotenv is used to manage configurations through an .env file or directly environment variables.
   • localhost contains the database configuration data such as host, user, password, etc.

2. Pool Creation:

   • mysql.createPool(localhost) creates a persistent connection, which is ideal for applications that handle multiple connections simultaneously.

3. Promisification:

   • SQL queries are promissified to be used as asynchronous functions, making them easier to handle in modern applications.

4. Error Management:

   • Common errors such as lost connections, access denied, or query rejections are carefully handled to provide useful messages to the developer and avoid crashes.

Use in Projects

This code is useful for connecting Node.js applications with MySQL databases, providing a robust solution for connection and error management using modern techniques such as promisification and proper exception handling.

const mysql = require('mysql2');
const { promisify } = require('util');
require('dotenv').config();  // Cargar variables de entorno
const localhost = process.env.DATABASE_CONFIG || require('../config');  // Recuperar configuración desde dotenv o archivo de configuración

The above is the detailed content of Thread Continuation: How to Create a Personal Finance Application Integrating Google Drive, MySQL and Node.js. For more information, please follow other related articles on the PHP Chinese website!