[ feature request + huge bug ] not having to await connection in order to use async syntax with queries

[TamirCode]

Its forcing me to put my entire node program in async function main() {} just because for some reason const connection = await mysql.createConnection must have await in it, in order to use async in queries.

This seems extremely unnecessary, just because i want to use await in queries that might not run directly after, i shouldnt need to await connection.

Please dont require the use of async for the connection in order for async in queries to work, just like with normal promises, there is no difference between them other than syntax sugar.

Here is a code example

async function main() {
   const exp = require("express")
   const cors = require("cors")
   const mysql = require('mysql2/promise");

   const app = exp()

   const connection = await mysql.createConnection({
   	host: 'localhost',
   	user: 'root',
   	password: "",
   	database: 'my_database'
   })

   app.use(exp.json())
   app.use(cors())

   app.get("/test", async (req, res) => {
   	try {
   		const [ rows ]= await connection.execute(`SELECT * FROM users WHERE username = "e"`)
   		res.send({ data: rows })
   	} catch (error) {
   		console.log(error)
   		res.status(500).send({ err: "fail" })
   	}
   })

   app.listen(1000, () => console.log("localhost:1000"))
}
main()

[TamirCode]

I want my async queries to work without having to put my entire program in async function just to make the connection async.

it makes no sense why normal promise queries dont have to do this but async syntax queries have to.

[TamirCode]

I also cant create an alias for connection.execute or even import it from other files since everything is in async function it ruins the entire program . If I want to put my database configuration in another file, i have to put that file in async function, and then i cant transfer the connection.execute method because its local

[TamirCode]

ALSO

its not working when using question marks in async queries (at least in my code above) . major bug

please test it and see

no work:   const [ rows ] = await connection.execute(`SELECT * FROM users WHERE username = "?"`, "e")
no work:   const [ rows ] = await connection.execute(`SELECT * FROM users WHERE username = '?'`, "e")
works:     const [ rows ] = await connection.execute(`SELECT * FROM users WHERE username = "e"`)

tried with variables too.

[flipperlite]

This is the code I've written to reuse a connection pool and it cleans up the code a lot. In this code, the main function is not async. The query automatically releases its connection after use (according to the README.md). Copy the code into the designated files into the same directory. To run it, npm install and then npm start.

// index.js
const espress = require("express")
const { query } = require("./db")

function main() {
  const app = espress()

  app.use(espress.json())

  app.get("/indexplaceholders", async (req, res) => {
    try {
      // rigid with order of ? placeholders to your array indexes
      const [rows] = await query(`SELECT * FROM users WHERE username = ?, lastname = ?`,
        [
          'whatever',
          'smith'
        ])
      res.send({
        data: rows
      })
    } catch (error) {
      console.log(error)
      res.status(500).send({
        err: "fail"
      })
    }
  })

  app.get("/namedplaceholders", async (req, res) => {
    try {
      // gives you flexibility to move placeholders with any order
      const [rows] = await query(`SELECT * FROM users WHERE username = :username, lastname = :lastname`,
        {
          lastname: 'smith',
          username: 'whatever',
        })
      res.send({
        data: rows
      })
    } catch (error) {
      console.log(error)
      res.status(500).send({
        err: "fail"
      })
    }
  })

  app.listen(1000, () => console.log("localhost:1000"))
}
main()

// db.js
const mysql = require('mysql2/promise')

let _pool // one pool per app

async function pool() {
  if (_pool) return _pool

  _pool = await mysql.createPool({
    host: 'localhost',
    user: '',
    password: '',
    database: '',
    waitForConnections: true,
    namedPlaceholders: true,
    connectionLimit: 10,
    queueLimit: 0
  });

  return _pool
}

async function query(sql, values) {
  return await (await pool()).query(sql, values)
}

module.exports = {
  query
}

// package.json
{
  "name": "example_connection_pool",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "start": "nodemon index.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.3",
    "mysql2": "^2.3.3"
  },
  "devDependencies": {
    "nodemon": "^2.0.15"
  }
}

[wesgarland]

top-level await is available in Node.js as of version 14.8. Version 12.xx will be EOL next month.

[sidorares]

@TamirCode no issues for me with ? inside a string. Example:

test.mjs

import mysql from "mysql2/promise"

const conn = await mysql.createConnection({ 
    host: "localhost",
    port: 3306,
    user: 'root',
    password: 'test'
});

const [rows] = await conn.execute("SELECT '?' AS solution");
console.log(rows);

prints [ { solution: '?' } ]

Also note .mjs extension - to be able to use top level await you need node to treat your script as ESM module. See https://www.stefanjudis.com/today-i-learned/top-level-await-is-available-in-node-js-modules/ for more info