mcgrealife Comments - Answer Overflow

Topics

mcgrealife

Explore posts from servers

TTCTheo's Typesafe Cult DTDrizzle Team

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

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

SOLVED: by inspecting the query builder methods, and typing the args to match This has the unexpected benefit of moving the complexity (and flexibility!) to the caller. For example, instead of defining only one keyName, the function now accepts "values", and can update any number of values!

import { SQL } from 'drizzle-orm'
import { AnyMySqlTable, MySqlUpdateSetSource } from 'drizzle-orm/mysql-core'

// function declaration
async function CheckboxUpdate<TTable extends AnyMySqlTable>({
  ...args
}: {
  table: TTable
  values: MySqlUpdateSetSource<TTable>
  where: SQL
}) {
  await db.update(args.table).set(args.values).where(args.where)
}

// implementation caller
await CheckboxUpdate<typeof users>({
  table: users,
  values: {
    isSubscriber: true,
  },
  where: eq(users.id, 1),
})

import { SQL } from 'drizzle-orm'
import { AnyMySqlTable, MySqlUpdateSetSource } from 'drizzle-orm/mysql-core'

// function declaration
async function CheckboxUpdate<TTable extends AnyMySqlTable>({
  ...args
}: {
  table: TTable
  values: MySqlUpdateSetSource<TTable>
  where: SQL
}) {
  await db.update(args.table).set(args.values).where(args.where)
}

// implementation caller
await CheckboxUpdate<typeof users>({
  table: users,
  values: {
    isSubscriber: true,
  },
  where: eq(users.id, 1),
})

Also thanks to @ngregrichardson https://discord.com/channels/1043890932593987624/1105287645363441704/1105287645363441704

4 replies

•Created by Noahh on 5/9/2023 in #help

Typing columns based on Table

@ngregrichardson nice! do you have ane example using the paginatedQuery? I am having trouble passing a value that satisfies TTable type.

4 replies

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

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

If I change .update(args.model) to .update(schema.users) the .set() method does not error?

export async function CheckboxUpdate<
  T extends typeof schema.users | typeof schema.properties 
>({ ...args }: { model: T; keyName: keyof InferModel<T>; id: number }) {
  await db
    .update(schema.users) // changed from args.model to hardcode model
    .set({ [args.keyName]: true }) // no error, but loses the generic model
    .where(eq(args.model.id, args.id))
}

export async function CheckboxUpdate<
  T extends typeof schema.users | typeof schema.properties 
>({ ...args }: { model: T; keyName: keyof InferModel<T>; id: number }) {
  await db
    .update(schema.users) // changed from args.model to hardcode model
    .set({ [args.keyName]: true }) // no error, but loses the generic model
    .where(eq(args.model.id, args.id))
}

4 replies

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

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

AnyMySqlTable didn't work as expected Removing the checkbox for simplicity. I'm getting a little closer like this:

export async function CheckboxUpdate<
  T extends typeof schema.users | typeof schema.properties // <-- new
>({ ...args }: { model: T; keyName: keyof T; id: number }) {
  await db
    .update(args.model)
    .set({ [args.keyName]: true }) // errors here
    .where(eq(args.model.id, args.id))
}

export async function CheckboxUpdate<
  T extends typeof schema.users | typeof schema.properties // <-- new
>({ ...args }: { model: T; keyName: keyof T; id: number }) {
  await db
    .update(args.model)
    .set({ [args.keyName]: true }) // errors here
    .where(eq(args.model.id, args.id))
}

Error:

Argument of type '{ [x: string]: any; }' is not assignable to parameter of type 'Simplify<{ [Key in keyof T["_"]["columns"]]?: SQL<unknown> | (T["_"]["columns"][Key]["_"]["notNull"] extends true ? T["_"]["columns"][Key]["_"]["data"] : T["_"]["columns"][Key]["_"]["data"] | null) | undefined; }, {}>'.ts(2345)
any

