virk Posts - Answer Overflow

virk

•Created by virk on 4/11/2024 in #help

Need help improving a custom helper

My dopamine with Kysely is quite high right now, so I am experimenting all sorts of things to create custom helpers for common use-cases. Here's an example of a working demo https://old.kyse.link/?p=s&i=qkTHjgwIuyb847wjoRd3 Following is a trimmed snippet from the above mentioned demo, for which I need help.

function month<DB, TB extends keyof DB & string>(eb: ExpressionBuilder<DB, TB>, col: {
  [K in keyof DB[TB]]: DB[TB][K] extends Date | { __select__: Date } ? K : never
}[keyof DB[TB]]) {
  return sql<number>`EXTRACT(MONTH from ${sql.ref('created_at')}::date)`
}

const users = await db
  .selectFrom('users')
  .selectAll()
  .where((eb) => {
    return eb(month(eb, 'created_at'), '=', 11)
  })
  .select((eb) => {
    return month(eb, 'created_at').as('creationMonth')
  })
  .executeTakeFirst()

function month<DB, TB extends keyof DB & string>(eb: ExpressionBuilder<DB, TB>, col: {
  [K in keyof DB[TB]]: DB[TB][K] extends Date | { __select__: Date } ? K : never
}[keyof DB[TB]]) {
  return sql<number>`EXTRACT(MONTH from ${sql.ref('created_at')}::date)`
}

const users = await db
  .selectFrom('users')
  .selectAll()
  .where((eb) => {
    return eb(month(eb, 'created_at'), '=', 11)
  })
  .select((eb) => {
    return month(eb, 'created_at').as('creationMonth')
  })
  .executeTakeFirst()

Is it possible for me use the month helper without passing it the eb instance explicitly? I know for the most part the answer is NO. But still want to check my luck and see if there is something I am unaware of.

7 replies

KKysely

•Created by virk on 4/11/2024 in #help

Using raw SQL with `or` where

I have been using the raw SQL template tag to work with certain JSON columns and wanted to add a orWhere clause using it. Here is a simple example of what I am trying to achieve.

const result = await db
  .selectFrom('users')
  .where((eb) => {
    return eb.or([
      sql`address ->> 'street' = ${street}`,
      sql`address ->> 'street' = ${street1}`
    ])
  })
  .executeTakeFirst()

const result = await db
  .selectFrom('users')
  .where((eb) => {
    return eb.or([
      sql`address ->> 'street' = ${street}`,
      sql`address ->> 'street' = ${street1}`
    ])
  })
  .executeTakeFirst()

I can pass sql literal to the where clause directly, but in this case I want to write or where

8 replies

KKysely

•Created by virk on 4/8/2024 in #help

Solved: Exporting query builder classes

I saw this PR https://github.com/kysely-org/kysely/pull/763 where you guys have decided not to export classes as they are private. Is there any specific reason for which you don't want to make these classes public? Because if you change the public API of these classes, then anyways the query builder will be impacted by the change.

7 replies

KKysely

•Created by virk on 11/30/2023 in #help

Guidance to create a generic wrapper over Kysely

I am trying to create a generic authentication wrapper over Kysely. The idea is - You can pass some config to this function, which includes a reference to Kysely instance, a database table name and an array of column names. - The function will return an authenticator instance you can use to perform user lookups during Login. The internals of this function are not important for this discussion. This is what I have so far. You can copy/paste the following code to run it yourself.

import { Pool } from 'pg'
import { AnyColumn, ColumnType, Generated, Kysely, PostgresDialect } from 'kysely'

/**
 * Interfaces for the table and the database
 */
interface UserTable {
  id: Generated<number>
  email: string
  username: string
  password: string
  created_at: ColumnType<Date, string | undefined, never>
}
interface Database {
  users: UserTable
}

/**
 * PostgreSQL dialect and kysely instance
 */
const dialect = new PostgresDialect({ pool: new Pool(poolConfig) })
const db = new Kysely<Database>({ dialect })

/**
 * Authenticator function that need kysely instance
 * and an unknown table + columns
 */
function createAuthenticator<
  DB,
  TB extends keyof DB & string,
  Columns extends AnyColumn<DB, TB>
>(config: {
  db: Kysely<DB>
  table: TB,
  uids: Columns[]
}) {
  config
      .db
      .selectFrom(config.table)
      // The where clause gives an error
      .where(config.uids[0], '=', 'foobar')
}

/**
 * Using function to create authenticator
 */
createAuthenticator({
  db: db,
  table: 'users',
  uids: ['email'],
})

import { Pool } from 'pg'
import { AnyColumn, ColumnType, Generated, Kysely, PostgresDialect } from 'kysely'

/**
 * Interfaces for the table and the database
 */
interface UserTable {
  id: Generated<number>
  email: string
  username: string
  password: string
  created_at: ColumnType<Date, string | undefined, never>
}
interface Database {
  users: UserTable
}

/**
 * PostgreSQL dialect and kysely instance
 */
const dialect = new PostgresDialect({ pool: new Pool(poolConfig) })
const db = new Kysely<Database>({ dialect })

/**
 * Authenticator function that need kysely instance
 * and an unknown table + columns
 */
function createAuthenticator<
  DB,
  TB extends keyof DB & string,
  Columns extends AnyColumn<DB, TB>
>(config: {
  db: Kysely<DB>
  table: TB,
  uids: Columns[]
}) {
  config
      .db
      .selectFrom(config.table)
      // The where clause gives an error
      .where(config.uids[0], '=', 'foobar')
}

/**
 * Using function to create authenticator
 */
createAuthenticator({
  db: db,
  table: 'users',
  uids: ['email'],
})

The .where(config.uids[0]) method call gives a type error saying, "The Columns are not assignable to the input accepted by the where method". So, I need some guidance on which approach to take for this use case.

10 replies

Gaming

Programming