Optimizing Backend Development with Prisma and Mongoose with MongoDB

Overview

In the constantly changing world of backend development, the performance of your application and the developer experience can be greatly impacted by the database management tools you use. The use of Prisma with PostgreSQL and the integration of Mongoose for MongoDB functions are examined in this blog.

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

Understanding the Technologies

Prisma: The Next-Generation ORM

Prisma is a modern Object-Relational Mapping (ORM) tool that simplifies database operations with a type-safe API. It provides an intuitive way to define your database schema and interact with your database, offering features like:

Auto-generated migrations
Type-safe database queries
Powerful query builder
Support for multiple databases

PostgreSQL: The Robust Relational Database

PostgreSQL is a powerful, open-source relational database system known for its reliability, and performance. It excels at handling complex queries and relationships between data, making it ideal for structured data.

Mongoose: The MongoDB Object Modeling Tool

While primarily used with MongoDB, Mongoose provides schema validation, relationship management, and a simple query API. Some developers prefer its schema modeling capabilities even when working with relational databases.

Why Combine These Technologies?

You might wonder why you would use Prisma and Mongoose in a PostgreSQL project. Here are some compelling reasons:

Transition Strategy: When migrating from MongoDB to PostgreSQL, keeping Mongoose temporarily can ease the transition.
Best of Both Worlds: Leverage Prisma’s type safety with PostgreSQL while utilizing Mongoose’s schema validation.
Polyglot Persistence: Some parts of your application might benefit from MongoDB’s document model, while others need PostgreSQL’s relational capabilities.

Setting Up Your Project

Initial Setup

mkdir prisma-postgres-mongoose-app
cd prisma-postgres-mongoose-app
npm init -y
npm install prisma @prisma/client pg mongoose
npx prisma init

1

2

3

4

5

mkdir prisma-postgres-mongoose-app

cd prisma-postgres-mongoose-app

npm init -y

npm install prisma @prisma/client pg mongoose

npx prisma init

Configure Prisma with PostgreSQL

Modify your prisma/schema.prisma file:

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

generator client {

provider = "prisma-client-js"

}

datasource db {

provider = "postgresql"

url = env("DATABASE_URL")

}

model User {

id Int @id @default(autoincrement())

email String @unique

name String?

posts Post[]

createdAt DateTime @default(now())

updatedAt DateTime @updatedAt

}

model Post {

id Int @id @default(autoincrement())

title String

content String?

published Boolean @default(false)

author User @relation(fields: [authorId], references: [id])

authorId Int

createdAt DateTime @default(now())

updatedAt DateTime @updatedAt

}

Create a .env file in your project root:

DATABASE_URL="postgresql://username:password@localhost:5432/mydb?schema=public"
MONGODB_URI="mongodb://localhost:27017/mydb"

1 2	DATABASE_URL="postgresql://username:password@localhost:5432/mydb?schema=public" MONGODB_URI="mongodb://localhost:27017/mydb"

Setting Up Mongoose Schemas

Create a models directory and set up your Mongoose schemas:

// models/user.js
const mongoose = require('mongoose');

const userSchema = new mongoose.Schema({
  email: { 
    type: String, 
    required: true, 
    unique: true 
  },
  name: String,
  externalId: Number,  // To reference Prisma's User.id
  metadata: Object,    // Additional data not in PostgreSQL
}, { timestamps: true });

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

// models/user.js

const mongoose = require('mongoose');

const userSchema = new mongoose.Schema({

email: {

type: String,

required: true,

unique: true

},

externalId: Number, // To reference Prisma's User.id

metadata: Object, // Additional data not in PostgreSQL

}, { timestamps: true });

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

Integration Strategy

Database Connection Setup

Create a database.js file for centralizing your database connections:

// database.js
const { PrismaClient } = require('@prisma/client');
const mongoose = require('mongoose');
require('dotenv').config();

const prisma = new PrismaClient();

async function connectToMongoDB() {
  try {
    await mongoose.connect(process.env.MONGODB_URI);
    console.log('Connected to MongoDB');
  } catch (error) {
    console.error('MongoDB connection error:', error);
    process.exit(1);
  }
}

module.exports = { prisma, connectToMongoDB };

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

// database.js

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

const mongoose = require('mongoose');

require('dotenv').config();

const prisma = new PrismaClient();

