Kysely•8mo ago

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )

Hey guys, I'm currently working on a new version for our graphql wrapper. Currently we're using knex to generate the query - it works as expected but isn't typesafe with how we use it 🙈 I'm currently evaluating kysely and 99% seems to work. Some details: We're using trino as datasource ( via their http api ). The expected SQL Query requires to specify also the catalog name in the FROM statement. Short example:

SELECT
  fieldname
FROM
  <catalog>.<schema>.<table>
  # alternativ
  "<catalog>"."<schema>"."<table>"

SELECT
  fieldname
FROM
  <catalog>.<schema>.<table>
  # alternativ
  "<catalog>"."<schema>"."<table>"

I have tested some scenarios, but it seems I missed something. Question: Is it possible to generate this kind of FROM statement with kysely? here the playground link: https://kyse.link/EaWQ3

Solution:

You could actually get away with: ```ts const test = db .selectFrom<"person">(...

Jump to solution

7 Replies

Marcus - noxifyOP•8mo ago

Have seen, that kysely provides a sql helper ( sql.id ). Via:

  const test = db
  .selectFrom(sql.id("catalog_name", "schema_name", "person"))
  .selectAll();

  const test = db
  .selectFrom(sql.id("catalog_name", "schema_name", "person"))
  .selectAll();

I get the correct SELECT statement, but also some type errors 😕 Playground: https://kyse.link/sV7ww setting the types seems to work: https://kyse.link/fDkOU Question: Is this the "way to go" ? Note: it seems I missed something in the type definition, because I have now access to all fields from all tables ( tested it locally with two tables )

Igal•8mo ago

Hey 👋 When passing expressions to .selectFrom you're required to alias them with .as. Try this:

const test = db
  .selectFrom<"person as p">(
    sql.id("catalog_name", "schema_name", "person").as("p") as any,
  )
  .selectAll()
  .execute();

const test = db
  .selectFrom<"person as p">(
    sql.id("catalog_name", "schema_name", "person").as("p") as any,
  )
  .selectAll()
  .execute();

const test = db
  .selectFrom(
    sql
      .id("catalog_name", "schema_name", "person")
      .as("p") as unknown as "person as p",
  )
  .selectAll()
  .execute();

const test = db
  .selectFrom(
    sql
      .id("catalog_name", "schema_name", "person")
      .as("p") as unknown as "person as p",
  )
  .selectAll()
  .execute();

Marcus - noxifyOP•8mo ago

Hi, thanks for the quick response - will give it a try

Solution

Igal•8mo ago

You could actually get away with:

const test = db
  .selectFrom<"person">(
    sql.id("catalog_name", "schema_name", "person") as any,
  )
  .selectAll()
  .execute();

const test = db
  .selectFrom<"person">(
    sql.id("catalog_name", "schema_name", "person") as any,
  )
  .selectAll()
  .execute();

or:

const test = db
  .selectFrom(
    sql
      .id("catalog_name", "schema_name", "person") as unknown as "person",
  )
  .selectAll()
  .execute();

const test = db
  .selectFrom(
    sql
      .id("catalog_name", "schema_name", "person") as unknown as "person",
  )
  .selectAll()
  .execute();

Marcus - noxifyOP•8mo ago

Will try the second approach since my current eslint setup will throw an error if there is an any (using the t3 turbo stack) this works perfect! Thanks for the quick help

Marcus - noxifyOP•8mo ago

Marcus - noxifyOP•8mo ago

Updated the code and made it a bit generic:

// ---- query builder

/**
 * Special thanks goes to https://github.com/igalklebanov
 * for solving our edge case issue with multiple schematas <3
 * https://www.answeroverflow.com/m/1227929774144618598
 */
import type { SelectExpression } from "kysely"
import {
  sql,
  DummyDriver,
  Kysely,
  PostgresAdapter,
  PostgresIntrospector,
  PostgresQueryCompiler,
} from "kysely"

export const initDb = <T>() => {
  return new Kysely<T>({
    dialect: {
      createAdapter() {
        return new PostgresAdapter()
      },
      createDriver() {
        return new DummyDriver()
      },
      createIntrospector(db: Kysely<T>) {
        return new PostgresIntrospector(db)
      },
      createQueryCompiler() {
        return new PostgresQueryCompiler()
      },
    },
  })
}

interface KyselyDatabase<TableDefinition> {
  tablename: TableDefinition
}

interface GenerateQueryProps<TableDefinition> {
  catalog: string
  schema: string
  table: string
  selectFields: SelectExpression<
    KyselyDatabase<TableDefinition>,
    keyof KyselyDatabase<TableDefinition>
  >[]
}

export function generateQuery<TableDefinition>(
  props: GenerateQueryProps<TableDefinition>,
) {
  const db = initDb<KyselyDatabase<TableDefinition>>()

  const query = db
    .selectFrom(
      sql.id(
        props.catalog,
        props.schema,
        props.table,
      ) as unknown as "tablename",
    )
    .select(props.selectFields)

  return query.compile()
}


// ---- vitest

import { describe, test } from "vitest"
import { generateQuery } from "../src"

interface DummyTableDefinition {
  stringField: string
  numberField: number
  boolField: boolean
}

describe("Query Builder", () => {
  test("simple", () => {
    const query = generateQuery<DummyTableDefinition>({
      catalog: "hive",
      schema: "tier1_dummy_lake",
      table: "dummy_table",
      selectFields: ["stringField"],
    })
  })
})

// ---- query builder

/**
 * Special thanks goes to https://github.com/igalklebanov
 * for solving our edge case issue with multiple schematas <3
 * https://www.answeroverflow.com/m/1227929774144618598
 */
import type { SelectExpression } from "kysely"
import {
  sql,
  DummyDriver,
  Kysely,
  PostgresAdapter,
  PostgresIntrospector,
  PostgresQueryCompiler,
} from "kysely"

export const initDb = <T>() => {
  return new Kysely<T>({
    dialect: {
      createAdapter() {
        return new PostgresAdapter()
      },
      createDriver() {
        return new DummyDriver()
      },
      createIntrospector(db: Kysely<T>) {
        return new PostgresIntrospector(db)
      },
      createQueryCompiler() {
        return new PostgresQueryCompiler()
      },
    },
  })
}

interface KyselyDatabase<TableDefinition> {
  tablename: TableDefinition
}

interface GenerateQueryProps<TableDefinition> {
  catalog: string
  schema: string
  table: string
  selectFields: SelectExpression<
    KyselyDatabase<TableDefinition>,
    keyof KyselyDatabase<TableDefinition>
  >[]
}

export function generateQuery<TableDefinition>(
  props: GenerateQueryProps<TableDefinition>,
) {
  const db = initDb<KyselyDatabase<TableDefinition>>()

  const query = db
    .selectFrom(
      sql.id(
        props.catalog,
        props.schema,
        props.table,
      ) as unknown as "tablename",
    )
    .select(props.selectFields)

  return query.compile()
}


// ---- vitest

import { describe, test } from "vitest"
import { generateQuery } from "../src"

interface DummyTableDefinition {
  stringField: string
  numberField: number
  boolField: boolean
}

describe("Query Builder", () => {
  test("simple", () => {
    const query = generateQuery<DummyTableDefinition>({
      catalog: "hive",
      schema: "tier1_dummy_lake",
      table: "dummy_table",
      selectFields: ["stringField"],
    })
  })
})

the test shows how I want to implement it later in the graphql resolver selectFields are "type-safe" and you can only select the fields which are defined in the provided table definition hope this is not bad practices

Gaming

Programming

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )