Drizzle Team•7mo ago

Issues with the "with" parameter

Hello! I am super sure this is a noob questions but this is my first time using Drizzle with Hono in Bun, coming from sequelize. So I am trying to make this short: When I try to do

apps
  .get('/:identifier', async (c) => {
    try {
      const name = c.req.param('identifier');
      const data = await db.query.app.findFirst({
        where: eq(app.name, name),
        with: { feature: true },
        // FIXME: 500 error
      });
      if (!data) return c.notFound();
      return c.json(data);
    } catch (error) { return c.json(error, 500); }
  })
...

apps
  .get('/:identifier', async (c) => {
    try {
      const name = c.req.param('identifier');
      const data = await db.query.app.findFirst({
        where: eq(app.name, name),
        with: { feature: true },
        // FIXME: 500 error
      });
      if (!data) return c.notFound();
      return c.json(data);
    } catch (error) { return c.json(error, 500); }
  })
...

I receive a SQL error from my MariaDB telling me that there is an issue with the following SQL Query:

select
  `app`.`id`,
  `app`.`name`,
  `app`.`createdAt`,
  `app`.`updatedAt`,
  `app_feature`.`data` as `feature`
from
  `apps` `app`
  left join lateral (
    select
      json_array(
        `app_feature`.`appID`,
        `app_feature`.`trackMessage`,
        `app_feature`.`deleteMessage`,
        `app_feature`.`webhookSupport`,
        `app_feature`.`inviteLinks`,
        `app_feature`.`createdAt`,
        `app_feature`.`updatedAt`
      ) as `data`
    from
      (
        select
          *
        from
          `features` `app_feature`
        where
          `app_feature`.`appID` = `app`.`id`
        limit
          1
      ) `app_feature`
  ) `app_feature` on true
where
  `app`.`name` = 'sdfsdfsdf'
limit
  1

select
  `app`.`id`,
  `app`.`name`,
  `app`.`createdAt`,
  `app`.`updatedAt`,
  `app_feature`.`data` as `feature`
from
  `apps` `app`
  left join lateral (
    select
      json_array(
        `app_feature`.`appID`,
        `app_feature`.`trackMessage`,
        `app_feature`.`deleteMessage`,
        `app_feature`.`webhookSupport`,
        `app_feature`.`inviteLinks`,
        `app_feature`.`createdAt`,
        `app_feature`.`updatedAt`
      ) as `data`
    from
      (
        select
          *
        from
          `features` `app_feature`
        where
          `app_feature`.`appID` = `app`.`id`
        limit
          1
      ) `app_feature`
  ) `app_feature` on true
where
  `app`.`name` = 'sdfsdfsdf'
limit
  1

with the error message:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select json_array(`app_feature`.`appID`, `app_feature`.`trackMessage`, `app_...' at line 1

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select json_array(`app_feature`.`appID`, `app_feature`.`trackMessage`, `app_...' at line 1

When i remove the "with feature" from my code it does work fine. So my guess is either that the json_array is causing issues, or my schema is incorrect and might have done a mistake designing the structure... I have the following schema with only the relevant parts:

import { relations } from 'drizzle-orm';

import {
  int, mysqlTable, boolean, tinytext, timestamp, json, unique, varchar,
} from 'drizzle-orm/mysql-core';

export const app = mysqlTable('apps', {
  id: int('id').primaryKey().autoincrement(),
  name: tinytext('name').notNull(),
  createdAt: timestamp('createdAt').notNull().defaultNow(),
  updatedAt: timestamp('updatedAt').notNull().defaultNow(),
});

export const feature = mysqlTable('features', {
  appID: int('appID').primaryKey().references(() => app.id),
  trackMessage: boolean('trackMessage').notNull().default(false),
  deleteMessage: boolean('deleteMessage').notNull().default(false),
  webhookSupport: boolean('webhookSupport').notNull().default(false),
  inviteLinks: boolean('inviteLinks').notNull().default(false),
  createdAt: timestamp('createdAt').notNull().defaultNow(),
  updatedAt: timestamp('updatedAt').notNull().defaultNow(),
});

export const appRelations = relations(app, ({ many, one }) => ({
  feature: one(feature, { fields: [app.id], references: [feature.appID] }),
}));

export const featureRelations = relations(feature, ({ one }) => ({
  app: one(app, { fields: [feature.appID], references: [app.id] }),
}));

import { relations } from 'drizzle-orm';

import {
  int, mysqlTable, boolean, tinytext, timestamp, json, unique, varchar,
} from 'drizzle-orm/mysql-core';

export const app = mysqlTable('apps', {
  id: int('id').primaryKey().autoincrement(),
  name: tinytext('name').notNull(),
  createdAt: timestamp('createdAt').notNull().defaultNow(),
  updatedAt: timestamp('updatedAt').notNull().defaultNow(),
});

export const feature = mysqlTable('features', {
  appID: int('appID').primaryKey().references(() => app.id),
  trackMessage: boolean('trackMessage').notNull().default(false),
  deleteMessage: boolean('deleteMessage').notNull().default(false),
  webhookSupport: boolean('webhookSupport').notNull().default(false),
  inviteLinks: boolean('inviteLinks').notNull().default(false),
  createdAt: timestamp('createdAt').notNull().defaultNow(),
  updatedAt: timestamp('updatedAt').notNull().defaultNow(),
});

export const appRelations = relations(app, ({ many, one }) => ({
  feature: one(feature, { fields: [app.id], references: [feature.appID] }),
}));

export const featureRelations = relations(feature, ({ one }) => ({
  app: one(app, { fields: [feature.appID], references: [app.id] }),
}));

The full one can be found here: https://github.com/FlippedCodes/I-SH2/blob/main/src/api/db/schema.ts

GitHub

I-SH2/src/api/db/schema.ts at main · FlippedCodes/I-SH2

The new version of I-SH. Contribute to FlippedCodes/I-SH2 development by creating an account on GitHub.

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Issues with the "with" parameter