When building modern web applications, choosing the right database is crucial for your project's success. Today, we'll dive deep into two popular database solutions: PostgreSQL with Prisma and MongoDB with Mongoose, both integrated with Node.js and Express.js.
What is PostgreSQL?
PostgreSQL is a powerful, open-source relational database management system (RDBMS) that has been around since 1986. It's known for its reliability, feature robustness, and performance. PostgreSQL uses structured query language (SQL) and follows ACID properties, making it perfect for applications that require complex relationships, transactions, and data integrity.
What is MongoDB?
MongoDB is a NoSQL document database that stores data in flexible, JSON-like documents called BSON. It's designed for scalability and flexibility, allowing developers to work with data in a more natural way for many applications. MongoDB is schema-less, meaning you can add fields to documents without having to modify the entire collection structure.
Setting Up the Project
Let's create a practical example using a Product model to demonstrate the differences between these two approaches.
PostgreSQL with Prisma Setup
- Schema Definition With Prisma, you define your database schema in a schema.prisma file:
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model Product {
id String @id @default(cuid())
name String
description String?
price Float
category String
stock Int
tags String[]
specifications Json?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@map("products")
}
2. Migration Process
Important: After defining your schema, you need to run migrations:
# Generate and apply the migration
npx prisma migrate dev --name init
# Generate the Prisma client
npx prisma generate
This creates the actual database tables and generates the type-safe client.
3. Prisma Client Setup
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
module.exports = prisma;
MongoDB with Mongoose Setup
1. Schema Definition
With Mongoose, you define schemas using JavaScript:
const mongoose = require('mongoose');
const productSchema = new mongoose.Schema({
name: {
type: String,
required: true,
trim: true
},
description: {
type: String,
trim: true
},
price: {
type: Number,
required: true,
min: 0
},
category: {
type: String,
required: true
},
stock: {
type: Number,
required: true,
min: 0
},
tags: [String],
specifications: {
type: Map,
of: mongoose.Schema.Types.Mixed
}
}, {
timestamps: true // Automatically adds createdAt and updatedAt
});
module.exports = mongoose.model('Product', productSchema);
2. Database Connection
No Migration Needed: MongoDB automatically creates collections when you first insert data.
const mongoose = require('mongoose');
const connectDB = async () => {
try {
await mongoose.connect(process.env.MONGODB_URI);
console.log('MongoDB connected successfully');
} catch (error) {
console.error('MongoDB connection error:', error);
process.exit(1);
}
};
module.exports = connectDB;
CRUD Operations Comparison
Let's see how basic operations differ between the two approaches:
Creating Products
PostgreSQL + Prisma:
const prisma = require('../lib/prisma');
// Create a new product
app.post('/api/products', async (req, res) => {
try {
const product = await prisma.product.create({
data: {
name: req.body.name,
description: req.body.description,
price: req.body.price,
category: req.body.category,
stock: req.body.stock,
tags: req.body.tags,
specifications: req.body.specifications
}
});
res.status(201).json(product);
} catch (error) {
res.status(400).json({ error: error.message });
}
});
MongoDB + Mongoose:
const Product = require('../models/Product');
// Create a new product
app.post('/api/products', async (req, res) => {
try {
const product = new Product({
name: req.body.name,
description: req.body.description,
price: req.body.price,
category: req.body.category,
stock: req.body.stock,
tags: req.body.tags,
specifications: req.body.specifications
});
const savedProduct = await product.save();
res.status(201).json(savedProduct);
} catch (error) {
res.status(400).json({ error: error.message });
}
});
Fetching Products
PostgreSQL + Prisma:
// Get all products with filtering and pagination
app.get('/api/products', async (req, res) => {
try {
const { category, minPrice, maxPrice, page = 1, limit = 10 } = req.query;
const where = {};
if (category) where.category = category;
if (minPrice || maxPrice) {
where.price = {};
if (minPrice) where.price.gte = parseFloat(minPrice);
if (maxPrice) where.price.lte = parseFloat(maxPrice);
}
const products = await prisma.product.findMany({
where,
skip: (page - 1) * limit,
take: parseInt(limit),
orderBy: { createdAt: 'desc' }
});
const totalCount = await prisma.product.count({ where });
res.json({
products,
pagination: {
page: parseInt(page),
limit: parseInt(limit),
total: totalCount,
pages: Math.ceil(totalCount / limit)
}
});
} catch (error) {
res.status(500).json({ error: error.message });
}
});
// Get single product by ID
app.get('/api/products/:id', async (req, res) => {
try {
const product = await prisma.product.findUnique({
where: { id: req.params.id }
});
if (!product) {
return res.status(404).json({ error: 'Product not found' });
}
res.json(product);
} catch (error) {
res.status(500).json({ error: error.message });
}
});
MongoDB + Mongoose:
// Get all products with filtering and pagination
app.get('/api/products', async (req, res) => {
try {
const { category, minPrice, maxPrice, page = 1, limit = 10 } = req.query;
const filter = {};
if (category) filter.category = category;
if (minPrice || maxPrice) {
filter.price = {};
if (minPrice) filter.price.$gte = parseFloat(minPrice);
if (maxPrice) filter.price.$lte = parseFloat(maxPrice);
}
const products = await Product
.find(filter)
.limit(limit * 1)
.skip((page - 1) * limit)
.sort({ createdAt: -1 });
const totalCount = await Product.countDocuments(filter);
res.json({
products,
pagination: {
page: parseInt(page),
limit: parseInt(limit),
total: totalCount,
pages: Math.ceil(totalCount / limit)
}
});
} catch (error) {
res.status(500).json({ error: error.message });
}
});
// Get single product by ID
app.get('/api/products/:id', async (req, res) => {
try {
const product = await Product.findById(req.params.id);
if (!product) {
return res.status(404).json({ error: 'Product not found' });
}
res.json(product);
} catch (error) {
res.status(500).json({ error: error.message });
}
});
Updating Products
PostgreSQL + Prisma:
app.put('/api/products/:id', async (req, res) => {
try {
const product = await prisma.product.update({
where: { id: req.params.id },
data: {
name: req.body.name,
description: req.body.description,
price: req.body.price,
category: req.body.category,
stock: req.body.stock,
tags: req.body.tags,
specifications: req.body.specifications
}
});
res.json(product);
} catch (error) {
if (error.code === 'P2025') {
return res.status(404).json({ error: 'Product not found' });
}
res.status(400).json({ error: error.message });
}
});
MongoDB + Mongoose:
app.put('/api/products/:id', async (req, res) => {
try {
const product = await Product.findByIdAndUpdate(
req.params.id,
{
name: req.body.name,
description: req.body.description,
price: req.body.price,
category: req.body.category,
stock: req.body.stock,
tags: req.body.tags,
specifications: req.body.specifications
},
{ new: true, runValidators: true }
);
if (!product) {
return res.status(404).json({ error: 'Product not found' });
}
res.json(product);
} catch (error) {
res.status(400).json({ error: error.message });
}
});
Key Differences Explained
3. Data Types
PostgreSQL + Prisma:
model Product {
id String @id @default(cuid())
price Float
tags String[] // Array of strings
data Json? // JSON field
}
MongoDB + Mongoose:
const schema = new mongoose.Schema({
_id: mongoose.Schema.Types.ObjectId,
price: Number,
tags: [String],
data: mongoose.Schema.Types.Mixed
});
When to Choose Which?
Choose PostgreSQL + Prisma when:
- You need ACID transactions
- Complex relationships between data
- Strong consistency is crucial
- You want type safety and compile-time checks
- Your team is familiar with SQL
Choose MongoDB + Mongoose when:
- Rapid prototyping and iteration
- Flexible, evolving data structures
- Horizontal scaling requirements
- Document-based data fits naturally
- You need high write performance
Conclusion
Both PostgreSQL with Prisma and MongoDB with Mongoose are excellent choices for Node.js applications, but they serve different needs. PostgreSQL excels in structured data scenarios requiring strong consistency and complex queries, while MongoDB shines in flexible, document-based applications that need rapid development and scaling.
The choice ultimately depends on your specific use case, team expertise, and long-term scalability requirements. Consider starting with the technology that best matches your current needs while keeping future requirements in mind.
What's your experience with these databases? Share your thoughts in the comments below!
Top comments (0)