Generating a Typesafe Supabase API - Part 1 - Scope and Goals

Defining Project Boundaries: A Closer Look at the Scope and Objectives of this multipart series on creating a typesafe Supabase API

tech
supabase

In this introductory section, we will delve into the scope and goals of this multipart series on creating a typesafe Supabase API. This will outline the challenges, how to address them, and the benefits of achieving type safety in your Supabase-powered applications.

This multipart series is divided into the following parts, which will be covered in future articles:

  • Part 1 - Scope and Goals
    • Defining the project scope
    • Setting clear objectives
    • Proposed API template
    • Benefits of type safety in Supabase
  • Part 2 - Understanding TypeScript’s AST
    • Introduction to Abstract Syntax Tree (AST)
    • Navigating Typescripts AST
  • Part 3 - Parsing the AST and Preparing Data
    • Extracting information from AST
    • Data preparation for code generation
    • Setting the stage for API building
  • Part 4 - Emitting Basic Logic API
    • Generating CRUD operations
    • Establishing foundational logic
    • Code emission
  • Part 5 - Additional Type Generation
    • Enums
    • Relationships
  • Part 6 - Relationships inner One-to-One/One-to-Many
    • Understanding relationships
    • Generating typesafe code for joins
  • Part 7 - Supabase and DB Function
    • Difference between DB functions and Supabase edge functions
    • Generation of DB functions
  • Part 8 - Relationships reverse One-to-One/One-to-Many
    • Extending code generation logic
  • Part 9 - Improvements
    • AST emitting
    • Incremental source generation pipeline
    • Future enhancements and optimizations

Problem Definition

The Supabase API enables the generation of an interface definition used for typing in the client SDK. The basic structure of the query builder looks like this: supabase.from(tableName).select().eq().... The tableName is a string, and the select() method returns a Promise of type PostgrestQueryBuilder<RowType>. When creating the Supabase client, you can specify the generated database definition in generics. This allows your language server to provide the correct types when calling your Supabase functions.

However, when you want to use the type definition of a table in your code, you need to define it again or use some special types. This works well for representing tables. Take this as an example:

export type TableKeys = keyof Database['public']['Tables'];
export type ViewKeys = keyof Database['public']['Views'];
export type TableViewKeys = TableKeys | ViewKeys;

export type Row<T extends TableViewKeys> = T extends TableKeys
    ? Database['public']['Tables'][T]['Row']
    : T extends ViewKeys
        ? Database['public']['Views'][T]['Row']
        : never;

This provides correct type information for the row of a table or view, such as Row<'users'>. The same can be done for insert or update definitions:

export type InsertDto<T extends TableViewKeys> = T extends TableKeys
    ? Database['public']['Tables'][T]['Insert']
    : T extends ViewKeys
        ? Database['public']['Views'][T]['Insert']
        : never;

export type UpdateDto<T extends TableViewKeys> = T extends TableKeys
    ? Database['public']['Tables'][T]['Update']
    : T extends ViewKeys
        ? Database['public']['Views'][T]['Update']
        : never;

This makes the keys that are not needed for an insert (like id) optional. The same applies to the update, where all of them, except id, are partial.

If you want to build a wrapper on top of Supabase, you might want to reference specific keys by column name. This can be achieved with:

export type Column<T extends TableViewKeys> =
    T extends TableKeys ? keyof Database['public']['Tables'][T]['Row']
        : T extends ViewKeys ? keyof Database['public']['Views'][T]['Row'] : never;

export type Columns<T extends TableViewKeys> = T extends TableKeys
    ? Partial<{ [K in keyof Database['public']['Tables'][T]['Row']]: any }>
    : T extends ViewKeys
        ? Partial<{ [K in keyof Database['public']['Views'][T]['Row']]: any }>
        : never;

This allows you to use function select<T extends TableViewKeys>(tableName: T, { key: Column<T>, value: string }), enabling you to reference the column by name. This is useful, for example, when building a search function that allows you to search for a value in a specific column.

Now, let’s examine foreign keys. To obtain type information for a OneToOne or OneToMany mapping, you could use:

export type OneToMany<
    ParentTable extends keyof Database['public']['Tables'],
    ChildTable extends keyof Database['public']['Tables']
