cheekybuddha Posts - Answer Overflow

cheekybuddha

•Created by cheekybuddha on 2/13/2024 in #help

How do I specify a MySQL index hint?

Hi, I am trying to build the following query (as a CTE, as part of a larger query), but I can not work out how I can pass the index hint (USE INDEX ...):

  SELECT 
    sc.synopsis_fk,
    'characters',
    JSON_ARRAYAGG(c.name)
  FROM synopsis_characters sc
  USE INDEX (idx_character_order)
  INNER JOIN characters c         
          ON sc.character_fk = c.id 
  GROUP BY 
    sc.synopsis_fk

  SELECT 
    sc.synopsis_fk,
    'characters',
    JSON_ARRAYAGG(c.name)
  FROM synopsis_characters sc
  USE INDEX (idx_character_order)
  INNER JOIN characters c         
          ON sc.character_fk = c.id 
  GROUP BY 
    sc.synopsis_fk

I tried using raw sql:

    .with(
      'chars',
      db => db
        .selectFrom(sql`synopsis_characters USE INDEX idx_character_order`).as('sc')
        .select([
          ...
        ])
    )

    .with(
      'chars',
      db => db
        .selectFrom(sql`synopsis_characters USE INDEX idx_character_order`).as('sc')
        .select([
          ...
        ])
    )

but I get typescript error:

No overload matches this call.
  The last overload gave the following error.
    Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'TableExpression<DB & { sections:
...

No overload matches this call.
  The last overload gave the following error.
    Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'TableExpression<DB & { sections:
...

I can get it to work if I build the whole complete query with sql`...`, but that brings about other issues such as how to use .stream() instead of sql`...`.execute(db) Any clues?

5 replies

KKysely

•Created by cheekybuddha on 8/8/2023 in #help

Typescript Error when creating MySQL db pool

Hi Everyone, I'm getting the following error when creating a database:

kysely Type '() => Promise<Pool>' is not assignable to type 'MysqlPool | (() => Promise<MysqlPool>)'

kysely Type '() => Promise<Pool>' is not assignable to type 'MysqlPool | (() => Promise<MysqlPool>)'

I'm using the following code:

// db.ts
import {
  Kysely,
  MysqlDialect,
}                     from 'kysely'
import { createPool } from 'mysql2'
import {
  DB_HOST,
  DB_PORT,
  DB_USER,
  DB_PASS,
  DB_NAME
}                     from '$env/static/private'
import type { DB }    from 'kysely-codegen'

export const mysqlPoolOpts = {
  database:           DB_NAME,
  host:               DB_HOST,
  port:               parseInt(DB_PORT),
  user:               DB_USER,
  password:           DB_PASS,
  waitForConnections: true,
  connectionLimit:    10,
  // maxIdle:            10, // max idle connections, the default value is the same as `connectionLimit`
  // idleTimeout:        60000, // idle connections timeout, in milliseconds, the default value 60000
  queueLimit:         0
}

export const db = new Kysely<DB>({
  dialect: new MysqlDialect({
    pool: async () => createPool(mysqlPoolOpts)
//   ^^ -- error here
  })
})

// db.ts
import {
  Kysely,
  MysqlDialect,
}                     from 'kysely'
import { createPool } from 'mysql2'
import {
  DB_HOST,
  DB_PORT,
  DB_USER,
  DB_PASS,
  DB_NAME
}                     from '$env/static/private'
import type { DB }    from 'kysely-codegen'

export const mysqlPoolOpts = {
  database:           DB_NAME,
  host:               DB_HOST,
  port:               parseInt(DB_PORT),
  user:               DB_USER,
  password:           DB_PASS,
  waitForConnections: true,
  connectionLimit:    10,
  // maxIdle:            10, // max idle connections, the default value is the same as `connectionLimit`
  // idleTimeout:        60000, // idle connections timeout, in milliseconds, the default value 60000
  queueLimit:         0
}

export const db = new Kysely<DB>({
  dialect: new MysqlDialect({
    pool: async () => createPool(mysqlPoolOpts)
//   ^^ -- error here
  })
})

(cont'd in following message ...)

19 replies

Gaming

Programming