Code sharing: type-safe select query builder with Typescript

[parad0xe]

I would like to present type-safe select query builder that integrates with Supabase using TypeScript. The main goal is to provide a robust and type-checked way of constructing database queries and helping developers reduce runtime errors.

Key features

• Type-Safe Queries: Ensures that queries are checked at compile-time, eliminating common mistakes during development.
• Dynamic Relationship Handling: Supports various relationship types such as one-to-one and one-to-many.
• Automatic Type Inference: The builder automatically infers the correct types for fields and select query results, minimizing manual type definitions.
• Query Construction: A flexible select and build function allows developers to build queries dynamically, specifying tables, fields, and related data, while maintaining type safety and correct select query structure.

Code overview

Code

SHOW CODE (+400 lines)

/* eslint-disable  @typescript-eslint/no-explicit-any */

import {Database, Tables} from "@PATH_TO_YOUR/database.types.ts"

// --- Basic utility types ---

type IsNullable<T> = null extends T
    ? true
    : undefined extends T
        ? true
        : false

type TableName = keyof Database['public']['Tables']

type TableRelationships<TTable extends TableName> =
    Database['public']['Tables'][TTable]['Relationships'][number]

type TableForeignKeyName<TTable extends TableName> =
    TableRelationships<TTable>['foreignKeyName']

type TableRelationshipColumn<
    TTable extends TableName,
    TForeignKey extends Readonly<TableForeignKeyName<TTable>>
> = Extract<
    TableRelationships<TTable>,
    { foreignKeyName: TForeignKey }
>['columns'][0]

type FieldsOf<T extends Readonly<TableName>> = keyof Tables<T> | "*"

type ForeignKeysOf<T extends Readonly<TableName>> =
    Database['public']["Tables"][T]['Relationships'][number]["foreignKeyName"]

// --- Relation type structures ---

type SelectDefinition<
    TTable extends Readonly<TableName>,
    TFields extends ReadonlyArray<FieldsOf<TTable>>,
    TRelations extends ReadonlyArray<AnyRelation>
> = {
    table: TTable
    fields: TFields
    relations: TRelations
}

type OneRelation<
    TName extends Readonly<string>,
    TForeignFrom extends Readonly<string>,
    TForeignKey extends Readonly<string>,
    TNullable extends Readonly<boolean>,
    TTable extends Readonly<TableName>,
    TFields extends ReadonlyArray<FieldsOf<TTable>>,
    TRelations extends ReadonlyArray<AnyRelation> = []
> = {
    kind: "one"
    name: TName
    table: TTable
    fields: TFields
    relations: TRelations
    foreignFrom: TForeignFrom
    foreignKey: TForeignKey
    nullable: TNullable
}

type ManyRelation<
    TName extends Readonly<string>,
    TForeignFrom extends Readonly<string>,
    TForeignKey extends Readonly<string>,
    TNullable extends Readonly<boolean>,
    TTable extends Readonly<TableName>,
    TFields extends ReadonlyArray<FieldsOf<TTable>>,
    TRelations extends ReadonlyArray<AnyRelation> = []
> = {
    kind: "many"
    name: TName
    table: TTable
    fields: TFields
    relations: TRelations
    foreignFrom: TForeignFrom
    foreignKey: TForeignKey
    nullable: TNullable
}


type Relation<
    TName extends Readonly<string>,
    TForeignFrom extends Readonly<string>,
    TForeignKey extends Readonly<string>,
    TNullable extends Readonly<boolean>,
    TTable extends Readonly<TableName>,
    TFields extends ReadonlyArray<FieldsOf<TTable>>,
    TRelations extends ReadonlyArray<AnyRelation>
> =
    | OneRelation<TName, TForeignFrom, TForeignKey, TNullable, TTable, TFields, TRelations>
    | ManyRelation<TName, TForeignFrom, TForeignKey, TNullable, TTable, TFields, TRelations>

type AnyRelation = Relation<string, string, string, boolean, TableName, any, [] | ReadonlyArray<AnyRelation>>

type ForeignDefinition<
    TForeignFrom extends Readonly<TableName>,
    TForeignKey extends Readonly<ForeignKeysOf<TForeignFrom> | "inner">
