Kysely•3y ago

JSON in columns (postgresql)

Hey

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

language

language

language column and want to set a default value for the

locale

locale

locale

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>
}

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

migrations/0-initials.ts

migrations/0-initials.ts

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();

}

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

JSON in columns (postgresql)

Similar Threads

Similar Threads

Similar Threads