A more complete readme.md

[suchislife801]

Hello. I'm really grateful you ported this library to Deno. What a gem!

I wanted to propose a more complete readme.md for new & existing devs who run into mysql2 for Deno.

mysql2

MySQL 8 client for Deno with focus on performance. Supports prepared statements, non-utf8 encodings, binary log protocol, compression and much more.

Forked from https://github.com/sidorares/node-mysql2

To-Do app Queryflow Example

1. Install MySQL 8 or rent a MySQL Managed Database from a cloud service.
2. Create a database with the following SQL query

CREATE DATABASE IF NOT EXISTS `devdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT ENCRYPTION = 'N';

3. Run example

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

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

const pool = mysql2.createPool({
  host: "127.0.0.1",
  port: 3306,
  user: "test_user",
  password: "test_password",
  database: "nest_database",
  connectionLimit: 4,
  timezone: "+00:00" // Sync app timezone with MySQL server UTC timezone
});

/* CREATE temp table tblTodos in database memory */
const sqlCREATE1 = 
`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 = 
`SELECT 
  * 
FROM 
  tblTodos;`;

/* INSERT 1 adds a Pending todo record to the table */
const sqlINSERT1 = 
`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 = 
`SELECT 
  * 
FROM 
  tblTodos 
WHERE 
  todoStatus = 'Pending';`;

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

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

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

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

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

const connection = await pool.getConnection();

try {

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

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

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

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

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

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

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

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

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

} catch(error) {

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

}

await pool.end();

Output:

Table tblToDos created.
Table tblToDos contains 0 records.
1 record(s) inserted. id: 2001
[
  {
    id: 2001,
    userId: 1001,
    todoCreated: 2022-09-09T21:24:24.000Z,
    todoModified: 2022-09-09T21:24:24.000Z,
    todoStatus: "Pending",
    todoTitle: "Shopping List #1",
    todoBody: "Apples & Oranges"
  }
]
1 record(s) updated.
[
  {
    id: 2001,
    userId: 1001,
    todoCreated: 2022-09-09T21:24:24.000Z,
    todoModified: 2022-09-09T21:24:24.000Z,
    todoStatus: "Complete",
    todoTitle: "Shopping List #1",
    todoBody: "Apples & Oranges"
  }
]