MySQL/PostgreSQL with Express

Imagine you're organizing a massive library. Every book has a specific place, every member has a card, and records are maintained in neat, structured tables. That's how SQL databases work everything is organized, relationships are clear, and data integrity is guaranteed. MySQL and PostgreSQL are two of the most popular SQL databases used with Express.

What are SQL Databases?

SQL (Structured Query Language) databases are relational databases that store data in tables with rows and columns. They have a fixed schema and support complex relationships between tables.

MySQL Fast, reliable, and widely used. Great for web applications.
PostgreSQL Advanced, feature-rich, supports JSON and custom data types. Known for reliability and standards compliance.

Connecting to MySQL with mysql2

First, install the driver:

npm install mysql2

Create a connection pool:

const mysql = require('mysql2');

// Create a connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10
});

// Convert pool to use promises
const promisePool = pool.promise();

Connecting to PostgreSQL with pg

Install the driver:

npm install pg

Create a connection pool:

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

const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'mydb',
  password: 'password',
  port: 5432
});

CRUD Operations with MySQL/PostgreSQL

Let's assume we have a 'users' table with columns: id, name, email, age.

Create (INSERT) a User

app.post('/users', async (req, res) => {
  try {
    const { name, email, age } = req.body;
    const [result] = await promisePool.query(
      'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
      [name, email, age]
    );
    res.status(201).json({ id: result.insertId, name, email, age });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

Read (SELECT) Users

// Get all users
app.get('/users', async (req, res) => {
  try {
    const [rows] = await promisePool.query('SELECT * FROM users');
    res.json(rows);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Get user by ID
app.get('/users/:id', async (req, res) => {
  try {
    const [rows] = await promisePool.query(
      'SELECT * FROM users WHERE id = ?',
      [req.params.id]
    );
    if (rows.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }
    res.json(rows[0]);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

Update (UPDATE) a User

app.put('/users/:id', async (req, res) => {
  try {
    const { name, email, age } = req.body;
    const [result] = await promisePool.query(
      'UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?',
      [name, email, age, req.params.id]
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'User not found' });
    }
    res.json({ id: req.params.id, name, email, age });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

Delete (DELETE) a User

app.delete('/users/:id', async (req, res) => {
  try {
    const [result] = await promisePool.query(
      'DELETE FROM users WHERE id = ?',
      [req.params.id]
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'User not found' });
    }
    res.json({ message: 'User deleted successfully' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

Using an ORM: Sequelize

For complex applications, an ORM like Sequelize can make life easier.

npm install sequelize mysql2

const { Sequelize, DataTypes } = require('sequelize');

// Connect to MySQL
const sequelize = new Sequelize('mydb', 'root', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

// Define a model
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING, allowNull: false },
  email: { type: DataTypes.STRING, allowNull: false, unique: true },
  age: { type: DataTypes.INTEGER }
});

// Sync models with database
sequelize.sync();

Two Minute Drill

MySQL and PostgreSQL are popular SQL databases for Express.
Use drivers like mysql2 or pg to connect and query.
Always use connection pools for better performance.
Use parameterized queries to prevent SQL injection.
For complex apps, consider ORMs like Sequelize.
Handle errors properly and always check if operations affected rows.

Practice Exercises Interview Questions

Need more clarification?

Drop us an email at career@quipoinfotech.com

Welcome to Quipoin

Quipoin Menu

MySQL/PostgreSQL with Express

Need more clarification?