0

I've decided to start using mysql2 library lately and created the following example to illustrate what currently works.

A MySQL tempt table is created, a series of queries are run in parallel and then it terminates.

I then found this article MySQL Limits on Digital Ocean.

Specifically the bullet point referring to:

Connection pooling is not supported for MySQL.

My question is, should I use mysql.createConnection() instead of mysql.createPool()? I ask because, both of them work just fine.

// deno run --allow-env --allow-net todo.ts

import * as mysql2 from "https://deno.land/x/mysql2/mod.ts";

const pool = mysql2.createPool({
  host: "db.ondigitalocean.com",
  port: 12345,
  user: "user1",
  password: "letmein",
  database: "devdb",
  connectionLimit: 10,
  timezone: "+00:00" // Development
  // timezone: "-06:00" // Production
});

/* CREATE temp table tblTodos in database memory */
const sqlCREATE1: string = 
`CREATE TEMPORARY TABLE IF NOT EXISTS tblTodos (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  userId BIGINT UNSIGNED DEFAULT NULL,
  CHECK (userId >= 0),
  todoCreated TIMESTAMP DEFAULT NULL,
  todoModified TIMESTAMP DEFAULT NULL,
  todoStatus VARCHAR(16) DEFAULT NULL,
  todoTitle VARCHAR(128) DEFAULT NULL, 
  todoBody VARCHAR(1024) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE = INNODB
  AUTO_INCREMENT=2001
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;`;

/* SELECT 1 shows an empty table */
const sqlSELECT1: string = 
`SELECT 
  * 
FROM 
  tblTodos;`;

/* INSERT 1 adds a Pending todo record to the table */
const sqlINSERT1: string = 
`INSERT INTO tblTodos (
  id, userId,
  todoCreated, todoModified, todoStatus,
  todoTitle, todoBody
) 
VALUES 
  (
    NULL, 1001, 
    NOW(), NOW(), 'Pending', 
    'Shopping List #1', 'Apples & Oranges'
  );`;

/* SELECT 2 shows the Pending todo record */
const sqlSELECT2: string = 
`SELECT 
  * 
FROM 
  tblTodos 
WHERE 
  todoStatus = 'Pending';`;

/* UPDATE 1 changes todo status from Pending to Complete */
const sqlUPDATE1: string = 
`UPDATE 
  tblTodos 
SET 
  todoModified = NOW(), 
  todoStatus = 'Complete'
WHERE 
  id = 2001
  AND userId = 1001;`;

/* SELECT 3 shows the Complete todo record */
const sqlSELECT3: string = 
`SELECT 
  * 
FROM 
  tblTodos 
WHERE 
  todoStatus = 'Complete'
  AND userId = 1001;`;

/* DELETE 1 deletes todo from table */
const sqlDELETE1: string = 
`DELETE FROM 
  tblTodos 
WHERE 
  id = 2001
  AND userId = 1001;`;

/* SELECT 4 once again shows an empty table */
const sqlSELECT4: string = 
`SELECT 
  * 
FROM 
  tblTodos;`;

/* DROP 1 deletes table tblTodos from database */
const sqlDROP1: string = 
`DROP 
  TEMPORARY TABLE IF EXISTS tblTodos;`;

const connection = await pool.getConnection();

let create1Result, select1Result, insert1Result, select2Result, 
    update1Result, select3Result, delete1Result, select4Result, 
    drop1Result;

try {

  create1Result = await connection.execute(sqlCREATE1);
  if (create1Result) console.log("Table tblToDos created.");

  select1Result = await connection.execute(sqlSELECT1);
  if (select1Result) console.log("Table tblToDos contains", select1Result[0].length, "records.");

  insert1Result = await connection.execute(sqlINSERT1);
  if (insert1Result) console.log(insert1Result[0].affectedRows, "record(s) inserted.", "id:", insert1Result[0].insertId);

  select2Result = await connection.execute(sqlSELECT2);
  if (select2Result) console.log(select2Result[0]);

  update1Result = await connection.execute(sqlUPDATE1);
  if (update1Result) console.log(update1Result[0].affectedRows, "record(s) updated.");

  select3Result = await connection.execute(sqlSELECT3);
  if (select3Result) console.log(select3Result[0]);

  delete1Result = await connection.execute(sqlDELETE1);
  if (delete1Result) console.log(delete1Result[0].affectedRows, "record(s) deleted.");

  select4Result = await connection.execute(sqlSELECT4);
  if (select4Result) console.log("Table tblToDos contains", select1Result[0].length, "records.");

  drop1Result = await connection.execute(sqlDROP1);
  if (drop1Result) console.log("Table tblToDos droped.");

} catch(error) {

  // Complete MySQL error message.
  // console.log(`%cError: ${error.message}`, "color: #e53935");

  // Build a smaller MySQL error message.
  const errorPart1 = error.message.split(";")[0];
  const errorPart2 = error.message.split(" use ")[1];
  console.log(`%cError: ${errorPart1} ${errorPart2}`, "color: #e53935");

} finally {

  connection.release();

}

// Debug created datetime
console.log(select3Result[0][0].todoCreated.toLocaleString());

// End pool when web server shuts down.
await pool.end();
suchislife
  • 4,251
  • 10
  • 47
  • 78

0 Answers0