Choosing the Best Join Strategy in Prisma ORM: join vs query

Prisma ORM supports two strategies for loading related data. With the join strategy, the database merges the relations itself and returns nested data from a single SQL query, using JSON aggregation and lateral joins on PostgreSQL. With the query strategy, Prisma sends one query per table and merges the results in the application. You pick the strategy per query with the relationLoadStrategy option, available on PostgreSQL, CockroachDB, and MySQL behind the relationJoins preview feature flag; once the flag is on, join is the default. This post explains how both strategies work under the hood and when to use which.

Contents

• The two join strategies in Prisma ORM
• What actually goes over the wire
• join vs query: when to use which?
• Understanding relations in SQL databases
• What's happening under the hood?
• Joins in Prisma Next
• Try it out and share your feedback

The two join strategies in Prisma ORM

Support for database-level joins was one of the most requested features in Prisma ORM, and it shipped as the relationLoadStrategy option.

For any relation query with include (or select), the top-level relationLoadStrategy option accepts one of two values:

• join (default): Uses the database-level join strategy to merge the data in the database.
• query: Uses the application-level join strategy by sending multiple queries to individual tables and merging the data in the application layer.

To enable relationLoadStrategy, add the relationJoins preview feature flag to the generator block of your Prisma schema:

// prisma/schema.prisma
generator client {
  provider        = "prisma-client"
  output          = "../src/generated/prisma"
  previewFeatures = ["relationJoins"]
}

datasource db {
  provider = "postgresql"
}

Note: relationLoadStrategy is available for PostgreSQL, CockroachDB, and MySQL databases. Without the flag, Prisma Client always uses the application-level strategy, and the relationLoadStrategy option is rejected as an unknown argument.

You'll need a PostgreSQL database to follow along. The fastest way to get one is Prisma Postgres: running npm create db provisions a database and gives you the connection string to put in your .env file.

After adding the flag, re-run npx prisma generate for the change to take effect. Here is the data model we'll use, along with the client setup and an example query using the join strategy:

model User {
  id      Int      @id @default(autoincrement())
  name    String?
  posts   Post[]
}

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

import { PrismaClient } from "./generated/prisma/client";
import { PrismaPg } from "@prisma/adapter-pg";

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });

const usersWithPosts = await prisma.user.findMany({
  relationLoadStrategy: "join", // or "query"
  include: {
    posts: true,
  },
});

Because "join" is the default once the preview flag is enabled, the relationLoadStrategy option could technically also be omitted in the snippet above. We show it here for illustration purposes.

What actually goes over the wire

The difference between the two strategies is easy to see in the SQL that Prisma Client generates. We ran the query above on Prisma ORM 7.8 against a PostgreSQL database and logged the statements.

With the query strategy (or without the preview flag), fetching users with their posts sends two queries, one per table:

SELECT "public"."User"."id", "public"."User"."name" FROM "public"."User" WHERE 1=1 OFFSET $1
SELECT "public"."Post"."id", "public"."Post"."title", "public"."Post"."authorId" FROM "public"."Post" WHERE "public"."Post"."authorId" IN ($1,$2) OFFSET $3

Prisma Client then merges the two result sets in memory to build the nested objects.

With the join strategy, the same Prisma Client query sends exactly one statement, in which the database performs a lateral join and builds the nested JSON structures itself:

SELECT "t0"."id", "t0"."name", "User_posts"."__prisma_data__" AS "posts"
FROM "public"."User" AS "t0"
LEFT JOIN LATERAL (
  SELECT COALESCE(JSONB_AGG("__prisma_data__"), '[]') AS "__prisma_data__"
  FROM (...) -- posts of the current user, shaped as JSON
) AS "User_posts" ON TRUE

One round trip, no in-memory merging, and the JSON aggregation happens where the data lives.

join vs query: when to use which?

With two query strategies available, you'll wonder: when to use which?

Because of the lateral, aggregated JOINs that Prisma ORM uses on PostgreSQL and the correlated subqueries on MySQL, the join strategy is likely to be more efficient in the majority of cases (a later section has more details on this). Database engines are very powerful and great at optimizing query plans. The join relation load strategy pays tribute to that.

However, there may be cases where you still want to use the query strategy to perform one query per table and merge data at the application level. Depending on the dataset and the indexes that are configured in the schema, sending multiple queries could be more performant. Profiling and benchmarking your queries is crucial to identify these situations.

