significantotter
significantotter
Explore posts from servers
DIAdiscord.js - Imagine an appKKyselyDTDrizzle Team
PostsComments
KKysely
Created by significantotter on 11/15/2023 in #help
Are JSON Arrays not supported for paramterization in the postgres driver?
and here's that helper function: https://github.com/significantotter/kobold/blob/compendium/src/services/kobold/lib/kysely-json.ts
16 replies
KKysely
Created by significantotter on 11/15/2023 in #help
Are JSON Arrays not supported for paramterization in the postgres driver?
https://github.com/significantotter/kobold/blob/compendium/src/services/kobold/models/sheet-record.model.ts
16 replies
KKysely
Created by significantotter on 11/15/2023 in #help
Are JSON Arrays not supported for paramterization in the postgres driver?
I did, however, end up with a workable (if unappealing) solution. I just manually use a function to generate a sql json array insert statement from the array.
16 replies
KKysely
Created by significantotter on 11/15/2023 in #help
Are JSON Arrays not supported for paramterization in the postgres driver?
I didn't, because it was built into a sqlite driver package and I'm using postgres
16 replies
KKysely
Created by significantotter on 11/15/2023 in #help
Are JSON Arrays not supported for paramterization in the postgres driver?
For anyone who references this in the future, this was the solution I put together: 
// in a helper file:

/**
 * The node-pg driver automatically treats all array value parameters as postgres arrays
 * In order to serialize JSONB arrays, we have to prepare it into a string-like value first
 */
export function sqlJSON<T>(object: T): RawBuilder<T> {
    return sql`(${JSON.stringify(object)}::jsonb)`;
}

SheetRecordModel
    /** ... */
    public async update(
        { id }: { id: SheetRecordId },
        args: SheetRecordUpdate
    ): Promise<SheetRecord> {
       return await this.db
            .updateTable('sheetRecord')
            .set({
                ...args,
                modifiers: args.modifiers !== undefined ? sqlJSON(args.modifiers) : undefined,
                actions: args.modifiers !== undefined ? sqlJSON(args.actions) : undefined,
                rollMacros: args.modifiers !== undefined ? sqlJSON(args.rollMacros) : undefined,
            })
            .where('sheetRecord.id', '=', id)
            .returningAll()
            .executeTakeFirstOrThrow();
    }
    /** ... */
}
// in a helper file:

/**
 * The node-pg driver automatically treats all array value parameters as postgres arrays
 * In order to serialize JSONB arrays, we have to prepare it into a string-like value first
 */
export function sqlJSON<T>(object: T): RawBuilder<T> {
    return sql`(${JSON.stringify(object)}::jsonb)`;
}

