Express with SQL Databases
MongoDB suits flexible, document-shaped data, but many real-world applications use SQL databases like MySQL or PostgreSQL. SQL databases store data in structured tables with relationships between them — ideal for banking systems, e-commerce platforms, and any application where data integrity and complex queries matter. This topic connects Express to a SQL database using Sequelize, the most popular SQL ORM for Node.js.
SQL vs NoSQL: The Right Tool for the Job
Think of a SQL database like a spreadsheet with strict rules: every row must fill in every column, you cannot add a column halfway through, and tables can reference each other. A NoSQL database (MongoDB) is like a folder of sticky notes — each note can have different information and you can add fields at any time.
SQL Database (PostgreSQL / MySQL):
┌─────┬──────────┬────────────────────┬─────────┐
│ id │ name │ email │ role │
├─────┼──────────┼────────────────────┼─────────┤
│ 1 │ Alice │ alice@example.com │ admin │
│ 2 │ Bob │ bob@example.com │ user │
└─────┴──────────┴────────────────────┴─────────┘
↑ Every row must have every column
NoSQL Database (MongoDB):
{ _id: 1, name: "Alice", email: "alice@example.com", role: "admin" }
{ _id: 2, name: "Bob", hobbies: ["reading"] } ← Different fields OK
When to Choose SQL
┌──────────────────────────────────────────────────────────────────┐ │ Choose SQL When... │ ├──────────────────────────────────────────────────────────────────┤ │ Data has fixed structure (user accounts, orders, products) │ │ Data has clear relationships (users have orders, orders have │ │ products) │ │ Data integrity is critical (financial transactions) │ │ You need complex multi-table queries (JOINs, aggregations) │ │ Your team already uses MySQL or PostgreSQL │ └──────────────────────────────────────────────────────────────────┘
Install Sequelize and a Database Driver
Sequelize supports PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL. Install Sequelize plus the driver for your chosen database:
npm install sequelize # PostgreSQL: npm install pg pg-hstore # MySQL: npm install mysql2 # SQLite (great for development, no server needed): npm install sqlite3
Connect to the Database
const { Sequelize } = require('sequelize');
// PostgreSQL connection
const sequelize = new Sequelize(process.env.DATABASE_URL, {
dialect: 'postgres',
logging: false // Disable SQL query logging (set to console.log to see queries)
});
// SQLite (file-based, ideal for development)
const sequelize = new Sequelize({
dialect: 'sqlite',
storage: './database.sqlite'
});
// Test the connection
const connectDB = async () => {
try {
await sequelize.authenticate();
console.log('Database connected successfully');
} catch (err) {
console.error('Connection failed:', err);
process.exit(1);
}
};
connectDB();
Define a Model
A Sequelize model defines a table's columns, data types, and validation rules — similar to a Mongoose schema for MongoDB:
const { DataTypes } = require('sequelize');
const User = sequelize.define('User', {
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true
},
name: {
type: DataTypes.STRING(100),
allowNull: false,
validate: {
notEmpty: true,
len: [2, 100]
}
},
email: {
type: DataTypes.STRING,
allowNull: false,
unique: true,
validate: {
isEmail: true
}
},
role: {
type: DataTypes.ENUM('user', 'admin'),
defaultValue: 'user'
}
}, {
tableName: 'users', // Explicit table name
timestamps: true // Adds createdAt and updatedAt columns automatically
});
module.exports = User;
Sync Models with the Database
Sequelize can create database tables directly from your models:
// Sync all models — creates tables if they don't exist
await sequelize.sync();
// Sync and drop existing tables first (DANGEROUS — only for development)
await sequelize.sync({ force: true });
// Add new columns without dropping existing data
await sequelize.sync({ alter: true });
In production, use database migrations instead of sync() to manage schema changes safely without data loss.
CRUD Operations with Sequelize
CREATE
app.post('/api/users', async (req, res) => {
try {
const user = await User.create({
name: req.body.name,
email: req.body.email
});
res.status(201).json({ success: true, data: user });
} catch (err) {
if (err.name === 'SequelizeUniqueConstraintError') {
return res.status(400).json({ error: 'Email already exists' });
}
res.status(400).json({ error: err.message });
}
});
READ ALL with Filtering
app.get('/api/users', async (req, res) => {
try {
const { role, search } = req.query;
const where = {};
if (role) where.role = role;
if (search) {
const { Op } = require('sequelize');
where.name = { [Op.like]: `%${search}%` };
}
const users = await User.findAll({
where,
attributes: ['id', 'name', 'email', 'role', 'createdAt'],
order: [['createdAt', 'DESC']]
});
res.json({ count: users.length, data: users });
} catch (err) {
res.status(500).json({ error: err.message });
}
});
READ ONE
app.get('/api/users/:id', async (req, res) => {
try {
const user = await User.findByPk(req.params.id);
if (!user) return res.status(404).json({ error: 'User not found' });
res.json({ data: user });
} catch (err) {
res.status(500).json({ error: err.message });
}
});
UPDATE
app.patch('/api/users/:id', async (req, res) => {
try {
const user = await User.findByPk(req.params.id);
if (!user) return res.status(404).json({ error: 'User not found' });
await user.update(req.body);
res.json({ data: user });
} catch (err) {
res.status(400).json({ error: err.message });
}
});
DELETE
app.delete('/api/users/:id', async (req, res) => {
try {
const user = await User.findByPk(req.params.id);
if (!user) return res.status(404).json({ error: 'User not found' });
await user.destroy();
res.json({ message: 'User deleted' });
} catch (err) {
res.status(500).json({ error: err.message });
}
});
Table Relationships (Associations)
SQL databases excel at modeling relationships between tables. Sequelize defines these in code:
const Post = sequelize.define('Post', {
title: { type: DataTypes.STRING, allowNull: false },
content: { type: DataTypes.TEXT }
});
// One User has many Posts
User.hasMany(Post, { foreignKey: 'userId', as: 'posts' });
Post.belongsTo(User, { foreignKey: 'userId', as: 'author' });
// Sync both models
await sequelize.sync();
Query a user along with all their posts in one call using include:
app.get('/api/users/:id/posts', async (req, res) => {
const user = await User.findByPk(req.params.id, {
include: [{ model: Post, as: 'posts' }]
});
if (!user) return res.status(404).json({ error: 'User not found' });
res.json({ data: user });
});
Sequelize generates this SQL behind the scenes:
SELECT users.*, posts.* FROM users LEFT JOIN posts ON posts.userId = users.id WHERE users.id = 1;
Sequelize Operators for Complex Queries
const { Op } = require('sequelize');
// Users created in the last 7 days
const recent = await User.findAll({
where: {
createdAt: { [Op.gte]: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000) }
}
});
// Users with name starting with 'A' or role of 'admin'
const filtered = await User.findAll({
where: {
[Op.or]: [
{ name: { [Op.startsWith]: 'A' } },
{ role: 'admin' }
]
}
});
Summary
SQL databases use structured tables with strict schemas and support powerful relationships between data. Sequelize connects Express to SQL databases including PostgreSQL, MySQL, and SQLite. Define models with sequelize.define() to describe table columns, data types, and validations. Use sequelize.sync() in development to create tables from models. Perform CRUD with Model.create(), Model.findAll(), Model.findByPk(), instance.update(), and instance.destroy(). Model associations like hasMany and belongsTo let Sequelize generate SQL JOINs automatically when you use the include option on queries. Use Sequelize's Op operators for flexible filtering without writing raw SQL.