12 Database Integration Mongoose Prisma

title: Database Integration with Mongoose and Prisma in Node.js weight: 22 date: 2026-06-28 lastmod: 2026-06-28 description: Learn database integration for Node.js REST APIs using Mongoose (MongoDB) and Prisma (SQL) with schema definitions, queries, relationships, and migrations. tags: [api-development, nodejs]

Database integration for Node.js REST APIs uses Mongoose for MongoDB with flexible schemas and Prisma for SQL databases with type-safe queries, migrations, and powerful relationship handling.

```mermaid
flowchart TD
  A[Database ORM] --> B[Mongoose - MongoDB]
  A --> C[Prisma - SQL]
  B --> D[Schema Definition]
  B --> E[CRUD Operations]
  B --> F[Population]
  C --> G[Schema Definition]
  C --> H[Migrations]
  C --> I[Type-safe Queries]
  style A fill:#e1f5fe
  style B fill:#c8e6c9
  style C fill:#c8e6c9

Mongoose provides a schema-based solution for MongoDB with built-in validation, middleware (hooks), and population for references. Prisma provides an ORM for SQL databases with auto-generated types, migrations, and an intuitive query API. Both handle CRUD, relationships, and indexing.

Think of Mongoose like a flexible storage unit where you can rearrange items as needed (schemaless). Prisma is like a library with a card catalog (strict schema) where every book has a designated spot.

Example: Mongoose Schema and Model

const mongoose = require('mongoose');

const userSchema = new mongoose.Schema({
  name: { type: String, required: true, trim: true },
  email: { type: String, required: true, unique: true, lowercase: true },
  password: { type: String, required: true, select: false },
  role: { type: String, enum: ['user', 'admin'], default: 'user' },
  createdAt: { type: Date, default: Date.now }
}, { timestamps: true });

// Index
userSchema.index({ email: 1 });

// Pre-save hook
userSchema.pre('save', async function(next) {
  if (this.isModified('password')) {
    this.password = await bcrypt.hash(this.password, 12);
  }
  next();
});

// Instance method
userSchema.methods.toJSON = function() {
  const obj = this.toObject();
  delete obj.password;
  delete obj.__v;
  return obj;
};

module.exports = mongoose.model('User', userSchema);

Example: Prisma Schema

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

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

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

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

enum Role {
  USER
  ADMIN
}

Example: CRUD with Prisma Client

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

app.get('/api/users/:id', async (req, res, next) => {
  try {
    const user = await prisma.user.findUnique({
      where: { id: parseInt(req.params.id) },
      include: { posts: { select: { id: true, title: true } } }
    });

    if (!user) {
      return res.status(404).json({ error: 'User not found' });
    }

    delete user.password;
    res.json({ status: 'success', data: user });
  } catch (error) {
    next(error);
  }
});

app.post('/api/users', async (req, res, next) => {
  try {
    const hashedPassword = await bcrypt.hash(req.body.password, 12);
    const user = await prisma.user.create({
      data: {
        name: req.body.name,
        email: req.body.email,
        password: hashedPassword,
        role: 'USER'
      }
    });
    delete user.password;
    res.status(201).json({ status: 'success', data: user });
  } catch (error) {
    if (error.code === 'P2002') {
      return res.status(409).json({ error: 'Email already exists' });
    }
    next(error);
  }
});

Common Mistakes

1. Not using connection pooling — Every database connection is a resource. Use connection pooling (mongoose.createConnection() or Prisma's built-in pool) to reuse connections.
2. Forgetting to handle unique constraint violations — Unique fields (email, username) cause errors on duplicate entry. Catch these and return 409 Conflict.
3. Selecting sensitive fields by default — In Mongoose, use select: false on password fields. In Prisma, use a select statement that excludes sensitive fields.
4. Not using indexes for queried fields — Queries on unindexed fields are slow on large datasets. Index fields used in WHERE, sort, and join conditions.
5. N+1 query problem — Fetching data in a loop creates N+1 queries. Use eager loading (Mongoose .populate()) or include (Prisma include) to fetch related data in one query.

Practice Questions

1. What is the difference between Mongoose and Prisma?
2. How do you handle one-to-many relationships in Prisma?
3. Why should you index frequently queried fields?
4. What is the N+1 query problem and how do you prevent it?
5. Challenge: Design a database schema for an e-commerce platform with Users, Products, Orders, and OrderItems. Implement it with both Mongoose and Prisma. Include relationships, indexes, and timestamps.

FAQ

Mini Project

Build a database layer for a blog API using Prisma (SQL) and Mongoose (MongoDB). Implement User and Post models with relationships, CRUD operations, proper indexing, and error handling for constraint violations. Write and run migrations for the Prisma schema.

What's Next

Now learn about CRUD endpoints in Building REST APIs with Node.js.