TheMelonAssassin Posts - Answer Overflow

TheMelonAssassin

Explore posts from servers

•Created by TheMelonAssassin on 12/13/2024 in #help

Typing enums

First of all, I still really enjoy using Kysely, great library! I have the following pattern for enums. I create the role table as an "enum" table (which I got from another help post I was reading earlier) and add it as an foreign key to the teacher table.

export const up = async (db: Kysely<any>) => {
  await db.schema
    .createTable("role")
    .addColumn("role", "text", (c) => c.primaryKey().defaultTo(ROLES.LESGEVER))
    .execute();

  await db.schema
    .createTable("teacher")
    .addColumn("ID", "uuid", (c) => c.primaryKey())
    .addColumn("createdAt", "timestamptz", (c) =>
      c.notNull().defaultTo("now()")
    )
    .addColumn("updatedAt", "timestamptz", (c) =>
      c.notNull().defaultTo("now()")
    )
    .addColumn("email", "text", (c) => c.notNull().unique())
    // Other columns
    .addColumn("role", "text", (c) =>
      c.notNull().references("role.role").onDelete("cascade")
    )
    .execute();

  await sql`
    CREATE TRIGGER set_teacher_updated_at
    BEFORE UPDATE ON teacher
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();
  `.execute(db);
};

export const down = async (db: Kysely<any>) => {
   // Down function
};

export const up = async (db: Kysely<any>) => {
  await db.schema
    .createTable("role")
    .addColumn("role", "text", (c) => c.primaryKey().defaultTo(ROLES.LESGEVER))
    .execute();

  await db.schema
    .createTable("teacher")
    .addColumn("ID", "uuid", (c) => c.primaryKey())
    .addColumn("createdAt", "timestamptz", (c) =>
      c.notNull().defaultTo("now()")
    )
    .addColumn("updatedAt", "timestamptz", (c) =>
      c.notNull().defaultTo("now()")
    )
    .addColumn("email", "text", (c) => c.notNull().unique())
    // Other columns
    .addColumn("role", "text", (c) =>
      c.notNull().references("role.role").onDelete("cascade")
    )
    .execute();

  await sql`
    CREATE TRIGGER set_teacher_updated_at
    BEFORE UPDATE ON teacher
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();
  `.execute(db);
};

export const down = async (db: Kysely<any>) => {
   // Down function
};

I also have the ROLES enum to use across my back-end for various purposes

export const ROLES = {
  LESGEVER: "Lesgever",
  COORDINATOR: "Coördinator",
  BESTUUR: "Bestuur",
} as const;

export type Role = (typeof ROLES)[keyof typeof ROLES];

export const ROLES = {
  LESGEVER: "Lesgever",
  COORDINATOR: "Coördinator",
  BESTUUR: "Bestuur",
} as const;

export type Role = (typeof ROLES)[keyof typeof ROLES];

When I define the types for this table I do the following:

export interface GenderTable {
  gender: string;
}

export type Gender = Selectable<GenderTable>;
export type InsertableGender = Insertable<GenderTable>;
export type UpdatableGender = Updateable<GenderTable>;

export interface TeacherTable {
  ID: Generated<UUID>;
  createdAt: ColumnType<Date | string, never, never>;
  updatedAt: ColumnType<Date | string, never, never>;
  email: string;

  role: ColumnType<string, never, string>;
}

export type Teacher = Selectable<TeacherTable>;
export type InsertableTeacher = Insertable<TeacherTable>;
export type UpdateableTeacher = Updateable<TeacherTable>;

export interface GenderTable {
  gender: string;
}

export type Gender = Selectable<GenderTable>;
export type InsertableGender = Insertable<GenderTable>;
export type UpdatableGender = Updateable<GenderTable>;

export interface TeacherTable {
  ID: Generated<UUID>;
  createdAt: ColumnType<Date | string, never, never>;
  updatedAt: ColumnType<Date | string, never, never>;
  email: string;

  role: ColumnType<string, never, string>;
}

export type Teacher = Selectable<TeacherTable>;
export type InsertableTeacher = Insertable<TeacherTable>;
export type UpdateableTeacher = Updateable<TeacherTable>;

