Node.js / Node.js Database Integration
Integrating MySQL and PostgreSQL with Node.js
In this tutorial, we will explore how to integrate MySQL and PostgreSQL with Node.js. These powerful relational databases can be harnessed to create more dynamic and data-intensiv…
Section overview
5 resourcesCovers integrating Node.js with databases such as MongoDB, MySQL, and PostgreSQL.
Integrating MySQL and PostgreSQL with Node.js
1. Introduction
In this tutorial, our goal is to understand how to integrate MySQL and PostgreSQL with Node.js. These robust relational databases can significantly enhance the dynamism and data-centricity of your web applications.
By the end of this guide, you will be able to set up a connection to MySQL and PostgreSQL databases using Node.js and be able to perform CRUD operations.
Prerequisites:
- Basic understanding of JavaScript
- Node.js and NPM installed on your machine
- MySQL and PostgreSQL installed on your machine
2. Step-by-Step Guide
First, we need to install the necessary modules. For MySQL we will use the mysql module, and for PostgreSQL, we will use the pg module.
npm install mysql pg
Connecting to MySQL:
const mysql = require('mysql');
// Create connection
const db = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'nodemysql'
});
// Connect
db.connect((err) => {
if(err) throw err;
console.log('MySQL Connected...');
});
Connecting to PostgreSQL:
const { Client } = require('pg');
// Create client
const client = new Client({
host: 'localhost',
user: 'postgres',
password: 'password',
database: 'node_postgres',
});
// Connect
client.connect(err => {
if (err) {
console.error('connection error', err.stack)
} else {
console.log('PostgreSQL Connected...')
}
});
3. Code Examples
MySQL CRUD Operations:
// Create database
let sql = 'CREATE DATABASE nodemysql';
db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
console.log('Database created...');
});
// Insert post
let post = {title:'Post One', body:'This is post number one'};
let sql = 'INSERT INTO posts SET ?';
let query = db.query(sql, post, (err, result) => {
if(err) throw err;
console.log(result);
console.log('Post added...');
});
// Select posts
let sql = 'SELECT * FROM posts';
let query = db.query(sql, (err, results) => {
if(err) throw err;
console.log(results);
console.log('Posts fetched...');
});
// Delete post
let sql = `DELETE FROM posts WHERE title = 'Post One'`;
let query = db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
console.log('Post deleted...');
});
PostgreSQL CRUD Operations:
// Create table
client.query('CREATE TABLE posts(id SERIAL PRIMARY KEY, title VARCHAR(50), body VARCHAR(255))', (err, res) => {
if(err) throw err;
console.log('Table created...');
});
// Insert post
let postText = 'INSERT INTO posts(title, body) VALUES($1, $2) RETURNING *';
let values = ['Post One', 'This is post number one'];
client.query(postText, values, (err, res) => {
if(err) throw err;
console.log('Post added...');
});
// Select posts
client.query('SELECT * FROM posts', (err, res) => {
if(err) throw err;
console.log(res.rows);
});
// Delete post
let deleteText = 'DELETE FROM posts WHERE title = $1';
let deleteValue = ['Post One'];
client.query(deleteText, deleteValue, (err, res) => {
if(err) throw err;
console.log('Post deleted...');
});
4. Summary
In this tutorial, we learned how to integrate MySQL and PostgreSQL with Node.js, how to connect to the databases, and perform basic CRUD operations.
The next step would be to understand how to handle errors and exceptions while working with these databases.
Additional resources:
- MySQL NPM
- Node Postgres
5. Practice Exercises
- Create a new PostgreSQL database and connect to it using Node.js.
- Create a new table in the PostgreSQL database and insert 5 different posts into it.
- Create a new MySQL database and perform the same operations as in exercises 1 and 2.
Remember to practice handling errors and exceptions while working with these databases.
Need Help Implementing This?
We build custom systems, plugins, and scalable infrastructure.
Related topics
Keep learning with adjacent tracks.
Popular tools
Helpful utilities for quick tasks.
Latest articles
Fresh insights from the CodiWiki team.
AI in Drug Discovery: Accelerating Medical Breakthroughs
In the rapidly evolving landscape of healthcare and pharmaceuticals, Artificial Intelligence (AI) in drug dis…
Read articleAI in Retail: Personalized Shopping and Inventory Management
In the rapidly evolving retail landscape, the integration of Artificial Intelligence (AI) is revolutionizing …
Read articleAI in Public Safety: Predictive Policing and Crime Prevention
In the realm of public safety, the integration of Artificial Intelligence (AI) stands as a beacon of innovati…
Read articleAI in Mental Health: Assisting with Therapy and Diagnostics
In the realm of mental health, the integration of Artificial Intelligence (AI) stands as a beacon of hope and…
Read articleAI in Legal Compliance: Ensuring Regulatory Adherence
In an era where technology continually reshapes the boundaries of industries, Artificial Intelligence (AI) in…
Read article