Argument of type '{ [x: string]: any; }' is not assignable to parameter of type 'Simplify<{ [Key in keyof T["_"]["columns"]]?: SQL<unknown> | (T["_"]["columns"][Key]["_"]["notNull"] extends true ? T["_"]["columns"][Key]["_"]["data"] : T["_"]["columns"][Key]["_"]["data"] | null) | undefined; }, {}>'.ts(2345)
any

4 replies

•Created by Shubham-Sinha on 6/3/2023 in #help

How to type results that includes relations ?

if you invoke findMany() inside a function, you can infer the ReturnType of the function https://discord.com/channels/1043890932593987624/1112458428070236229/1112519441561956352

3 replies

•Created by BREAD on 5/31/2023 in #help

Performance questions

@BREAD have you considered edge runtimes? In my case, the cold start time of lambdas node runtime is where most of the overhead occurs. In edge runtimes though, the cold start is milliseconds. From there, Most multiple round trips to the database are near-negligible difference on total duration. As long as the edge runtime region is limited to be near the PlanetScale MySql server (both in US-east in my case)

40 replies

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

How to get InferModel to work with Relation model?

@foreach I'm not sure if there is a specific helper to InferModel with types from relations (that looks like a clean syntax you propose though ^), but in case you are attempting to infer the type of a relation query, it's possible to wrap the query in a function and Infer the ReturnType of that https://discord.com/channels/1043890932593987624/1112458428070236229/1112519441561956352

2 replies

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

Infer type for relational query

Ah I see. I answered a different question. Confirmed that my relation queries definitely infer their own return types (without manual type annotation)! I answered from the context of nextjs api files. Where the export type keyword can only be used in the outer scope. So I wrap the db query in a function defined in the outer scope, invoke that function from the inner scope, but export it's return type in the outer scope. Thanks Cayter

12 replies

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

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

Ah yes, I see now that error is from PlanetScale, not drizzle. I should have searched first https://github.com/planetscale/discussion/discussions/270 Thanks to both 🙌

6 replies

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

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

