Include relations of Query API not working with mysql2 (local MariaDB/Mysql)

[ashoktamang]

Please refer to this error reproduction code:

# schema.ts
import { relations } from "drizzle-orm";
import { int, mysqlTable, varchar } from "drizzle-orm/mysql-core";

export const user = mysqlTable("user", {
  id: int("id").primaryKey().autoincrement().notNull().unique(),
  name: varchar("name", { length: 255 }).default("Name"),
  photoId: int("photo_id"),
});

export const photo = mysqlTable("photo", {
  id: int("id").primaryKey().autoincrement().notNull().unique(),
  url: varchar("url", { length: 255 }).default("https://picsum.photos/200"),
});

export const userRelations = relations(user, ({ one }) => ({
  photo: one(photo, {
    fields: [user.photoId],
    references: [photo.id],
  }),
}));


# db.ts

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import * as schema from "./schema";

const client = await mysql.createConnection({
  uri: "mysql://root:password@localhost:3308/db",
});

const db = drizzle(client, { schema, logger: true, mode: "default" });

export default db;

# index.ts

import db from "./db";
import { photo, user } from "./schema";

// Create  photo
const newPhoto = await db.insert(photo).values({});

// Create user
const newUser = await db
  .insert(user)
  .values({ name: "John Doe", photoId: newPhoto[0].insertId });

console.log("🚀 ~ newUser:", newUser);

// Get users
const users = await db.query.user.findMany({
  with: {
    photo: true,
  },
});
console.log("🚀 ~ users:", users);

After running index.ts, I get the following error:

Records are successfully being created in database with relations as well:

When implemented with posgres, it is working fine.

Any help will be appreciated. I am thinking of completely abandoning Query API and resorting to JOINS.

[ashoktamang]

It seems MariaDB does not support LATERAL JOINS which is being used in with of Query API.

Note: I am using MariaDB 10.6.x

Can anyone confirm?

[tsulatsitamim]

yes