Another consideration could be the database load that's incurred by a complex join query. If, for some reason, resources on the database server are scarce, you may want to move the heavy compute that's required by a complex join query with filters and pagination to your application servers, which may be easier to scale.

TLDR:

• The join strategy will be more efficient in most scenarios.
• There may be edge cases where query could be more performant depending on the characteristics of the dataset and query. We recommend that you profile your database queries to identify these scenarios.
• Use query if you want to save resources on the database server and do the heavy lifting of merging and transforming data in the application server, which might be easier to scale.

Understanding relations in SQL databases

Now that we learned about Prisma ORM's join strategies, let's review how relation queries generally work in SQL databases.

Flat vs nested data structures for relations

SQL databases store data in flat (i.e. normalized) ways. Relations between entities are represented via foreign keys that specify references across tables.

On the other hand, application developers are typically used to working with nested data, i.e. objects that can nest other objects arbitrarily deep.

This is a huge difference, not only in the way how data is physically laid out on disk and in memory, but also when it comes to the mental model and reasoning about the data.

Relational data needs to be "merged" for application developers

Since related data is stored physically separately in the database, it needs to be merged somewhere to become the nested structure an application developer is familiar with. This merge is also called "join".

There are two places where this join can happen:

• On the database-level: A single SQL query is sent to the database. The query uses the JOIN keyword or a correlated subquery to let the database perform the join across multiple tables and returns the nested structures.
• On the application-level: Multiple queries are sent to the database. Each query only accesses a single table and the query results are then merged in-memory in the application layer. This was the only query strategy that Prisma Client supported before v5.9.0, and it remains the default behavior when the relationJoins preview flag is not enabled.

Which approach is more desirable depends on the database that's used, the size and characteristics of the dataset, and the complexity of the query. Read on to learn when it's recommended to use which strategy.

What's happening under the hood?

Prisma ORM implements the join relation load strategy using LATERAL joins and DB-level JSON aggregation (e.g. via json_agg) in PostgreSQL and correlated subqueries on MySQL.

In the following sections, we'll investigate why the LATERAL joins and DB-level JSON aggregation approach on PostgreSQL is more efficient than plain, traditional JOINs.

Preventing redundancy in query results with JSON aggregation

When using database-level JOINs, there are several options for constructing a SQL query. Let's consider the SQL table definition for the Prisma schema from above:

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "name" TEXT,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    "authorId" INTEGER NOT NULL,

    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
);

-- AddForeignKey
ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

To retrieve all users with their posts, you can use a simple LEFT JOIN query:

SELECT
    u.id AS user_id,
    u.name AS user_name,
    p.id AS post_id,
    p.title AS post_title
FROM
    "user" u
LEFT JOIN
    post p ON u.id = p.author_id
ORDER BY
    u.id, p.id;

This is what the result could look like with some sample data:

Notice the redundancy in the user_name column in this case. This redundancy is only going to get worse the more tables are being joined. For example, assume there's another Comment table, where each comment has a postId foreign key that points to a record in the Post table.

Here's a SQL query to represent that:

SELECT
    u.id AS user_id,
    u.name AS user_name,
    p.id AS post_id,
    p.title AS post_title,
    c.id AS comment_id,
    c.body AS comment_body
FROM
    "user" u
LEFT JOIN
    post p ON u.id = p.author_id
LEFT JOIN
    comment c ON p.id = c.post_id
ORDER BY
    u.id, p.id, c.id;

Now, assume the first post had multiple comments:

The size of the result set in this case grows exponentially with the number of tables that are being joined. Since this data goes over the wire from the database to the application server, this can become very expensive.

The join strategy implemented by Prisma with JSON aggregation on the database-level solves this problem.

Here is an example for PostgreSQL that uses json_agg and json_build_object to solve the redundancy problem and return the posts per user in JSON format:

SELECT
    u.id AS user_id,
    u.name AS user_name,
    jsonb_agg(
        jsonb_build_object(
            'post_id', p.id,
            'post_title', p.title
        )
    ) AS posts
FROM
    "user" u
LEFT JOIN
    post p ON u.id = p.author_id
GROUP BY
    u.id, u.name
ORDER BY
    u.id;

