Database Integration

Database Options for Express

Express works with any database. The most popular choices are MongoDB with Mongoose and PostgreSQL with Prisma. Choose based on your data structure and scaling needs.

MongoDB with Mongoose

npm install mongoose
npm install -D @types/mongoose

// db/connection.ts
import mongoose from 'mongoose';

const MONGODB_URI = process.env.MONGODB_URI || 'mongodb://localhost:27017/myapp';

export const connectDB = async () => {
  try {
    await mongoose.connect(MONGODB_URI);
    console.log('✅ MongoDB connected');
  } catch (error) {
    console.error('❌ MongoDB connection error:', error);
    process.exit(1);
  }
};

// Handle connection events
mongoose.connection.on('disconnected', () => {
  console.log('MongoDB disconnected');
});

mongoose.connection.on('error', (err) => {
  console.error('MongoDB error:', err);
});

// server.ts
import { connectDB } from './db/connection';
import app from './app';

const start = async () => {
  await connectDB();
  app.listen(3000, () => console.log('Server running'));
};

start();

Mongoose Models

// models/User.ts
import mongoose, { Document, Schema } from 'mongoose';
import bcrypt from 'bcrypt';

export interface IUser extends Document {
  email: string;
  password: string;
  name: string;
  role: 'user' | 'admin';
  createdAt: Date;
  comparePassword(candidatePassword: string): Promise<boolean>;
}

const userSchema = new Schema<IUser>({
  email: {
    type: String,
    required: [true, 'Email is required'],
    unique: true,
    lowercase: true,
    trim: true
  },
  password: {
    type: String,
    required: [true, 'Password is required'],
    minlength: 8,
    select: false  // Don't include in queries by default
  },
  name: {
    type: String,
    required: true,
    trim: true
  },
  role: {
    type: String,
    enum: ['user', 'admin'],
    default: 'user'
  }
}, {
  timestamps: true  // Adds createdAt and updatedAt
});

// Hash password before saving
userSchema.pre('save', async function(next) {
  if (!this.isModified('password')) return next();
  this.password = await bcrypt.hash(this.password, 12);
  next();
});

// Instance method
userSchema.methods.comparePassword = async function(candidatePassword: string) {
  return bcrypt.compare(candidatePassword, this.password);
};

// Static method
userSchema.statics.findByEmail = function(email: string) {
  return this.findOne({ email });
};

export const User = mongoose.model<IUser>('User', userSchema);

Mongoose CRUD Operations

// routes/users.ts
import { Router } from 'express';
import { User } from '../models/User';
import { NotFoundError } from '../errors/AppError';

const router = Router();

// Create
router.post('/', async (req, res) => {
  const user = await User.create(req.body);
  res.status(201).json(user);
});

// Read all with pagination
router.get('/', async (req, res) => {
  const page = parseInt(req.query.page as string) || 1;
  const limit = parseInt(req.query.limit as string) || 10;
  const skip = (page - 1) * limit;

  const [users, total] = await Promise.all([
    User.find().skip(skip).limit(limit).sort({ createdAt: -1 }),
    User.countDocuments()
  ]);

  res.json({
    users,
    pagination: {
      page,
      limit,
      total,
      pages: Math.ceil(total / limit)
    }
  });
});

// Read one
router.get('/:id', async (req, res) => {
  const user = await User.findById(req.params.id);
  if (!user) throw new NotFoundError('User');
  res.json(user);
});

// Update
router.patch('/:id', async (req, res) => {
  const user = await User.findByIdAndUpdate(
    req.params.id,
    req.body,
    { new: true, runValidators: true }
  );
  if (!user) throw new NotFoundError('User');
  res.json(user);
});

// Delete
router.delete('/:id', async (req, res) => {
  const user = await User.findByIdAndDelete(req.params.id);
  if (!user) throw new NotFoundError('User');
  res.status(204).send();
});

export default router;

PostgreSQL with Prisma

npm install prisma @prisma/client
npx prisma init

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  password  String
  name      String
  role      Role     @default(USER)
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
  createdAt DateTime @default(now())
}

enum Role {
  USER
  ADMIN
}

// Generate client
npx prisma generate

// Push schema to database
npx prisma db push

📖 Prisma Documentation →

Prisma Client Usage

// db/prisma.ts
import { PrismaClient } from '@prisma/client';

// Prevent multiple instances in development
const globalForPrisma = global as unknown as { prisma: PrismaClient };

export const prisma = globalForPrisma.prisma || new PrismaClient({
  log: ['query', 'error', 'warn']
});

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

// routes/users.ts with Prisma
import { Router } from 'express';
import { prisma } from '../db/prisma';
import bcrypt from 'bcrypt';

const router = Router();

// Create user
router.post('/', async (req, res) => {
  const { email, password, name } = req.body;
  
  const user = await prisma.user.create({
    data: {
      email,
      password: await bcrypt.hash(password, 12),
      name
    },
    select: {
      id: true,
      email: true,
      name: true,
      role: true
    }
  });
  
  res.status(201).json(user);
});

// Get all with relations
router.get('/', async (req, res) => {
  const users = await prisma.user.findMany({
    include: {
      posts: {
        where: { published: true }
      },
      _count: {
        select: { posts: true }
      }
    }
  });
  
  res.json(users);
});

// Get one
router.get('/:id', async (req, res) => {
  const user = await prisma.user.findUnique({
    where: { id: req.params.id },
    include: { posts: true }
  });
  
  if (!user) throw new NotFoundError('User');
  res.json(user);
});

// Update
router.patch('/:id', async (req, res) => {
  const user = await prisma.user.update({
    where: { id: req.params.id },
    data: req.body
  });
  
  res.json(user);
});

// Delete
router.delete('/:id', async (req, res) => {
  await prisma.user.delete({
    where: { id: req.params.id }
  });
  
  res.status(204).send();
});

// Complex queries
router.get('/search', async (req, res) => {
  const { q } = req.query;
  
  const users = await prisma.user.findMany({
    where: {
      OR: [
        { name: { contains: q as string, mode: 'insensitive' } },
        { email: { contains: q as string, mode: 'insensitive' } }
      ]
    }
  });
  
  res.json(users);
});

export default router;

Database Best Practices

// 1. Use transactions for related operations
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: userData });
  const profile = await tx.profile.create({ 
    data: { userId: user.id, ...profileData } 
  });
  return { user, profile };
});

// Mongoose transaction
const session = await mongoose.startSession();
session.startTransaction();
try {
  const user = await User.create([userData], { session });
  await Profile.create([{ userId: user[0].id }], { session });
  await session.commitTransaction();
} catch (error) {
  await session.abortTransaction();
  throw error;
} finally {
  session.endSession();
}

// 2. Use indexes for performance
// Mongoose
userSchema.index({ email: 1 });
userSchema.index({ createdAt: -1 });

// Prisma - in schema
model User {
  email String @unique
  @@index([createdAt])
}

// 3. Connection pooling
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL
    }
  }
});

// 4. Graceful shutdown
process.on('SIGTERM', async () => {
  await prisma.$disconnect();
  await mongoose.disconnect();
  process.exit(0);
});