PostgreSQL with Node.js

Using Sequelize ORM with PostgreSQL

Sequelize is a promise-based Node.js ORM (Object-Relational Mapping) that supports PostgreSQL and several other databases. It provides an abstraction layer that maps JavaScript objects to database entities, making database interactions more intuitive and type-safe.

Advantages of Using an ORM

• Database Agnosticism: Switch between different database systems with minimal code changes
• Model Definitions: Define your schema in JavaScript/TypeScript with validation
• Migrations: Manage database schema changes over time
• Associations: Define and work with relationships easily
• Data Type Safety: Automatic type conversions and validations
• Query Building: Build complex queries without writing raw SQL

Setting Up Sequelize

# Install Sequelize and related packages
npm install sequelize sequelize-cli pg pg-hstore

# Initialize Sequelize in your project
npx sequelize-cli init
            

This creates several directories:

• config/ - Database configuration
• models/ - Model definitions
• migrations/ - Database migrations
• seeders/ - Seed data for development

Configure Database Connection

Edit config/config.json:

{
  "development": {
    "username": "myapp_user",
    "password": "myapp_password",
    "database": "myapp_development",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "test": {
    "username": "myapp_user",
    "password": "myapp_password",
    "database": "myapp_test",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
    "username": "myapp_user",
    "password": "myapp_password",
    "database": "myapp_production",
    "host": "127.0.0.1",
    "dialect": "postgres",
    "logging": false
  }
}
            

Creating Models with Sequelize

// Using the Sequelize CLI to generate a model (and migration)
npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string,password:string

// Alternatively, define a model manually in models/user.js
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    firstName: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        notEmpty: true
      }
    },
    lastName: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        notEmpty: true
      }
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: {
        isEmail: true
      }
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        len: [6, 100] // Minimum 6 characters
      }
    }
  }, {
    // Model options
    tableName: 'users',
    timestamps: true // Adds createdAt and updatedAt
  });
  
  // Define associations
  User.associate = (models) => {
    // For example, a User has many Posts
    User.hasMany(models.Post, {
      foreignKey: 'userId',
      as: 'posts',
      onDelete: 'CASCADE'
    });
  };
  
  return User;
};
            

Running Migrations

# Run all pending migrations
npx sequelize-cli db:migrate

# Undo the last migration
npx sequelize-cli db:migrate:undo

# Undo all migrations
npx sequelize-cli db:migrate:undo:all
            

Creating and Running Seeders

# Generate a seeder file
npx sequelize-cli seed:generate --name demo-users

# Edit the seeder file (seeders/xxxx-demo-users.js)
module.exports = {
  up: async (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('Users', [
      {
        firstName: 'John',
        lastName: 'Doe',
        email: 'john@example.com',
        password: 'hashedpassword123', // In real app, use bcrypt to hash
        createdAt: new Date(),
        updatedAt: new Date()
      },
      {
        firstName: 'Jane',
        lastName: 'Smith',
        email: 'jane@example.com',
        password: 'hashedpassword456',
        createdAt: new Date(),
        updatedAt: new Date()
      }
    ]);
  },

  down: async (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('Users', null, {});
  }
};

# Run seeders
npx sequelize-cli db:seed:all
            

Using Sequelize Models in Express

// app.js - Main application file with Sequelize
const express = require('express');
const { sequelize, User, Post } = require('./models');

const app = express();
app.use(express.json());

// GET all users
app.get('/api/users', async (req, res) => {
  try {
    const users = await User.findAll({
      attributes: ['id', 'firstName', 'lastName', 'email', 'createdAt'],
      // Exclude password for security
    });
    res.json(users);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET user by ID including their posts
app.get('/api/users/:id', async (req, res) => {
  try {
    const user = await User.findByPk(req.params.id, {
      attributes: ['id', 'firstName', 'lastName', 'email', 'createdAt'],
      include: [{
        model: Post,
        as: 'posts',
        attributes: ['id', 'title', 'content']
      }]
    });
    
    if (!user) {
      return res.status(404).json({ error: 'User not found' });
    }
    
    res.json(user);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// POST new user
app.post('/api/users', async (req, res) => {
  try {
    const { firstName, lastName, email, password } = req.body;
    
    // Create user
    const user = await User.create({
      firstName,
      lastName,
      email,
      password, // In real app, hash this password first!
    });
    
    res.status(201).json({
      id: user.id,
      firstName: user.firstName,
      lastName: user.lastName,
      email: user.email,
      createdAt: user.createdAt
    });
  } catch (err) {
    console.error(err);
    
    // Handle validation errors
    if (err.name === 'SequelizeValidationError') {
      return res.status(400).json({
        error: 'Validation Error',
        details: err.errors.map(e => e.message)
      });
    }
    
    // Handle unique constraint errors
    if (err.name === 'SequelizeUniqueConstraintError') {
      return res.status(400).json({
        error: 'Email already in use'
      });
    }
    
    res.status(500).json({ error: 'Internal server error' });
  }
});

// Start server and sync with database
const PORT = process.env.PORT || 3000;
app.listen(PORT, async () => {
  console.log(`Server running on port ${PORT}`);
  
  // Sync database models (in development only!)
  // In production, use migrations instead
  try {
    await sequelize.authenticate();
    console.log('Database connection has been established successfully.');
    
    // Sync models with database (creates tables if they don't exist)
    // Force: true will drop tables first - CAREFUL with this in production!
    // await sequelize.sync({ force: true });
    
    await sequelize.sync();
    console.log('Database synchronized');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
  }
});
            

Sequelize Query Examples

// Find one user by email
const user = await User.findOne({
  where: { email: 'john@example.com' }
});

// Find all users with lastName 'Smith'
const smiths = await User.findAll({
  where: { lastName: 'Smith' }
});

// Find users with complex conditions
const users = await User.findAll({
  where: {
    [Op.or]: [
      { firstName: 'John' },
      { lastName: 'Doe' }
    ]
  },
  order: [['createdAt', 'DESC']],
  limit: 10
});

// Update a user
await User.update(
  { firstName: 'Johnny' },
  { where: { id: 1 } }
);

// Delete a user
await User.destroy({
  where: { id: 5 }
});

// Count users
const count = await User.count({
  where: { 
    createdAt: {
      [Op.gte]: new Date(new Date() - 7 * 24 * 60 * 60 * 1000) // Last 7 days
    }
  }
});

// Pagination
const PAGE_SIZE = 10;
const page = req.query.page || 1;

const { count, rows } = await User.findAndCountAll({
  limit: PAGE_SIZE,
  offset: (page - 1) * PAGE_SIZE,
  order: [['createdAt', 'DESC']]
});

// Response with pagination metadata
res.json({
  users: rows,
  pagination: {
    totalItems: count,
    totalPages: Math.ceil(count / PAGE_SIZE),
    currentPage: parseInt(page, 10)
  }
});