Type safety is not being adhered on insert queries

[dvrfluxchat]

I have a insert query in my db which looks like this and I am using the typescript version of supabase-js.

await supaServiceClient
        .from("table_name")
        .insert({
          column1:value,
          column2:value,
          column3:value
        });

I changed the column "column1" to "newColumn1" but the above query did not throw any errors where there is no "column1". This lead to issues in production where this query stopped working as the insert was failing saying there is no column1 but the compiler did not throw any error. Is this the expected behaviour? If so how do i ensure to get compile time warnings for such scenarios.

[tecoad]

I have the same issue. Did you find out @dvrfluxchat ?

[FilipPano]

I am experiencing the same issue. There is no type safety on insert for values that are not part of your table schema.

E.g

const {data, error} = await supaServiceClient
        .from("table_name")
        .insert({
          column1:value, // this exists as a column in table_name
          column2:value, // this exists too
          column3:value // this does not
        }).select("*");

I think It should throw a type error letting you know that there is no column3 column in your table.

[Lambtsa]

A little irritating that queries are not typesafe automatically if the dbClient is typed but the queries are generic so passing in a type definition will solve this issue

export const insertCountry = async () => {
  return await supabaseClient
    .from('country')
    .insert<UpsertCountryInput>([
      { name: 'Nepal' },
      { name: 'Vietnam' },
    ]);
};

[avallete]

Still seems to be an issue where extra invalids parameters are juste silently dismissed if the object have all the other properties it need:

I'm not sure this is really a bug tough or an intended behaviour, I can see this come handy if you have things like:

  const res = await postgrest
    .from('users')
    .insert({
      ...someObjectWithExtraKeys
    })

[StuartMorris0]

I too thought that the insert would be typechecked, but it appears not. I'm not sure I understand why it wouldn't be though.

For me, I decided to just add a type to inserts, which I can get from the generated types:

type GameInviteType = TablesInsert<'game_invites'>;

Then when I make the data for an insert I can use typescripts satisfies to validate against the type.

const inviteesData: GameInviteType[] = invitees.map(
        (invitee) =>
          ({
            game_id: gameData.id,
            email: invitee.email,
          }) satisfies GameInviteType,
      );

This would then complain if I added another attribute that does not exist on the type 👍

[atsixian]

+1, it would be nice if insert catches excess properties by default

[DarkLite1]

I'm running into the same issue and tried the suggestion here, but it's not working:

import { Database, Tables, TablesInsert } from './types/database.types'

const { data, error } = await supabase
  .from(tableName)
  .insert(body as TablesInsert<T>)
  .select()

Posted the full code on StackOverflow. Thx for any help on avoiding this TS error.

[avallete]

The issue with this is underlyingly how typescript "type duck" object that are not objects literals: https://www.typescriptlang.org/docs/handbook/2/objects.html#excess-property-checks

In order to get the excess-property-checks the way you assign the object must change to nudge typescript into a "literal" object checking and as far as I know, this can't really be dynamic.

This is why this won't raise any TS error:

  const res = await postgrest.from('users').insert({'username': 'toto', 'invalid_key': 'titi'} as TablesInsert<"users">)

But this will

function createTableInsert(user: TablesInsert<"users">): TablesInsert<"users"> {
  return user
}
const res = await postgrest.from('users').insert(createTableInsert({'username': 'toto', 'invalid_key': 'titi'}))

Or this will:

  // will raise a ts error here
  const value: TablesInsert<"users"> = {'username': 'toto', 'invalid_key': 'titi'}
  const res = await postgrest.from('users').insert(value)

insert type is dynamic (it's the same for all the tables, only pick the right value depending of previous chaining), while createTableInsert can be strict (which allow object literal excess properties check to apply).

I don't see any ways to turn the .insert method type into one that would allow this to apply to it. This probably won't be fixed, workaround is to do it in two steps, first one with exact type (this is where excess properties will be checked) and then pass it to the insert option (that will "duck type" due to TS default behavior with dynamic objects).

Edit:
One way to allow for excess properties checking would be to migrate toward typescript (interfaces)[https://www.typescriptlang.org/docs/handbook/interfaces.html#excess-property-checks]

[Minikhil]

Running into same issue, below method solved it for me

const insertData: TablesInsert<'table_name'> = {
  column1: value1,
  column2: value2,
  // TypeScript will error if you use invalid column names
};

const { error, data } = await supabase
  .from('table_name')
  .insert(insertData)
  .select();