Varna Posts - Answer Overflow

Varna

Explore posts from servers

NNuxt CDCloudflare Developers DTDrizzle Team

•Created by Varna on 7/23/2024 in #help

Counting many-to-many items

Hello, any suggestions how should I query for item and count the numbers of times it is referenced via many-to-many table?

const items = await db.select({
  ...getTableColumns(A),
  countB: // count of B items on A
  countC: // count of C items on A
})
  .from(A)
  .groupBy(A.id)

const items = await db.select({
  ...getTableColumns(A),
  countB: // count of B items on A
  countC: // count of C items on A
})
  .from(A)
  .groupBy(A.id)

Given these tables:

export const A = mysqlTable(
  "A", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const B = mysqlTable(
  "B", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const C = mysqlTable(
  "C", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const AB = mysqlTable(
  "AB", {
    A: int("A").notNull().references(() => A.id),
    B: int("B").notNull().references(() => B.id),
  }
)

export const AC = mysqlTable(
  "AC", {
    A: int("A").notNull().references(() => A.id),
    C: int("C").notNull().references(() => C.id),
  }
)

export const A = mysqlTable(
  "A", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const B = mysqlTable(
  "B", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const C = mysqlTable(
  "C", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const AB = mysqlTable(
  "AB", {
    A: int("A").notNull().references(() => A.id),
    B: int("B").notNull().references(() => B.id),
  }
)

export const AC = mysqlTable(
  "AC", {
    A: int("A").notNull().references(() => A.id),
    C: int("C").notNull().references(() => C.id),
  }
)

4 replies

DTDrizzle Team

•Created by Varna on 4/16/2024 in #help

text mode json for sqlite Dq doesn't parse by default

I get SyntaxError: "undefined" is not valid JSON for json_string: text("json_string", { mode: "json" }) field. When I use methods like:

db.query.Issue.findMany();

db.query.Issue.findMany();

db.select().from(Issue)

db.select().from(Issue)

while it does work and return string, If I manually select it by:

db.select({
  json_string: Issue.json_string
}).from(Issue)

db.select({
  json_string: Issue.json_string
}).from(Issue)

I'm using D1 db.

3 replies

DTDrizzle Team

•Created by Varna on 3/6/2024 in #help

How can I extend drizzle tables?

I noticed that I write a lot of boilerplate code. When I had to update one of such types, I refactored them a bit, into reusable thing:

// /__prefabs.ts
import { sql } from "drizzle-orm"
import { datetime, varchar, int } from "drizzle-orm/mysql-core"
import { User } from "./NextAuth"

export const id = int("id").primaryKey().autoincrement()
export const name = varchar("name", { length: 191 }).notNull()
export const slug = varchar("slug", { length: 191 }).notNull()

export const Updated = {
  updatedAt: datetime("updatedAt", { mode: "date", fsp: 3 })
    .default(sql`CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)`)
    .notNull(),
  updatedById: varchar("updatedById", { length: 191 }).references(
    () => User.id,
    { onDelete: "set null", onUpdate: "cascade" }
  )
}

// /__prefabs.ts
import { sql } from "drizzle-orm"
import { datetime, varchar, int } from "drizzle-orm/mysql-core"
import { User } from "./NextAuth"

export const id = int("id").primaryKey().autoincrement()
export const name = varchar("name", { length: 191 }).notNull()
export const slug = varchar("slug", { length: 191 }).notNull()

export const Updated = {
  updatedAt: datetime("updatedAt", { mode: "date", fsp: 3 })
    .default(sql`CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)`)
    .notNull(),
  updatedById: varchar("updatedById", { length: 191 }).references(
    () => User.id,
    { onDelete: "set null", onUpdate: "cascade" }
  )
}

// Category.ts
import { mysqlTable, unique } from "drizzle-orm/mysql-core"
import { id, name, slug, Updated } from "./__prefabs"

export const Category = mysqlTable(
  "Category",
  {
    id,
    name,
    slug,
    ...Updated
  },
  (table) => {
    return {
      Category_name_key: unique("Category_name_key").on(table.name),
      Category_slug_key: unique("Category_slug_key").on(table.slug)
    }
  }
)

// Category.ts
import { mysqlTable, unique } from "drizzle-orm/mysql-core"
import { id, name, slug, Updated } from "./__prefabs"

export const Category = mysqlTable(
  "Category",
  {
    id,
    name,
    slug,
    ...Updated
  },
  (table) => {
    return {
      Category_name_key: unique("Category_name_key").on(table.name),
      Category_slug_key: unique("Category_slug_key").on(table.slug)
    }
  }
)

This worked out pretty well for that time. But now, I keep writing tons of services, and I want to make some reusable functions, but I struggle with TS trying to do that. i.e. I want to make a service getItems:

// services/getItems.ts
import { db } from "@/app/(db)"
import { type Table } from "drizzle-orm"

type ItemTable = {
  id: number,
  name: string,
  slug: string
}

export async function getItems<T extends ItemTable>(table: Table<T>) {
  return db
    .select({
      id: table.id,
      name: table.name,
      slug: table.slug
    })
    .from(table)
}

// services/getItems.ts
import { db } from "@/app/(db)"
import { type Table } from "drizzle-orm"

type ItemTable = {
  id: number,
  name: string,
  slug: string
}

export async function getItems<T extends ItemTable>(table: Table<T>) {
  return db
    .select({
      id: table.id,
      name: table.name,
      slug: table.slug
    })
    .from(table)
}

But types on this thing are totally wrong, and I'm not sure where should I start to approach this problem. Another example, I have some subqueries like these:

const getProblemId = db
  .select({
    id: Problem.id
  })
  .from(Problem)
  .where(eq(Problem.slug, sql.placeholder("problemSlug")))
  .limit(1)

const getProblemId = db
  .select({
    id: Problem.id
  })
  .from(Problem)
  .where(eq(Problem.slug, sql.placeholder("problemSlug")))
  .limit(1)

It would be nice if I could refactor it into getID, that takes Table as prop:

const getID = (Table) => db
  .select({
    id: Table.id
  })
  .from(Table)
  .where(eq(Table.slug, sql.placeholder("slug")))
  .limit(1)

const getID = (Table) => db
  .select({
    id: Table.id
  })
  .from(Table)
  .where(eq(Table.slug, sql.placeholder("slug")))
  .limit(1)

But I just don't get how to define the Table type in TS.

1 replies

Gaming

Programming