This works for typesafety of course, but I'd like for role to be only values of the ROLES enum. Does it make sense to just change it from string to Role or am I missing something

4 replies

DTDrizzle Team

•Created by TheMelonAssassin on 12/12/2024 in #help

Defining nested types with Drizzle Zod

What would be the correct way to create nested types with drizzle-zod here? I have an acccount table like this:

import { pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";
import { townhall } from "./townhall";

export const account = pgTable("account", {
  ID: uuid().defaultRandom().primaryKey().notNull(),
  createdAt: timestamp({
    withTimezone: true,
    mode: "string",
  })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp({
    withTimezone: true,
    mode: "string",
  })
    .defaultNow()
    .$onUpdate(() => new Date().toISOString())
    .notNull(),
  username: text().notNull(),
  townhallID: uuid()
    .references(() => townhall.ID)
    .notNull(),
});

import { pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";
import { townhall } from "./townhall";

export const account = pgTable("account", {
  ID: uuid().defaultRandom().primaryKey().notNull(),
  createdAt: timestamp({
    withTimezone: true,
    mode: "string",
  })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp({
    withTimezone: true,
    mode: "string",
  })
    .defaultNow()
    .$onUpdate(() => new Date().toISOString())
    .notNull(),
  username: text().notNull(),
  townhallID: uuid()
    .references(() => townhall.ID)
    .notNull(),
});

And a townhall table like this:

import { integer, pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";

export const townhall = pgTable("townhall", {
  ID: uuid().defaultRandom().primaryKey().notNull(),
  createdAt: timestamp({
    withTimezone: true,
    mode: "string",
  })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp({
    withTimezone: true,
    mode: "string",
  })
    .defaultNow()
    .$onUpdate(() => new Date().toISOString())
    .notNull(),
  level: integer().unique("townhall_level_unique").notNull(),
  color: text().notNull(),
});

import { integer, pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";

export const townhall = pgTable("townhall", {
  ID: uuid().defaultRandom().primaryKey().notNull(),
  createdAt: timestamp({
    withTimezone: true,
    mode: "string",
  })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp({
    withTimezone: true,
    mode: "string",
  })
    .defaultNow()
    .$onUpdate(() => new Date().toISOString())
    .notNull(),
  level: integer().unique("townhall_level_unique").notNull(),
  color: text().notNull(),
});

How can I make sure the type comes back like this? That's the type I receive from my query and I need it to define my tanstack-table columns

Account = {
  id: string;
  username: string;
  townhall: {
    id: string;
    level: number;
    color: string;
  };
};

Account = {
  id: string;
  username: string;
  townhall: {
    id: string;
    level: number;
    color: string;
  };
};

I have the omit part down:

import { createSelectSchema } from "drizzle-zod";
import { account } from "@/db/schema/account";

const selectAccountSchema = createSelectSchema(account).omit({
  createdAt: true,
  updatedAt: true,
});

export type Account = typeof selectAccountSchema._type;

import { createSelectSchema } from "drizzle-zod";
import { account } from "@/db/schema/account";

const selectAccountSchema = createSelectSchema(account).omit({
  createdAt: true,
  updatedAt: true,
});

export type Account = typeof selectAccountSchema._type;

1 replies

KKysely

•Created by TheMelonAssassin on 11/1/2024 in #help

Making Updateable<X> fields required

Excuse me if this has already been posted, but I recently turned on strict mode after completing my migration to TS and I'm struggling to make certain fields required when updating an entity. When I hover over UpdateableActivity it shows:

type UpdateableActivity = {
    ID?: number | undefined;
    name?: string | undefined;
    year?: number | undefined;
    isCurrent?: boolean | undefined;
    venueID?: number | undefined;
    moduleID?: number | undefined;
}

type UpdateableActivity = {
    ID?: number | undefined;
    name?: string | undefined;
    year?: number | undefined;
    isCurrent?: boolean | undefined;
    venueID?: number | undefined;
    moduleID?: number | undefined;
}

but hovering over Updateable shows:

type UpdateablePerson = Updateable<PersonTable>
// {
//   id?: number,
//   first_name?: string
// }

type UpdateablePerson = Updateable<PersonTable>
// {
//   id?: number,
//   first_name?: string
// }

if possible I'd like it to be something like:

type UpdateableActivity = {
    ID?: number;
    name: string;
    year: number;
    isCurrent: boolean;
    venueID: number;
    moduleID: number;
}

type UpdateableActivity = {
    ID?: number;
    name: string;
    year: number;
    isCurrent: boolean;
    venueID: number;
    moduleID: number;
}

https://kyse.link/MA3jQ is my playground link if that helps!

6 replies

KKysely

•Created by TheMelonAssassin on 10/14/2024 in #help

Issue with how timestamptz is returned depending on main or subquery

I'm running into the following issue where my date is returned in two different ways. Date is a timestamptz type column When I grab the lesson as followed:

export const getLessonByID = async (lessonID: number) => {
  const lesson = await db
    .selectFrom("lesson")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      Category.byID(eb.ref("lesson.categoryID")).as("category"),
    .where("lesson.ID", "=", lessonID)
    .executeTakeFirst();

  return lesson;
};

export const getLessonByID = async (lessonID: number) => {
  const lesson = await db
    .selectFrom("lesson")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      Category.byID(eb.ref("lesson.categoryID")).as("category"),
    .where("lesson.ID", "=", lessonID)
    .executeTakeFirst();

  return lesson;
};

I receive "date": "2024-09-13T22:00:00.000Z" When I use a subquery like this:

export const Lesson = {
  byID: (lessonID: Expression<number>) => {
    const eb = expressionBuilder<Database, never>();

    return jsonObjectFrom(
      eb
        .selectFrom("lesson")
        .select(["lesson.ID", "lesson.date", "lesson.index"])
        .whereRef("lesson.ID", "=", lessonID)
    );
  },
}


export const getGroupByLessonID = async (groupID: number, lessonID: number) => {
  const group = await db
    .selectFrom("group")
    .select((eb) => [
      "group.ID",
      "group.name",
      "group.rank",
      "group.minTeachers",
      "group.maxTeachers",
      "group.hours",
      "group.timeframe",
      "group.paymentType",
      Lesson.byID(eb.val(lessonID)).as("lesson"),
    ])
    .where("group.ID", "=", groupID)
    .executeTakeFirst();

  return group;
};

export const Lesson = {
  byID: (lessonID: Expression<number>) => {
    const eb = expressionBuilder<Database, never>();

    return jsonObjectFrom(
      eb
        .selectFrom("lesson")
        .select(["lesson.ID", "lesson.date", "lesson.index"])
        .whereRef("lesson.ID", "=", lessonID)
    );
  },
}


export const getGroupByLessonID = async (groupID: number, lessonID: number) => {
  const group = await db
    .selectFrom("group")
    .select((eb) => [
      "group.ID",
      "group.name",
      "group.rank",
      "group.minTeachers",
      "group.maxTeachers",
      "group.hours",
      "group.timeframe",
      "group.paymentType",
      Lesson.byID(eb.val(lessonID)).as("lesson"),
    ])
    .where("group.ID", "=", groupID)
    .executeTakeFirst();

  return group;
};

I receive "date": "2024-09-14T00:00:00+02:00"

15 replies

KKysely

•Created by TheMelonAssassin on 9/13/2024 in #help

Creating helper functions

I've gotten to the point in my application where a lot of the objects I'm building in my queries are returning in other queries. In the following example I'd like to extract the module (with color) into something I can use in other queries. I've been trying to find the correct (and working) way to this

export const getSchematicByLessonID = async (lessonID: number) => {
  const schematic = await db
    .with("get_groups", () => findGroupsByLessonID(lessonID))
    .with("get_attendances", () => findAttendancesByLessonID(lessonID))
    .selectFrom("lesson")
    .innerJoin("activity", "lesson.activityID", "activity.ID")
    .innerJoin("module", "activity.moduleID", "module.ID")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      jsonObjectFrom(
        eb
          .selectFrom("module")
          .select([
            "module.ID",
            "module.name",
            "module.abbreviation",
            jsonObjectFrom(
              eb
                .selectFrom("color")
                .select([
                  "color.ID",
                  "color.name",
                  "color.hex",
                  "color.transparency",
                  "color.textColor",
                ])
                .whereRef("color.ID", "=", "module.colorID")
            ).as("color"),
          ])
          .whereRef("module.ID", "=", "activity.moduleID")
      ).as("module"),
       /* Other data */
    ])
    .where("lesson.ID", "=", lessonID)
    .executeTakeFirst();

  return schematic;
};

export const getSchematicByLessonID = async (lessonID: number) => {
  const schematic = await db
    .with("get_groups", () => findGroupsByLessonID(lessonID))
    .with("get_attendances", () => findAttendancesByLessonID(lessonID))
    .selectFrom("lesson")
    .innerJoin("activity", "lesson.activityID", "activity.ID")
    .innerJoin("module", "activity.moduleID", "module.ID")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      jsonObjectFrom(
        eb
          .selectFrom("module")
          .select([
            "module.ID",
            "module.name",
            "module.abbreviation",
            jsonObjectFrom(
              eb
                .selectFrom("color")
                .select([
                  "color.ID",
                  "color.name",
                  "color.hex",
                  "color.transparency",
                  "color.textColor",
                ])
                .whereRef("color.ID", "=", "module.colorID")
            ).as("color"),
          ])
          .whereRef("module.ID", "=", "activity.moduleID")
      ).as("module"),
       /* Other data */
    ])
    .where("lesson.ID", "=", lessonID)
    .executeTakeFirst();

  return schematic;
};

