Kysely•14mo ago

Shorthand way to pass the entire update object inside of doUpdateSet when updating multiple values.

Greetings! Thanks for developing Kysely, some of the stuff that it does actually blows my mind. I only started using it a few days ago, and I love it so far. Anyway, I have a quick and simple question. I am trying to update (upsert ) multiple values into a table like this:

const countries = [
    { id: 1, name: 'UK', continent: 'Europe' },
    { id: 2, name: 'France', continent: 'Europe' }
];

await db.insertInto('countries')
    .values(countries)
    .onConflict(oc => oc
        .column('id')
        .doUpdateSet(eb => ({
            name: eb.ref('excluded.name'),
            continent: eb.ref('excluded.continent')
        }))
    )
    .execute();

const countries = [
    { id: 1, name: 'UK', continent: 'Europe' },
    { id: 2, name: 'France', continent: 'Europe' }
];

await db.insertInto('countries')
    .values(countries)
    .onConflict(oc => oc
        .column('id')
        .doUpdateSet(eb => ({
            name: eb.ref('excluded.name'),
            continent: eb.ref('excluded.continent')
        }))
    )
    .execute();

This all works as expected, however, imagine that my countries had tons of keys besides id, name and continent. It would become really tedious and error prone if I had to write down my_column: eb.ref('excluded.my_coulmn') inside of the doUpdateSet method. This is not an issue if you are upserting a single object at a time (like a single country instead of an entire array), because then you can just reference that object inside the doUpdateSet, but I am working with an array of objects (countries). So my question is if there is a workaround/solution for this problem. Cheers!

29 Replies

Kristian Notari•14mo ago

I have created an helper that given an object return the "update object" with all the columns with the excluded.column thing:

export const updateAllSet = <DB, TB extends keyof DB, V extends Insertable<DB[TB]>>(
    record: V
): UpdateObject<OnConflictDatabase<DB, TB>, OnConflictTables<TB>, OnConflictTables<TB>> =>
    // eslint-disable-next-line @typescript-eslint/consistent-type-assertions
    pipe(
        record,
        R.mapWithIndex(
            column => (eb: ExpressionBuilder<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>) =>
                // eslint-disable-next-line @typescript-eslint/consistent-type-assertions
                eb.ref(`excluded.${column}` as StringReference<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>)
        )
    ) as UpdateObject<OnConflictDatabase<DB, TB>, OnConflictTables<TB>, OnConflictTables<TB>>

export const updateAllSet = <DB, TB extends keyof DB, V extends Insertable<DB[TB]>>(
    record: V
): UpdateObject<OnConflictDatabase<DB, TB>, OnConflictTables<TB>, OnConflictTables<TB>> =>
    // eslint-disable-next-line @typescript-eslint/consistent-type-assertions
    pipe(
        record,
        R.mapWithIndex(
            column => (eb: ExpressionBuilder<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>) =>
                // eslint-disable-next-line @typescript-eslint/consistent-type-assertions
                eb.ref(`excluded.${column}` as StringReference<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>)
        )
    ) as UpdateObject<OnConflictDatabase<DB, TB>, OnConflictTables<TB>, OnConflictTables<TB>>

I couldn't make it completely type safe though (notice the various as). Here R.mapWithIndex is the same as mapping over all the entries of the record given as input so... I couple that utility with a chunking utility, because when dealing with large arrays (hence parameters you pass to database) there's a parameters limit (using postgres for example) so I actually chunk the array and do multiple inserts as needed. That way, I get chunks where the first elements of the array is always there, and I use that as a reference for the "updateAllSet"

decho•14mo ago

Hey mate, thanks a lot for this, I really appreciate the time you spent to help. That being said, before replying here I wanted to analyse and test your code but I'm getting some errors. The types I import from kysely, but where are pipe and R coming from? I am kinda familiar with what piping is from linux command line, but sure exactly sure how it's implemented in your example

Kristian Notari•14mo ago

yeah those comes from fp-ts which is another library for functional-like programming/composing in ts. You can omit those and just Object.entries . map . Object.fromEntries your record or whatever you'd like to map over the record keys/objects

decho•14mo ago

right, i figured they might come from a popular lib and then the implementation would be like:

await db.insertInto('countries')
    .values([{ id: 1, name: 'France', continent: 'Europe' }])
    .onConflict(oc => updateAllSet(oc))
    .execute();

await db.insertInto('countries')
    .values([{ id: 1, name: 'France', continent: 'Europe' }])
    .onConflict(oc => updateAllSet(oc))
    .execute();

Kristian Notari•14mo ago