SheetRecordModel
    /** ... */
    public async update(
        { id }: { id: SheetRecordId },
        args: SheetRecordUpdate
    ): Promise<SheetRecord> {
       return await this.db
            .updateTable('sheetRecord')
            .set({
                ...args,
                modifiers: args.modifiers !== undefined ? sqlJSON(args.modifiers) : undefined,
                actions: args.modifiers !== undefined ? sqlJSON(args.actions) : undefined,
                rollMacros: args.modifiers !== undefined ? sqlJSON(args.rollMacros) : undefined,
            })
            .where('sheetRecord.id', '=', id)
            .returningAll()
            .executeTakeFirstOrThrow();
    }
    /** ... */
}
16 replies
KKysely
Created by significantotter on 11/15/2023 in #help
Are JSON Arrays not supported for paramterization in the postgres driver?
Hm, I just found this PR from a year ago about a similar topic. I totally get your reasoning for not putting it into the main code, but I was wondering if you ever made progress on the generic serializer plugin? That kind of thing would be very helpful for my use case, to consolidate the serialization work by type and by input.
16 replies
KKysely
Created by significantotter on 11/15/2023 in #help
Are JSON Arrays not supported for paramterization in the postgres driver?
Thanks for the answer!
16 replies
KKysely
Created by significantotter on 11/15/2023 in #help
Are JSON Arrays not supported for paramterization in the postgres driver?
I see! I'll update my kanel types and build a helper, I think. Maybe I can use a utility type like Kanel does for ids to make sure that it gets processed correctly before use
16 replies
KKysely
Created by significantotter on 11/11/2023 in #help
Why is eb inferred as any in this update query?
When I do 
.where(eb=>/**/)
.where(eb=>/**/)
after that, it does infer eb as: 
(parameter) eb: ExpressionBuilder<{
    [x: string]: any;
    [x: number]: any;
    [x: symbol]: any;
}, "sheet_insert" | "initiative_actor">
(parameter) eb: ExpressionBuilder<{
    [x: string]: any;
    [x: number]: any;
    [x: symbol]: any;
}, "sheet_insert" | "initiative_actor">
5 replies
KKysely
Created by significantotter on 11/11/2023 in #help
Why is eb inferred as any in this update query?
Yup, it's for use in a migration, which I think are supposed to do that, right?
5 replies
KKysely
Created by significantotter on 11/4/2023 in #help
Any thoughts on how to move data migrations from knex to kysely?
I've also been wondering about ways to manage brownfield project migration directories. We have a huuuuge problem with this at my work. Small database changes for individual tickets each get their migration file. Then get dumped into a massive directory of impossible to organize code files. And this is the environment where we have to support many databases in the field with very different code versions
9 replies
KKysely
Created by significantotter on 11/4/2023 in #help
Any thoughts on how to move data migrations from knex to kysely?
It uses a common language instead of trying to find the numeric id of the old migration state and translate that onto the numeric id of the new state
9 replies
KKysely
Created by significantotter on 11/4/2023 in #help
Any thoughts on how to move data migrations from knex to kysely?
I've never written a migration library, so I don't know the challenges involved, but I wonder why migrations tend to be predicated on an id value instead of a time/datestamp. I was thinking about that after seeing the kysely suggestion of the ISO date as the id value in the file. I feel like a standardized time value, and then maybe a way to init a migrator onto a time value for that environment, would be really useful for solving these kinds of issues (especially when tools like the old knex migrator cli would auto-generate ids for you)
9 replies
KKysely
Created by significantotter on 11/4/2023 in #help
Any thoughts on how to move data migrations from knex to kysely?
Totally agree. It just leaves a bit of a sour taste in my mouth to have the best solution be "leave the legacy technology that we have a better implementation for in the app, unable to be improved, but unable to be upgraded or removed"
9 replies
KKysely
Created by significantotter on 11/4/2023 in #help
Any thoughts on how to move data migrations from knex to kysely?
Oof, I don't know how much I like that solution, as useful as it might be for maintaining a long-lived project. I might recommend that if I was migrating my company over, since we have an ungodly amount of migration files built up over 7 years, as well as many customers on varying versions, necessitating that we maintain the ability to migrate from an arbitrary point. Luckily my current project is a personal one, a discord bot that is in production, but only has 3 active databases, prod, staging, and dev. So I don't need to worry too much about database state in the wild. Which is why I was thinking those two options were best for me.
9 replies
KKysely
Created by significantotter on 11/3/2023 in #help
Recipe for generated types + JSON?
Ah! I see the import type comment now. I’ll play with that
10 replies
KKysely
Created by significantotter on 11/3/2023 in #help
Recipe for generated types + JSON?
I must be, sorry! Will do
10 replies
KKysely
Created by significantotter on 11/3/2023 in #help
Recipe for generated types + JSON?
I need to be able to later reference the type rather than supply it wholesale. The types are pretty large
10 replies
KKysely
Created by significantotter on 11/3/2023 in #help
Recipe for generated types + JSON?
That doesn’t exactly solve my issue. I don’t want a general custom type for JSONB. I want to assign each specific JSONB column the proper type for that object
10 replies