cheekybuddha Comments - Answer Overflow

cheekybuddha

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

How do I specify a MySQL index hint?

Thank you, and apologies for my impatience! Unfortunately the generated SQL in not correct (for MySQL), or at least I can't get it to work. With the example in your link:

const rows = await db
  .selectFrom(sql<Person>`person use index foobar`.as('person'))
  .innerJoin("pet", "owner_id", "person.id")
  .where("first_name", "=", sql.lit("Jennifer"))
  .where("species", "=", 'cat')
  .select(["first_name", "pet.name as pet_name"])
  .execute();

const rows = await db
  .selectFrom(sql<Person>`person use index foobar`.as('person'))
  .innerJoin("pet", "owner_id", "person.id")
  .where("first_name", "=", sql.lit("Jennifer"))
  .where("species", "=", 'cat')
  .select(["first_name", "pet.name as pet_name"])
  .execute();

produces:

SELECT
  `first_name`,
  `pet`.`name` AS `pet_name`
FROM
  person
USE INDEX foobar AS `person`
INNER JOIN `pet` ON `owner_id` = `person`.`id`
WHERE
  `first_name` = 'Jennifer'
  AND `species` = ?

SELECT
  `first_name`,
  `pet`.`name` AS `pet_name`
FROM
  person
USE INDEX foobar AS `person`
INNER JOIN `pet` ON `owner_id` = `person`.`id`
WHERE
  `first_name` = 'Jennifer'
  AND `species` = ?

however I think it needs to be:

-- ...
FROM
  person AS `person`
USE INDEX (foobar)
-- ...

-- ...
FROM
  person AS `person`
USE INDEX (foobar)
-- ...

I can add the parentheses in the sql`` call, but adding the alias in there too causes typescript to have a fit (No overload matches this call.) Am I missing something? TIA

5 replies

KKysely

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

How do I specify a MySQL index hint?

OK, I guess this is not possible in kysely. I solved it by creating a view on the server, which makes the kysely code a lot simpler! Many thanks for a great bit of software!

5 replies

KKysely

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

Typescript Error when creating MySQL db pool

Tried to add screenshots, but it failed. Will try again later.

19 replies

KKysely

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

Typescript Error when creating MySQL db pool

Actually, something is strange - returning .pool gets rid of the error, but it is not listed as a property in the intellisense. Like I said, I'm still new to typescript, so I don't know whether this is expected.

19 replies

KKysely

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

Typescript Error when creating MySQL db pool

OK, keep us posted - will be interesting to see what you discover. Thanks for this, and Kysely!! 👍

19 replies

KKysely

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

Typescript Error when creating MySQL db pool

Don't you love it when you search for the solution to a problem for a couple of days and find the answer immediately after posting asking for assistance!! 😖 😳 I have found the answer here: https://lucia-auth.com/guidebook/kysely Turns out that createPool() does not return the pool - you have to access its pool property:

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

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

Thanks for looking in 👍

19 replies

KKysely

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

Typescript Error when creating MySQL db pool

// ./.svelte-kit/tsconfig.json
{
    "compilerOptions": {
        "paths": {
            "$lib": [
                "../src/lib"
            ],
            "$lib/*": [
                "../src/lib/*"
            ]
        },
        "rootDirs": [
            "..",
            "./types"
        ],
        "importsNotUsedAsValues": "error",
        "isolatedModules": true,
        "preserveValueImports": true,
        "lib": [
            "esnext",
            "DOM",
            "DOM.Iterable"
        ],
        "moduleResolution": "node",
        "module": "esnext",
        "target": "esnext",
        "ignoreDeprecations": "5.0"
    },
    "include": [
        "ambient.d.ts",
        "./types/**/$types.d.ts",
        "../vite.config.ts",
        "../src/**/*.js",
        "../src/**/*.ts",
        "../src/**/*.svelte",
        "../tests/**/*.js",
        "../tests/**/*.ts",
        "../tests/**/*.svelte"
    ],
    "exclude": [
        "../node_modules/**",
        "./[!ambient.d.ts]**",
        "../src/service-worker.js",
        "../src/service-worker.ts",
        "../src/service-worker.d.ts"
    ]
}

