Enhanced Type Inference for JSON Fields in supabase-js

[avallete]

TypeScript users, here's a cool new feature! Starting from v2.48.0, defining custom types for JSON fields in supabase-js and using them with the JSON selector is now easier, making your code more type-safe and intuitive.

Quick Example

Define your custom JSON type:

type CustomJsonType = {
  foo: string;
  bar: { baz: number };
  en: 'ONE' | 'TWO' | 'THREE';
};

export type Database = MergeDeep<
  DatabaseGenerated,
  {
    your_schema: {
      Tables: {
        your_table: {
          Row: {
            data: CustomJsonType | null;
          };
          // Optional: Use if you want type-checking for inserts and update
          // Insert: {
          //  data?: CustomJsonType | null;
          // };
          // Update: {
          //  data?: CustomJsonType | null;
          // };
        }
      }
      Views: {
        your_view: {
          Row: {
            data: CustomJsonType | null;
          };
        }
      }
    }
  }
>

What You Get

Now, when you query your data:

const res = await client
  .from('your_table')
  .select('data->bar->baz, data->en, data->bar');

if (res.data) {
  console.log(res.data);
  // TypeScript infers the shape of your JSON data:
  // [
  //   {
  //     baz: number;
  //     en: 'ONE' | 'TWO' | 'THREE';
  //     bar: { baz: number };
  //   }
  // ]
}

Get Started

Start using this feature with our guides:

• Generating Types
• Typescript Support
• Helper Types for Tables and Joins

[stevengrimaldo]

I see the extended example starts directly with your_table, however in the supabase docs on this it starts with public > Views > your_table:

export type Database = MergeDeep<
  DatabaseGenerated,
  {
    public: {
      Views: {
        movies_view: {
          Row: {
            // id is a primary key in public.movies, so it must be `not null`
            id: number
          }
        }
      }
    }
  }
>

which is correct?

also if the docs are correct, shouldn't it be Table instead of Views?

[stevengrimaldo]

thank you!

Follow up question,

you know how the generated types create Database but also separately Tables, TablesInsert, TablesUpdate, Enums, and CompositeTypes which leverage the generated Database shape?

How does this override version impact the use of those, as per the docs it suggests you're doing a DeepMerge in a new file while the generated is in another file, wouldn't you lose access to being able to use any of those Tables directly? i think you would have to manually duplicated them in your new file so it uses your extended Database instead right?

Screenshot for reference:

Update: I haven't tested this yet but if the answer is you'd have to duplicate, i suppose also DeepMerging the Tables type would work?

Example:

import { MergeDeep } from 'type-fest';
import type { Database as DatabaseGenerated, Tables as TablesGenerated } from './database.generated.types';

export type Database = MergeDeep<
  DatabaseGenerated,
  {
    ...
  }
>

export type Tables = MergeDeep<TablesGenerated>;

but im not so sure thats how it works exactly, like i think that the deep merged Tables would still be references the non-extended version of Database in which i'd still need to just do a full copy of that snippet right?

[avallete]

Hey there!

Thanks for pointing that out. Indeed, the utils type would use the "non-augmented" table definition type. Unfortunately, the MergeDeep solution wouldn’t work as those type helpers are generics that expect arguments and are constructed from the Database definition where they’re declared.

The good news is that, since these types are just utils derived from the Database type in the same file, you can duplicate the one you need in your database.override.types.ts file with your own database definition like so:

import { MergeDeep } from 'type-fest';
import { Database as DatabaseGenerated } from './database.types.ts';

export type Database = MergeDeep<
  DatabaseGenerated,
  ...
>;

// These definitions are duplicated from the original generated database.types.ts
type PublicSchema = Database[Extract<keyof Database, 'public'>];

export type Tables<
  PublicTableNameOrOptions extends
    | keyof (PublicSchema['Tables'] & PublicSchema['Views'])
    | { schema: keyof Database },
  TableName extends PublicTableNameOrOptions extends { schema: keyof Database }
    ? keyof (Database[PublicTableNameOrOptions['schema']]['Tables'] &
        Database[PublicTableNameOrOptions['schema']]['Views'])
    : never = never
> = ...;

export type TablesInsert<
  PublicTableNameOrOptions extends keyof PublicSchema['Tables'] | { schema: keyof Database },
  TableName extends PublicTableNameOrOptions extends { schema: keyof Database }
    ? keyof Database[PublicTableNameOrOptions['schema']]['Tables']
    : never = never
> = ...;

export type TablesUpdate<
  PublicTableNameOrOptions extends keyof PublicSchema['Tables'] | { schema: keyof Database },
  TableName extends PublicTableNameOrOptions extends { schema: keyof Database }
    ? keyof Database[PublicTableNameOrOptions['schema']]['Tables']
    : never = never
> = ...;

export type Enums<
  PublicEnumNameOrOptions extends keyof PublicSchema['Enums'] | { schema: keyof Database },
  EnumName extends PublicEnumNameOrOptions extends { schema: keyof Database }
    ? keyof Database[PublicEnumNameOrOptions['schema']]['Enums']
    : never = never
> = ...;

It’s not an ideal solution, and we might look into making those helpers more generic. That way, you could have something less verbose, such as:

import { MergeDeep } from 'type-fest';
import { Database as DatabaseGenerated, TablesHelpersFactory, EnumsHelpersFactory } from './database.types.ts';

export type Database = MergeDeep<
  DatabaseGenerated,
  ...
>;

export type Tables = TablesHelpersFactory<Database>;
export type Enums = EnumsHelpersFactory<Database>;

[stevengrimaldo]

Thank you!

I assume that since those are just helper utilities i don't need to re-copy them for any future generated types in my generated file right?

thanks again, happy typing!

[avallete]

Hey there !

Indeed once they're copied over to your override file none of them should have to change.

[benaduggan]

Definitely think those type factories would be a great solution!

In the meantime I just hacked a little script together that renames the Database type and prepends the file with my overrides. It's not pretty, but it is functional. Maybe someone will benefit, or have a better way to accomplish the same thing!

#!/usr/bin/env bash

output_file="src/types/db/supabase.types.ts"
overrides_file="src/types/db/overrides.txt"

# Generate types from local db
supabase --workdir '../../apps/project' gen types typescript --local --schema public,reco > "$output_file"

# Replace `export type Database` with `type BaseDatabase` so we can override it (using gnused, macOS sed has different syntax)
sed -i 's/export type Database/type BaseDatabase/g' "$output_file"

# prepend the overrides into the generated types file
cat "$overrides_file" "$output_file" > temp
mv temp "$output_file"

and the overrides.txt is just importing MergeDeep and defining / exporting the Database type so the existing Tables helpers and such get the updated Database type

import type { MergeDeep } from 'type-fest';

// allows us to specify JSON type overrides in the database schema or
// override the default of view columns coming over as nullable
export type Database = MergeDeep<
  BaseDatabase,
  {
    public: {
      Tables: {
        your_table: {
          Row: {
	          ...your overrides
          };
          Insert: {
	          ...your overrides
          };
          Update: {
	          ...your overrides
          };
        };
      };
      Views: {
        your_view: {
          Row: {
	          ...your overrides
          };
        };
      };
    };
  }
>;