IgnisMurasaki
IgnisMurasaki
PostsComments
DTDrizzle Team
Created by IgnisMurasaki on 9/12/2024 in #help
Drizzle failed to parse an empty JSONB Array
so I have a query that calls ARRAY_AGG() function and the result of query returned the aggregated value since the aggregated column is a JSONB this results in Array of JSONB, but some of the row may return an empty json, 
id|jsonb_array_col                    |
1 | {{"a":"a","b":2},{"a":"a","b":2}} | 
2 | {}                                |
id|jsonb_array_col                    |
1 | {{"a":"a","b":2},{"a":"a","b":2}} | 
2 | {}                                |
this is the query for the selector 
{
  logs: sql<z.infer<typeof RoomDurationMeta>[]>`COALESCE(ARRAY_AGG(meta ORDER BY ${activitiesLog.meta} ->> 'joinTime'), '{}'::jsonb[] )
            `.as('logs')
}
{
  logs: sql<z.infer<typeof RoomDurationMeta>[]>`COALESCE(ARRAY_AGG(meta ORDER BY ${activitiesLog.meta} ->> 'joinTime'), '{}'::jsonb[] )
            `.as('logs')
}
however due to the particular empty array i got an error SyntaxError: Unexpected token N in JSON at position 0, I was able to confirm this by adding a limit(1) to the query by limiting the query which only return 1 row 
id|jsonb_array_col                    |
1 | {{"a":"a","b":2},{"a":"a","b":2}} | 
id|jsonb_array_col                    |
1 | {{"a":"a","b":2},{"a":"a","b":2}} |
the query was performed sucesfully
1 replies
DTDrizzle Team
Created by IgnisMurasaki on 8/13/2024 in #help
Setup drizzle using 'pg' instead of 'postgres' driver package
No description
1 replies
DTDrizzle Team
Created by IgnisMurasaki on 7/10/2024 in #help
A more modular way to combine predefined query into a single transaction
Hello currently I write my query into something like this 
export const addUser = (data: InsertUser) => {
  return db.insert(users).values(data).returning();
};


export const getUserByEmail = (email: string) => {
  return db.select().from(users).where(eq(users.email, email));
};

async insertParticipant(userID : number, eventID: number) {
  return db.insert(participants).values({userID,eventID}).returning()
 }
export const addUser = (data: InsertUser) => {
  return db.insert(users).values(data).returning();
};


export const getUserByEmail = (email: string) => {
  return db.select().from(users).where(eq(users.email, email));
};

async insertParticipant(userID : number, eventID: number) {
  return db.insert(participants).values({userID,eventID}).returning()
 }