> =
    {
        foreignFrom: TForeignFrom,
        foreignKey: TForeignKey,
        by: TForeignKey extends ForeignKeysOf<TForeignFrom> ? TableRelationshipColumn<TForeignFrom, TForeignKey> : undefined
        nullable: TForeignKey extends ForeignKeysOf<TForeignFrom>
            ? IsNullable<Tables<TForeignFrom>[TableRelationshipColumn<TForeignFrom, TForeignKey> extends keyof Tables<TForeignFrom> ? TableRelationshipColumn<TForeignFrom, TForeignKey> : never]>
            : TForeignKey extends ("inner" | undefined)
                ? false
                : true
    }

// --- Inference utilities ---

type InferFields<
    TTable extends Readonly<TableName>,
    TFields extends ReadonlyArray<FieldsOf<TTable>>
> =
    TFields extends readonly ["*"]
        ? { [K in keyof Tables<TTable>]: Tables<TTable>[K] }
        : TFields extends readonly (keyof Tables<TTable>)[]
            ? { [K in keyof Pick<Tables<TTable>, TFields[number]>]: Tables<TTable>[K] }
            : never

type InferRelation<TRelation extends AnyRelation> =
    TRelation extends OneRelation<infer _Name, infer _ForeignFrom, infer _ForeignKey, infer _TNullable, infer _Table, infer _Fields, infer _Relations>
        ? _ForeignKey extends "inner"
            ? InferFields<_Table, _Fields> & InferRelations<_Relations>
            : _TNullable extends false
                ? InferFields<_Table, _Fields> & InferRelations<_Relations>
                : _TNullable extends true
                    ? (InferFields<_Table, _Fields> & InferRelations<_Relations>) | null
                    : never
        : TRelation extends ManyRelation<infer _Name, infer _ForeignFrom, infer _ForeignKey, infer _TNullable, infer _Table, infer _Fields, infer _Relations>
            ? Array<InferFields<_Table, _Fields> & InferRelations<_Relations>>
            : never

type InferRelations<TRelations extends ReadonlyArray<AnyRelation>> =
    TRelations extends readonly [infer R extends AnyRelation]
        ? { [K in R["name"]]: InferRelation<R> }
        : TRelations extends readonly [infer R extends AnyRelation, ...infer Rest extends AnyRelation[]]
            ? { [K in R["name"]]: InferRelation<R> } & InferRelations<Rest>
            : {}

type InferSelect<TSelect extends SelectDefinition<any, any, any>> =
    TSelect extends SelectDefinition<infer TTable, infer TFields, infer TRelations>
        ? TRelations extends ([] | undefined)
            ? InferFields<TTable, TFields>
            : InferFields<TTable, TFields> & InferRelations<TRelations>
        : never

// --- Builder functions ---

function select<
    const TTable extends TableName,
    const TFields extends ReadonlyArray<FieldsOf<TTable>>,
    const TRelations extends ReadonlyArray<AnyRelation> = []
>(def: {
    table: TTable
    fields: TFields
    relations?: TRelations
}): SelectDefinition<TTable, TFields, TRelations> {
    return {
        table: def.table,
        fields: def.fields,
        relations: (def.relations ?? []) as TRelations
    }
}


function one<
    const TName extends string,
    const TTable extends TableName,
    const TFields extends ReadonlyArray<FieldsOf<TTable>>,
    const TRelations extends ReadonlyArray<AnyRelation>
>(
    name: TName,
    def: {
        table: TTable
        fields: TFields
        relations?: TRelations
    }
): OneRelation<TName, "", "", false, TTable, TFields, TRelations>
function one<
    const TName extends string,
    const TTable extends TableName,
    const TFields extends ReadonlyArray<FieldsOf<TTable>>,
    const TRelations extends ReadonlyArray<AnyRelation>,
    const TForeignFrom extends string,
    const TForeignKey extends string,
    const TNullable extends boolean
>(
    name: TName,
    def: {
        table: TTable
        fields: TFields
        relations?: TRelations
    },
    options: {
        foreignFrom: TForeignFrom,
        foreignKey: TForeignKey,
        nullable: TNullable
    }
): OneRelation<TName, TForeignFrom, TForeignKey, TNullable, TTable, TFields, TRelations>
function one(
    name: any,
    def: any,
    options?: {
        foreignFrom: any,
        foreignKey: any,
        nullable: any
    }
): any {
    return {
        ...def,
        relations: (def.relations ?? []),
        foreignFrom: (options?.foreignFrom ?? ""),
        foreignKey: (options?.foreignKey ?? ""),
        nullable: (options?.nullable ?? false),
        kind: "one",
        name
    }
}

function many<
    const TName extends string,
    const TTable extends TableName,
    const TFields extends ReadonlyArray<FieldsOf<TTable>>,
    const TRelations extends ReadonlyArray<AnyRelation>
>(
    name: TName,
    def: {
        table: TTable
        fields: TFields
        relations?: TRelations
    }
): ManyRelation<TName, "", "", false, TTable, TFields, TRelations>
function many<
    const TName extends string,
    const TTable extends TableName,
    const TFields extends ReadonlyArray<FieldsOf<TTable>>,
    const TRelations extends ReadonlyArray<AnyRelation>,
    const TForeignFrom extends string,
    const TForeignKey extends string,
    const TNullable extends boolean
>(
    name: TName,
    def: {
        table: TTable
        fields: TFields
        relations?: TRelations
    },
    options: {
        foreignFrom: TForeignFrom,
        foreignKey: TForeignKey,
        nullable: TNullable
    }
): ManyRelation<TName, TForeignFrom, TForeignKey, TNullable, TTable, TFields, TRelations>
function many(
    name: any,
    def: any,
    options?: {
        foreignFrom: any,
        foreignKey: any,
        nullable: any
    }
): any {
    return {
        ...def,
        relations: (def.relations ?? []),
        foreignFrom: (options?.foreignFrom ?? ""),
        foreignKey: (options?.foreignKey ?? ""),
        nullable: (options?.nullable ?? false),
        kind: "many",
        name
    }
}

function ref<
    const TForeignFrom extends TableName,
    const TForeignKey extends "inner"
>(from: TForeignFrom, key: TForeignKey): ForeignDefinition<TForeignFrom, TForeignKey>
function ref<
    const TForeignFrom extends TableName,
    const TForeignKey extends ForeignKeysOf<TForeignFrom>,
    const TBy extends TableRelationshipColumn<TForeignFrom, TForeignKey>
>(from: TForeignFrom, key: TForeignKey, by: TBy): ForeignDefinition<TForeignFrom, TForeignKey>
function ref(from: any, key: any, by?: any): any {
    if(key === "inner")
        return { foreignFrom: from, foreignKey: key, by: undefined } as const
    return { foreignFrom: from, foreignKey: key, by } as const
}

function merge<
    const TTable extends Readonly<TableName>,
    const TFields1 extends ReadonlyArray<FieldsOf<TTable>>,
    const TRelations1 extends ReadonlyArray<AnyRelation>,
    const TFields2 extends ReadonlyArray<FieldsOf<TTable>> = [],
    const TRelations2 extends ReadonlyArray<AnyRelation> = []
>(
    base: SelectDefinition<TTable, TFields1, TRelations1>,
    extras: {
        fields?: TFields2,
        relations?: TRelations2
    }
): SelectDefinition<TTable, [...TFields2, ...TFields1], [...TRelations2, ...TRelations1]> {
    return {
        table: base.table,
        fields: [...base.fields, ...(extras.fields ?? [])],
        relations: [...(base.relations ?? []), ...(extras.relations ?? [])]
    } as any
}

function buildRelations(
    relations: readonly AnyRelation[] = [],
): string {
    return relations
        .map((relation) => {
            const foreignBy =
                relation.foreignKey && relation.foreignKey !== "" ? `!${relation.foreignKey}` : ""

            const nested = buildRelations(relation.relations ?? [])

            const fields =
                relation.fields.length === 1
                    ? `${relation.fields[0]}`
                    : `${relation.fields.join(", ")}`

            return `${relation.name}:${relation.table}${foreignBy}(${fields}${
                nested ? `, ${nested}` : ""
            })`
        })
        .join(", ")
}

function build<
    const TSelect extends SelectDefinition<any, any, any>
>(
    def: TSelect
): Build<typeof def> {
    const fieldStr = `${def.fields.join(", ")}`

    const relationStr = buildRelations(def.relations)

    const result = [fieldStr, relationStr].filter(Boolean).join(", ")

    return result as any
}


type DepthCounter = [never, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17]
type Decrement<D extends number> = D extends keyof DepthCounter ? DepthCounter[D] : never

// --- Recursive stringify of select definition with depth limit ---

