Digital Ocean Managed MySQL DB Test Example. (Informational)

[suchislife801]

Hello. Just wanted to share a todo TEMP TABLE example for those who use Digital Ocean Managed MySQL databases.

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

import { Client } from "https://deno.land/x/mysql/mod.ts";

/* New Client Connection */
const client = await new Client().connect({
  hostname: "hostname_here",
  port: 0, // <-- port number here
  username: "user_name_here",
  password: "password_here",
  db: "db_name_here",
  poolSize: 3, // connection limit
});

/* 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;`;

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

try {

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

  select1Result = await client.execute(sqlSELECT1);
  if (select1Result) console.log("Table tblToDos contains", select1Result.rows.length, "records.");

  insert1Result = await client.execute(sqlINSERT1);
  if (insert1Result) console.log(insert1Result.affectedRows, "record(s) inserted.", "id:", insert1Result.lastInsertId);

  select2Result = await client.execute(sqlSELECT2);
  if (select2Result) console.log(select2Result.rows);

  update1Result = await client.execute(sqlUPDATE1);
  if (update1Result) console.log(update1Result.affectedRows, "record(s) updated.");

  select3Result = await client.execute(sqlSELECT3);
  if (select3Result) console.log(select3Result.rows);

  delete1Result = await client.execute(sqlDELETE1);
  if (delete1Result) console.log(delete1Result.affectedRows, "record(s) deleted.");

  select4Result = await client.execute(sqlSELECT4);
  if (select4Result) console.log("Table tblToDos contains", select1Result.rows.length, "records.");

  drop1Result = await client.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 {

  await client.close();

}

Output:

INFO connecting...
INFO connected
Table tblToDos created.
Table tblToDos contains 0 records.
1 record(s) inserted. id: 2001
[
  {
    id: 2001,
    userId: 1001,
    todoCreated: 2023-03-27T09:43:34.000Z,
    todoModified: 2023-03-27T09:43:34.000Z,
    todoStatus: "Pending",
    todoTitle: "Shopping List #1",
    todoBody: "Apples & Oranges"
  }
]
1 record(s) updated.
[
  {
    id: 2001,
    userId: 1001,
    todoCreated: 2023-03-27T09:43:34.000Z,
    todoModified: 2023-03-27T09:43:34.000Z,
    todoStatus: "Complete",
    todoTitle: "Shopping List #1",
    todoBody: "Apples & Oranges"
  }
]
1 record(s) deleted.
Table tblToDos contains 0 records.
Table tblToDos droped.
INFO close connection