Kysely•12mo ago

JSON in columns (postgresql)

Hey <a:YA_Wave:743393941369651201> I am completely new to using SQL databases and have a (hopefully) simple question. I want to store JSON inside a column and want to make certain keys not null and/or set a default value, how would I do that? In this case, I have a language column and want to set a default value for the locale key This is my UserTable type:

export interface UserTable {
    _id: Generated<Buffer>
    userId: string;

    language: {
        locale: string;
        // other things
    };

    created_at: ColumnType<Date, string, never>
}

export interface UserTable {
    _id: Generated<Buffer>
    userId: string;

    language: {
        locale: string;
        // other things
    };

    created_at: ColumnType<Date, string, never>
}

This is the current state of my migrations/0-initials.ts

import { ColumnDefinitionBuilder, Kysely, sql } from "kysely";

export async function up(db: Kysely<any>): Promise<void> {

    await db.schema
        .createTable("users")
        .addColumn("_id", sql`binary(16)`, (col: ColumnDefinitionBuilder) => col.primaryKey().defaultTo(sql`(uuid_to_bin(uuid()))`))
        .addColumn("userId", "varchar(40)", (col) => col.notNull().unique())

        // how would I add a default value to 'language.locale' ?
        .addColumn("language", "json", (col) => col)
        
        .execute();

}

import { ColumnDefinitionBuilder, Kysely, sql } from "kysely";

export async function up(db: Kysely<any>): Promise<void> {

    await db.schema
        .createTable("users")
        .addColumn("_id", sql`binary(16)`, (col: ColumnDefinitionBuilder) => col.primaryKey().defaultTo(sql`(uuid_to_bin(uuid()))`))
        .addColumn("userId", "varchar(40)", (col) => col.notNull().unique())

        // how would I add a default value to 'language.locale' ?
        .addColumn("language", "json", (col) => col)
        
        .execute();

}

(stripped things away that are not needed for this post) Thanks love

6 Replies

koskimas•12mo ago

Hey! You need to JSON.stringify the value https://kyse.link/?p=s&i=C7P9iTOVEu0XonyYB7BK

Blåhaj•12mo ago

Ah okey thank you! And when I get the data out of the db, I would have to JSON.parse() all jsob columns? Is there a way to directly get it as json?

koskimas•12mo ago

~~postgres~~ The pg driver does usually parse json for you, but there are some weird corner cases where it doesn't. But usually there's no need to parse. You might need to stringify the inputs in some insert and update queries. If I recall correctly, postgres stringifies objects but not arrays. You can use ColumnType to allow strings in inputs, but not in output: https://kyse.link/?p=s&i=hRzxktOvhghrlyUMfpWs The reason you need to stringify in defaultTo is because there are no parameters in DDL queries. Everything's always inline

Blåhaj•12mo ago

thank you alot SilvLove

Will play around with it in a few hours and come back if I have issues!

Blåhaj•12mo ago

It says that type binary does not exist..? my code:

.addColumn("userId", sql`binary(16)`, (col) => col.notNull().unique())

.addColumn("userId", sql`binary(16)`, (col) => col.notNull().unique())

data-type-node.d.ts:

export declare type ColumnDataType = ... | 'binary' | `binary(${number})` | ...

export declare type ColumnDataType = ... | 'binary' | `binary(${number})` | ...

koskimas•12mo ago

I think binary is a MySQL data type Postgres has at least bytea

Gaming

Programming

JSON in columns (postgresql)