[BUG]: `.all()` chain method does not exist

[DeveloperTheExplorer]

What version of drizzle-orm are you using?

0.29.4

What version of drizzle-kit are you using?

0.20.14

Describe the Bug

In the Many-to-One example on the Join documentation page, it demonstrates that you can chain your drizzle select query with .all() to leftJoin all related entities.

However, in reality, the .all() does not exist.

Property 'all' does not exist on type 'Omit<MySqlSelectBase<"courses", Record<"courses", { id: MySqlColumn<{ name: "id"; tableName: "courses"; dataType: "string"; columnType: "MySqlVarChar"; data: string; driverParam: string | number; notNull: true; hasDefault: true; enumValues: [...]; baseColumn: never; }, object>; ... 13 more ...; ownerId: MySqlColumn<...'.ts(2339)
⚠ Error (TS2339)  | 
Property all does not exist on type

For reference here are the relevant files and entities:

// db.ts
import { DB_HOST, DB_USERNAME, DB_PASSWORD, DB_PORT } from '$env/static/private';

import { drizzle } from 'drizzle-orm/planetscale-serverless';
import { Client } from '@planetscale/database';

import * as chapterSchema from './models/chapter.model';
import * as courseSchema from './models/course.model';

const client = new Client({
	host: DB_HOST,
	username: DB_USERNAME,
	password: DB_PASSWORD,
	url: `https://${DB_HOST}:${DB_PORT}`
});

export const db = drizzle(client, {
	schema: {
		...chapterSchema,
		...courseSchema,
	}
});

// course.model.ts
import { relations } from 'drizzle-orm';
import { mysqlTable, text, varchar, int } from 'drizzle-orm/mysql-core';

import { generateUUID } from '$lib/utils/hash.util';
import { chapters } from './chapter.model';

export const courses = mysqlTable('courses', {
  id: varchar('id', { length: 36 }).primaryKey().$defaultFn(generateUUID),
  name: varchar('name', { length: 128 }).notNull(),
  slug: varchar('slug', { length: 128 }).unique().notNull(),
  description: text('description'),
  duration: int('duration').notNull(),
  progress: int('progress').notNull().$default(() => 0),
  skills: text('skills'),
  difficulty: int('difficulty'),
  technologies: text('technologies').notNull(),
  modelUsed: varchar('model_used', { length: 128 }).notNull(),
  prompt: text('prompt').notNull(),
  promptHash: text('prompt_hash').notNull(),
  contentHash: varchar('content_hash', { length: 64 }).notNull().unique(),
  content: text('content'),
});

export const courseRelations = relations(courses, ({ many, one }) => ({
  chapters: many(chapters),
}));

export type ICourse = typeof courses.$inferSelect; // return type when queried
export type NewCourse = Omit<typeof courses.$inferInsert, 'slug'> & {
  slug?: string;
};

// chapter.model.ts
import { relations } from 'drizzle-orm';
import { mysqlTable, text, varchar, int } from 'drizzle-orm/mysql-core';

import { generateUUID } from '$lib/utils/hash.util';
import { courses } from './course.model';

export const chapters = mysqlTable('chapters', {
  id: varchar('id', { length: 36 }).primaryKey().$defaultFn(generateUUID),
  name: varchar('name', { length: 128 }),
  description: text('description'),
  duration: int('duration'),
  skills: text('skills'),
  difficulty: int('difficulty'),
  technologies: text('technologies'),
  modelUsed: varchar('model_used', { length: 128 }),
  content: text('content'),
  courseId: varchar('course_id', { length: 36 }).references(() => courses.id)
});

export const chapterRelations = relations(chapters, ({ one, many }) => ({
  course: one(courses, {
    fields: [chapters.courseId],
    references: [courses.id]
  })
}));

export type IChapter = typeof chapters.$inferSelect; // return type when queried
export type NewChapter = typeof chapters.$inferInsert; // insert type

Expected behavior

For .all() to exist or for the inferred type of the select leftJoin query to include chapters as an array, rather than singular since this is a Many-to-One relationship.

Environment & setup

Using SvelteKit 2.0 with TypeScript 5.0. Database is MySQL with the latest Docker image.

[ashar-nadeem-lumi]

Any update on this issue? Hard blocked by this if we want to move to Drizzle

[DeveloperTheExplorer]

@ashar-nadeem-lumi I switched to MikroORM. You can also use Drizzle Queries instead.

[timonaldenhoff]

Same issue in version 0.30.10.

[AndriiSherman]

.all() is available only for SQLite, and you are using MySQL. We have imports from SQLite in the documentation but haven't mentioned it properly yet. We will update it.

For MySQL, you should just await the query to get the result or use .execute().