Integrating MySQL and PostgreSQL with Node.js

Tutorial 3 of 5

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.