cal Posts - Answer Overflow

cal

Explore posts from servers

•Created by cal on 1/13/2024 in #help

Changing a column name completely breaks the queries

I went back and forth multiple times now and this is really weird. So when using the following schema and queries everything works as expected:

export const habitStats = pgTable(
  "habit_stats",
  {
    id: serial("id").primaryKey(),
    habitId: integer("habit_id")
      .notNull()
      .references(() => habits.id),
    profileId: integer("profile_id")
      .notNull()
      .references(() => profiles.id),
    completed: timestamp("completed", {
      precision: 4,
      withTimezone: true,
    })
      .array()
      .default(sql`array[current_timestamp]`),
    updatedAt: date("updated_at").defaultNow(), // this is set every time that habit is completed
  },
  (t) => ({
    unq: unique().on(t.habitId, t.profileId),
  }),
);

export const habitStats = pgTable(
  "habit_stats",
  {
    id: serial("id").primaryKey(),
    habitId: integer("habit_id")
      .notNull()
      .references(() => habits.id),
    profileId: integer("profile_id")
      .notNull()
      .references(() => profiles.id),
    completed: timestamp("completed", {
      precision: 4,
      withTimezone: true,
    })
      .array()
      .default(sql`array[current_timestamp]`),
    updatedAt: date("updated_at").defaultNow(), // this is set every time that habit is completed
  },
  (t) => ({
    unq: unique().on(t.habitId, t.profileId),
  }),
);

Upsert query

const query = sql`insert into
                    habit_stats (habit_id, profile_id)
                  values
                    (${body.habitId}, ${body.profileId}) on conflict (habit_id, profile_id)
                  do
                  update
                  set
                    completed = current_timestamp || habit_stats.completed,
                    updated_at = current_date
                  where
                    habit_stats.updated_at is null or
                    fate (habit_stats.updated_at) != current_date;`;

const query = sql`insert into
                    habit_stats (habit_id, profile_id)
                  values
                    (${body.habitId}, ${body.profileId}) on conflict (habit_id, profile_id)
                  do
                  update
                  set
                    completed = current_timestamp || habit_stats.completed,
                    updated_at = current_date
                  where
                    habit_stats.updated_at is null or
                    fate (habit_stats.updated_at) != current_date;`;

Reset query

const query = sql`update habit_stats set completed = completed[2:], updated_at = null;`;

const query = sql`update habit_stats set completed = completed[2:], updated_at = null;`;

when changing updated_at to completed_at while keeping everything else the same, I am getting the following error:

{
  "name": "PostgresError",
  "severity_local": "ERROR",
  "severity": "ERROR",
  "code": "42601",
  "position": "336",
  "file": "scan.l",
  "originalLine": 27,
  "originalColumn": 10,
  "routine": "scanner_yyerror"
}

{
  "name": "PostgresError",
  "severity_local": "ERROR",
  "severity": "ERROR",
  "code": "42601",
  "position": "336",
  "file": "scan.l",
  "originalLine": 27,
  "originalColumn": 10,
  "routine": "scanner_yyerror"
}

If I revert back to updatedAt and push the schema again, everything works fine... What am I missing here?

1 replies

DTDrizzle Team

•Created by cal on 10/25/2023 in #help

`sql.join` with `ODER BY` leads to syntax error while the sql query seems correct.

I am conditionally sorting the results from a database query

const sortSqlChunks: SQL[] = [];
if (sorting.length > 1) {
  const customSort = sorting.shift();
  if (customSort) {
    const [property, order] = Object.entries(customSort)[0];
    sortSqlChunks.push(
      sql`${ad[property as unknown as keyof AdSelect]} ${order} nulls last`,
    );
  }
}
sortSqlChunks.push(sql`${ad.publisherCreatedDateTime} desc nulls last`);

const ads = await this.dataAccessService
      .getDb("AD")
      .select()
      .from(ad)
      .where(inArray(ad.publicId, elasticSearchAds.publicIds))
      .orderBy(sql.join(sortSqlChunks, ", "));

const sortSqlChunks: SQL[] = [];
if (sorting.length > 1) {
  const customSort = sorting.shift();
  if (customSort) {
    const [property, order] = Object.entries(customSort)[0];
    sortSqlChunks.push(
      sql`${ad[property as unknown as keyof AdSelect]} ${order} nulls last`,
    );
  }
}
sortSqlChunks.push(sql`${ad.publisherCreatedDateTime} desc nulls last`);

const ads = await this.dataAccessService
      .getDb("AD")
      .select()
      .from(ad)
      .where(inArray(ad.publicId, elasticSearchAds.publicIds))
      .orderBy(sql.join(sortSqlChunks, ", "));

