kysely-org / kysely

A type-safe typescript SQL query builder
https://kysely.dev
MIT License
10.71k stars 271 forks source link

Can't set `updatedAt` from `onConflict` #617

Closed romeerez closed 10 months ago

romeerez commented 1 year ago

Hi, and thanks for maintaining this wonderful library!

I encountered a problem trying to set updatedAt inside onConflict.

interface ItemTable {
  uniqueKey: string;
  value: number;
  // maybe I can change something here?
  updatedAt: ColumnType<Date, never, string | undefined>;
}

type NewItem = Insertable<ItemTable>;

interface Database {
  item: ItemTable;
}

const dialect = new PostgresDialect({
  pool: new Pool({ ...config }),
});

const db = new Kysely<Database>({
  dialect,
});

function upsertItems(
  items: NewItem[]
): Promise<InsertResult[]> {
  return db
    .insertInto("item")
    .values(items)
    .onConflict((oc) =>
      oc.column("uniqueKey").doUpdateSet({
        // no problem setting a regular value:
        value: (eb) => eb.ref("excluded.value"),
        // TS error on the following line
        updatedAt: (eb) => eb.ref("excluded.updatedAt"),
      })
    )
    .execute();
}

TS error:

Property '#private' in type 'ExpressionWrapper' refers to a different member that cannot be accessed from within type 'SelectQueryBuilder'

Looks like the problem is related to the fact that updatedAt is defined using ColumnType.

It's not critical though, I worked this around by changing the line inside onConflict to:

updatedAt: (eb) => eb.sql`now()`,
koskimas commented 1 year ago

We might be using the insert type there, even though we should be using the update type. Thanks for the issue! We'll take a look soon.

ejabu commented 5 months ago

updatedAt: (eb) => eb.sqlnow(),

This above syntax seems outdated.

As workaround I used this.

import { sql } from 'kysely';

export const bulkInsert = (rows: NewIssues[]) => {
  const query = kyselyDB
    .insertInto('table_name')
    .values(rows)
    .onConflict((oc) =>
      oc.column('id').doUpdateSet({
        updatedAt: (eb) => sql`now()`,
        id: (eb) => eb.ref('excluded.id'),
      }),
    )
    .compile();

  console.log('sql =>\n', query.sql);
  console.log('sql =>\n', query.parameters);
  return query;
};
mb21 commented 1 month ago

For those looking for a generic way to update all fields, see https://github.com/kysely-org/kysely/issues/677#issuecomment-2292177699