mcgrealife Posts - Answer Overflow

mcgrealife

Explore posts from servers

•Created by mcgrealife on 6/15/2023 in #help

Generic update component using table schema – how to type the table argument?

SOLVED IN BOTTOM COMMENT I'm attempting a generic update checkbox component where I can pass a type of MySQL (PlanetScale) table, and an associated keyName, and use those params in the db.update() query. Can anyone guide me on how to type the table argument?

import * as schema from '@/db/schema'
import { AnyMySqlTable } from 'drizzle-orm/mysql-core' // just found this, testing now
import { eq } from 'drizzle-orm'

export function CheckboxUpdate<T extends AnyMySqlTable>({ ...args }: {
  model: T
  keyName: keyof T & string
  id: number
}) {
  return (
    <input
      type='checkbox'
      onChange={async (e) => {
        'use server' // pseudo code
        await db
          .update(args.model)
          .set({ [args.keyName]: e.target.checked })
          .where(eq(args.model.id, args.id))
      }}
    />
  )
}

await CheckboxUpdate<typeof users>({
  model: users,
  keyName: 'isSubscriber',
  id: 1,
})

import * as schema from '@/db/schema'
import { AnyMySqlTable } from 'drizzle-orm/mysql-core' // just found this, testing now
import { eq } from 'drizzle-orm'

export function CheckboxUpdate<T extends AnyMySqlTable>({ ...args }: {
  model: T
  keyName: keyof T & string
  id: number
}) {
  return (
    <input
      type='checkbox'
      onChange={async (e) => {
        'use server' // pseudo code
        await db
          .update(args.model)
          .set({ [args.keyName]: e.target.checked })
          .where(eq(args.model.id, args.id))
      }}
    />
  )
}

await CheckboxUpdate<typeof users>({
  model: users,
  keyName: 'isSubscriber',
  id: 1,
})

4 replies

DTDrizzle Team

•Created by mcgrealife on 6/1/2023 in #help

MySQL column type: "Generated"?

Does drizzle support defining generated columns? https://planetscale.com/courses/mysql-for-developers/schema/generated-columns Something like a columnType sql If not, is it dangerous to send MySQL a custom DDL statement to Add a generated column? The schema wouldn't not be aware of it, but maybe I could still reference the generated column in sql commands. Thank you

3 replies

DTDrizzle Team

•Created by mcgrealife on 5/28/2023 in #help

`$with` example in docs error "unsupported: with expression in select statement"

I have a user with id of 42, just like the docs https://orm.drizzle.team/docs/crud#with-clause but executing the same query throws an Error: unsupported: with expression in select statement

const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)))
const result = await db.with(sq).select().from(sq)

const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)))
const result = await db.with(sq).select().from(sq)

The $with clause looks superb.

6 replies

DTDrizzle Team

•Created by mcgrealife on 5/27/2023 in #help

Relation query `extras` needs access to `with`

I have a mysql table with two relations to another table. (one is for source values, one is for optional admin edited values.) The app logic is "if admin value exists, return admin value, else return source value". (But do not return both, since it would be a large network payload) The relation query easily includes the 2 related columns via with key. But, the extras key cannot access the related columns. Is there another way to achieve this "computed" extras column type? (Maybe this is a good use for "Views" feature) e.g.:

db.query.table.findMany({
  with: {
    relation1: true,
    relation2: true, // optional
  },
  extras: {
    relationComputed: table.relation2 ?? table.relation1,
  },
})

db.query.table.findMany({
  with: {
    relation1: true,
    relation2: true, // optional
  },
  extras: {
    relationComputed: table.relation2 ?? table.relation1,
  },
})

2 replies

DTDrizzle Team

•Created by mcgrealife on 5/27/2023 in #help

Optional filter param, coalesce to true?

Is there a way to achieve this coalescing technique, but in valid drizzle sql?

const filterByBool: boolean | undefined | null = undefined

await db
  .select()
  .from(test)
  .where(test.bool == filterByBool ?? true)

const filterByBool: boolean | undefined | null = undefined

await db
  .select()
  .from(test)
  .where(test.bool == filterByBool ?? true)

8 replies

DTDrizzle Team

•Created by mcgrealife on 5/26/2023 in #help

Relational query, Planetscale throws: `ResourceExhausted desc = Out of sort memory`

SOLUTION: the problem was that I had a json column storing a massive value on each row.

code = ResourceExhausted desc = Out of sort memory, consider increasing server sort buffer size (errno 1038) (sqlstate HY001)

Possible reasons? - bloated table: 76 columns, half of type json() or text() – but only 228 rows – total db size only 1.61MB - drizzle relational query over-selecting? Drizzle relational query:

await db.query.properties.findFirst({
  columns: {},
  with: {
    neighborhood: true, // 2 columns
  },
})

await db.query.properties.findFirst({
  columns: {},
  with: {
    neighborhood: true, // 2 columns
  },
})

The logged sql statement selects all 78 columns. Shouldn't it only need need ~two columns for relation lookup?

select cast(`neighborhood` as json) from (select `properties`.`id`, ...**75 other properties columns** ),
...

select cast(`neighborhood` as json) from (select `properties`.`id`, ...**75 other properties columns** ),
...

9 replies

Gaming

Programming