Using PostgreSQL with Node.js APIs: pg and Sequelize

Why PostgreSQL?

PostgreSQL is a powerful, open-source relational database with excellent JSON support and advanced features.

Setting Up PostgreSQL

# Install pg driver
npm install pg

# Or use Sequelize ORM
npm install sequelize pg pg-hstore

Basic Connection with pg

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

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

// Query example
app.get('/api/users', async (req, res) => {
    try {
        const result = await pool.query('SELECT * FROM users');
        res.json(result.rows);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// Insert example
app.post('/api/users', async (req, res) => {
    const { name, email } = req.body;
    try {
        const result = await pool.query(
            'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
            [name, email]
        );
        res.status(201).json(result.rows[0]);
    } catch (error) {
        res.status(400).json({ error: error.message });
    }
});

Using Sequelize ORM

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

const sequelize = new Sequelize('myapp', 'postgres', 'password', {
    host: 'localhost',
    dialect: 'postgres'
});

// Define Model
const User = sequelize.define('User', {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false
    },
    email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
    }
});

// Sync database
await sequelize.sync();

// CRUD Operations
app.post('/api/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 });
    }
});

app.get('/api/users', async (req, res) => {
    const users = await User.findAll();
    res.json(users);
});

app.get('/api/users/:id', async (req, res) => {
    const user = await User.findByPk(req.params.id);
    if (!user) return res.status(404).json({ error: 'User not found' });
    res.json(user);
});

app.put('/api/users/:id', async (req, res) => {
    const [updated] = await User.update(req.body, {
        where: { id: req.params.id }
    });
    if (!updated) return res.status(404).json({ error: 'User not found' });
    const user = await User.findByPk(req.params.id);
    res.json(user);
});

app.delete('/api/users/:id', async (req, res) => {
    const deleted = await User.destroy({
        where: { id: req.params.id }
    });
    if (!deleted) return res.status(404).json({ error: 'User not found' });
    res.json({ message: 'User deleted' });
});

Advanced Queries

// Find with conditions
const users = await User.findAll({
    where: {
        age: { [Op.gte]: 18 }
    },
    order: [['createdAt', 'DESC']],
    limit: 10
});

// Include associations
const user = await User.findByPk(id, {
    include: [Post]
});

// Raw queries
const result = await sequelize.query(
    'SELECT * FROM users WHERE age > :age',
    {
        replacements: { age: 18 },
        type: QueryTypes.SELECT
    }
);

Best Practices

• Use connection pooling
• Use parameterized queries to prevent SQL injection
• Create indexes on frequently queried columns
• Use transactions for complex operations
• Handle connection errors gracefully