14 replies

KKysely

•Created by TheMelonAssassin on 9/10/2024 in #help

Making CTE's reusable

I wanted to make certain cte's in my project reusable and came up with the following solution. Is there a "kysely" way to do this?

export const getPiggyBank = async (teacherID: number) => {
  const lessons = await db
    .with("get_attendances", () => Attendance.byTeacherID(teacherID))
    .selectFrom("lesson")
    .innerJoin("get_attendances", "lesson.ID", "get_attendances.lessonID")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      jsonArrayFrom(
        eb
          .selectFrom("get_attendances as a")
          .select([
            "a.ID",
            "a.function",
            "a.hours",
            "a.paymentType",
            "a.hourlyWage",
          ])
          .whereRef("a.lessonID", "=", "lesson.ID")
      ).as("attendances"),
    ])
    .where("lesson.isActive", "=", true)
    .groupBy(["lesson.ID", "lesson.date", "lesson.index"])
    .execute();

  return lessons;
};

// CTE's

export const Attendance = {
  byTeacherID: (teacherID: number) =>
    db
      .selectFrom("attendance")
      .innerJoin("function", "attendance.functionID", "function.ID")
      .innerJoin("group", "attendance.groupID", "group.ID")
      .select([
        "attendance.ID",
        "attendance.lessonID",
        "group.hours",
        "group.paymentType",
        "function.name as function",
        "function.hourlyWage",
      ])
      .where("attendance.teacherID", "=", teacherID)
      .where("attendance.attendanceType", "=", ATTENDANCETYPES.PRESENT)
      .where("attendance.isActive", "=", true),
};

