In modern backend development, we often follow a service-oriented architecture where each model is initialized in its own service. However, there are cases where a particular service needs access to a model defined in a different service. One common requirement in such scenarios is to perform a JOIN query between models residing in separate services.

In this guide, we'll explore how to add a JOIN query efficiently when your models are initialized separately and need to interact within a specific service.

Understanding the Problem

When working with a microservices-based backend, models are often encapsulated within their respective services. For instance:

• UserService manages the User model.

• OrderService manages the Order model.

• ProductService manages the Product model.

If OrderService needs to retrieve user details along with order data, it must perform a JOIN operation while keeping services decoupled.

Approaches to Implementing JOIN Queries

1. Using Direct Database Query in the Service

If all models are using the same database, you can write raw SQL queries or use an ORM (like Sequelize, TypeORM, or Prisma) to perform the JOIN.

Example with SQL Query (Using PostgreSQL & Sequelize)

const { Sequelize, DataTypes } = require("sequelize");
const sequelize = new Sequelize("database", "user", "password", {
    host: "localhost",
    dialect: "postgres",
});

async function getOrdersWithUsers() {
    const query = `
        SELECT orders.id, orders.amount, users.name, users.email
        FROM orders
        INNER JOIN users ON orders.user_id = users.id;
    `;
    const results = await sequelize.query(query, { type: Sequelize.QueryTypes.SELECT });
    return results;
}

getOrdersWithUsers().then(data => console.log(data));

2. Using ORM with Associations (Best for Monolithic Services)

If you're using an ORM like Sequelize, you can define relationships and fetch related data seamlessly.

Sequelize Example with Associations

const Order = sequelize.define("Order", {
    id: { type: DataTypes.INTEGER, primaryKey: true },
    user_id: { type: DataTypes.INTEGER },
    amount: { type: DataTypes.FLOAT },
});

const User = sequelize.define("User", {
    id: { type: DataTypes.INTEGER, primaryKey: true },
    name: { type: DataTypes.STRING },
    email: { type: DataTypes.STRING },
});

Order.belongsTo(User, { foreignKey: "user_id" });

async function getOrdersWithUsers() {
    return await Order.findAll({
        include: [{ model: User, attributes: ["name", "email"] }],
    });
}

This approach keeps services independent and avoids tight coupling.

3. Using API Calls Between Services (Best for Microservices Architecture)

If models exist in separate services, the best approach is to make an API call from one service to another to fetch the required data.

Example: Fetching User Data from UserService in OrderService

const axios = require("axios");

async function getOrdersWithUsers() {
    const orders = await Order.findAll();
    const userIds = orders.map(order => order.user_id);
    
    const usersResponse = await axios.post("http://userservice/api/users", { ids: userIds });
    const usersData = usersResponse.data;
    
    return orders.map(order => ({
        ...order.toJSON(),
        user: usersData.find(user => user.id === order.user_id),
    }));
}

This approach keeps services independent and avoids tight coupling.

4. Using an Event-Driven Approach (Best for High Scalability)

Instead of making synchronous API calls, you can publish events when a record is created and store necessary data in a local copy within the service.

For example, when a new user is created in UserService, an event can be published, and OrderService can subscribe to store user details locally.

Example: Using Redis Pub/Sub for Event-Based Sync

const redis = require("redis");
const subscriber = redis.createClient();

subscriber.subscribe("user_created");

subscriber.on("message", (channel, message) => {
    if (channel === "user_created") {
        const userData = JSON.parse(message);
        UserCache.save(userData); // Store locally
    }
});

This ensures efficient data retrieval without repeated API calls.

Conclusion

When integrating JOIN queries in a service-oriented architecture where models are initialized in separate services, different approaches can be applied:

• Direct JOIN queries for a single database setup.

• ORM-based relations for a structured monolithic approach.

• API calls to fetch related data in microservices.

• Event-driven architecture for real-time synchronization.

Choosing the right approach depends on your project’s architecture, scalability requirements, and performance considerations.

Which approach do you use for handling JOIN queries in a microservices setup? Let us know in the comments!