Connecting Node.js to MySQL or PostgreSQL

NodeJs

"Node.js is like a magical kitchen for JavaScript—originally, it could only cook in the browser, but now it can build backends, manage servers, and even make coffee... if there's an npm package for it!"

 

Databases are like the backbone of any web application, and choosing between MySQL and PostgreSQL is like choosing between coffee and tea—both work, but each has its own flavor. In this guide, we’ll explore how to connect Node.js to both MySQL and PostgreSQL so you can build robust, database-driven applications.

What is MySQL?

MySQL is a popular open-source relational database management system (RDBMS) known for its speed, reliability, and ease of use. It uses structured query language (SQL) to store and retrieve data in tables.

What is PostgreSQL?

PostgreSQL, often called Postgres, is another powerful open-source RDBMS that offers advanced features like support for JSON, complex queries, and excellent scalability. It’s known for being highly extensible and standards-compliant.

Prerequisites

Before we start, make sure you have:

• Node.js installed
• MySQL or PostgreSQL installed and running
• MySQL2 or pg package installed (npm install mysql2 pg)

Step 1: Installing Required Packages

To connect Node.js to MySQL, install the MySQL2 package:

npm install mysql2

For PostgreSQL, install the pg package:

npm install pg

Step 2: Connecting to MySQL

Create a server.js file and set up a MySQL connection:

const mysql = require('mysql2');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase'
});

connection.connect(err => {
    if (err) {
        console.error('Error connecting to MySQL:', err);
        return;
    }
    console.log('Connected to MySQL');
});

Run the script, and if everything is set up correctly, you should see "Connected to MySQL" in the terminal.

Step 3: Connecting to PostgreSQL

For PostgreSQL, create a connection like this:

const { Client } = require('pg');

const client = new Client({
    user: 'postgres',
    host: 'localhost',
    database: 'mydatabase',
    password: 'password',
    port: 5432
});

client.connect()
    .then(() => console.log('Connected to PostgreSQL'))
    .catch(err => console.error('Error connecting to PostgreSQL:', err));

If your PostgreSQL server is running properly, you should see "Connected to PostgreSQL" in the terminal.

Step 4: Creating and Inserting Data

To create a table and insert data into MySQL:

const createTableQuery = `CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
)`;

connection.query(createTableQuery, err => {
    if (err) throw err;
    console.log('Users table created');
});

const insertUser = `INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')`;
connection.query(insertUser, err => {
    if (err) throw err;
    console.log('User inserted');
});

For PostgreSQL, the equivalent code:

const createTableQuery = `CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
)`;

client.query(createTableQuery)
    .then(() => console.log('Users table created'))
    .catch(err => console.error('Error creating table:', err));

const insertUser = `INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')`;
client.query(insertUser)
    .then(() => console.log('User inserted'))
    .catch(err => console.error('Error inserting user:', err));

Step 5: Retrieving Data

To fetch all users from MySQL:

connection.query('SELECT * FROM users', (err, results) => {
    if (err) throw err;
    console.log('Users:', results);
});

For PostgreSQL:

client.query('SELECT * FROM users')
    .then(res => console.log('Users:', res.rows))
    .catch(err => console.error('Error fetching users:', err));

Step 6: Updating Data

Updating a user in MySQL:

const updateUser = `UPDATE users SET name = 'Jane Doe' WHERE email = 'john@example.com'`;
connection.query(updateUser, err => {
    if (err) throw err;
    console.log('User updated');
});

For PostgreSQL:

const updateUser = `UPDATE users SET name = 'Jane Doe' WHERE email = 'john@example.com'`;
client.query(updateUser)
    .then(() => console.log('User updated'))
    .catch(err => console.error('Error updating user:', err));

Step 7: Deleting Data

Deleting a user in MySQL:

const deleteUser = `DELETE FROM users WHERE email = 'john@example.com'`;
connection.query(deleteUser, err => {
    if (err) throw err;
    console.log('User deleted');
});

For PostgreSQL:

const deleteUser = `DELETE FROM users WHERE email = 'john@example.com'`;
client.query(deleteUser)
    .then(() => console.log('User deleted'))
    .catch(err => console.error('Error deleting user:', err));

Conclusion

By now, you should have a solid understanding of how to connect Node.js to both MySQL and PostgreSQL. You’ve learned how to:

• Connect to MySQL and PostgreSQL using Node.js
• Create tables and insert data
• Perform CRUD (Create, Read, Update, Delete) operations

Now that you're equipped with the knowledge of handling relational databases in Node.js, go ahead and build something awesome!