not really. You need to prepare your onConflict how you want, then call oc.doUpdateSet(updateAllSet(record)) where record is the first element of your values (I use the first one usually, don't know if they change in your case)

decho•14mo ago

okay yes, that makes more sense

await db.insertInto('countries')
    .values([{ id: 1, name: 'France', continent: 'Europe' }])
    .onConflict(oc => oc
        .column('id')
        .doUpdateSet(updateAllSet(oc))
    )
    .execute();

await db.insertInto('countries')
    .values([{ id: 1, name: 'France', continent: 'Europe' }])
    .onConflict(oc => oc
        .column('id')
        .doUpdateSet(updateAllSet(oc))
    )
    .execute();

ok, thanks for showing me this helper, need to do some brainstorming and analysing, only started a few days ago with kysely

Kristian Notari•14mo ago

const values = [{ id: 1, name: 'France', continent: 'Europe' }]
await db.insertInto('countries')
    .values(values)
    .onConflict(oc => oc
        .column('id')
        .doUpdateSet(updateAllSet(values[0]))
    )
    .execute();

const values = [{ id: 1, name: 'France', continent: 'Europe' }]
await db.insertInto('countries')
    .values(values)
    .onConflict(oc => oc
        .column('id')
        .doUpdateSet(updateAllSet(values[0]))
    )
    .execute();

more like this it's up to you to check of the first value not being undefined (eg. being there)

decho•14mo ago

ok seems like i misunderstood what the helper does then it's point is just to extract the column names i guess hey I'm sorry to bother but could you show me the exact imports you're using from fp-ts. Seems like there is a ton going on with that library and I'm familiar with it

Kristian Notari•14mo ago

you can use the helper without any other libraries if you want the "helper" is just a mapper for each key value pair of one of the values to set the eb.ref('excluded.${column}')

const record = { a: "hello", b: 42 }
const updateAllSet = (r) => {
   const entries = Object.entries(r)
   const mapped = entries.map(([k,v]) => [k, (eb) => eb.ref('excluded.${k}')])
   return Object.fromEntries(mapped) 
}

const record = { a: "hello", b: 42 }
const updateAllSet = (r) => {
   const entries = Object.entries(r)
   const mapped = entries.map(([k,v]) => [k, (eb) => eb.ref('excluded.${k}')])
   return Object.fromEntries(mapped) 
}

decho•14mo ago

okay, thanks that would be a better starting point since i was struggling to understand exactly what was going on in the previous function yep ok that worked minus all the typescript errors, cheers 👍 i will figure the rest out

Kristian Notari•14mo ago

😉

decho•14mo ago

Well, after studying your example and brainstorming quite a bit, I came up with my modified version:

function updateAllSet<DB, TB extends keyof DB, V extends Array<Insertable<DB[TB]>>>(records: V) {
    return Object.keys(records[0])
        .reduce<UpdateObject<OnConflictDatabase<DB, TB>, OnConflictTables<TB>, OnConflictTables<TB>>>((acc, key) => {
            return {
                ...acc,
                [key]: (eb: ExpressionBuilder<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>) =>
                    eb.ref(`excluded.${key as string}` as StringReference<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>)
            };
        }, {});
}

function updateAllSet<DB, TB extends keyof DB, V extends Array<Insertable<DB[TB]>>>(records: V) {
    return Object.keys(records[0])
        .reduce<UpdateObject<OnConflictDatabase<DB, TB>, OnConflictTables<TB>, OnConflictTables<TB>>>((acc, key) => {
            return {
                ...acc,
                [key]: (eb: ExpressionBuilder<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>) =>
                    eb.ref(`excluded.${key as string}` as StringReference<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>)
            };
        }, {});
}

However, I can't say I'm really satisfied. On runtime level, I understand everything. On compile/type level, not so much, especially some of these UpdateObjects with tons of generic params, really difficult to trace exactly what's going on. It would have been so much easier if ref provided some extra helpers, like:

doUpdateSet(eb.ref.allExcluded()))
// or
doUpdateSet((eb) => eb('excluded'))

doUpdateSet(eb.ref.allExcluded()))
// or
doUpdateSet((eb) => eb('excluded'))

something like this. But I guess this solution also isn't bad and you gotta work with what you got.

Kristian Notari•14mo ago

Kysely doesn’t store a runtime value defining all the possible columns. It should rely on the values you passed in. But what if you are doing an insert from other tables, so with an expression method instead of values? It’s hard to get it right. Also using the first value or the last one could, potentially, be different, based on the array of values you gave in. Even if the type is respected, they could have different keys, so you could end up creating an exclude update for a non existing column at db. If you need a subset of excludes you can write that manually, key after key. If you want to mentally say “everything I gave you as values” or “everything this table specifies as columns” you should do the values[0] thing I’m doing (if you can trust the keys in the values you’re using) or create a dummy “table record” with the keys on the runtime/value level, so you can use that object as the record to pass to the helper I wrote to you Either way, I advice using/creating a plugin for kysely (you would like to write your own) which filters the non existing columns from stuff like insert/update or on conflict set as in your example. Cause you don’t really know what could end up being in the objects you pass as values to kysely at runtime, even if the type is correct they could have more keys which map to non existing columns. For kysely/this plugin to do so you have to rely on a dummy table record value built just for this purpose

