TypeScript-typed generateQuery for creating single-SQL nested queries for a certain schema

example-query.sql

select json_build_object('__table', 'user', 'firstName', e."first_name", 'lastName', e."last_name", 'managedResources', coalesce("t_managedResources"."data", '[]'), 'ratings', coalesce("t_ratings"."data", '[]')) as "data"
from "user" as "e"
left join (
  select "manager_id" as "fkey", json_agg(json_build_object('__table', 'resource', 'ratingTotals', "t_ratingTotals"."data")) as "data"
  from "resource" as "e"
  left join (
    select "resource_id" as "fkey", json_build_object('__table', 'rating_totals', 'avgSatisfaction', e."avg_satisfaction") as "data"
    from "rating_totals" as "e"
  ) as "t_ratingTotals" on "t_ratingTotals"."fkey" = "e"."id"
  group by "manager_id"
) as "t_managedResources" on "t_managedResources"."fkey" = "e"."id"
left join (
  select "sender_id" as "fkey", json_agg(json_build_object('__table', 'rating', 'satisfaction', e."satisfaction", 'resource', "t_resource"."data")) as "data"
  from "rating" as "e"
  left join (
    select "e"."id", json_build_object('__table', 'resource', 'latitude', e."latitude", 'ratingTotals', "t_ratingTotals"."data") as "data"
    from "resource" as "e"
    left join (
      select "resource_id" as "fkey", json_build_object('__table', 'rating_totals', 'avgSatisfaction', e."avg_satisfaction", 'numRatings', e."num_ratings") as "data"
      from "rating_totals" as "e"
    ) as "t_ratingTotals" on "t_ratingTotals"."fkey" = "e"."id"
  ) as "t_resource" on "t_resource"."id" = "e".resource_id
  group by "sender_id"
) as "t_ratings" on "t_ratings"."fkey" = "e"."id"

example-query.ts

const { data: users, count } = await executeQuery(client, UserSchema, {
  include: {
    firstName: true,
    lastName: true,
    managedResources: {
      include: {
        ratingTotals: {
          include: {
            avgSatisfaction: true,
          },
        },
      },
    },
    ratings: {
      include: {
        satisfaction: true,
        resource: {
          include: {
            latitude: true,
            ratingTotals: {
              include: {
                avgSatisfaction: true,
                numRatings: true,
              },
            },
          },
        },
      },
    },
  },
});

// Now, `users` is fully typed, e.g.
const a: number = users[0].managedResources[0].ratingTotals.avgSatisfaction;

example-schema.ts

type User = {
  firstName: string;
  lastName: string;
  fullName: string;
  age: number;
  password: string;
  ratings: Rating[];
  managedResources: Resource[];
};

export const UserSchema: EntitySchema<User> = {
  tableName: "user",
  columns: {
    firstName: "first_name",
    lastName: "last_name",
    age: "age",
  },
  relations: {
    ratings: {
      type: "hasMany",
      foreignKey: "sender_id",
      getRelatedEntitySchema() {
        return RatingSchema;
      },
    },
    managedResources: {
      type: "hasMany",
      foreignKey: "manager_id",
      getRelatedEntitySchema() {
        return ResourceSchema;
      },
    },
  },
};

type Rating = {
  satisfaction: number;
  review: number;
  sender: User;
  resource: Resource;
};

export const RatingSchema: EntitySchema<Rating> = {
  tableName: "rating",
  columns: {
    satisfaction: "satisfaction",
    review: "review",
  },
  relations: {
    sender: {
      type: "belongsTo",
      foreignKey: "sender_id",
      getRelatedEntitySchema() {
        return UserSchema;
      },
    },
    resource: {
      type: "belongsTo",
      foreignKey: "resource_id",
      getRelatedEntitySchema() {
        return ResourceSchema;
      },
    },
  },
};

type Resource = {
  location: string;
  latitude: number;
  longitude: number;
  manager: User;
  ratingTotals: RatingTotals;
  ratings: Rating[];
};

export const ResourceSchema: EntitySchema<Resource> = {
  tableName: "resource",
  columns: {
    location: "location",
    latitude: "latitude",
    longitude: "longitude",
  },
  relations: {
    manager: {
      type: "belongsTo",
      foreignKey: "manager_id",
      getRelatedEntitySchema() {
        return UserSchema;
      },
    },
    ratingTotals: {
      type: "hasOne",
      foreignKey: "resource_id",
      getRelatedEntitySchema() {
        return RatingTotalsSchema;
      },
    },
    ratings: {
      type: "hasMany",
      foreignKey: "resource_id",
      getRelatedEntitySchema() {
        return RatingSchema;
      },
    },
  },
};

type RatingTotals = {
  avgSatisfaction: number;
  numRatings: number;
  resource: Resource;
};

export const RatingTotalsSchema: EntitySchema<RatingTotals> = {
  tableName: "rating_totals",
  columns: {
    avgSatisfaction: "avg_satisfaction",
    numRatings: "num_ratings",
  },
  relations: {
    resource: {
      type: "belongsTo",
      foreignKey: "resource_id",
      getRelatedEntitySchema() {
        return ResourceSchema;
      },
    },
  },
};