The result set this time doesn't contain redundant data. Additionally, the data structure conveniently already has the shape that's returned by Prisma Client, which saves the extra work of transforming results in the client:

Lateral JOINs for more efficient queries with pagination and filters

Relation queries (like most other ones) almost never fetch the entire data from a table, but come with additional result set constraints like filters and pagination. Specifically pagination can become very complex with traditional JOINs, let's look at another example.

Consider this Prisma Client query that fetches 10 users and 5 posts per user:

await prisma.user.findMany({
  take: 10,
  include: {
    posts: {
      take: 5,
    },
  },
});

When writing this in raw SQL, you might be tempted to use a LIMIT clause inside the sub-query, e.g.:

SELECT "user".id,
       name,
       title
FROM ("user"
      LEFT JOIN
        (SELECT *
         FROM post
         LIMIT 5) AS p ON (p.author_id = "user".id))
LIMIT 10;

However, this won't work because the inner SELECT doesn't actually return five posts per user. Instead, it returns at most five posts in total, which is of course not at all the desired outcome.

Using a traditional JOIN, this could be resolved by using the row_number() function to assign incrementing integers to the records in the result set with which the computation of the pagination could be performed manually.

This approach becomes very complex very fast though and thus isn't ideal for building paginated relation queries.

SELECT "user".id,
       name,
       title,
       rn
FROM ("user"
      LEFT JOIN
        (SELECT *,
                row_number() OVER (PARTITION BY author_id) AS rn
         FROM post) AS p ON (p.author_id = "user".id))
WHERE p.rn >= 1
  AND p.rn <= 5
  OR p.rn IS NULL
LIMIT 10;

Maintaining, scaling and debugging these kinds of SQL queries is daunting and can consume hours of development time.

Thankfully, newer database versions solve this with a new kind of query: the lateral JOIN.

The above query can be simplified by using the LATERAL keyword:

SELECT "user".id,
       name,
       title
FROM ("user"
      LEFT JOIN LATERAL
        (SELECT *
         FROM post
         WHERE post.author_id = "user".id
         LIMIT 5) AS p ON TRUE)
LIMIT 10;

This not only makes the query more readable, but the database engine also likely is more capable of optimizing the query because it can understand more about the intent of the query.

Conclusion

Let's review the different options for joining data from relation queries with Prisma.

With the application-level join strategy (query, and the behavior before v5.9.0), Prisma Client sends multiple queries to the database and does all the work of merging and transforming the results into the expected JavaScript object structures inside the client:

Using plain, traditional JOINs, the merging of the data would be delegated to the database. However, as explained above, there are problems with data redundancy (the result sets grow exponentially with the number of tables in the relation query) and the complexity of queries that contain filters and pagination:

To work around these issues, Prisma ORM implements modern, lateral JOINs accompanied with JSON aggregation on the database-level. That way, all the heavy lifting that's needed to resolve the query and bring the data into the expected JavaScript object structures is done on the database-level:

Joins in Prisma Next

Prisma Next, the next generation of Prisma ORM (available in Early Access today, becoming Prisma 8 at GA), takes the idea in this post one step further: you no longer choose a join strategy at all.

In Prisma Next, the join strategy is selected from your database target's declared capabilities. We verified this on the current Early Access build: db.orm.public.User.include("posts").all() sends a single SQL query in which PostgreSQL builds the nested JSON with json_agg and json_build_object, with no preview flag and no per-query option. On targets without JSON aggregation support, Prisma Next falls back to multiple queries with in-application merging, so the same application code picks the best available strategy per database.

Prisma Next is also fast in general: in our published benchmark, a fork of the open-source drizzle-benchmarks suite, it reaches roughly 90% of the raw pg driver's speed, holds p95 latency around 4 ms at 6,000 to 7,000 requests/second, and ships a client of about 148.5 KB gzipped. And when you need full control over a join, it includes a type-safe SQL query builder with explicit leftJoin support, kept in sync with your schema.

Like Prisma ORM, Prisma Next pairs with Prisma Postgres out of the box. For new projects, especially ones built with AI coding agents, it's the direction to watch.

Try it out and share your feedback

We'd love for you to try out the join relation load strategy. If you don't have a database at hand, npm create db gives you a free Prisma Postgres database to experiment with. Let us know what you think and share your feedback with us!