MongoDB - SQL Databases

SQL Databases

We will learn how to use Sequelize, a powerful and flexible ORM (Object-Relational Mapping) tool for Node.js, to connect to SQL databases like MySQL and PostgreSQL in this section. Sequelize hides the difficulty of working with SQL databases so that you can use JavaScript objects instead of writing raw SQL queries to deal with your database. We will talk about how to link to a database, set up schemas and models, and do CRUD tasks.

Step 1: Initialize the Project

To begin, make a new Node.js app and add the resources you need:

mkdir sequelize-app
cd sequelize-app
npm init -y

Install Sequelize and Database Drivers

Next, install both Sequelize and the MySQL or PostgreSQL database tools that you need.

For MySQL:

npm install sequelize mysql2

For PostgreSQL:

npm install sequelize pg pg-hstore

• Sequelize: The ORM tool you will use to talk to the database.
• mysql2: Required for connecting to MySQL databases.
• pg and pg-hstore: Required for PostgreSQL connections.

Connecting to the Database

Getting connected to your SQL database is the next step after setting up your project. Create a config/database.js file:

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

const sequelize = new Sequelize('database_name', 'username', 'password', {
    host: 'localhost',
    dialect: 'mysql', // or 'postgres' for PostgreSQL
});

sequelize.authenticate()
    .then(() => console.log('Database connected...'))
    .catch(err => console.log('Error: ' + err));

module.exports = sequelize;

• Sequelize constructor: Sets up a new version of Sequelize, which uses the details you give it to connect to your SQL database.
• authenticate(): Tests the connection to the database and logs success or error.
• dialect: Indicates whether you're using MySQL or PostgreSQL.

Defining Schemas and Models

Now that you've connected to the database, you can use Sequelize models to create your data layout. Create a models/user.js file:

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const User = sequelize.define('User', {
    firstName: {
        type: DataTypes.STRING,
        allowNull: false
    },
    lastName: {
        type: DataTypes.STRING,
        allowNull: false
    },
    email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
    }
}, {
    timestamps: true
});

module.exports = User;

• Model: define() creates a "User" model with fields for firstName, lastName, and email.
• DataTypes: Used to specify the data types of model fields (like STRING).
• timestamps: true: Automatically adds createdAt and updatedAt fields to the table.

Performing CRUD Operations

Once the models are set, you can do CRUD tasks on the database. Create a routes/userRoutes.js file:

const express = require('express');
const User = require('../models/user');
const router = express.Router();

// Create a new user
router.post('/users', async (req, res) => {
    try {
        const user = await User.create(req.body);
        res.status(201).json(user);
    } catch (error) {
        res.status(400).json({ error: error.message });
    }
});

// Get all users
router.get('/users', async (req, res) => {
    try {
        const users = await User.findAll();
        res.json(users);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// Get a user by ID
router.get('/users/:id', async (req, res) => {
    try {
        const user = await User.findByPk(req.params.id);
        if (user) {
            res.json(user);
        } else {
            res.status(404).json({ error: 'User not found' });
        }
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// Update a user by ID
router.put('/users/:id', async (req, res) => {
    try {
        const user = await User.update(req.body, { where: { id: req.params.id }, returning: true });
        res.json(user);
    } catch (error) {
        res.status(400).json({ error: error.message });
    }
});

// Delete a user by ID
router.delete('/users/:id', async (req, res) => {
    try {
        const result = await User.destroy({ where: { id: req.params.id } });
        if (result) {
            res.json({ message: 'User deleted' });
        } else {
            res.status(404).json({ error: 'User not found' });
        }
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

module.exports = router;

• Create: Adds a new user to the database using User.create().
• Read: Fetches all users or a specific user by ID using findAll() and findByPk().
• Update: Modifies a user’s data by ID using update().
• Delete: Removes a user from the database using destroy().

Syncing the Database

Make sure that Sequelize syncs your models with the database before you run the server. Update app.js to include syncing:

const express = require('express');
const sequelize = require('./config/database');
const userRoutes = require('./routes/userRoutes');

const app = express();

app.use(express.json());
app.use('/api', userRoutes);

sequelize.sync({ alter: true })
    .then(() => {
        app.listen(3000, () => {
            console.log('Server is running on http://localhost:3000');
        });
    })
    .catch(err => console.log('Error: ' + err));

• sequelize.sync({ alter: true }): Syncs the models with the database. If the tables don't exist, it creates them.
• alter: true: Ensures models are updated without data loss.