CKN Technology Data · Mysql Class

CKN Technology Data
Mysql

This page documents the Mysql class – a high-level wrapper around the MySQL driver that extends a base Database abstraction and integrates with Table, Row, and core helpers from @ckn-technology/core.

Import

The Mysql class is provided by @ckn-technology/data and depends on:

  • Database base abstraction (host, user, password, database, logging).
  • Table and Row for structured result handling.
  • MySQL driver (mysql NPM package).
  • Core helpers from @ckn-technology/core, including Date/Boolean formatting and logging.
import { Mysql } from "@ckn-technology/data";

const db = new Mysql({
    host: "localhost",
    user: "root",
    password: "password",
    database: "test"
});

1. Overview

Mysql is a high-level wrapper around the MySQL driver that provides:

  • Query execution returning data as a Table.
  • Bulk INSERT operations.
  • UPDATE with custom conditions.
  • UPSERT via INSERT ... ON DUPLICATE KEY UPDATE.
  • Table existence checks and schema inspection via information_schema.
  • Automatic conversion of JavaScript types (Date, boolean, null) into SQL expressions.
// Basic usage
const db = new Mysql({
    host: "localhost",
    user: "root",
    password: "password",
    database: "test"
});

const users = await db.query("SELECT id, name FROM users");
console.log(users.length, users.rows[0].name);

2. Constructor & Base Class

Extends Database

Description
The Mysql class extends a generic Database abstraction that provides the base configuration:

  • host
  • user
  • password
  • database
  • log (logger instance)

The constructor is inherited from Database. You typically pass a config object (host, user, password, database) when creating a new Mysql instance.

3. Methods

3.1 query(query)

Description
Executes a SQL query and returns the result mapped into a Table.

  • Logs the query via this.log.info.
  • Creates a new MySQL connection per call.
  • Maps each record in the MySQL response into a Row using Table.addRows.
  • Resolves with a populated Table instance.

Signature

query(query: string): Promise<Table>

Example

const db = new Mysql();

const table = await db.query("SELECT id, name FROM users");

console.log(table.length);        // number of rows
console.log(table.rows[0].name);  // access via Row

3.2 insert(tableName, objs)

Description
Bulk-inserts one or more rows into a table.

  • Accepts a single Row or an array of Row objects.
  • Converts row fields into column/value arrays.
  • Generates a SQL multi-value insert statement: 
    INSERT INTO `table` (`col1`,`col2`,...) VALUES ?
  • Uses parameterized queries with VALUES ?.
  • Resolves with the MySQL result object.

Signature

insert(tableName: string, objs: Row | Row[]): Promise<any>

Example

await db.insert("users", [
  new Row({ id: 1, name: "Alice" }),
  new Row({ id: 2, name: "Bob" })
]);

3.3 update(tableName, objs, condition)

Description
Updates one or more rows using a SQL UPDATE statement.

  • Accepts a single Row or an array of Row objects.
  • Converts row fields into SET expressions.
  • Escapes single quotes in strings (' → '').
  • Formats Date values using dateTimeDataFormat().
  • Converts booleans into 1 or 0.
  • Uses a custom WHERE condition provided by the caller.

Signature

update(
  tableName: string,
  objs: Row | Row[],
  condition: string
): Promise<any>

Example

await db.update(
  "users",
  new Row({ name: "Alice Updated", active: true }),
  "id = 1"
);

3.4 getFields(table)

Description
Returns metadata for columns in a table by querying information_schema.columns.

Signature

getFields(table: string): Promise<Table>

Example

const fields = await db.getFields("users");
console.log(fields.rows);

3.5 checkTable(table)

Description
Checks if a table exists in the current database by querying the schema.

  • Returns true if at least one column is found.
  • Returns false otherwise.

Signature

checkTable(table: string): Promise<boolean>

Example

if (await db.checkTable("users")) {
  console.log("Table exists");
}

3.6 insertOrUpdateOnDuplicateKey(tableName, objs)

Description
Performs insert-or-update logic using INSERT ... ON DUPLICATE KEY UPDATE. 

INSERT INTO `table` SET col='value', ...
ON DUPLICATE KEY UPDATE col='value', ...

Useful for:

  • Upserts.
  • Synchronization jobs.
  • Cache refresh operations.

Handles:

  • String escaping (quotes).
  • Date formatting via dateTimeDataFormat().
  • Boolean conversion to 1 or 0.
  • Ignores array properties.

Signature

insertOrUpdateOnDuplicateKey(
  tableName: string,
  objs: Row | Row[]
): Promise<any>

Example

await db.insertOrUpdateOnDuplicateKey("users", new Row({
  id: 1,
  name: "Alice",
  active: true
}));

4. Internal Behavior Notes

4.1 Connection Handling

Each method:

  • Creates a new MySQL connection.
  • Executes the generated SQL statement.
  • Closes the connection in a try/catch block.

4.2 Field Conversion Rules

During UPDATE / UPSERT operations, JavaScript values are converted as:

JS Type MySQL Value
string escaped 'value'
Date 'YYYY-MM-DD HH:MM:SS'
boolean 1 or 0
null NULL
array ignored in UPDATE / UPSERT expressions

4.3 Logging

All SQL statements executed by Mysql are logged using the core Log class, typically with: 

this.log.info("QUERY", sql);

This ensures consistent, timestamped query traces across all services using Mysql.

5. Summary

The Mysql class provides a high-level, opinionated wrapper around the MySQL driver, extending a base Database abstraction.

  • Integrates tightly with Table and Row for structured data handling.
  • Supports common operations:
    • query() – run arbitrary SQL and get a Table back.
    • insert() – bulk insert Row objects.
    • update() – update fields with custom conditions.
    • insertOrUpdateOnDuplicateKey() – UPSERT using ON DUPLICATE KEY UPDATE.
    • getFields(), checkTable() – schema inspection and table existence checks.
  • Automatically converts common JavaScript types (Date, boolean, null) into appropriate SQL values.
  • Manages connections per query and logs all executed SQL for observability.

With these features, Mysql offers a consistent, framework-style API for interacting with MySQL across all services in @ckn-technology/data.