2

I want to use the MYSQL2 promise API, and am struggling with the proper way to connect to the database.

Hoping for someone to weigh in on the correct way.

Edit for clarity: The issue is that mysql.createConnection() returns a promise, so when setting it equal to my connection variable so I can then make queries on it in the future, it is equal to the promise, and not the result of the promise. Thus the need for AWAIT or .then chain.

Option 1: Top level await by making the whole project a module.

import dotenv from 'dotenv'
dotenv.config()

import mysql from 'mysql2/promise.js'
import express from 'express'
import ejs from 'ejs'
import {faker} from '@faker-js/faker'
import path from 'path'
const app = express();

app.set("view engine", "ejs");
app.use(express.json())

const connection = await mysql.createConnection({
    host: "localhost",
    user: "root",
    password: process.env.SQL_PASSWORD,
    database: "joinus"
})
connection.connect()

app.get('/users', async(req,res,next) => {
    const query = await connection.query('SELECT * FROM users')
        res.send(query)
    })


app.listen(5000, () => {
    console.log("I hear you");
});

Option 2: Wrap the entire app in an async function main, and call that function to start execution of the app. This way I can await without converting to a module and using top level await.

if (process.env.NODE_env !== "production") {
    require("dotenv").config();
}
const mysql = require("mysql2/promise");
const express = require("express");
const ejs = require("ejs");
const { faker } = require('@faker-js/faker');
const path = require("path");
const app = express();

app.set("view engine", "ejs");
app.set("views", path.join(__dirname + "/views"));
app.use(express.json())


async function main() {
        const connection = await mysql.createConnection({
            host: "localhost",
            user: "root",
            password: process.env.SQL_PASSWORD,
            database: "joinus"
        })

    app.get('/users', async(req,res,next) => {
        const query = await connection.query('SELECT * FROM users')
            res.send(query)
        })


    app.listen(5000, () => {
        console.log("I hear you");
    });
}
main()

Option 3: Chain a then onto my connection creation and proceed as normal

if (process.env.NODE_env !== "production") {
    require("dotenv").config();
}
const mysql = require("mysql2/promise");
const express = require("express");
const ejs = require("ejs");
const { faker } = require("@faker-js/faker");
const path = require("path");
const app = express();

app.set("view engine", "ejs");
app.set("views", path.join(__dirname + "/views"));
app.use(express.json());

let connection;
mysql
.createConnection({
    host: "localhost",
    user: "root",
    password: process.env.SQL_PASSWORD,
    database: "joinus"
}).then((result) => connection = result)

app.get("/users", async (req, res, next) => {
    const query = await connection.query("SELECT * FROM users");
    res.send(query);
});

app.listen(5000, () => {
    console.log("I hear you");
});

Option 4: ???

Please guide a hobbyist/new coder towards the right path.

  • There should be enough time for the connection to stabilize before the first request is received. Are you not seeing that? I usually put stuff like that in a module so you can import it and then `db.query()` or something equivalent where `db` is the exported, fully-functional connection, often using "sync" calls to create a database *pool*. The pool itself handles queries and, if necessary, creating connections, all part of the same `await db.query()` call. – tadman Aug 09 '23 at 21:56
  • Option 2: Init your app, wait for db connection, and start the app. `mysql.createConnection().then(() => app.listen(5000));` – hoangdv Aug 10 '23 at 01:50
  • Editing my post for greater clarity. The issue is not that the DB isn't connecting before I make requests. The issue is that mysql.createConnection() returns a promise, so when setting it equal to my connection variable so I can then make queries on it in the future, it is equal to the promise, and not the result of the promise. Thus the need for AWAIT or .then chain. Just wasn't sure the right approach to take from the ones I listed. – northendben Aug 10 '23 at 04:40
  • What is the *problem* here? If this is a question about working options, but you want a review, then this is not the right place. – trincot Aug 10 '23 at 17:48

0 Answers0