The query builder returns the following query

{
  sql: 'select ... from "ads"."Ad" where "Ad"."publicId" in (...) order by "Ad"."price" $26 nulls last$27"Ad"."publisherCreatedDateTime" desc nulls last',
  params: [
    ...
    'desc',
    ', '
  ]
}

{
  sql: 'select ... from "ads"."Ad" where "Ad"."publicId" in (...) order by "Ad"."price" $26 nulls last$27"Ad"."publisherCreatedDateTime" desc nulls last',
  params: [
    ...
    'desc',
    ', '
  ]
}

Which seems to be correct. When executing the code I get the following PostgresError:

syntax error at or near \"$26\"

syntax error at or near \"$26\"

Something seems to be off with 'desc' but I don't have a clue what is causing this. Any help is appreciated

6 replies

DTDrizzle Team

•Created by cal on 8/20/2023 in #help

findMany without additional options

When using const results = await db.query.habits.findMany() in my project, results is []. If add any option like

const results = await db.query.habits.findMany({
      orderBy: [asc(habits.name)]
    })

const results = await db.query.habits.findMany({
      orderBy: [asc(habits.name)]
    })

I get all habits in the table. Normally findMany() should be enough, shouldn't it? https://orm.drizzle.team/docs/rqb#find-many

6 replies

DTDrizzle Team

•Created by cal on 7/24/2023 in #help

How do I define a composite foreign key?

Similar to this syntax in prisma?

%field%   %Type% @relation(fields: [id, objectType], references: [objectId, objectType])

%field%   %Type% @relation(fields: [id, objectType], references: [objectId, objectType])

I saw the section about composite primary keys but I didn't find anything about composite foreign keys. Thank you in advance

9 replies

TTCTheo's Typesafe Cult

•Created by cal on 7/9/2023 in #questions

Handle custom error class in error UI boundary

Hey guys, I am throwing a custom error

throw new GQLError(workout.message, workout.__typename);

throw new GQLError(workout.message, workout.__typename);

in a subpage. I then try to handle it using a error UI boundary. Everything works as expected but

const isGQLError = error instanceof GQLError;

const isGQLError = error instanceof GQLError;

inside of error.tsx is always false. I assume nextjs internally handles/wraps the error. Is there a way to get a hold of the thrown error directly in order to display different UI in case of an GQLError? Thank you in advance

2 replies

TTCTheo's Typesafe Cult

•Created by cal on 6/28/2023 in #questions

Where would I initialize a 3rd party API to use in TRPC routes?

I am currently initializing this client in server/client.ts similar to the global definition of prisma. I then import client in my TRPC router. Does this make sense or would it be smarter to do that somewhere else.

interface Client {
  $ad: typeof api.$ad;
  $ads: api.Ads;
  $geo: api.Geo;
}

const globalForClient = globalThis as unknown as {
  immoledo: Client | undefined;
  abortController: AbortController | undefined;
};

const initClient = async () => {
  if (globalForClient.abortController) {
    globalForClient.abortController.abort();
  }

  globalForClient.abortController = new AbortController();
  const { signal } = globalForClient.abortController;

  api.init(env.API_URL);

  api.$global.net.errors.onUnauthorized = () =>
    console.warn("Client unauthorized");

  try {
    const success = await api.$anonymousUser.profile.login(
      env.API_CLIENT_ID,
      env.API_CLIENT_SECRET,
      true,
      signal
    );
    console.log(`🏠 Client login ${success ? "✅" : "❌"}`);
  } catch (error) {
    console.error("🏚️ Client login error:\n", error);
  }

  return api;
};

export const client = await initClient();

interface Client {
  $ad: typeof api.$ad;
  $ads: api.Ads;
  $geo: api.Geo;
}

const globalForClient = globalThis as unknown as {
  immoledo: Client | undefined;
  abortController: AbortController | undefined;
};

const initClient = async () => {
  if (globalForClient.abortController) {
    globalForClient.abortController.abort();
  }

  globalForClient.abortController = new AbortController();
  const { signal } = globalForClient.abortController;

  api.init(env.API_URL);

  api.$global.net.errors.onUnauthorized = () =>
    console.warn("Client unauthorized");

  try {
    const success = await api.$anonymousUser.profile.login(
      env.API_CLIENT_ID,
      env.API_CLIENT_SECRET,
      true,
      signal
    );
    console.log(`🏠 Client login ${success ? "✅" : "❌"}`);
  } catch (error) {
    console.error("🏚️ Client login error:\n", error);
  }

  return api;
};

export const client = await initClient();

Best reards and thank you in advance 🙂

1 replies

Gaming

Programming