executeQuery.ts

import { Query, EntitySchema } from "./types.ts";
import { Client } from "pg";

export async function executeQuery<E, Q extends Query<E>>(
  client: Client,
  schema: EntitySchema<E>,
  query: Q
): Promise<{
  count: number;
  data: Array<ResultT<E, Q>>;
}> {
  const sql = generateQuery(schema, query);
  console.log(sql);
  const { rows, rowCount } = await client.query(sql);
  return {
    count: rowCount,
    data: (rows as any).map((r: any) => r.data),
  };
}

generateQuery.ts

import { Query, EntitySchema, RelationSpec } from "./types.ts";

export function generateQuery<E, Q extends Query<E>>(
  schema: EntitySchema<E>,
  query: Q,
  asRelated?: RelationSpec<any>
): string {
  const data = `json_build_object(${[
    `'__table', '${schema.tableName}'`,
    ...Object.entries(query.include).map(([propertyKey, q]: any) => {
      if (typeof q === "boolean") {
        return `'${propertyKey}', e."${(schema.columns as any)[propertyKey]}"`;
      } else {
        const spec: RelationSpec<any> = (schema.relations as any)[propertyKey];
        const sub = `"t_${propertyKey}"`;
        if (spec.type === "hasMany") {
          return `'${propertyKey}', coalesce(${sub}."data", '[]')`;
        } else {
          return `'${propertyKey}', ${sub}."data"`;
        }
      }
    }),
  ].join(", ")})`;

  const select = !asRelated
    ? `${data} as "data"`
    : asRelated.type === "hasMany"
    ? `"${asRelated.foreignKey}" as "fkey", json_agg(${data}) as "data"`
    : asRelated.type === "hasOne"
    ? `"${asRelated.foreignKey}" as "fkey", ${data} as "data"`
    : `"e"."id", ${data} as "data"`;

  return [
    `select ${select}`,
    `from "${schema.tableName}" as "e"`,
    ...Object.entries(query.include)
      .filter((p) => typeof p[1] === "object")
      .flatMap(([propertyKey, q]: any) => {
        const spec: RelationSpec<any> = (schema.relations as any)[propertyKey];
        const sub = `"t_${propertyKey}"`;
        if (spec.type === "hasMany" || spec.type === "hasOne") {
          return [
            "left join (",
            indent(generateQuery(spec.getRelatedEntitySchema(), q, spec)),
            `) as ${sub} on ${sub}."fkey" = "e"."id"`,
          ];
        } else {
          return [
            "left join (",
            indent(generateQuery(spec.getRelatedEntitySchema(), q, spec)),
            `) as ${sub} on ${sub}."id" = "e".${spec.foreignKey}`,
          ];
        }
      }),
    asRelated?.type === "hasMany" ? `group by "${asRelated.foreignKey}"` : ``,
  ]
    .filter(Boolean)
    .join("\n");
}

function indent(str: string) {
  return "  " + str.replace(/\n/g, "\n  ");
}

types.ts

// Schema specification
// ===

export type EntitySchema<E> = {
  tableName: string;

  // mapping of entity properties to table field names
  columns: Partial<Record<keyof E, string>>;

  // mapping of entity properties to relationship specifications;
  //  the conditional type is added to map
  //  properties of type E[] to RelationSpec<E>
  relations: Partial<{
    [K in keyof E]: RelationSpec<E[K] extends Array<infer E> ? E : E[K]>;
  }>;
};

export type HasManySpec<E> = {
  type: "hasMany";
  foreignKey: string;
  getRelatedEntitySchema(): EntitySchema<E>;
};

export type HasOneSpec<E> = {
  type: "hasOne";
  foreignKey: string;
  getRelatedEntitySchema(): EntitySchema<E>;
};

export type BelongsToSpec<E> = {
  type: "belongsTo";
  foreignKey: string;
  getRelatedEntitySchema(): EntitySchema<E>;
};

export type RelationSpec<E> = HasManySpec<E> | HasOneSpec<E> | BelongsToSpec<E>;


// Queries & results

type Prim<T> = T extends number | string | boolean ? T : never;

export type Query<E> = {
  include: Partial<{
    [K in keyof E]: E[K] extends Prim<E[K]>
      ? true
      : E[K] extends Array<infer IE>
      ? Query<IE>
      : Query<E[K]>;
  }>;
};

export type ResultT<E, Q extends Query<E>> = {
  [K in keyof Q["include"]]: Q["include"][K] extends true
    ? E[K]
    : E[K] extends Array<infer IE>
      // The ignore annotations seem to be necessary because of the
      //  `Partial<...>` in the `Query<...>`, and I'm not really sure
      //  how to get rid of them...
    ? // @ts-expect-error
      Array<ResultT<IE, Q["include"][K]>>
    : // @ts-expect-error
      ResultT<E[K], Q["include"][K]>;
};