I am using mysql via planetscale (sorry i'll mention next time) Note: the nearly identical subquery (without the $with) in the docs right below the $with section does work.

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

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

https://orm.drizzle.team/docs/crud#select-from-subquery

6 replies

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

Infer type for relational query

Ah yes, I should have mentioned this too! Your Awaited implementation is actually cleaner than mine!, but same idea to unwrap the promise, yes! I have been using NonNullable anytime I use the type – it is much better to use it directly in the custom type definition! Thanks for the tip @Dbeg 👍

12 replies

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

Infer type for relational query

@Dbeg I have achieved this by moving the query inside of a function, then using typescript's ReturnType helper.

const getUserWithPostsAndComments = async () => {
  const connection = connect({...})
  const db = drizzle(connection, { schema })

return db.query.users.findFirst({...})
}

export type CustomRelationType = ReturnType<typeof getUserWithPostsAndComments>

const getUserWithPostsAndComments = async () => {
  const connection = connect({...})
  const db = drizzle(connection, { schema })

return db.query.users.findFirst({...})
}

export type CustomRelationType = ReturnType<typeof getUserWithPostsAndComments>

I am using this pattern in nextjs api routes

12 replies

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

Relation query `extras` needs access to `with`

Maybe this is what the docs are mentioning:

As of now aggregations are not supported in extras, please use core queries for that

https://orm.drizzle.team/docs/rqb#include-custom-fields which is no problem, because I am discovering the sql operator really is magic! 👍

2 replies

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

Optional filter param, coalesce to true?

Thanks to both! Being able to use javascript inside a query builder is delightful. 🙌

8 replies

•Created by raaden on 5/21/2023 in #help

Missing 'with' clause additional operators (where, limit, offset, etc)

I have a query that uses where within a with

await db.query.parent.findMany({
  with: {
    child: {
      where: (child) => eq(child.show, true),
    },
  },
})

await db.query.parent.findMany({
  with: {
    child: {
      where: (child) => eq(child.show, true),
    },
  },
})

2 replies

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

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

EDIT: Wow, I had a rogue json column storing a massive value on each row. I deleted that and now it's working perfectly 🙌

9 replies

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

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

for now, I will just do multiple select() queries (non relational), since they do not have memory issues

9 replies

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

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

In my schema, if I comment out my other 76 properties columns (without db push), the query succeeds! (even as a findMany). Here is the drizzle logged raw sql:

select cast(neighborhood as json) from (select properties.id, properties.name, properties.neighborhoodId, if(count(properties_neighborhood.id) = 0, '[]', json_arrayagg(json_array(properties_neighborhood.id, properties_neighborhood.name))) as neighborhood from properties left join neighborhoods properties_neighborhood on properties.neighborhoodId = properties_neighborhood.id group by properties.id) properties

select cast(neighborhood as json) from (select properties.id, properties.name, properties.neighborhoodId, if(count(properties_neighborhood.id) = 0, '[]', json_arrayagg(json_array(properties_neighborhood.id, properties_neighborhood.name))) as neighborhood from properties left join neighborhoods properties_neighborhood on properties.neighborhoodId = properties_neighborhood.id group by properties.id) properties

But if I comment the columns back in, and execute the same raw sql query that only uses the required columns, it fails with the same error:

await db.execute(
sql`select cast(neighborhood as json) from (select properties.id, properties.name, properties.neighborhoodId, if(count(properties_neighborhood.id) = 0, '[]', json_arrayagg(json_array(properties_neighborhood.id, properties_neighborhood.name))) as neighborhood from properties left join neighborhoods properties_neighborhood on properties.neighborhoodId = properties_neighborhood.id group by properties.id) properties`
)

await db.execute(
sql`select cast(neighborhood as json) from (select properties.id, properties.name, properties.neighborhoodId, if(count(properties_neighborhood.id) = 0, '[]', json_arrayagg(json_array(properties_neighborhood.id, properties_neighborhood.name))) as neighborhood from properties left join neighborhoods properties_neighborhood on properties.neighborhoodId = properties_neighborhood.id group by properties.id) properties`
)

Because the drizzle query generated from the db.execute(sql``) query does contain the other 76 (many json) columns again So maybe the issue is a combination of: - drizzle query is over-selecting - my table has many json column types (even though the actual data stored in the json is much less than 1.6Mib total)

9 replies

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

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

EDIT: https://stackoverflow.com/questions/29575835/error-1038-out-of-sort-memory-consider-increasing-sort-buffer-size Some mysql users report this error when using large json columns. So maybe it is related to by ~30 json columns. My json columns are only storing small array of strings. But maybe the drizzle query could be optimized to only select the columns necessary for the relation lookup query? (or maybe sql requires it)

9 replies

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

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

Here are my relations definitions

export const properties = mysqlTable('properties', {
  id: serial('id').primaryKey(),
  neighborhoodId: int('neighborhoodId'),
  // ... 76 other columns
})

export const neighborhoods = mysqlTable('neighborhoods', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
})

export const neighborhoodRelations = relations(neighborhoods, ({ many }) => ({
  properties: many(properties),
}))

export const propertyRelations = relations(properties, ({ one }) => ({
  neighborhood: one(neighborhoods, {
    fields: [properties.neighborhoodId],
    references: [neighborhoods.id],
  }),
}))

export const properties = mysqlTable('properties', {
  id: serial('id').primaryKey(),
  neighborhoodId: int('neighborhoodId'),
  // ... 76 other columns
})

export const neighborhoods = mysqlTable('neighborhoods', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
})

export const neighborhoodRelations = relations(neighborhoods, ({ many }) => ({
  properties: many(properties),
}))

export const propertyRelations = relations(properties, ({ one }) => ({
  neighborhood: one(neighborhoods, {
    fields: [properties.neighborhoodId],
    references: [neighborhoods.id],
  }),
}))

9 replies