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();