Am I Releasing my SQL Connection Correctly?

I have an app that does many API calls, but I release each and every connection like this?

app.put("/interview/create/questions/:lastEmployeeId", function (req, res) {
  console.log("It is getting to the route");
  const employee_id = req.body.lastEmployeeId;
  const tableName = req.body.table;
  connection.getConnection(function (err, connection) {
    connection.query(
      `INSERT INTO ${tableName} (employee_id)
            VALUES (?)`,
      [employee_id],
      function (error, results, fields) {
        if (error) throw error;
        res.json(results);
        console.log(`Interview has been created`);
      }
    );
    connection.release();
  });
});

Is this the correct way, because I’m getting a lot of inconsistent 502 and 503 errors. It feels to me like maybe it’s running out of resources. The paths are correct, because sometimes they work, and sometimes they don’t. It’s random, so I can’t figure out what to do.

Doesn’t the connection.release() free up the resources? I currently have the max connections set to 50, which should be more than I need because only one person at a time will be using this, and there is a method where it makes 8 calls. I wouldn’t mind some help refactoring this, but I still need this many calls I believe because I need it to create rows in 8 different tables. This long method below here is just taking a value entered in one table, and entering it as a reference in the 8 other tables.

createQuestions() {
    fetch(
      API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
      {
        method: "PUT",
        body: JSON.stringify({
          lastEmployeeId: this.state.lastEmployeeId,
          table: "audit_general",
        }),
        headers: { "Content-Type": "application/json" },
      }
    )
      .then((res) => {
        if (!res.ok) {
          throw new Error();
        }
        return res.json();
      })
      .then((data) => console.log(data))
      .catch((err) => console.log(err))
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_culture",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_performance",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_policies",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_risk",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_strategy",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_rewards",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_workforce",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      );
  }

1 thought on “Am I Releasing my SQL Connection Correctly?”

  1. You’re releasing the connection before the query completes. This needs to be addressed:

    app.put("/interview/create/questions/:lastEmployeeId", function (req, res) {
      console.log("It is getting to the route");
      const employee_id = req.body.lastEmployeeId;
      const tableName = req.body.table;
    
      connection.getConnection(function (err, connection) {
        if (err) {
          res.statusCode = 500;
          res.send('DB connection could not be acquired.');
          return;
        }
    
        connection.query(
          `INSERT INTO ${tableName} (employee_id)
                VALUES (?)`,
          [ employee_id ],
          function (error, results, fields) {
            // throw here does nothing useful, it's inside a callback and
            // the calling function may have terminated ages ago. You need
            // to handle this error here and now.
            if (error) {
              res.statusCode = 500;
              res.send('Nope!');
              return;
            }
    
            res.json(results);
            console.log(`Interview has been created`);
    
            // Now you can release the handle
            connection.release();
          }
        );
        
        // Code here runs before the query can complete.
      });
    });
    

    Think of your asynchronous program flow like this:

    connection.getConnection(..., cbGC) executes and returns
    
    ** Long time passes
    
    cbGC executes -> connection.query(..., cbQ) executes and returns
    
    ** Tiny eternity elapses
    
    cbQ executes!
    

    The time interval between calling a function that takes a callback and that callback actually executing can be significant, seconds or more, which means the calling function is not only dead, it is ancient history, probably garbage collected.

    You need to organize your code around this principle of nesting anything that depends on that sequencing.

    Reply

Leave a Comment