decho•14mo ago

So what you're basically saying is, what if we do a join from another table (or some other custom logic) and then we want to magically remap all to ref('excluded....'). Kysely and by extension ref would usually have the type information about that new data, but such data doesn't exist in runtime, so it's not possible to magically/automatically remap that on runtime level. You're right I think that explanation makes a whole lot of sense. for the time being I'm using this method which should be safe enough:

import type { Countries } from '../types/index.js'; // kysely-codegen
type Country = Insertable<Countries>;

const data = [{ id: 1, name: 'UK' },{ id: 2, name: 'France' }];

const countries = data.map<Country>(country => ({
    id: country.id,
    name: country.name
}));

// proceed to do database stuff with "countries"

import type { Countries } from '../types/index.js'; // kysely-codegen
type Country = Insertable<Countries>;

const data = [{ id: 1, name: 'UK' },{ id: 2, name: 'France' }];

const countries = data.map<Country>(country => ({
    id: country.id,
    name: country.name
}));

// proceed to do database stuff with "countries"

but once I get my hands more dirty and gain better understanding of kysely I will most likely consider your suggestion. by the way if you don't mind me asking, are you also a developer/contributor to kysely or just like helping people on the server? I have to say huge thanks again 🙂

Kristian Notari•14mo ago

what I've done for one of my codebases was to create a plugin which accepted all the database tables with key: true values like:

type Database = {
  a: {
     a1: string
     a2: number
  },
  b: {
     b1: string
     b2: number
  }
}
const Database = {
  a: {
     a1: true,
     a2: true
  },
  b: {
     b1: true,
     b2: true
  }
}

type Database = {
  a: {
     a1: string
     a2: number
  },
  b: {
     b1: string
     b2: number
  }
}
const Database = {
  a: {
     a1: true,
     a2: true
  },
  b: {
     b1: true,
     b2: true
  }
}

and then I initialize such plugin with the Database value. Doing so I can filter out unexpected keys when doing stuff like insert from values or on conflict excluded update set just helping and loving kysely approach so far

decho•14mo ago

so basically you're creating a runtime representation of your types, that's a cool idea i might steal 😉

Kristian Notari•14mo ago

yeah, so then you can use your values[0] as a record to base your eb => excluded thing for update set on conflicts without worrying about unexpected columns

decho•14mo ago

then you can import and use that for dummy great idea

Kristian Notari•14mo ago

IMO less to reason about, more time spent on actual business logic

decho•14mo ago

me too, initially I wanted to use Prisma, but I tested it and it doesn't even do joins properly. like it does multiple separate queries/calls to the database and glues them together in javascript land performance obviously isn't going to be as good but yeah im glad i found this amazing library just need to get some better understanding of all the complex types, but that will probably come with experience if you have the privileges, please tag the thread as "solved", for some reason I can't do it myself thanks again

Kristian Notari•14mo ago

@decho have you followed this ?

decho•14mo ago

I read it yesterday but didn't pay enough attention and was trying to manually tag my thread 😅 cheers mate. if you don't mind though, I will tag my own solution instead of your own because it's using 3rd party libs and stuff so it would be easier for someone coming later if they can see native JS methods. but we both know that i was never gonna reach that point without your help

decho•14mo ago

Hey, @Kristian Notari you mentioned you create runtime objects based on your DB types. if you are using kysely-codegen I made a small script that reads the file it generates, and then generates another file file with the runtime object like this:

/** Generated by ts-morph script do not edit. */

import type { DB } from './index.js';

export type DBDummy<T = DB> = {
  readonly [K in keyof T]: ReadonlyArray<keyof T[K]>;
}

export const dbDummy: DBDummy = {
  countries: [
    'id',
    'name'
  ],
}

/** Generated by ts-morph script do not edit. */

import type { DB } from './index.js';

export type DBDummy<T = DB> = {
  readonly [K in keyof T]: ReadonlyArray<keyof T[K]>;
}

export const dbDummy: DBDummy = {
  countries: [
    'id',
    'name'
  ],
}

Still a work in progress but perhaps it can be useful to you. https://gist.github.com/virtuallyunknown/bdb68925ab2a40c0e4e07ba8ca30741e

Gist

Generate runtime types from .ts file generated by kysely-codegen.