// ./.svelte-kit/tsconfig.json
{
    "compilerOptions": {
        "paths": {
            "$lib": [
                "../src/lib"
            ],
            "$lib/*": [
                "../src/lib/*"
            ]
        },
        "rootDirs": [
            "..",
            "./types"
        ],
        "importsNotUsedAsValues": "error",
        "isolatedModules": true,
        "preserveValueImports": true,
        "lib": [
            "esnext",
            "DOM",
            "DOM.Iterable"
        ],
        "moduleResolution": "node",
        "module": "esnext",
        "target": "esnext",
        "ignoreDeprecations": "5.0"
    },
    "include": [
        "ambient.d.ts",
        "./types/**/$types.d.ts",
        "../vite.config.ts",
        "../src/**/*.js",
        "../src/**/*.ts",
        "../src/**/*.svelte",
        "../tests/**/*.js",
        "../tests/**/*.ts",
        "../tests/**/*.svelte"
    ],
    "exclude": [
        "../node_modules/**",
        "./[!ambient.d.ts]**",
        "../src/service-worker.js",
        "../src/service-worker.ts",
        "../src/service-worker.d.ts"
    ]
}

19 replies

KKysely

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

Typescript Error when creating MySQL db pool

Sure!

// tsconfig.json
{
    "extends": "./.svelte-kit/tsconfig.json",
    "compilerOptions": {
        "allowJs": true,
        "checkJs": true,
        "esModuleInterop": true,
        "forceConsistentCasingInFileNames": true,
        "resolveJsonModule": true,
        "skipLibCheck": true,
        "sourceMap": true,
        "strict": true
    }
    // Path aliases are handled by https://kit.svelte.dev/docs/configuration#alias
    //
    // If you want to overwrite includes/excludes, make sure to copy over the relevant includes/excludes
    // from the referenced tsconfig.json - TypeScript does not merge them in
}

// tsconfig.json
{
    "extends": "./.svelte-kit/tsconfig.json",
    "compilerOptions": {
        "allowJs": true,
        "checkJs": true,
        "esModuleInterop": true,
        "forceConsistentCasingInFileNames": true,
        "resolveJsonModule": true,
        "skipLibCheck": true,
        "sourceMap": true,
        "strict": true
    }
    // Path aliases are handled by https://kit.svelte.dev/docs/configuration#alias
    //
    // If you want to overwrite includes/excludes, make sure to copy over the relevant includes/excludes
    // from the referenced tsconfig.json - TypeScript does not merge them in
}

Full error:

Type '() => Promise<Pool>' is not assignable to type 'MysqlPool | (() => Promise<MysqlPool>)'.
  Type '() => Promise<Pool>' is not assignable to type '() => Promise<MysqlPool>'.
    Type 'Promise<Pool>' is not assignable to type 'Promise<MysqlPool>'.
      Type 'Pool' is not assignable to type 'MysqlPool'.
        Types of property 'getConnection' are incompatible.
          Type '(callback: (err: any, connection: PoolConnection) => any) => void' is not assignable to type '(callback: (error: unknown, connection: MysqlPoolConnection) => void) => void'.
            Types of parameters 'callback' and 'callback' are incompatible.
              Types of parameters 'connection' and 'connection' are incompatible.
                Property 'query' is missing in type 'PoolConnection' but required in type 'MysqlPoolConnection'.ts(2322)
mysql-dialect-config.d.ts(34, 5): 'query' is declared here.
mysql-dialect-config.d.ts(15, 5): The expected type comes from property 'pool' which is declared here on type 'MysqlDialectConfig'
(property) MysqlDialectConfig.pool: MysqlPool | (() => Promise<MysqlPool>)
A mysql2 Pool instance or a function that returns one.

If a function is provided, it's called once when the first query is executed.

https://github.com/sidorares/node-mysql2#using-connection-pools

