kysely-org / kysely

A type-safe typescript SQL query builder
https://kysely.dev
MIT License
10.8k stars 275 forks source link

Bug: Cannot execute `.withSchema` documentation example #61

Closed rubenferreira97 closed 2 years ago

rubenferreira97 commented 2 years ago

Given the following documentation:

Expand documentation ```TS /** * Sets the schema to be used for all table references that don't explicitly * specify a schema. * * This only affects the query created through the builder returned from * this method and doesn't modify the `db` instance. * * ### Examples * * ``` * await db.withSchema('mammals') * .selectFrom('pet') * .selectAll() * .innerJoin('public.person', 'public.person.id', 'pet.owner_id') * .execute() * ``` * * The generated SQL (PostgreSQL): * * ```sql * select * from "mammals"."pet" * inner join "public"."person" * on "public"."person"."id" = "mammals"."pet"."owner_id" * ``` * * `withSchema` is smart enough to not add schema for aliases, * common table expressions or other places where the schema * doesn't belong to: * * ``` * await db.withSchema('mammals') * .selectFrom('pet as p') * .select('p.name') * .execute() * ``` * * The generated SQL (PostgreSQL): * * ```sql * select "p"."name" from "mammals"."pet" as "p" * ``` */ ```

I can't execute this:

import { DB_NAME } from 'database';
import { MysqlDialectConfig, Kysely, MysqlDialect } from 'kysely';

const {
  DB_HOST, DB_PORT, DB_USER, DB_PASSWORD,
} = process.env;

const config : MysqlDialectConfig = {
  host: DB_HOST,
  database: DB_NAME,
  password: DB_PASSWORD,
  port: Number(DB_PORT),
  user: DB_USER,
};

interface Pet {
  id: number;
  owner_id: number;
}

interface Person {
  id: number;
  name: string;
}

interface Database {
  pet: Pet;
  person: Person;
}
const db = new Kysely<Database>({
  dialect: new MysqlDialect(config),
});

db.withSchema('mammals')
  .selectFrom('pet')
  .selectAll()
  .innerJoin('public.person', 'public.person.id', 'pet.owner_id') // Error here. Can't add a schema.
  .execute();

Am I missing something?

koskimas commented 2 years ago

If you want to refer to schemas explicitly, you need to add them into the Database interface like this:

interface Database {
   pet: Pet;
   person: Person;
  'public.pet': Pet,
  'public.person': Person,
}

The withSchema method can be used regardless, but for explicit references (like public.person) to work, you need to tell Kysely about those schemas. We can't add public automatically because other dialects don't have schemas and we don't want the autocompletions to pop up for everyone.

I should probably update the documentation a little bit.

koskimas commented 2 years ago

I tried to clarify things in this recipe https://github.com/koskimas/kysely/blob/master/recipes/schemas.md