export const getPiggyBank = async (teacherID: number) => {
  const lessons = await db
    .with("get_attendances", () => Attendance.byTeacherID(teacherID))
    .selectFrom("lesson")
    .innerJoin("get_attendances", "lesson.ID", "get_attendances.lessonID")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      jsonArrayFrom(
        eb
          .selectFrom("get_attendances as a")
          .select([
            "a.ID",
            "a.function",
            "a.hours",
            "a.paymentType",
            "a.hourlyWage",
          ])
          .whereRef("a.lessonID", "=", "lesson.ID")
      ).as("attendances"),
    ])
    .where("lesson.isActive", "=", true)
    .groupBy(["lesson.ID", "lesson.date", "lesson.index"])
    .execute();

  return lessons;
};

// CTE's

export const Attendance = {
  byTeacherID: (teacherID: number) =>
    db
      .selectFrom("attendance")
      .innerJoin("function", "attendance.functionID", "function.ID")
      .innerJoin("group", "attendance.groupID", "group.ID")
      .select([
        "attendance.ID",
        "attendance.lessonID",
        "group.hours",
        "group.paymentType",
        "function.name as function",
        "function.hourlyWage",
      ])
      .where("attendance.teacherID", "=", teacherID)
      .where("attendance.attendanceType", "=", ATTENDANCETYPES.PRESENT)
      .where("attendance.isActive", "=", true),
};

