Drizzle Team•9mo ago

Issues with the generated sql files

Hey there guys, I used the following docs (https://orm.drizzle.team/docs/rqb) about using drizzle-query to include tables but I am getting "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 coalesce(json_arrayagg(json_array(sections_courses.ind, sections...' at line 1" error. Am I missing some options in my schema.ts` file or other config thing? Thanks in advance

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

8 Replies

HeisnbergOP•9mo ago

schema.ts

export const sections = mysqlTable("sections", {
  id: int("id").autoincrement().primaryKey(),
  language_level_id: int("language_level_id")
    .notNull()
    .references(() => languageLevels.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),
  name: text("name").notNull(),
  description: text("description").notNull(),
  created_at: timestamp("created_at")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  updated_at: timestamp("updated_at")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
});

export const courses = mysqlTable("courses", {
  id: int("ind").autoincrement().primaryKey(),
  section_id: int("section_id")
    .notNull()
    .references(() => sections.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),
  name: text("name").notNull(),
  description: text("description").notNull(),
  created_at: timestamp("created_at")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  updated_at: timestamp("updated_at")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
});

export const sectionsRelations = relations(sections, ({ many, one }) => ({
  languageLevels: one(languageLevels),
  courses: many(courses),
  sectionExam: one(sectionExam)
}));

export const coursesRelations = relations(courses, ({ many, one }) => ({
  sections: one(sections, { fields: [courses.section_id], references: [sections.id] }),
  courseContent: many(courseContent),
  courseExam: many(courseExam),
}));

export const sections = mysqlTable("sections", {
  id: int("id").autoincrement().primaryKey(),
  language_level_id: int("language_level_id")
    .notNull()
    .references(() => languageLevels.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),
  name: text("name").notNull(),
  description: text("description").notNull(),
  created_at: timestamp("created_at")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  updated_at: timestamp("updated_at")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
});

export const courses = mysqlTable("courses", {
  id: int("ind").autoincrement().primaryKey(),
  section_id: int("section_id")
    .notNull()
    .references(() => sections.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),
  name: text("name").notNull(),
  description: text("description").notNull(),
  created_at: timestamp("created_at")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  updated_at: timestamp("updated_at")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
});

export const sectionsRelations = relations(sections, ({ many, one }) => ({
  languageLevels: one(languageLevels),
  courses: many(courses),
  sectionExam: one(sectionExam)
}));

export const coursesRelations = relations(courses, ({ many, one }) => ({
  sections: one(sections, { fields: [courses.section_id], references: [sections.id] }),
  courseContent: many(courseContent),
  courseExam: many(courseExam),
}));

section.model.ts

export const getSectionByLanguageLevelId = async (languageLevelId: number) => {
  return await db.query.sections.findMany({
    where: (section, { eq }) => eq(section.language_level_id, languageLevelId),
    with: {
      courses: true
    }
  });
}

export const getSectionByLanguageLevelId = async (languageLevelId: number) => {
  return await db.query.sections.findMany({
    where: (section, { eq }) => eq(section.language_level_id, languageLevelId),
    with: {
      courses: true
    }
  });
}

Startup Spells 🪄 Newsletter Guy•9mo ago

not enough info on the error. paste the whole error & note the line down in console. it should point to the code that's erroring out. at least line number.

HeisnbergOP•9mo ago

This the full error log

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 coalesce
(json_arrayagg(json_array(`sections_courses`.`ind`, `sections...' at line 1\n    at PromisePool.query (/home/bes/WebDev/Backend/
MSSLanguage_learning/node_modules/mysql2/promise.js:356:22)\n    at MySql2PreparedQuery.execute (/home/bes/WebDev/Backend/MSSLan
guage_learning/node_modules/src/mysql2/session.ts:88:31)\n    at QueryPromise.execute (/home/bes/WebDev/Backend/MSSLanguage_lear
ning/node_modules/src/mysql-core/query-builders/query.ts:149:25)\n    at QueryPromise.then (/home/bes/WebDev/Backend/MSSLanguage
_learning/node_modules/src/query-promise.ts:31:15)\n    at processTicksAndRejections (node:internal/process/task_queues:95:5)","
code":"ER_PARSE_ERROR","errno":1064,"sql":"select `sections`.`id`, `sections`.`language_level_id`, `sections`.`name`, `sections`
.`description`, `sections`.`created_at`, `sections`.`updated_at`, `sections_courses`.`data` as `courses` from `sections` left jo
in lateral (select coalesce(json_arrayagg(json_array(`sections_courses`.`ind`, `sections_courses`.`section_id`, `sections_course
s`.`name`, `sections_courses`.`description`, `sections_courses`.`created_at`, `sections_courses`.`updated_at`)), json_array()) a
s `data` from `courses` `sections_courses` where `sections_courses`.`section_id` = `sections`.`id`)

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 coalesce
(json_arrayagg(json_array(`sections_courses`.`ind`, `sections...' at line 1\n    at PromisePool.query (/home/bes/WebDev/Backend/
MSSLanguage_learning/node_modules/mysql2/promise.js:356:22)\n    at MySql2PreparedQuery.execute (/home/bes/WebDev/Backend/MSSLan
guage_learning/node_modules/src/mysql2/session.ts:88:31)\n    at QueryPromise.execute (/home/bes/WebDev/Backend/MSSLanguage_lear
ning/node_modules/src/mysql-core/query-builders/query.ts:149:25)\n    at QueryPromise.then (/home/bes/WebDev/Backend/MSSLanguage
_learning/node_modules/src/query-promise.ts:31:15)\n    at processTicksAndRejections (node:internal/process/task_queues:95:5)","
code":"ER_PARSE_ERROR","errno":1064,"sql":"select `sections`.`id`, `sections`.`language_level_id`, `sections`.`name`, `sections`
.`description`, `sections`.`created_at`, `sections`.`updated_at`, `sections_courses`.`data` as `courses` from `sections` left jo
in lateral (select coalesce(json_arrayagg(json_array(`sections_courses`.`ind`, `sections_courses`.`section_id`, `sections_course
s`.`name`, `sections_courses`.`description`, `sections_courses`.`created_at`, `sections_courses`.`updated_at`)), json_array()) a
s `data` from `courses` `sections_courses` where `sections_courses`.`section_id` = `sections`.`id`)

Startup Spells 🪄 Newsletter Guy•9mo ago

and what does it point to towards code? like it should point towards a line number in your editor. if it doesn't, then you need to use console.log or debugger to find where the error lies in the code. try commenting stuff out one-by-one where you think the code is unreachable. if you know binary search, then you can do a half-way comment & see if it reaches that part of the code. you need to put more effort mate to find it. the error is what it says it is. a syntax error somewhere in the code. maybe see if the same code works if you use another database like sqlite. then you can come back & try to fix it. or better yet, make a minimal reproduction on github/stackblitz with just the error. ik its a lot of work. will take 1-2 hours but you'll learn quickly that way & find the error faster.

HeisnbergOP•9mo ago

I already tried commenting some part of the code and found that the with section in the section.model.ts causing it but the weird thing is I am getting sql error which is generated by drizzle-orm

Startup Spells 🪄 Newsletter Guy•9mo ago

see if the sql query works in drizzle studio's sql runner... you can even paste it in drizzle runner... both can be found on drizzle studio

HeisnbergOP•9mo ago

I tried that in phpmyadmin sql playground it’s throwing the same error

RequestFX•9mo ago

I get similar error, im trying to query a m-m relation schema.ts (pretty sure thats correct, pasted only relevant info)

export const user = mysqlTable("user", {
    id: int("id").primaryKey().autoincrement(),
});

export const game_event = mysqlTable("game_event", {
    id: int("id").primaryKey().autoincrement(),
    fk_host_user: int("fk_host_user").references(() => user.id).notNull(),
});

// m-m relations
export const userToGameEvent = mysqlTable('user_to_game_event', {
    fk_user: int('fk_user').references(() => user.id).notNull(),
    fk_event: int('fk_event').references(() => game_event.id).notNull(),
}, (t) => { return { pk: primaryKey({ columns: [t.fk_user, t.fk_event] }) } }
);

export const userRelations = relations(user, ({ one, many }) => ({
    userToGameEvent: many(userToGameEvent),
}));

export const game_eventRelations = relations(game_event, ({ one, many }) => ({
    userToGameEvent: many(userToGameEvent),
}));

export const userToGameEventRelations = relations(userToGameEvent, ({ one }) => ({
    game_event: one(game_event, {
        fields: [userToGameEvent.fk_event],
        references: [game_event.id],
    }),
    user: one(user, {
        fields: [userToGameEvent.fk_user],
        references: [user.id],
    }),
}));

export const user = mysqlTable("user", {
    id: int("id").primaryKey().autoincrement(),
});

export const game_event = mysqlTable("game_event", {
    id: int("id").primaryKey().autoincrement(),
    fk_host_user: int("fk_host_user").references(() => user.id).notNull(),
});

// m-m relations
export const userToGameEvent = mysqlTable('user_to_game_event', {
    fk_user: int('fk_user').references(() => user.id).notNull(),
    fk_event: int('fk_event').references(() => game_event.id).notNull(),
}, (t) => { return { pk: primaryKey({ columns: [t.fk_user, t.fk_event] }) } }
);

export const userRelations = relations(user, ({ one, many }) => ({
    userToGameEvent: many(userToGameEvent),
}));

export const game_eventRelations = relations(game_event, ({ one, many }) => ({
    userToGameEvent: many(userToGameEvent),
}));

export const userToGameEventRelations = relations(userToGameEvent, ({ one }) => ({
    game_event: one(game_event, {
        fields: [userToGameEvent.fk_event],
        references: [game_event.id],
    }),
    user: one(user, {
        fields: [userToGameEvent.fk_user],
        references: [user.id],
    }),
}));

Query

const gameEvents = await DB.query.user.findMany({ with: { userToGameEvent: { with: { game_event: true } } }});

const gameEvents = await DB.query.user.findMany({ with: { userToGameEvent: { with: { game_event: true } } }});

Error

"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 coalesce(json_arrayagg(json_array(`user_userToGameEvent`.`fk_user`, `...' 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 coalesce(json_arrayagg(json_array(`user_userToGameEvent`.`fk_user`, `...' at line 1"

mysql Ver 15.1 Distrib 10.4.32-MariaDB, for Win64 (AMD64), source revision c4143f909528e3fab0677a28631d10389354c491

Gaming

Programming

Issues with the generated sql files