> = {
    [K in keyof Database['public']['Tables'][ParentTable]['Row'] | ChildTable]:
    K extends keyof Database['public']['Tables'][ParentTable]['Row']
        ? Database['public']['Tables'][ParentTable]['Row'][K]
        : K extends ChildTable
            ? Database['public']['Tables'][ChildTable]['Row'][]
            : never;
};

export type OneToOne<
    ParentTable extends keyof Database['public']['Tables'],
    ChildTable extends keyof Database['public']['Tables']
> = {
    [K in keyof Database['public']['Tables'][ParentTable]['Row'] | ChildTable]:
    K extends keyof Database['public']['Tables'][ParentTable]['Row']
        ? Database['public']['Tables'][ParentTable]['Row'][K]
        : K extends ChildTable
            ? Database['public']['Tables'][ChildTable]['Row']
            : never;
};

You can see where this leads to. While the current approach works well for joining two tables, it becomes cumbersome when dealing with three or more tables or when there are two foreign keys to the same table. Consider the following example: a user table user: id, name, address_id, and billing_address_id. If you want to join on the address to get a result like user: id, name, address_id, address, billing_address_id, billing_address, it’s not feasible with the existing definitions.

This often results in numerous hand-written type definitions, such as type UserWithAddress = User & { address: Row<'address'>, billing_address: Row<'address'> }. These definitions may be scattered and out of sync with the database if changes occur.

Furthermore, building a generalized API layer on top requires defining all table names as strings, introducing challenges such as code refactoring difficulties, lack of find-usages support, and unnecessary boilerplate.

A similar challenge arises with enums. Although they can be defined in the database, using them in code requires redundant definitions. Without proper type integration, hardcoded strings proliferate, making refactoring more challenging and reducing overall code readability. For instance, changing a UserAccessRole value from admin to product_admin necessitates manually searching the entire codebase for occurrences of this string, whereas with proper types, refactoring tools can perform the task seamlessly with a simple rename.

Proposed Solution

To implement the suggested API layer along with the required typings, this solution heavily relies on code generation. The code generation process is designed to be executed on CI, in pre-build, or any other necessary environment.

API Layer Functions

The proposed generalized template is structured as follows: logic.[tableName].[function]([params]). The supported functions include:

  • single(filter: Columns<[tableName]>): returns a single row that exists.
  • singleMaybe(filter: Columns<[tableName]>): returns a single row that might not exist.
  • select(filter: Columns<[tableName]>): selects multiple rows.
  • selectMaybe(filter: Columns<[tableName]>): similar to select, but allowed to return undefined if no rows match.
  • selectCount(filter: Columns<[tableName]>): selects one or multiple rows, including the count of all rows.
  • selectIn<K extends KeyOfRow<[tableName]>>(key: K, values: any[]): selects multiple rows by a list of values.
  • insert(data: InsertDto<[tableName]>): inserts a single entry.
  • insertMany(data: InsertDto<[tableName]>[]): inserts many entries.
  • update(data: Columns<[tableName]>, filter: UpdateDto<[tableName]>): updates a single entry.
  • upsert(data: InsertDto<[tableName]>): inserts or updates a single entry.
  • delete(filter: Columns<[tableName]>): deletes one or multiple entries.

Select functions for joins will take the form of with[JoinTable] like withAddress(filter).

API Layer Types

In addition to previously defined types, and more, joined types for every join will be generated in the form of [TableName]With[ForeignTable] like UserDataWithAddress.

API Layer Enums

Enums will be transformed into TypeScript enums, allowing their use in code with correct type information. The proposed format is: [TypeName].[Value] = '[value]' like UserAccessRole.Admin = 'admin'.

Parsing and Emitting Strategy

To obtain all necessary types and functions, the Supabase-generated interface type will be parsed by walking the AST, extracting the required values, and formatting them in a way that the code emitter can utilize for code generation. The code emitter will then generate the code based on the parsed data. The code emitter will operate from string templates filled with the parsed data, allowing for easy modifications to the code emitter and the code generation logic. While this method may evolve to proper emitting found in other code generators in the future, it currently serves as the most effective way to initiate code generation and ensure usability.

Out of Scope

This solution, at present, will not support the following:

  • Proper source code syntax node emitting
  • Incremental source code generation
  • Complex joins
  • Application of all Supabase functions to joins (count, orderBy, etc.)