4 replies

KKysely

•Created by TheMelonAssassin on 8/14/2024 in #help

Issue with select

How would I handle doing what I'm doing in the raw part during the select?

 const result = await db
    .with("townhallDistribution", (qb) =>
      qb
        .selectFrom("account")
        .innerJoin("townhall", "account.townhallID", "townhall.ID")
        .select(["townhall.level", db.fn.count("account.ID").as("amount")])
        .where("account.clanID", "=", id)
        .groupBy("townhall.level")
    )
    .selectFrom("clan")
    .select([
      "clan.ID",
      "clan.name",
      "clan.level",
      "clan.location",
      "clan.language",
      "clan.cwl",
      "clan.longestWinStreak",
      sql`
        COALESCE(
          (
            SELECT json_agg(
              json_build_object(
                'level', townhall_level,
                'amount', amount
              )
            )
            FROM townhallDistribution
          ),
          '[]'
        ) AS townhallDistribution
      `,
    ])
    .executeTakeFirst();

 const result = await db
    .with("townhallDistribution", (qb) =>
      qb
        .selectFrom("account")
        .innerJoin("townhall", "account.townhallID", "townhall.ID")
        .select(["townhall.level", db.fn.count("account.ID").as("amount")])
        .where("account.clanID", "=", id)
        .groupBy("townhall.level")
    )
    .selectFrom("clan")
    .select([
      "clan.ID",
      "clan.name",
      "clan.level",
      "clan.location",
      "clan.language",
      "clan.cwl",
      "clan.longestWinStreak",
      sql`
        COALESCE(
          (
            SELECT json_agg(
              json_build_object(
                'level', townhall_level,
                'amount', amount
              )
            )
            FROM townhallDistribution
          ),
          '[]'
        ) AS townhallDistribution
      `,
    ])
    .executeTakeFirst();

I get the following error: No overload matches this call. Overload 1 of 3, '(selections: readonly SelectExpression<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">[]): SelectQueryBuilder<Database & { ...; }, "clan", { ...; }>', gave the following error. Type 'RawBuilder<unknown>' is not assignable to type 'SelectExpression<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">'. Type 'RawBuilder<unknown>' is missing the following properties from type 'DynamicReferenceBuilder<any>': #private, dynamicReference, refType Overload 2 of 3, '(callback: SelectCallback<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">): SelectQueryBuilder<Database & { ...; }, "clan", { ...; }>', gave the following error. Argument of type '(string | RawBuilder<unknown>)[]' is not assignable to parameter of type 'SelectCallback<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">'. Type '(string | RawBuilder<unknown>)[]' provides no match for the signature '(eb: ExpressionBuilder<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">): readonly SelectExpression<Database & { ...; }, "clan">[]'. Overload 3 of 3, '(selection: SelectExpression<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">): SelectQueryBuilder<Database & { ...; }, "clan", { ...; }>', gave the following error. Argument of type '(string | RawBuilder<unknown>)[]' is not assignable to parameter of type 'SelectExpression<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">'.ts(2769)

14 replies

KKysely

•Created by TheMelonAssassin on 8/13/2024 in #help

Issue with migrating from knex

I've recently learned about Kysely and how it is very compatible with TS compared to Knex. I'm also very new to coding in general and have been following the structure I was taught at school earlier this year but I'm struggling right now to migrate the file that initializes data and shuts it down. If anyone would be so kind to help me figure out how to move this from Knex to Kysely!

5 replies

Gaming

Programming