This function sometimes called separately, but there are times that I wanted to combine those query into a single transaction. What I do right now is creating a whole new transaction query without reusing the query above something like 
const res = db.transactions((tx)=>{
  let user : undefined | selectUsers 
  user = await   tx.select().from(users).where(eq(users.email,email)
  if(!user){
      //insert a new user
      user = await tx.insert(users).values({....}).returning()
    }
  
  const join = await tx.insert(participant).values({a: user.ID, b:fk.ID}).returning()
 
    return {
    ...user,
    ...join
  }

return res
})
const res = db.transactions((tx)=>{
  let user : undefined | selectUsers 
  user = await   tx.select().from(users).where(eq(users.email,email)
  if(!user){
      //insert a new user
      user = await tx.insert(users).values({....}).returning()
    }
  
  const join = await tx.insert(participant).values({a: user.ID, b:fk.ID}).returning()
 
    return {
    ...user,
    ...join
  }

return res
})
as you can see it's seems inefficient, since the query is the same with those 3 defined function, is there any way i can utilize it and use it into transaction ? i suspect that i can use a QueryBuilder or something a suggestion would be very much appreciated
3 replies
DTDrizzle Team
Created by IgnisMurasaki on 3/26/2024 in #help
Magic SQL type parse into zod schema
wondering if it's possible to parse a value from the select using magic sql into a zod schema to parse into validated object you need to call the schema.parse() function, however the magic sql only give typehints 
const timeSchema = z.object({  
  joinTime: z
    .string()
    .datetime({ offset: true })
    .transform((val) => new Date(val)),
  leaveTime: z
    .string()
    .datetime({ offset: true })
    .transform((val) => new Date(val))
})

const arrayTimeSchema = z.array(timeSchema)

// this will not work since it only give type hint into the returned object but doens't actually pase it
db.select({
  timeData : sql<z.infer<typeof timeSchema>>`SQL QUERY`
})
const timeSchema = z.object({  
  joinTime: z
    .string()
    .datetime({ offset: true })
    .transform((val) => new Date(val)),
  leaveTime: z
    .string()
    .datetime({ offset: true })
    .transform((val) => new Date(val))
})

const arrayTimeSchema = z.array(timeSchema)

// this will not work since it only give type hint into the returned object but doens't actually pase it
db.select({
  timeData : sql<z.infer<typeof timeSchema>>`SQL QUERY`
})
it's not a big problem for a single query selector, but the thing is that actually i'm using it to aggregate an array
1 replies
DTDrizzle Team
Created by IgnisMurasaki on 3/18/2024 in #help
having trouble to include relations with query function
how do you exactly use the with field in the query options to include relations ?, my functions always returned referencedTable error my relation is declared like the following 
export const eventsRelations = relations(events, ({ many, one }) => ({
  host: one(users, {
    fields: [events.createdBy],
    references: [users.id],
  }),
}));

export const usersRelations = relations(users, ({ many }) => ({
  events: many(events),
}));
export const eventsRelations = relations(events, ({ many, one }) => ({
  host: one(users, {
    fields: [events.createdBy],
    references: [users.id],
  }),
}));

export const usersRelations = relations(users, ({ many }) => ({
  events: many(events),
}));
and here's the query 
  async getEventBySlug(slug: string): Promise<selectEvent | undefined> {
    const data = await db.query.events.findFirst({
      with: { host: true },
      where: and(eq(events.slug, slug), isNull(events.deletedAt)),
    });

    if (data) return data as selectEvent;
    else return undefined;
  }
  async getEventBySlug(slug: string): Promise<selectEvent | undefined> {
    const data = await db.query.events.findFirst({
      with: { host: true },
      where: and(eq(events.slug, slug), isNull(events.deletedAt)),
    });

    if (data) return data as selectEvent;
    else return undefined;
  }
i've also tried to change the with object to {users:true} but still receiving the referencedTable error
5 replies
DTDrizzle Team
Created by IgnisMurasaki on 3/13/2024 in #help
conditional select return wrong type
const registeree = (getCount?: boolean) => {
        return tx
          .select({
            ...(getCount ? { total: count() } : {}),
          })
          .from(eventHasParticipant)
          .innerJoin(events, eq(eventHasParticipant.eventId, events.id))
          .innerJoin(
            participants,
            eq(eventHasParticipant.participantId, participants.id)
          )
          .where(and(eq(events.id, eventID), eq(events.createdBy, createdBy)));
      };

      const total = await registeree(true).limit(limit).offset((page - 1) * limit);
      const registereeResult = await registeree();

      total[0].total = total[0].total || 0;

      return { total: total[0].total, registereeResult };
const registeree = (getCount?: boolean) => {
        return tx
          .select({
            ...(getCount ? { total: count() } : {}),
          })
          .from(eventHasParticipant)
          .innerJoin(events, eq(eventHasParticipant.eventId, events.id))
          .innerJoin(
            participants,
            eq(eventHasParticipant.participantId, participants.id)
          )
          .where(and(eq(events.id, eventID), eq(events.createdBy, createdBy)));
      };

      const total = await registeree(true).limit(limit).offset((page - 1) * limit);
      const registereeResult = await registeree();

      total[0].total = total[0].total || 0;

      return { total: total[0].total, registereeResult };
so I've tried using conditional select query, so that i'm able to reuse my query syntax without repeating to get the rows and the count, I'm not sure where I'm wrong at be when I do this the registereeResult would return this object instead 
const registereeResult: {
    total?: number | undefined;
}[]
const registereeResult: {
    total?: number | undefined;
}[]
3 replies