Knex.connection timeouts when providing a mysql2/promise connection + no TS types

[clemgrim]

Environment

Knex version: 2.1.0 Database + version: MySQL 8.0 (docker container) OS: Docker desktop on Mac OSX (12.3.1)

Select applicable template from below. If issue is about oracledb support, tag @ atiertant. For MSSql tag @ smorey2. Rest of dialects don't need tags. If issue is about TypeScript definitions, tag @lorefnon.

Bug

1. Explain what kind of behaviour you are getting and how you think it should do

2 things:

• There are no types for knex.connection() function
• When I try to pass a connection created by mysql2/promise to knex.connection() function to run a simple query, I get a timeout.

2. Error message None, the query simply timeouts. The query is valid, I tried with an invalid query and I get the expected error message from MySQL, which is weird. If I debug the query with .toSQL or .toString I get the expected query.

3. Reduced test code, for example in https://npm.runkit.com/knex or if it needs real database connection to MySQL or PostgreSQL, then single file example which initializes needed data and demonstrates the problem.

Simplified version:

import mysql from "mysql2/promise";
import knex from "knex";

const pool = mysql.createPool({ ... });
const connection = await pool.getConnection();

const db = await knex({
    client: "mysql2"
});

const results = await db
    .connection(connection)
    .select("*")
    .from("test")
    .where({ id: 1 });

I get a timeout with this, it works if I remove .connection(connection) and provide the mysql connection config to knex, but that's not what I want. I already have the connection pool and I only want to use knex as a query builder.

[swateek]

Just putting this here for others who might come in, I am on latest lts/gallium (v16.5.1) and my client is mssql so I had to install latest knex 2.1.0 and latest tedious 14.5.0

This worked for me.

[clemgrim]

Just putting this here for others who might come in, I am on latest lts/gallium (v16.5.1) and my client is mssql so I had to install latest knex 2.1.0 and latest tedious 14.5.0

This worked for me.

I just tested again, I have a simple NextJS application (version 12.1.6):

here's the handler:

// src/pages/api/test.ts
import knex from "knex";
import mysql from "mysql2/promise";
import { NextApiHandler } from "next";

const handler: NextApiHandler = async (req, res) => {
  const pool = mysql.createPool({
    host: "localhost",
    user: "admin",
    password: "password",
    database: "test",
  });
  const connection = await pool.getConnection();

  try {
    const qb = await knex({
      client: "mysql2"
    });

    const results = await qb
      .connection(connection)
      .select("*")
      .from("user")
      .where({ id: 1 });

    res.json(results);
  } catch (e) {
    console.log(e);
    res.status(500).json({});
  } finally {
    connection.release();
  }
};

export default handler;

Command:

curl localhost:3000/api/test

the endpoint does not respond.

I also tried without the pool:

import knex from "knex";
import mysql from "mysql2/promise";
import { NextApiHandler } from "next";

const handler: NextApiHandler = async (req, res) => {
  const connection = await mysql.createConnection({
    host: "localhost",
    user: "admin",
    password: "password",
    database: "test",
  });

  try {
    const qb = await knex({
      client: "mysql2",
    });

    const results = await qb
      .connection(connection)
      .select("*")
      .from("user")
      .where({ id: 1 });

    res.json(results);
  } catch (e) {
    console.log(e);
    res.status(500).json({});
  }
};

export default handler;

Same result.

But it works if I create a connection that is not a promise:

import knex from "knex";
import mysql from "mysql2";
import { NextApiHandler } from "next";

const handler: NextApiHandler = async (req, res) => {
  const connection = mysql.createConnection({
    host: "localhost",
    user: "admin",
    password: "password",
    database: "test",
  });

  try {
    const qb = await knex({
      client: "mysql2",
    });

    const results = await qb
      .connection(connection)
      .select("*")
      .from("user")
      .where({ id: 1 });

    res.json(results);
  } catch (e) {
    console.log(e);
    res.status(500).json({});
  }
};

export default handler;

[clemgrim]

More details if you're interested:

node version: v16.13.1
npm version: 8.1.2
mysql2 version: 2.3.3
docker desktop version: 4.8.2
docker version: 20.10.14

[azivkovi]

Were you able to resolve this issue? I am having the same problem as of recently.