Generate runtime types from .ts file generated by kysely-codegen. - kysely-runtime.js

Kristian Notari•14mo ago

Thanks for sharing! Right now I’m doing it manually cause it’s typed to be something specific for an input to one of my plugins and I get compile time errors when I add a column or a table and that’s not reflected in the runtime values for the plugin, so I guess I don’t really need it for my usecase, but still helpful

decho•14mo ago

no problem, I thought I'd share with you just in case you might find it useful, perhaps for some future project or something like that btw I just finished putting everything together, the updateAllSet function with your help from a few days ago, and making that work in conjunction with the runtime types generator script. I'd be interested to hear your opinion/feedback about it, but I don't want to become annoying so if you want to take a look I can paste the code, but otherwise no probs.

Kristian Notari•14mo ago

Sure go ahead

decho•14mo ago

Well basically you have these 2 generated files, one by kysely-codegen, and the second by my script I just showed you:

// db.ts
export interface Countries {
  id: number;
  name: string;
}
export interface DB {
  countries: Countries;
}

// db-runtime.ts
import type { DB } from './db.js';

type DBDummy = {
  readonly [K in keyof DB]: ReadonlyArray<keyof DB[K]>;
}
export const dbDummy: DBDummy = {
  countries: [
    'id',
    'name'
  ]
}

// db.ts
export interface Countries {
  id: number;
  name: string;
}
export interface DB {
  countries: Countries;
}

// db-runtime.ts
import type { DB } from './db.js';

type DBDummy = {
  readonly [K in keyof DB]: ReadonlyArray<keyof DB[K]>;
}
export const dbDummy: DBDummy = {
  countries: [
    'id',
    'name'
  ]
}

then the function:

// kysely.ts
import type { OnConflictDatabase, OnConflictTables, StringReference, ExpressionBuilder, UpdateObject } from 'kysely';
import type { DB as PGDB } from '../types/db.js';
import { dbDummy } from '../types/db-runtime.js';

export function updateAllSet<DB extends PGDB, TB extends keyof DB & keyof PGDB>(table: TB) {
    return dbDummy[table].reduce<UpdateObject<OnConflictDatabase<DB, TB>, OnConflictTables<TB>, OnConflictTables<TB>>>((acc, column) => {
        return {
            ...acc,
            [column]: (eb: ExpressionBuilder<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>) =>
                eb.ref(`excluded.${column as string}` as StringReference<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>)
        };
    }, {});
}

// kysely.ts
import type { OnConflictDatabase, OnConflictTables, StringReference, ExpressionBuilder, UpdateObject } from 'kysely';
import type { DB as PGDB } from '../types/db.js';
import { dbDummy } from '../types/db-runtime.js';

export function updateAllSet<DB extends PGDB, TB extends keyof DB & keyof PGDB>(table: TB) {
    return dbDummy[table].reduce<UpdateObject<OnConflictDatabase<DB, TB>, OnConflictTables<TB>, OnConflictTables<TB>>>((acc, column) => {
        return {
            ...acc,
            [column]: (eb: ExpressionBuilder<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>) =>
                eb.ref(`excluded.${column as string}` as StringReference<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>)
        };
    }, {});
}

finally, you use it like this:

import { db } from '../util/db.js';
import { updateAllSet } from '../util/kysely.js';

const countries = [{ id: 1, name: 'a' }, { id: 2, name: 'b' }];

await db.insertInto('countries').values(countries)
    .onConflict(oc => oc
        .column('id')
        .doUpdateSet(
            updateAllSet('countries') // here you get type safety and can only select "countries"
        ))
    .execute();

import { db } from '../util/db.js';
import { updateAllSet } from '../util/kysely.js';

const countries = [{ id: 1, name: 'a' }, { id: 2, name: 'b' }];

await db.insertInto('countries').values(countries)
    .onConflict(oc => oc
        .column('id')
        .doUpdateSet(
            updateAllSet('countries') // here you get type safety and can only select "countries"
        ))
    .execute();

And that's basically it. Overall I am happy with how that turned out.

Kristian Notari•14mo ago

Ok so basically you can get rid of passing an actual record to updateAllSet

decho•14mo ago

yes pretty much. and since we have the column names as an array/tuple in runtime, we just use reduce on them to output a mapped object with eb.ref('excluded...') for each one of the array members. the only part i was slightly unhappy about was the generics in the function signature, i had to do cheat a little bit 1. TB extends keyof DB & keyof PGDB 2. use spread operator inside instead of acc[key] = eb.ref(...) otherwise Typescript would just complain that table: TB can't be used to index dbTable. but oh well, it works

Gaming

Programming

Shorthand way to pass the entire update object inside of doUpdateSet when updating multiple values.