Postgres: using "with" keyword with custom schema name

Hi,

We are using Kysely for a while now, thanks for the great work done here !

We encounter an issue with the use of the with keyword in Postgres dialect.

This works perfectly fine, when not using a custom schema name. For testing purpose, we use the Migrator with a db object that has a WithSchemaPlugin defined.

This schema is also applied when using the keyword with

When the migration is executed, the following error is thrown:

error: relation "test_xxx.W_UserList" does not exist

This should help to reproduce

type OrganizationTable = {
  id: Generated<number>;
  name: string;
}

type OrganizationHasUserTable = {
  organizationId: number;
  userId: number;
  permission: string;
};

type UserTable = {
  id: Generated<number>;
  email: string;
}

type MyDB = {
  Organization: OrganizationTable;
  OrganizationHasUser: OrganizationHasUserTable;
  User: UserTable;
}

// Migration
export async function up(db: Kysely<MyDB>): Promise<void>{
  await db.schema
    .createTable("Organization")
    .addColumn("id", "int", (col) => col.primaryKey())
    .addColumn("name", "varchar(255)", (col) => col.notNull().unique())
    .execute();

  await db.schema
    .createTable("User")
    .addColumn("id", "int", (col) => col.primaryKey())
    .addColumn("email", "varchar(327)", (col) => col.notNull().unique()) 
    .execute();

   await db.schema
     .createTable("OrganizationHasUser")
     .addColumn("organizationId", "integer", (col) =>
       col.notNull().references("Organization.id").onDelete("cascade")
     )
     .addColumn("userId", "integer", (col) =>
       col.notNull().references("User.id").onDelete("cascade")
     )
     .addColumn("permission", "text", (col) =>
       col.notNull().defaultTo("USR_ORG_MEMBER")
     )
     .addUniqueConstraint("cst_organization_user_permission", [
       "organizationId",
       "userId",
       "permission",
     ])
     .execute();

  await db.schema
    .createView("V_OrganizationWithCounts")
    .as(
      db
        .with("W_UserList", (sdb) =>
          sdb
            .selectFrom("User")
            .leftJoin(
              "OrganizationHasUser",
              "User.id",
              "OrganizationHasUser.userId"
            )
            .select(["User.id", "OrganizationHasUser.organizationId"])
            .distinct()
        )
        .selectFrom("Organization")
        .selectAll("Organization")
        .select(
          (s) =>
            s
              .selectFrom("W_UserList")
              .select((s1) => s1.fn.count<number>("W_UserList.id").as("cnt"))
              .whereRef("W_UserList.organizationId", "=", "Organization.id")
              .as("nbUsers")
          // Workaround: use a raw sql query instead of the above to avoid the schema name to be added to the table name
          // sql<number>`( SELECT count("W_UserList".id) AS cnt
          //                   FROM "W_UserList"
          //                   WHERE "W_UserList"."organizationId" = "Organization".id)`.as('nbUsers')
        )
        .groupBy("Organization.id")
    )
    .execute();
}

export async function down(db: Kysely<MyDB>): Promise<void> {
  await db.schema.dropTable("OrganizationHasUser").execute();
  await db.schema.dropTable("Organization").execute();
  await db.schema.dropTable("User").execute();

  await db.schema.dropViewIfExists('V_OrganizationWithCounts').execute();
}

Is there a cleaner workaround ?

kysely-org / kysely

Postgres: using "with" keyword with custom schema name #537