async function connectToMongoDB() {

try {

await mongoose.connect(process.env.MONGODB_URI);

console.log('Connected to MongoDB');

} catch (error) {

console.error('MongoDB connection error:', error);

process.exit(1);

}

module.exports = { prisma, connectToMongoDB };

Creating Services for Data Operations

Organize your business logic with service files:

// services/userService.js
const { prisma } = require('../database');
const UserModel = require('../models/user');

async function createUser(userData) {
  // Create user in PostgreSQL using Prisma
  const prismaUser = await prisma.user.create({
    data: {
      email: userData.email,
      name: userData.name || null
    }
  });
  
  // Store additional data in MongoDB using Mongoose
  const mongoUser = await UserModel.create({
    email: userData.email,
    name: userData.name,
    externalId: prismaUser.id,
    metadata: userData.metadata || {}
  });
  
  return {
    ...prismaUser,
    metadata: mongoUser.metadata
  };
}

async function getUserById(id) {
  // Get core user data from PostgreSQL
  const prismaUser = await prisma.user.findUnique({
    where: { id: parseInt(id) },
    include: { posts: true }
  });
  
  if (!prismaUser) return null;
  
  // Get additional user data from MongoDB
  const mongoUser = await UserModel.findOne({ externalId: id });
  
  return {
    ...prismaUser,
    metadata: mongoUser?.metadata || {}
  };
}

module.exports = { createUser, getUserById };

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

// services/userService.js

const { prisma } = require('../database');

const UserModel = require('../models/user');

async function createUser(userData) {

// Create user in PostgreSQL using Prisma

const prismaUser = await prisma.user.create({

data: {

email: userData.email,

}

});

// Store additional data in MongoDB using Mongoose

const mongoUser = await UserModel.create({

email: userData.email,

externalId: prismaUser.id,

metadata: userData.metadata || {}

});

return {

...prismaUser,

metadata: mongoUser.metadata

};

}

async function getUserById(id) {

// Get core user data from PostgreSQL

const prismaUser = await prisma.user.findUnique({

where: { id: parseInt(id) },

include: { posts: true }

});

if (!prismaUser) return null;

// Get additional user data from MongoDB

const mongoUser = await UserModel.findOne({ externalId: id });

return {

...prismaUser,

metadata: mongoUser?.metadata || {}

};

}

module.exports = { createUser, getUserById };

Implementing Express API Routes

Set up a simple Express server to demonstrate the integration:

// app.js
const express = require('express');
const { connectToMongoDB } = require('./database');
const userService = require('./services/userService');

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

// Connect to MongoDB
connectToMongoDB();

// Routes
app.post('/users', async (req, res) => {
  try {
    const user = await userService.createUser(req.body);
    res.status(201).json(user);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.get('/users/:id', async (req, res) => {
  try {
    const user = await userService.getUserById(req.params.id);
    if (!user) return res.status(404).json({ error: 'User not found' });
    res.json(user);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

// app.js

const express = require('express');

const { connectToMongoDB } = require('./database');

const userService = require('./services/userService');

const app = express();

app.use(express.json());

// Connect to MongoDB

connectToMongoDB();

// Routes

app.post('/users', async (req, res) => {

try {

const user = await userService.createUser(req.body);

res.status(201).json(user);

} catch (error) {

res.status(500).json({ error: error.message });

}

});

app.get('/users/:id', async (req, res) => {

try {

const user = await userService.getUserById(req.params.id);

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

res.json(user);

} catch (error) {

res.status(500).json({ error: error.message });

}

});

const PORT = process.env.PORT || 3000;

app.listen(PORT, () => {

console.log(`Server running on port ${PORT}`);

});

Best Practices and Considerations

Transaction Management

When modifying data in both PostgreSQL and MongoDB, consider handling failures:

async function updateUserWithTransaction(id, updateData) {
  try {
    // Start a Prisma transaction
    return await prisma.$transaction(async (tx) => {
      // Update PostgreSQL data
      const updatedPrismaUser = await tx.user.update({
        where: { id: parseInt(id) },
        data: {
          name: updateData.name,
          email: updateData.email
        }
      });
      
      // Update MongoDB data
      const updatedMongoUser = await UserModel.findOneAndUpdate(
        { externalId: id },
        { metadata: updateData.metadata },
        { new: true }
      );
      
      if (!updatedMongoUser) {
        // If MongoDB update fails, you might want to throw an error
        // to trigger a transaction rollback in Prisma
        throw new Error('MongoDB update failed');
      }
      
      return {
        ...updatedPrismaUser,
        metadata: updatedMongoUser.metadata
      };
    });
  } catch (error) {
    console.error('Transaction failed:', error);
    throw error;
  }
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

async function updateUserWithTransaction(id, updateData) {

try {

// Start a Prisma transaction

return await prisma.$transaction(async (tx) => {

// Update PostgreSQL data

const updatedPrismaUser = await tx.user.update({

where: { id: parseInt(id) },

data: {

email: updateData.email

}

});

// Update MongoDB data

const updatedMongoUser = await UserModel.findOneAndUpdate(

{ externalId: id },

{ metadata: updateData.metadata },

{ new: true }

);

if (!updatedMongoUser) {

// If MongoDB update fails, you might want to throw an error

// to trigger a transaction rollback in Prisma

throw new Error('MongoDB update failed');

}

return {

...updatedPrismaUser,

metadata: updatedMongoUser.metadata

};

});

} catch (error) {

console.error('Transaction failed:', error);

throw error;

}

Performance Optimization

To optimize performance in this hybrid setup:

Reduce Cross-Database Queries: Minimize operations that require data from both databases.
Caching: Implement Redis or in-memory caching for frequently accessed data.
Data Duplication Strategy: Decide which data needs to be in both systems and which can be exclusive to one.

Conclusion

Integrating Prisma with PostgreSQL while leveraging Mongoose offers a flexible approach to database management, especially during transitions or for polyglot persistence architectures. While this approach introduces additional complexity, the benefits of using specialized tools for different aspects of your data model can outweigh the costs.

Remember that this hybrid approach works best as a transitional strategy or when you genuinely need relational and document database features. For most applications, fully committing to Prisma with PostgreSQL or Mongoose with MongoDB will provide a simpler architecture with fewer potential issues.

Following the principles outlined in this guide, you can successfully integrate these powerful technologies while maintaining a clean, maintainable codebase.

Drop a query if you have any questions regarding Prisma or PostgreSQL and we will get back to you quickly.

About CloudThat

CloudThat is a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.