DeduplicateJoinsPlugin

Removes duplicate join clauses from queries automatically

Overview

The DeduplicateJoinsPlugin automatically removes duplicate join clauses from your queries. This is particularly useful when building complex queries dynamically or when using query composition patterns that might inadvertently add the same join multiple times.

Installation

import { Kysely, DeduplicateJoinsPlugin } from 'kysely'

const db = new Kysely<Database>({
  dialect,
  plugins: [new DeduplicateJoinsPlugin()]
})

Usage Example

Without the Plugin

const query = db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .innerJoin('pet', 'pet.owner_id', 'person.id')  // Duplicate!
  .selectAll()

// Generates SQL with duplicate joins:
// SELECT * FROM "person"
// INNER JOIN "pet" ON "pet"."owner_id" = "person"."id"
// INNER JOIN "pet" ON "pet"."owner_id" = "person"."id"

With the Plugin

const db = new Kysely<Database>({
  dialect,
  plugins: [new DeduplicateJoinsPlugin()]
})

const query = db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .innerJoin('pet', 'pet.owner_id', 'person.id')  // Automatically removed!
  .selectAll()

// Generates SQL with deduplicated joins:
// SELECT * FROM "person"
// INNER JOIN "pet" ON "pet"."owner_id" = "person"."id"

Methods

transformQuery

transformQuery(args: PluginTransformQueryArgs): RootOperationNode

Transforms the query by removing duplicate join clauses.

Parameters:

• args.queryId - Unique identifier for the query
• args.node - The root operation node to transform

Returns: The transformed operation node with duplicate joins removed

Source: ~/workspace/source/src/plugin/deduplicate-joins/deduplicate-joins-plugin.ts:19

transformResult

transformResult(
  args: PluginTransformResultArgs
): Promise<QueryResult<UnknownRow>>

Passes through the result unchanged. This plugin only modifies queries, not results.

Parameters:

• args.queryId - Unique identifier for the query
• args.result - The query result

Returns: A promise resolving to the original, unmodified result

Source: ~/workspace/source/src/plugin/deduplicate-joins/deduplicate-joins-plugin.ts:23

Real-World Use Cases

Dynamic Query Building

When building queries dynamically, you might add joins conditionally without tracking whether they’ve already been added:

function buildUserQuery(options: QueryOptions) {
  let query = db.selectFrom('users')
  
  // Multiple conditions might add the same join
  if (options.includePosts) {
    query = query.innerJoin('posts', 'posts.user_id', 'users.id')
  }
  
  if (options.filterByPostTitle) {
    query = query
      .innerJoin('posts', 'posts.user_id', 'users.id')  // Might be duplicate
      .where('posts.title', 'like', '%' + options.filterByPostTitle + '%')
  }
  
  return query.selectAll('users')
}

// With DeduplicateJoinsPlugin, the duplicate join is automatically removed

Query Composition with Helper Functions

function withPosts<QB extends SelectQueryBuilder<any, any, any>>(qb: QB) {
  return qb.innerJoin('posts', 'posts.user_id', 'users.id')
}

function withComments<QB extends SelectQueryBuilder<any, any, any>>(qb: QB) {
  return qb
    .innerJoin('posts', 'posts.user_id', 'users.id')  // Needed for comments
    .innerJoin('comments', 'comments.post_id', 'posts.id')
}

// Using both helpers would normally create duplicate joins
const query = db
  .selectFrom('users')
  .$call(withPosts)
  .$call(withComments)  // posts join is deduplicated
  .selectAll()

Reusable Query Fragments

class UserRepository {
  private withActiveStatus<QB extends SelectQueryBuilder<any, any, any>>(qb: QB) {
    return qb
      .innerJoin('user_status', 'user_status.user_id', 'users.id')
      .where('user_status.is_active', '=', true)
  }
  
  private withSubscription<QB extends SelectQueryBuilder<any, any, any>>(qb: QB) {
    return qb
      .innerJoin('user_status', 'user_status.user_id', 'users.id')
      .innerJoin('subscriptions', 'subscriptions.user_id', 'users.id')
  }
  
  async getActiveSubscribers() {
    // Both methods add user_status join - automatically deduplicated
    return db
      .selectFrom('users')
      .$call(this.withActiveStatus)
      .$call(this.withSubscription)
      .selectAll()
      .execute()
  }
}

Complex Multi-Table Queries

interface Database {
  orders: { id: number; customer_id: number; status: string }
  customers: { id: number; name: string }
  order_items: { id: number; order_id: number; product_id: number }
  products: { id: number; name: string; category_id: number }
  categories: { id: number; name: string }
}

const db = new Kysely<Database>({
  dialect,
  plugins: [new DeduplicateJoinsPlugin()]
})

// Building a complex query with potential duplicate joins
const query = db
  .selectFrom('orders')
  .innerJoin('customers', 'customers.id', 'orders.customer_id')
  .innerJoin('order_items', 'order_items.order_id', 'orders.id')
  .innerJoin('products', 'products.id', 'order_items.product_id')
  .innerJoin('categories', 'categories.id', 'products.category_id')

// Later conditions might add the same joins again
let finalQuery = query
  .innerJoin('customers', 'customers.id', 'orders.customer_id')  // Duplicate
  .where('customers.name', 'like', '%John%')

// Deduplicated automatically by the plugin

How It Works

The plugin uses a DeduplicateJoinsTransformer that:

1. Traverses the operation node tree
2. Identifies all join nodes in the query
3. Compares join nodes for equality
4. Removes subsequent joins that are identical to earlier ones
5. Preserves the order of the first occurrence of each unique join

When Joins Are Considered Duplicates

Two joins are considered duplicates if they have:

• Same join type (INNER, LEFT, RIGHT, etc.)
• Same table being joined
• Same join condition
• Same table alias (if any)

// These are duplicates:
.innerJoin('posts', 'posts.user_id', 'users.id')
.innerJoin('posts', 'posts.user_id', 'users.id')

// These are NOT duplicates (different join conditions):
.innerJoin('posts', 'posts.user_id', 'users.id')
.innerJoin('posts', 'posts.author_id', 'users.id')

// These are NOT duplicates (different aliases):
.innerJoin('posts as user_posts', 'user_posts.user_id', 'users.id')
.innerJoin('posts as admin_posts', 'admin_posts.user_id', 'users.id')

Performance Impact

The plugin adds minimal overhead:

• Only processes queries that have joins
• Comparison is done using operation node equality
• No runtime performance impact on query execution
• Prevents unnecessary database work by eliminating redundant joins

Per-Query Usage

You can apply the plugin to specific queries instead of globally:

const result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .selectAll()
  .withPlugin(new DeduplicateJoinsPlugin())
  .execute()

Related Resources

• Kysely Recipe: Deduplicate Joins
• KyselyPlugin Interface
• Creating Custom Plugins

See Also

• Join Methods
• Query Building
• OperationNodeTransformer