Type '() => Promise<Pool>' is not assignable to type 'MysqlPool | (() => Promise<MysqlPool>)'.
  Type '() => Promise<Pool>' is not assignable to type '() => Promise<MysqlPool>'.
    Type 'Promise<Pool>' is not assignable to type 'Promise<MysqlPool>'.
      Type 'Pool' is not assignable to type 'MysqlPool'.
        Types of property 'getConnection' are incompatible.
          Type '(callback: (err: any, connection: PoolConnection) => any) => void' is not assignable to type '(callback: (error: unknown, connection: MysqlPoolConnection) => void) => void'.
            Types of parameters 'callback' and 'callback' are incompatible.
              Types of parameters 'connection' and 'connection' are incompatible.
                Property 'query' is missing in type 'PoolConnection' but required in type 'MysqlPoolConnection'.ts(2322)
mysql-dialect-config.d.ts(34, 5): 'query' is declared here.
mysql-dialect-config.d.ts(15, 5): The expected type comes from property 'pool' which is declared here on type 'MysqlDialectConfig'
(property) MysqlDialectConfig.pool: MysqlPool | (() => Promise<MysqlPool>)
A mysql2 Pool instance or a function that returns one.

If a function is provided, it's called once when the first query is executed.

https://github.com/sidorares/node-mysql2#using-connection-pools

19 replies

KKysely

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

Typescript Error when creating MySQL db pool

My dependencies are:

$ pnpm ls --depth=0
Legend: production dependency, optional only, dev only

[email protected] /home/dm/Documents/websites/thdt_admin (PRIVATE)

dependencies:
@lucia-auth/adapter-mysql 2.0.0
kysely 0.26.1
lucia 2.0.0
mysql2 3.6.0

devDependencies:
@csstools/postcss-global-data 2.0.1      eslint 8.46.0                            postcss-preset-env 9.1.1                 sveltekit-superforms 1.5.0               
@sveltejs/adapter-auto 2.1.0             eslint-config-prettier 8.10.0            prettier 3.0.1                           tslib 2.6.1                              
@sveltejs/kit 1.22.4                     eslint-plugin-svelte 2.32.4              prettier-plugin-svelte 3.0.3             typescript 5.1.6                         
@typescript-eslint/eslint-plugin 5.62.0  kysely-codegen 0.10.1                    svelte 4.1.2                             vite 4.4.9                               
@typescript-eslint/parser 5.62.0         postcss 8.4.27                           svelte-check 3.4.6                       zod 3.21.4                               
autoprefixer 10.4.14                     postcss-csso 6.0.1                       svelte-preprocess 5.0.4

$ pnpm ls --depth=0
Legend: production dependency, optional only, dev only

[email protected] /home/dm/Documents/websites/thdt_admin (PRIVATE)

dependencies:
@lucia-auth/adapter-mysql 2.0.0
kysely 0.26.1
lucia 2.0.0
mysql2 3.6.0

devDependencies:
@csstools/postcss-global-data 2.0.1      eslint 8.46.0                            postcss-preset-env 9.1.1                 sveltekit-superforms 1.5.0               
@sveltejs/adapter-auto 2.1.0             eslint-config-prettier 8.10.0            prettier 3.0.1                           tslib 2.6.1                              
@sveltejs/kit 1.22.4                     eslint-plugin-svelte 2.32.4              prettier-plugin-svelte 3.0.3             typescript 5.1.6                         
@typescript-eslint/eslint-plugin 5.62.0  kysely-codegen 0.10.1                    svelte 4.1.2                             vite 4.4.9                               
@typescript-eslint/parser 5.62.0         postcss 8.4.27                           svelte-check 3.4.6                       zod 3.21.4                               
autoprefixer 10.4.14                     postcss-csso 6.0.1                       svelte-preprocess 5.0.4

I created the DB types using kysely-codegen:

$ npx kysely-codegen
• Loaded environment variables from .env file.
• No dialect specified. Assuming 'mysql'.
• Introspecting database...
✓ Introspected 29 tables and generated ./node_modules/kysely-codegen/dist/db.d.ts in 408ms.

$ npx kysely-codegen
• Loaded environment variables from .env file.
• No dialect specified. Assuming 'mysql'.
• Introspecting database...
✓ Introspected 29 tables and generated ./node_modules/kysely-codegen/dist/db.d.ts in 408ms.

I'm still very new with typescript - how can I get the type MysqlPool to map to Pool ? Or am I doing something else wrong? TIA for any assistance, d

19 replies

Gaming

Programming