Search code examples
jsontypescriptpostgresqlormdrizzle

Drizzle Columns Schema JSON stores my JSON as text in PostgreSQL

I'm using Drizzle for a Typescript backend that serves a few API endpoints. My database is Postgresql and there's a JSON column.

export const transactions = pgTable("transactions", {
    id: serial("id").primaryKey(),
    my_json: json('my_json')
});

I if try to store {"hello":"world"} from a supabase table editor, this is what I'm getting:

enter image description here

If I try to insert my {"hello":"world"} using TS/Drizzle, this is what I'm getting:

enter image description here

Somehow I can't find a setting or a way to make drizzle store it as a real JSON object and not a string version of it.

Solution

  • Currently (June 2023) it's not possible due to a bug: https://github.com/drizzle-team/drizzle-orm/issues/724

    You'll need to execute a raw DML query instead: db.execute(sql`INSERT INTO table(foo_id, foo_json)...VALUES(123,${yourObject})`)

    For example this worked for me.

    const statement = sql`
        INSERT INTO wikidata_article (wikidata_id, category, grade, en_raw_length, en_url_title, labels, sitelinks)
        VALUES (${articleDetails.wikiDataId}, ${articleDetails.category}, ${articleDetails.grade}, ${articleDetails.enBytes}, ${articleDetails.enUrlTitle}, ${articleDetails.labels}, ${articleDetails.sitelinks})
        ON CONFLICT (wikidata_id) DO UPDATE
        SET 
            category = ${articleDetails.category},
            grade = ${articleDetails.grade},    
            en_raw_length = ${articleDetails.enBytes},
            en_url_title = ${articleDetails.enUrlTitle},
            labels = ${articleDetails.labels},
            sitelinks = ${articleDetails.sitelinks}
        `;
        await db.execute(statement);