type Build<
    T extends Readonly<SelectDefinition<any, any, any>>,
    MaxDepth extends number = 8
> =
    MaxDepth extends never
        ? ""
        : JoinFields<T["table"], T["fields"], T['relations'], MaxDepth>

type JoinFields<
    Table extends Readonly<TableName>,
    Fields extends ReadonlyArray<FieldsOf<Table>>,
    Relations extends ReadonlyArray<AnyRelation>,
    Depth extends number
> =
    Depth extends never
        ? ""
        : Relations extends readonly []
            ? FieldsToString<Table, Fields>
            : Relations extends readonly [infer R extends AnyRelation]
                ? `${FieldsToString<Table, Fields>}, ${RelationToString<R, Depth>}`
                : Relations extends readonly [infer R extends AnyRelation, ...infer Rest extends AnyRelation[]]
                    ? `${FieldsToString<Table, Fields>}, ${RelationToString<R, Depth>}, ${RelationsToString<Rest, Decrement<Depth>>}`
                    : never


type FieldsToString<
    Table extends Readonly<TableName>,
    Fields extends ReadonlyArray<FieldsOf<Table>>,
> =
    Fields extends readonly [] ? "" :
        Fields extends readonly [infer F extends string]
            ? F
            : Fields extends readonly [infer F extends string, ...infer Rest extends ReadonlyArray<FieldsOf<Table>>]
                ? Rest extends readonly string[]
                    ? `${F}, ${FieldsToString<Table, Rest>}`
                    : never
                : never

type RelationsToString<
    Relations extends ReadonlyArray<AnyRelation>,
    Depth extends number
> =
    Depth extends never | 0
        ? ""
        : Relations extends readonly []
            ? ""
            : Relations extends readonly [infer R extends AnyRelation]
                ? RelationToString<R, Depth>
                : Relations extends readonly [infer R extends AnyRelation, ...infer Rest extends AnyRelation[]]
                    ? `${RelationToString<R, Depth>}, ${RelationsToString<Rest, Decrement<Depth>>}`
                    : never

type RelationToString<
    R extends Readonly<AnyRelation>,
    Depth extends number
> =
    R extends OneRelation<infer Name, infer TForeignFrom, infer TForeignKey, infer TNullable, infer Table, infer Fields, infer Relations>
        ? TForeignKey extends ""
            ? `${Name}:${Table}(${Build<SelectDefinition<Table, Fields, Relations>, Decrement<Depth>>})`
            : `${Name}:${Table}!${TForeignKey}(${Build<SelectDefinition<Table, Fields, Relations>, Decrement<Depth>>})`
        : R extends ManyRelation<infer Name, infer TForeignFrom, infer TForeignKey, infer TNullable, infer Table, infer Fields, infer Relations>
            ? TForeignKey extends ""
                ? `${Name}:${Table}(${Build<SelectDefinition<Table, Fields, Relations>, Decrement<Depth>>})`
                : `${Name}:${Table}!${TForeignKey}(${Build<SelectDefinition<Table, Fields, Relations>, Decrement<Depth>>})`
            : never

export {
    select,
    merge,
    one,
    many,
    ref,
    build
}
export type {
    InferSelect,
    Build
}

1. Utility types

The utility types serve as the backbone for the select query builder. They define the core structures for tables, fields, and relationships in Supabase. By leveraging these types, developers can manage database schemas, foreign keys, and relationships in a way that ensures type safety at every stage of the select query process.

Among these, the InferSelect and Build types are the basis for ensuring type safety and flexibility within the system.

• InferSelect: This type infers the fields and relations from a select definition. It automatically deduces the types for the fields and relationships of the specified table and its joins, providing enhanced safety for select query construction.

• Build: This type transforms a select query definition into a string representing the SQL select query. It dynamically handles select query construction while respecting the structure of fields and relationships defined in the database schema. This type supports recursion to handle deeply nested relations.

These types enable efficient select query management while ensuring that the generated queries align with database schema definitions, maintaining type safety throughout the process.

2. Dynamic select query construction

The select query builder functions, such as select, one, many and build, allow developers to construct complex queries dynamically while ensuring that the select query syntax is consistent with the database schema. This guarantees that field names, relationships, and table references remain valid, preventing issues caused by typos or incorrect references.

