Database Integration
Database integration in Node.js involves connecting the application to a database to store, retrieve, and manage data.
This process is essential for creating dynamic applications that interact with data.
1. Choosing a Database
SQL Databases: Such as PostgreSQL and MySQL, use structured query language (SQL) for defining and manipulating data.
NoSQL Databases: Such as MongoDB and CouchDB, are designed for unstructured data and offer flexible schemas.
2. Connecting to a Database
For SQL Databases: Use libraries like pg for PostgreSQL or mysql2 for MySQL.
3. Performing CRUD Operations
Create: Insert new data into the database
Read: Query data from the database.
Update: Modify existing data.
Delete: Remove data from the database.
MySQL Database Setup
To integrate a MySQL database with Node.js application, you need to configure a connection pool, handle queries, and ensure proper table creation.
Setting Up the Database Connection
Use the mysql2/promise library to create a connection pool that allows efficient handling of multiple database connections.
Database Connection Configuration:
import mysql from 'mysql2/promise';
const pool = mysql.createPool({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
connectionLimit: 10
});
console.log('MySQL Pool created successfully');
mysql.createPool(): Creates a pool of connections to the MySQL database.
connectionLimit: Specifies the maximum number of connections in the pool.
Query Execution Utility
Create a utility function for executing SQL queries. This function handles acquiring and releasing connections automatically.
Query Execution Function:
const query = async (sql, values) => {
const connection = await pool.getConnection();
try {
const [results] = await connection.query(sql, values);
return results;
} catch (err) {
return err;
} finally {
if (connection) {
connection.release();
}
}
};
export default query;pool.getConnection(): Obtains a connection from the pool.
connection.query(): Executes the SQL query.
connection.release(): Releases the connection back to the pool.
Recipe Controller Functions
Implement controller functions for managing recipe data. These functions interact with the database using the query utility.
Recipe Controller Example:
import query from '../config/db.js';
const recipeControllers = {
getAllRecipes: async (req, res) => {
try {
const sql = 'SELECT * FROM recipes';
const recipes = await query(sql);
res.status(200).json(recipes);
} catch (error) {
console.error('Error fetching recipes:', error);
res.status(500).json({ error: 'Error fetching recipes' });
}
},
getRecipeByID: async (req, res) => {
const { id } = req.params;
try {
const sql = 'SELECT * FROM recipes WHERE id = ?';
const recipe = await query(sql, [id]);
if (recipe.length === 0) {
res.status(404).json({ error: 'Recipe not found' });
} else {
res.status(200).json(recipe[0]);
}
} catch (error) {
console.error('Error fetching recipe:', error);
res.status(500).json({ error: 'Error fetching recipe' });
}
},
getRecipesByName: async (req, res) => {
const { q } = req.query;
try {
if (!q) {
return await recipeControllers.getAllRecipes(req, res);
}
const sql = 'SELECT * FROM recipes WHERE name LIKE ?';
const searchTerm = `%${q}%`;
const recipes = await query(sql, [searchTerm]);
if (recipes.length === 0) {
res.status(404).json({ error: 'Recipe not found' });
} else {
res.status(200).json(recipes);
}
} catch (error) {
console.error('Error fetching recipes by name:', error);
res.status(500).json({ error: 'Error fetching recipes by name' });
}
},
postRecipe: async (req, res) => {
const { name, description } = req.body;
try {
if (!name) {
return res.status(400).json({ error: 'Name of the recipe is required' });
}
const sql = 'INSERT INTO recipes (name, description) VALUES(?, ?)';
const values = [name, description || null];
const result = await query(sql, values);
const insertedRecipeId = result.insertId;
const fetchSql = 'SELECT * FROM recipes WHERE id = ?';
const newRecipe = await query(fetchSql, [insertedRecipeId]);
res.status(201).json({
recipe: newRecipe[0],
message: `"${newRecipe[0].name}" Recipe is added successfully`,
result: result
});
} catch (error) {
console.error('Error adding recipe:', error);
res.status(500).json({ error: 'Error adding recipe' });
}
},
updateRecipe: async (req, res) => {
const { id } = req.params;
const { name, description } = req.body;
try {
if (!name && !description) {
return res.status(400).json({ error: 'At least one field (name or description) is required to update' });
}
let sql = 'UPDATE recipes SET ';
const values = [];
if (name) {
sql += 'name = ?, ';
values.push(name);
}
if (description) {
sql += 'description = ?, ';
values.push(description);
}
sql = sql.slice(0, -2);
sql += ' WHERE id = ?';
values.push(id);
const result = await query(sql, values);
if (result.affectedRows === 0) {
return res.status(404).json({ error: 'Recipe not found' });
}
const fetchSql = 'SELECT * FROM recipes WHERE id = ?';
const updatedRecipe = await query(fetchSql, [id]);
res.status(200).json({ recipe: updatedRecipe[0], message: 'Recipe updated successfully', result: result });
} catch (error) {
console.error('Error updating recipe:', error);
res.status(500).json({ error: 'Error updating recipe' });
}
},
deleteRecipe: async (req, res) => {
const { id } = req.params;
try {
const sql = 'SELECT * FROM recipes WHERE id = ?';
const selectRecipe = await query(sql, [id]);
if (selectRecipe.length === 0) {
return res.status(404).json({ error: 'Recipe not found' });
}
const deleteSql = 'DELETE FROM recipes WHERE id = ?';
const result = await query(deleteSql, [id]);
res.status(200).json({ message: `"${selectRecipe[0].name}" is deleted successfully`, recipeId: id, result: result });
} catch (error) {
console.error('Error deleting recipe:', error);
res.status(500).json({ error: 'Error deleting recipe' });
}
}
};
export default recipeControllers;Table Creation Script
Create the necessary database table using a script that ensures the table is created if it doesn’t already exist.
Table Creation Script:
import query from '../config/db.js';
const createRecipeTable = async () => {
const sql = `
CREATE TABLE IF NOT EXISTS recipes (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
`;
try {
await query(sql);
} catch (error) {
console.error('Error creating recipes table:', error);
}
};
export default createRecipeTable;Table Definition:
id: Auto-increment primary key.
name: Non-nullable field for the recipe name
description: Optional field for additional details.
created_at & updated_at: Timestamps for record creation and last update.
This section demonstrates how to integrate MySQL with a Node.js application by configuring a connection pool, executing queries, and managing recipe data.
Last updated