This gist demonstrate the use of sql dataloaders in effect

DiseaseAreaRepository.spec.ts

import { Model } from '@effect/sql';
import { PgClient } from '@effect/sql-pg';
import { layer } from '@effect/vitest';
import {
  Array,
  Config,
  Effect,
  Exit,
  Fiber,
  flow,
  Layer,
  Runtime,
  Schema,
  Scope,
  String,
} from 'effect';

import { CTGovDiseaseArea } from '~/domain/CTGovDiseaseArea';
import { DiseaseArea } from '~/domain/DiseaseArea';
import { NonEmptyText } from '~/domain/NonEmptyText';

class DiseaseAreaDTO extends Model.Class<DiseaseAreaDTO>('DiseaseAreaDTO')({
  id: Model.GeneratedByApp(DiseaseArea.Id),
  createdAt: Schema.DateFromSelf,
  therapeuticAreaId: Schema.UUID,
  name: NonEmptyText.NonEmptyText,
  category: NonEmptyText.NonEmptyText.pipe(
    Schema.optionalWith({ as: 'Option', nullable: true }),
  ),
  ctGovDiseaseAreas: Schema.Array(CTGovDiseaseArea.CTGovDiseaseArea),
}) {}

const table = 'disease_area';

const PgLive = PgClient.layer({
  host: Config.string('DB_HOST'),
  port: Config.integer('DB_PORT'),
  username: Config.string('DB_USERNAME'),
  password: Config.redacted('DB_PASSWORD'),
  database: Config.string('DB_NAME'),
  transformQueryNames: Config.succeed(String.camelToSnake),
  transformResultNames: Config.succeed(String.snakeToCamel),
});

const makeRepo = Effect.gen(function* () {
  const { insert, findById } = yield* Model.makeDataLoaders(DiseaseAreaDTO, {
    idColumn: 'id',
    tableName: table,
    spanPrefix: table,
    window: 10,
    maxBatchSize: 5,
  });
  const getById = flow(findById, Effect.flatten);

  return { getById, insert, findById };
});

class Repo extends Effect.Tag('Repo')<
  Repo,
  Effect.Effect.Success<typeof makeRepo>
>() {
  static Default = Layer.scoped(this, makeRepo).pipe(Layer.provide(PgLive));
}

describe('DiseaseAreaRepository', () => {
  layer(Repo.Default)(it => {
    it.scopedLive('should insert and find a disease area', () =>
      Effect.gen(function* () {
        const diseaseArea = DiseaseArea.factories.factory();
        yield* Repo.insert(diseaseArea);

        const foundDiseaseArea = yield* Repo.getById(diseaseArea.id);
        expect(foundDiseaseArea).toEqual(diseaseArea);
      }),
    );
    it.scopedLive('should batch across parallel effects', () =>
      Effect.gen(function* () {
        const diseaseArea = DiseaseArea.factories.factory();
        yield* Effect.all(
          [
            Repo.insert(diseaseArea),
            ...[...Array.allocate(100)].map(() =>
              Repo.insert(DiseaseArea.factories.factory()),
            ),
          ],
          { batching: true },
        );

        const foundDiseaseArea = yield* Repo.getById(diseaseArea.id);
        expect(foundDiseaseArea).toEqual(diseaseArea);
      }),
    );
  });
  it('should batch across fibers', async () => {
    const scope = await Effect.runPromise(Scope.make());
    const runtime = await Effect.runPromise(
      Repo.Default.pipe(Layer.toRuntime, Scope.extend(scope)),
    );

    const runFork = Runtime.runFork(runtime);
    const run = Runtime.runPromise(runtime);

    const diseaseArea = DiseaseArea.factories.factory();
    const fiber1 = Repo.insert(diseaseArea).pipe(runFork);
    const fibers = [fiber1];
    for (const _ of Array.allocate(100)) {
      const fiber = Repo.insert(DiseaseArea.factories.factory()).pipe(runFork);
      fibers.push(fiber);
    }

    await Effect.runPromise(Fiber.joinAll(fibers));

    const foundDiseaseArea = await run(Repo.getById(diseaseArea.id));
    expect(foundDiseaseArea).toEqual(diseaseArea);
    await Effect.runPromise(Scope.close(scope, Exit.void));
  });
  it('should batch across parallel promises', async () => {
    const scope = await Effect.runPromise(Scope.make());
    const runtime = await Effect.runPromise(
      Repo.Default.pipe(Layer.toRuntime, Scope.extend(scope)),
    );

    const run = Runtime.runPromise(runtime);

    const diseaseArea = DiseaseArea.factories.factory();
    await Promise.all([
      Repo.insert(diseaseArea).pipe(run),
      ...[...Array.allocate(100)].map(() =>
        Repo.insert(DiseaseArea.factories.factory()).pipe(run),
      ),
    ]);

    const foundDiseaseArea = await run(Repo.getById(diseaseArea.id));
    expect(foundDiseaseArea).toEqual(diseaseArea);
    await Effect.runPromise(Scope.close(scope, Exit.void));
  });
});