The select function, in particular, provides a clean and flexible way to build select queries. Developers can specify which fields to retrieve and which related tables to join, all while ensuring that the resulting select query structure is type-checked and adheres to the database schema.

3. Inference and safety

Through TypeScript's powerful type inference system, the builder automatically determines the correct types for fields, relationships, and the resulting data. This automatic type checking ensures that the structure of select query results aligns with expectations, reducing the likelihood of runtime errors related to data mismatches.

4. Relation management

The select query builder handles complex relational data efficiently. It supports defining one-to-one and one-to-many relationships between tables. Nested relations are automatically handled, ensuring referential integrity and simplifying the process of select querying related data without the need for manual joins or complex select query logic.

Usage

Example: select query with one-to-one relationship

In this example, we will walk through the process of creating a select query that selects posts and their related categories using a select query builder.

1. Selecting posts

To start, we'll create a basic select query that selects posts from the database. We’ll focus on retrieving the id, name, and slug fields for each post.

const postSelect = select({
    table: "posts",
    fields: ["id", "name", "slug"]
})

This select query is straightforward and will return posts with the necessary fields: id, name, and slug.

2. Selecting categories

Next, we need to fetch categories from the categories table. In this case, we are selecting the id and name fields of each category. This gives us the relevant data about the categories linked to the posts.

const categorySelect = select({
    table: 'categories',
    fields: ['id', 'name'],
})

This select query will return categories with their id and name fields.

3. Merging the selects with a one-to-one relationship

Now, we combine the two queries—posts and categories—by merging them together using a relationship. This is where we define the one-to-one relationship between the posts and categories tables.

const postWithCategorySelect = merge(postSelect, {
    relations: [
        one('cat', categorySelect, ref("posts", "posts_category_id_fkey", "category_id"))
    ]
})

In this relationship:

• The first select query (to fetch posts) is merged with a second select query that retrieves the related categories.
• The relationship is defined by the one function, which links the posts to their respective categories. This is done through the foreign key relationship between the two tables.

A relationship is established between the foreign key posts_category_id_fkey and the category_id field in the posts table. This ensures the correct linking and typing of posts to their categories.

4. Final select query

After merging the queries, we now have a final select query that selects posts along with their related categories. The result will return posts and their associated categories in a structured format.

type PostWithCategory = InferSelect<typeof postWithCategorySelect>

// same as
type PostWithCategory = {
  id: number
  name: string
  slug: string
  cat: {
    id: number
    name: string
  }
}

By using this approach:

• The select query is type-safe, ensuring that the correct data types are returned.
• The structure of the data aligns with the expected one-to-one relationship between posts and categories.
• The select query builder is flexible, allowing you to easily extend it to support additional relationships or fields if necessary.

This code does the same thing as the previous example.

const postWithCategorySelect = select({
    table: "posts",
    fields: ["id", "name", "slug"],
    relations: [
        one('cat', categorySelect, ref("posts", "posts_category_id_fkey", "category_id"))
    ]
})

5. Build select query

You also have the option to build the SQL select query string for Supabase using the build function.

const queryString = build(postWithCategorySelect)

// queryString = "id, title, slug, cat:categories!posts_category_id_fkey(id, name)"

6. Use with supabase

const posts = await supabase.from("posts")
    .select(build(postWithCategorySelect))

// posts type is same as InferSelect<typeof postWithCategorySelect>

---

Summary

In this example:

• select is used to define basic queries for posts and categories.
• one is used to define a one-to-one relationship between posts and categories.
• merge is used to combine the post select query with the category relation, enabling the retrieval of posts with their associated categories.
• build is used to create the select query string used by supabase.select()

This approach ensures that relational data can be queried with full type safety, making it easier to work with complex database relationships in a structured manner.

Functions

• select: Creates a new select query definition.
• merge: Combines select queries into one.
• one: Defines a one-to-one relationship in the select query.
• many: Defines a one-to-many relationship in the select query.
• ref: Defines foreign key relationships between tables.
• build: Builds the final select query string based on the select defintion and their relationships.

Conclusion

This type-safe select query builder improves the development process by ensuring type safety throughout database interactions. It simplifies managing relationships, supports dynamic select query construction, and guarantees that select query results match the expected structure. By providing a flexible approach to building select queries and handling complex relational data, it helps to write cleaner, more reliable code with fewer runtime errors.

[marcusdotnet]

Can we get this noticed?