The "No Join Gateway" Pattern

When building applications with gateways (data access layers), it's tempting to add SQL JOINs to fetch related data. A job gateway might JOIN with the address table to return job + address in one query. This creates tight coupling and makes testing harder.

The Pattern

Rule: A gateway for one entity should NOT have SQL joins for another entity's table.

Instead:

1. Gateways only query their own entity table
2. Entities store foreign keys as IDs
3. Use cases coordinate multiple gateways to fetch related data

Example: Job and Address

A job has an addressId. Without this pattern, you might write:

-- Bad: JobGateway joining Address table
SELECT j.*, a.street, a.city
FROM jobs j
JOIN addresses a ON j.address_id = a.id
WHERE j.id = $1

With the "no join" pattern:

// JobGateway - only queries jobs table
class JobGateway {
  async getJob(jobId: string): Promise<Job> {
    // SELECT * FROM jobs WHERE id = $1
  }
}

// AddressGateway - only queries addresses table
class AddressGateway {
  async getAddress(addressId: string): Promise<Address> {
    // SELECT * FROM addresses WHERE id = $1
  }
}

The use case coordinates the gateways:

class GetJobUseCase {
  constructor(
    private jobGateway: JobGateway,
    private addressGateway: AddressGateway,
  ) {}

  async getJob(request: { jobId: string }) {
    const job = await this.jobGateway.getJob(request.jobId);
    const address = await this.addressGateway.getAddress(job.addressId);
    return { job, address };
  }
}

Batch Operations

When fetching multiple related entities, collect IDs first, then batch fetch:

class GetJobListUseCase {
  async getJobList() {
    const jobs = await this.jobGateway.getJobs();

    // Collect all address IDs
    const addressIds = jobs.map((j) => j.addressId);

    // Single batch query for all addresses
    const addresses = await this.addressGateway.getAddresses(addressIds);

    return jobs.map((job) => ({
      job,
      address: addresses.get(job.addressId),
    }));
  }
}

Benefits

Single Responsibility: Each gateway manages one entity type

Testability: Test each gateway independently with simple in-memory implementations

Explicit Data Flow: Use case code shows exactly what data is fetched

Flexibility: Different use cases can fetch different related data

Simpler Gateways: No complex multi-table queries to maintain