Drizzle Team•11mo ago

SQL syntax error when using relations in MariaDB

Does anyone else have issue similar to this one? https://github.com/drizzle-team/drizzle-orm/issues/1100 I have this issue on MariaDB 10.4 and also on 11.2. This is my schema:

export const items = mysqlTable('items', {
    id: int('id').autoincrement().primaryKey().notNull(),
    ...
})

export const itemsRelations = relations(items, ({ one }) => ({
    trade: one(trades, { fields: [items.tradeId], references: [trades.id] }),
}));

export const trades = mysqlTable('trades', {
    id: int('id').autoincrement().primaryKey().notNull(),
    ...
})

export const tradesRelations = relations(trades, ({ many }) => ({
    items: many(items)
}))

export const items = mysqlTable('items', {
    id: int('id').autoincrement().primaryKey().notNull(),
    ...
})

export const itemsRelations = relations(items, ({ one }) => ({
    trade: one(trades, { fields: [items.tradeId], references: [trades.id] }),
}));

export const trades = mysqlTable('trades', {
    id: int('id').autoincrement().primaryKey().notNull(),
    ...
})

export const tradesRelations = relations(trades, ({ many }) => ({
    items: many(items)
}))

I am getting error:

42000', sqlMessage: "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(`trades_items`.`id`, `trades_items`...' at line 1"

42000', sqlMessage: "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(`trades_items`.`id`, `trades_items`...' at line 1"

when doing trade query with with: {items: true}

GitHub

[BUG] MySQL relational query syntax error · Issue #1100 · drizzle-t...

What version of drizzle-orm are you using? 0.28.3 What version of drizzle-kit are you using? 0.19.5 Describe the Bug Using MySQL and the following schema: export const tickets = mysqlTable( "t...

5 Replies

nk•11mo ago

@Baterka maybe try restarting your DB like the instance and if that doesn't help, print the query.toSQL().sql

Angelelz•11mo ago

MariaDB doesn't support lateral queries or correlated subqueries. The RQB uses lateral queries on mode default and correlated subqueries in planetscale mode Also, mariaDB is not officially supported in drizzle yet

BaterkaOP•11mo ago

oh... so I should switch to mysql then?

Angelelz•11mo ago

Surely the support for your dialect in your favorite ORM is one of the things that go into deciding which one to use.

BaterkaOP•11mo ago

I mean, I am using MariaDB as MySQL compatibile alternative for years now... I do not rly want to give that up, but I really like the drizzle-orm

Gaming

Programming

SQL syntax error when using relations in MariaDB