Effect CommunityEC
Effect Community•16mo ago•
5 replies
canastro

Handling JSONB fields in SQL queries with TypeScript can be tricky due to type constraints. In yo...

I'm trying to migrate an insert from postgres to @effect/sql.

This is the original code:

INSERT INTO publications ${sql(
    publication,
    'id',
    'ownerId',
    'avatar',
    'title',
    'slug',
    'description',
    'socials',
    'settings',
)}
ON CONFLICT (id) DO UPDATE
SET ${sql(publication, 'avatar', 'title', 'description', 'socials', 'settings')}


So far I changed to this

const InsertPublicationSchema = Publication.pipe(
    S.pick(
        "id",
        "ownerId",
        "avatar",
        "title",
        "slug",
        "description",
        "socials", // JSONB column
        "settings", // JSONB column
    ),
);

const Save = SqlSchema.void({
    Request: InsertPublicationSchema,
    execute: (publication) => sql`
        INSERT INTO publications
            ${sql.insert(publication)}
        ON CONFLICT (id) DO UPDATE
        SET
            avatar = excluded.avatar,
            title = excluded.title,
            description = excluded.description,
            socials = excluded.socials,
            settings = excluded.settings
    `,
});


But seems like sql.insert doesnt like the fact that "socials" and "settings" are complex objects, that are to be inserted in a JSONB field.

I get a TS error that my complex columns socials and settings are not of Primitive | Fragment | undefined' .

How should one workaround this?
Was this page helpful?