Oro Mysql

• Overview
• Installation
• Example
• Methods
• Testing

Overview

OroMysql Class is a wrapper of npm-mysql2 to simplify their use, allowing async/await and custom output format.

npm-mysql2 is a Mysql API Wrapper for node.js.

Installation

npm install oro-mysql

Example:

// cjs
const { OMysql } = require( 'oro-mysql' );

// mjs, ts
import { OMysql } from 'oro-mysql';

//

const config = {
  host: 'localhost',
  database: 'custom-database',
  user: 'custom-user',
  password: 'custom-password',
}

const sqlClient = new OMysql( config );

const poolOpen = await sqlClient.poolOpen();
if( ! poolOpen.status ) { return poolOpen; }

const rows = await sqlClient.query( "SELECT * FROM table", 'array' );
// [ row, ... ]

const row = await sqlClient.query( "SELECT * FROM table WHERE id = 7", 'row' );
// { columnKey: columnValue, ... }

await sqlClient.poolClose();

Methods

• new OMysql()
• await .poolOpen()
• await .poolClose()
• .getClient()
• .getDB()
• .getInfo()
• .getStatus()
• .getAllQueries()
• .clearQueries()
• .getLastQuery()
• .getFirstQuery()
• .getAffectedRows()
• .sanitize()
• await .pqueryOnce()
• await .pquery()
• await .queryOnce()
• await .query()
  • ResultArray
  • (await .query) Format uses

new OMysql()

new OMysql( config?: OMysqlConfig );

type OMysqlConfig = mysql2.ConnectionOptions &  {
  host?: string;
  port?: number;
  database?: string;
  user?: string;
  password?: string;
}

const { OMysql } = require('oro-mysql');

const config = {
  host: 'localhost',
  database: '',
  user: 'root',
  password: '',
};

const sqlClient = new OMysql(config);

await .poolOpen()

await sqlClient.poolOpen(args?: OMysqlPoolOpenInput): Promise<OMysqlServerStatus>

interface OMysqlPoolOpenInput {
   oTimer?: OTimer;
   oTimerOpen?: string;
}

type OMysqlServerStatus =
  | SResponseOKSimple
  | SResponseKOObject<OMysqlServerStatusError>

interface SResponseOKSimple {
   status: true;
   msg: string;
}

interface SResponseKOObject {
   status: false;
   error: {
      msg: string;
      times?: OTimerStep[];
   }
}

interface OMysqlServerStatusError {
   msg: string;
   times?: OTimerStep[];
}

When pool is opened, the connection to database is created to execute queries.

const poolOpen = await sqlClient.poolOpen();

console.log(poolOpen);
// -> { status: true, msg: 'Connected successfully.' }
// -> { status: false, error: { msg: 'Error reason' } }

await .poolClose()

await sqlClient.poolClose(args?: OMysqlPoolCloseInput): Promise<SResponseOKSimple>

interface OMysqlPoolOpenInput {
   oTimer?: OTimer;
   oTimerClose?: string;
}

interface SResponseOKSimple {
   status: true;
   msg: string;
}

To close the opened pool.

const poolOpen = await sqlClient.poolOpen();

console.log(poolOpen);
// -> { status: true, msg: 'Disconnected successfully.' }

.getClient()static OMysql.getClient()

sqlClient.getClient(): mysql2/promise
// or
static OMysql.getClient(): mysql2/promise

If you want to use the library mysql2/promise, you can get it.

const mysql = sqlClient.getClient();

// or from static

const mysql = OMysql.getClient();

.getDB()

sqlClient.getDB(): mysql.Connection | undefined

When pool is opened, you can get the npm-mysql conn object.

const db = sqlClient.getDB();
// use mysql2/promise Connection

.getInfo()

sqlClient.getInfo(): OMysqlConfig

type OMysqlConfig = mysql2.ConnectionOptions &  {
   host?: string;
   port?: number;
   database?: string;
   user?: string;
   password?: string;
}

Get config info (with password setted as asterisk).

const info = sqlClient.getInfo();

console.log(info);
// -> {
//   host: 'localhost',
//   user: 'username'
//   password: '********'
// }

.getStatus().status

sqlClient.getStatus(): OMysqlServerStatus

type OMysqlServerStatus =
  | SResponseOKSimple
  | SResponseKOObject<OMysqlServerStatusError>

interface SResponseOKSimple {
   status: true;
   msg: string;
}

interface SResponseKOObject {
   status: false;
   error: {
      msg: string;
      times?: OTimerStep[];
   }
}

interface OMysqlServerStatusError {
   msg: string;
   times?: OTimerStep[];
}

Get the status object. If status is false, show the error message.

status is only true when pool is opened and it's enabled to call a query.

const statusObj = sqlClient.getStatus();

console.log(statusObj);
// -> { status: true }

Another way to simplify getting the boolean status is directly with using the property sqlCLient.status.

console.log(sqlCLient.status);
// -> true | false

.getAllQueries()

sqlClient.getAllQueries(raw?: boolean = false): ResultArray[]

Get all resultArray of the queries that have been done.

Note: By default, you get a deep copy of each resultArray to avoid modifying data, but if you need a better performance and you understand what are you doing, you can get the resultArray as shallow copy (with raw = true).

const allResults = sqlClient.getAllQueries();

console.log(allResults);
// -> [ resultArray, ... ]

.clearQueries()

sqlClient.clearQueries(): number

To reset the queryHistory to zero.

Note: By default, every query that is executed is saved in sqlClient, so to avoid memory issues it's recommended to clear them if there are going to be a lot of them.

const removed = sqlClient.clearQueries();

console.log(removed);
// -> 3

.getLastQuery()

sqlClient.getLastQuery(offset = 0, raw = false): ResultArray

Get the last resultArray of the queries, with the param offset you can get the preceding queries.

Note: By default, you get a deep copy of the resultArray to avoid modifying data, but if you need a better performance and you understand what are you doing, you can get the resultArray as shallow copy (with raw = true).

const lastResult = sqlClient.getLastQuery();

console.log(lastResult);
// -> resultArray

.getFirstQuery()

sqlClient.getFirstQuery(offset = 0, raw = false): ResultArray

Get the first resultArray of the queries, with the param offset you can get the following queries.

Note: By default, you get a deep copy of the resultArray to avoid modifying data, but if you need a better performance and you understand what are you doing, you can get the resultArray as shallow copy (with raw = true).

const firstResult = sqlClient.getFirstQuery();

console.log(firstResult);
// -> resultArray

.getAffectedRows()

sqlClient.getAffectedRows(): number

Get the total number of rows that are affected in the last query.

const count = sqlClient.getAffectedRows();

console.log(count);
// -> 1

.sanitize()static Omysql.sanitize()

sqlClient.sanitize(value: any): string
// or
OMysql.sanitize(value: any): string

Sanitize the value to avoid code injections.

const valNumber = sqlClient.sanitize(20);
console.log(valNumber);
// -> `20`

const valString = sqlClient.sanitize('chacho');
console.log(valString);
// -> `'chacho'`

const valInjection = sqlClient.sanitize(INJECTION_STRING);
console.log(valInjection);
// -> `SANITIZED_INJECTION_STRING`

await .pqueryOnce()

[!WARNING]
Deprecated: use await .queryOnce() instead.

await sqlClient.pqueryOnce(
  query: string,
  format: OMysqlQueryFormat = 'default',
  valueKey: string | number = 0,
  valueId: string | number = 0,
  fnSanitize?: Function,
): Promise<OMysqlQueryOnceResponse<any>>

export type OMysqlQueryFormat =
  | 'default'
  | 'id'
  | 'bool'
  | 'count'
  | 'value'
  | 'values'
  | 'valuesById'
  | 'array'
  | 'arrayById'
  | 'row'
  | 'rowStrict';

type OMysqlQueryOnceResponse<T> =
  | SResponseOKObject<OMysqlQueryOnceObject<T>> // result of [await .pquery()](#await-pquery)
  | SResponseKOObject<OMysqlServerStatusError>; // error of [await .poolOpen()](#await-poolopen)

interface SResponseOKObject<T> {
  status: true;
  result: T;
}

OMysqlQueryOnceObject<T> {
   result: T;
}

If you just need to call only one query, this function calls poolOpen() & pquery() & poolClose() respectively.

Note: Better use await .queryOnce()

By default the returned format value is resultArray. But, depends on format parameter, it returns a different result value. To understand each format, please review (await .query) Format uses.

await .pquery()

[!WARNING]
Deprecated: use await .query() instead.

await sqlClient.pquery<T>(
  query: string,
  format: OMysqlQueryFormat = 'default',
  valueKey: string | number = 0,
  valueId: string | number = 0,
  fnSanitize?: Function,
): Promise<any>

export type OMysqlQueryFormat =
  | 'default'
  | 'id'
  | 'bool'
  | 'count'
  | 'value'
  | 'values'
  | 'valuesById'
  | 'array'
  | 'arrayById'
  | 'row'
  | 'rowStrict';

Note: Better use await .query()

By default the returned format value is resultArray. But, depends on format parameter, it returns a different result value. To understand each format, please review (await .query) Format uses.

Parameters:

1. query: String "SELECT * FROM table".
2. format: String, Allowed values: default,id,bool,count,value,values,valuesById,array,arrayById,rowStrict,row.
3. valueKey: String|Number, name or position of the column to get the value.
4. valueId: String|Number, name or position of the column to use as param.
5. fnSanitize: Null|Function, function to map every value. Note: If format is row|array, it maps every column-value (fnValueSanitize), not the whole object.

await .queryOnce()

await sqlClient.queryOnce(query: string, opts?: OMysqlQueryOpts): Promise<OMysqlQueryOnceResponse<any>>

interface OMysqlQueryOpts { // as [await .query()](#await-query)
   format?: OMysqlQueryFormat;
   valueKey?: string | number;
   valueId?: string | number;
   fnSanitize?: Function;
   fnValueSanitize?: Function;
}

type OMysqlQueryOnceResponse<T> =
  | SResponseOKObject<OMysqlQueryOnceObject<T>>
  | SResponseKOObject<OMysqlServerStatusError>; // error of [await .poolOpen()](#await-poolopen)

interface SResponseOKObject<T> {
  status: true;
  result: T;
}

OMysqlQueryOnceObject<T> {
   result: T;
}

If you just need to call only one query, this function calls poolOpen() & query() & poolClose() respectively.

By default the returned format value is resultArray. But, depends on format parameter, it returns a different result value. To understand each format, please review (await .query) Format uses.

await .query()

await sqlClient.queryOnce(query: string, opts?: OMysqlQueryOpts): Promise<any>

interface OMysqlQueryOpts {
   format?: OMysqlQueryFormat;
   valueKey?: string | number;
   valueId?: string | number;
   fnSanitize?: Function;
   fnValueSanitize?: Function;
}

export type OMysqlQueryFormat =
  | 'default'
  | 'id'
  | 'bool'
  | 'count'
  | 'value'
  | 'values'
  | 'valuesById'
  | 'array'
  | 'arrayById'
  | 'row'
  | 'rowStrict';

[!NOTE]
Each format returns a different result format.

By default the returned format value is resultArray. But, depends on format parameter, it returns a different result value. To understand each format, please review (await .query) Format uses.

Parameters:

query: String, "SELECT * FROM table".

options:

1. format: String, Allowed values: default,id,bool,count,value,values,valuesById,array,arrayById,row,rowStrict.
2. valueKey: String | Number, name or position of the column to get the value.
3. valueId: String | Number, name or position of the column to use as param.
4. fnSanitize: Null | Function, function to map every value.
5. fnValueSanitize: Null | Function, if format is row or array, it maps every column-value, not the whole object-value.

ResultArray

class ResultArray extends Array {
  public status: true;
  public count: number;
  public statement: string;
  public columns: any[];
}

class ResultArray extends Array {
  public status: false;
  public statement: string;
  public error: ResultArrayError;
}

interface ResultArrayError extends Record<string, any> {
  type: ResultArrayErrorType;
  msg: string;
}

type ResultArrayErrorType =
  | 'server-down'
  | 'wrong-format'
  | 'wrong-fnsanitize'
  | 'wrong-fnvaluesanitize'
  | 'wrong-query';

By default the returned data from a .query() is resultArray.

This class extends from Array and it has extra params.

{
  status = true || false,
  count = 0, // affected row
  statement = 'QUERY';
  columns = []; // table columns data
  error?: { // only when status is false
      type: 'error type',
      msg: 'error reason',
      ...
  }
}

(await .query) Format uses

• Format use: default
• Format use: id
• Format use: bool
• Format use: count
• Format use: value
• Format use: values
• Format use: valuesById
• Format use: array
• Format use: arrayById
• Format use: row
• Format use: rowStrict

(await .query) Format: default

await sqlClient.query(query: string, opts?: OMysqlQueryDefaultOpts): Promise<ResultArray>

interface OMysqlQueryDefaultOpts {
  format?: 'default';
}

It returns always a resultArray.

✔️ When query is valid, result.status is true. ❌ When query fails, result.status is false.

const resultArray = await sqlClient.query(`SELECT * FROM table`);
// ✔️ [
//   0: { ... },
//   1: { ... }
//   status: true,
//   statement: 'SELECT * FROM table',
//   count: 2,
//   columns: [ ... ]
// ]
//
// ❌ [
//   status: false,
//   statement: 'SELECT * FROM table',
//   error: {
//     type: 'wrong-query';
//     msg: 'MYSQL error reason';
//   }
// ]

(await .query) Format: id

await sqlClient.query(query: string, opts: OMysqlQueryIdOpts): Promise<number | false>

interface OMysqlQueryIdOpts {
  format: 'id';
}

// OR

await sqlClient.query<T>(query: string, opts: OMysqlQueryIdFnOpts<T>): Promise<T | false>

interface OMysqlQueryIdFnOpts<T> {
  format: 'id';
  fnSanitize: (value: number) => T;
}

If the query is an INSERT and the table has an AUTO_INCREMENT value (usually used as primary key), this incremented value is returned as id.

✔️ When query is valid, result is type number. ⚠️ When query is valid and there is no auto-increment value, result is 0. ✔️ When query is valid with fnSanitize:       · function-input is type number,       · function-output is type T (result). ❌ When query fails, result is false.

const id = await sqlClient.query( `INSERT INTO table VALUES ( ... )`, { format: 'id' } );
// ✔️ -> 17
// ❌ -> false

// OR

const id = await sqlClient.query( `INSERT INTO table VALUES ( ... )`, {
    format: 'id',
    fnSanitize: (value: number) => `id-${value}`
} );
// ✔️ -> 'id-17'
// ❌ -> false

// OR

// when there is no auto-increment column
const id = await sqlClient.query( `INSERT INTO table VALUES ( ... )`, {
    format: 'id',
} );
// ✔️ -> 0
// ❌ -> false

(await .query) Format: bool

await sqlClient.query(query: string, opts: OMysqlQueryBoolOpts): Promise<boolean>

interface OMysqlQueryBoolOpts {
  format: 'bool';
}

// OR

await sqlClient.query<T>(query: string, opts: OMysqlQueryBoolFnOpts<T>): Promise<T | false>

interface OMysqlQueryBoolFnOpts<T> {
  format: 'bool';
  fnSanitize: (value: boolean) => T;
}

✔️ When query is valid and there is any affected row, result is true. ✔️ When query is valid and there aren't affected rows, result is false. ✔️ When query is valid with fnSanitize:       · function-input is type boolean,       · function-output is type T (result). ❌ When query fails, result is false.

• bool, if the query has affected rows it returned true.

const bool = await sqlClient.query( `UPDATE table SET value WHERE condition`, { format: 'bool' } );
// ✔️ -> true | false
// ❌ -> false

// OR

const bool = await sqlClient.query( `UPDATE table SET value WHERE condition`, {
    format: 'bool',
    fnSanitize: (value: boolean) => Number(value)
} );
// ✔️ -> 1 | 0
// ❌ -> false

(await .query) Format: count

await sqlClient.query(query: string, opts: OMysqlQueryCountOpts): Promise<number | false>

interface OMysqlQueryCountOpts {
  format: 'count';
}

// OR

await sqlClient.query<T>(query: string, opts: OMysqlQueryCountFnOpts<T>): Promise<T | false>

interface OMysqlQueryCountFnOpts<T> {
  format: 'count';
  fnSanitize: (value: number) => T;
}

✔️ When query is valid, result is the number of affected rows. ✔️ When query is valid with fnSanitize:       · function-input is type number,       · function-output is type T (result). ❌ When query fails, result is false.

const count = await sqlClient.query( `SELECT '' FROM table`, { format: 'count' } );
// ✔️ -> 2
// ❌ -> false

// OR

const count = await sqlClient.query( `SELECT '' FROM table`, {
    format: 'count',
    fnSanitize: (value: number) => value === 0 ? 'No' : 'Yes'
} );
// ✔️ -> 'No' | 'Yes'
// ❌ -> false

(await .query) Format: value

await sqlClient.query<T>(query: string, opts: OMysqlQueryValueOpts): Promise<T | undefined | false>

interface OMysqlQueryValueOpts {
  format: 'value';
  valueKey?: string | number;
}

// OR

await sqlClient.query<T>(query: string, opts: OMysqlQueryValueFnOpts<T>): Promise<T | false>

interface OMysqlQueryValueFnOpts<T> {
  format: 'value';
  valueKey?: string | number;
  fnSanitize: (value: any) => T;
}

✔️ When query is valid and there is any affected row, it only takes the first row and result is the value of the valueKey column*1 (typed as T). ⚠️ When query is valid and there aren't affected rows, result is undefined. ✔️ When query is valid with fnSanitize:       · function-input is the value of the valueKey column,       · function-output is type T (result). ❌ When query fails, result is false.

*1 Notes:

1. If valueKey is type string, then it references to the column name.
2. If valueKey is type number, then it references to the column position.

const value = await sqlClient.query( "SELECT column1, column2 FROM table", { format: 'value' } );
// ✔️ -> column1-value
// ❌ -> false

// OR

const value = await sqlClient.query( "SELECT column1, column2 FROM table WHERE 0 = 1", { format: 'value' } );
// ✔️ -> undefined
// ❌ -> false

// OR

const value = await sqlClient.query( "SELECT column1, column2 FROM table", { format: 'value', valueKey: 'column2' } );
// ✔️ -> column2-value
// ❌ -> false

// OR

const value = await sqlClient.query( "SELECT column1, column2 FROM table", { format: 'value', valueKey: 'column3' } );
// ✔️ -> undefined
// ❌ -> false

// OR

const value = await sqlClient.query( "SELECT column1, column2 FROM table", { format: 'value', valueKey: 1 } );
// ✔️ -> column2-value
// ❌ -> false

// OR

const value = await sqlClient.query( "SELECT count(*) FROM table", {
    format: 'value',
    fnSanitize: (value: number) => `Total rows: ${value}.`
} );
// ✔️ -> 'Total rows: 17.'
// ❌ -> false

(await .query) Format: values

await sqlClient.query<T>(query: string, opts: OMysqlQueryValuesOpts): Promise<Array<T | undefined> | false>

interface OMysqlQueryValuesOpts {
  format: 'values';
  valueKey?: string | number;
}

// OR

await sqlClient.query<T>(query: string, opts: OMysqlQueryValuesFnOpts<T>): Promise<Array<T> | false>

interface OMysqlQueryValuesFnOpts<T> {
  format: 'values';
  valueKey?: string | number;
  fnSanitize: (value: any) => T;
}

✔️ When query is valid and there is any affected row, it takes all rows and result is an array of every value of valueKey column*2 with type T. ⚠️ When query is valid and there aren't affected rows, result is an empty array []. ✔️ When query is valid with fnSanitize:       · function-input is the value of every valueKey column,       · function-output is type T (and result is T[]). ❌ When query fails, result is false.

*2 Notes:

1. If valueKey is type string, then it references to the column name.
2. If valueKey is type number, then it references to the column position.

const values = await sqlClient.query( "SELECT column1, column2 FROM table", { format: 'values' } );
// ✔️ -> [ column1-value-of-row1, column1-value-of-row2, ... ]
// ❌ -> false

// OR

const values = await sqlClient.query( "SELECT column1, column2 FROM table WHERE 0 = 1", { format: 'values' } );
// ✔️ -> []
// ❌ -> false

// OR

const values = await sqlClient.query( "SELECT column1, column2 FROM table", { format: 'value', valueKey: 'column2' } );
// ✔️ -> [ column2-value-of-row1, column2-value-of-row2, ... ]
// ❌ -> false

// OR

const values = await sqlClient.query( "SELECT column1, column2 FROM table", { format: 'value', valueKey: 'column3' } );
// ✔️ -> [ undefined, undefined, ... ]
// ❌ -> false

// OR

const values = await sqlClient.query( "SELECT column1, column2 FROM table", { format: 'value', valueKey: 1 } );
// ✔️ -> [ column2-value-of-row1, column2-value-of-row2, ... ]
// ❌ -> false

// OR

const values = await sqlClient.query( "SELECT column_optional FROM table", {
    format: 'value',
    fnSanitize: (value: string | null) => value === null ? 'default' : value
} );
// ✔️ -> [ 'value1', 'default', ... ]
// ❌ -> false

(await .query) Format: valuesById

await sqlClient.query<T>(query: string, opts: OMysqlQueryValuesByIdOpts): Promise<Record<string, T | undefined> | false>

interface OMysqlQueryValuesByIdOpts {
  format: 'valuesById';
  valueKey?: string | number;
  valueId?: string | number;
}

// OR

await sqlClient.query<T>(query: string, opts: OMysqlQueryValuesByIdFnOpts<T>): Promise<Record<string, T> | false>

interface OMysqlQueryValuesByIdFnOpts<T> {
  format: 'valuesById';
  valueKey?: string | number;
  valueId?: string | number;
  fnSanitize: (value: any) => T;
}

✔️ When query is valid and there is any affected row, it takes all rows and result is an object with:       · valueId column-value*3 as key,       · valueKey column-value*3 as value. ✔️ When query is valid and there aren't affected rows, result is an empty object {}. ✔️ When query is valid with fnSanitize:       · function-input is the value of every valueKey column, ❌ When query fails, result is false.

*3 Notes:

1. If valueKey or valueId is type string, then it references to the column name.
2. If valueKey or valueId is type number, then it references to the column position.

const valuesById = await sqlClient.query( "SELECT user_id, user_name FROM table", {
    format: 'valuesById',
    valueKey: 'user_name',
    valueId: 'user_id'
} );
// ✔️ -> { userID1: 'User Name 1', userID2: 'User Name 2', ... }
// ❌ -> false

// OR

const valuesById = await sqlClient.query( "SELECT user_id, user_name FROM table", {
    format: 'valuesById',
    valueKey: 'user_name',
    valueId: 'user_id',
    fnSanitize: (userName: string | null) => userName === null ? 'User Default' : userName
} );
// ✔️ -> { userID1: 'User Name 1', userID2: 'User Default', ... }
// ❌ -> false

(await .query) Format: array

await sqlClient.query<T>(query: string, opts: OMysqlQueryArrayOpts): Promise<T[] | false>

interface OMysqlQueryArrayOpts {
  format: 'array';
}

// OR

await sqlClient.query<T>(query: string, opts: OMysqlQueryArrayFnOpts<T>): Promise<T[] | false>

interface OMysqlQueryArrayFnOpts<T> {
  format: 'array';
  fnSanitize?: (object: Record<string, any>) => T;
  fnValueSanitize?: (value: any, key: string) => any;
}

✔️ When query is valid, it returns an array of rows. ⚠️ When query is valid and there aren't affected rows, result is an empty array []. ✔️ When query is valid with fnSanitize:       · function-input is each row. ✔️ When query is valid with fnValueSanitize:       · function-input is every column-value, column-key of each row. ❌ When query fails, result is false.

const array = await sqlClient.query( "SELECT * FROM table", { format: 'array' } );
// ✔️ -> [ row1, row2, ... ]
// ❌ -> false

// OR

const array = await sqlClient.query( "SELECT * FROM table", {
    format: 'array',
    fnValueSanitize: (value: any) => value === null ? undefined : value,
    fnSanitize: (row: any) => { row.fullname = `${row.name} ${row.lastname}`; return row; }
} );
// ✔️ -> [ customized-row1, customized-row2, ... ]
// ❌ -> false

(await .query) Format: arrayById

await sqlClient.query<T>(query: string, opts: OMysqlQueryArrayByIdOpts): Promise<Record<string, T> | false>

interface OMysqlQueryArrayByIdOpts {
  format: 'arrayById';
  valueKey?: string | number;
}

// OR

await sqlClient.query<T>(query: string, opts: OMysqlQueryArrayFnOpts<T>): Promise<Record<string, T> | false>

interface OMysqlQueryArrayFnOpts<T> {
  format: 'arrayById';
  valueKey?: string | number;
  fnSanitize?: (object: Record<string, any>) => T;
  fnValueSanitize?: (value: any, key: string) => any;
}

✔️ When query is valid, it returns an object where key is the value o of rows.       · valueKey column-value*4 as key,       · _The whole row as value. ⚠️ When query is valid and there aren't affected rows, result is an empty object {}. ✔️ When query is valid with fnSanitize:       · function-input is each row. ✔️ When query is valid with fnValueSanitize:       · function-input is every column-value, column-key of each row. ❌ When query fails, result is false.

*4 Notes:

1. If valueKey is type string, then it references to the column name.
2. If valueKey is type number, then it references to the column position.

const arrayById = await sqlClient.query( "SELECT * FROM table", {
    format: 'arrayById',
    valueKey: 'user_id',
} );
// ✔️ -> { userID1: row1, userID2: row2, ... }
// ❌ -> false

// OR

const arrayById = await sqlClient.query( "SELECT * FROM table", {
    format: 'arrayById',
    valueKey: 'user_id',
    fnValueSanitize: (value: any) => value === null ? undefined : value,
    fnSanitize: (row: any) => { row.fullname = `${row.name} ${row.lastname}`; return row; }
} );
// ✔️ -> { userID1: customRow1, userID2: customRow2, ... }
// ❌ -> false

(await .query) Format: row

await sqlClient.query<T>(query: string, opts: OMysqlQueryRowOpts): Promise<T | undefined | false>

interface OMysqlQueryRowOpts {
  format: 'row';
  valueKey?: number;
}

// OR

await sqlClient.query<T>(query: string, opts: OMysqlQueryRowFnOpts<T>): Promise<T | undefined | false>

interface OMysqlQueryRowFnOpts<T> {
  format: 'row';
  valueKey?: number;
  fnSanitize?: (object: Record<string, any>) => T;
  fnValueSanitize?: (value: any, key: string) => any;
}

✔️ When query is valid, it returns the row object as result.       · valueKey (default 0) is the position number of the query-array. ⚠️ When query is valid and there aren't affected rows, result is undefined. ⚠️ When query is valid, there are affected rows, but valueKey is higher than query-array.length, result is undefined. ✔️ When query is valid with fnSanitize:       · function-input is the row. ✔️ When query is valid with fnValueSanitize:       · function-input is every column-value, column-key of the row. ❌ When query fails, result is false.

const row = await sqlClient.query( "SELECT * FROM table WHERE id = $id", {
    format: 'row',
} );
// ✔️ -> row
// ❌ -> false

// OR

const row = await sqlClient.query( "SELECT * FROM table WHERE category = $category", {
    format: 'row',
    valueKey: 1,
} );
// ✔️ -> row2
// ❌ -> false

// OR

const row = await sqlClient.query( "SELECT * FROM table WHERE category = $category", {
    format: 'row',
    valueKey: 999,
} );
// ✔️ -> undefined
// ❌ -> false

// OR

const value = await sqlClient.query( "SELECT * FROM table WHERE id = $id", {
    format: 'row',
    fnValueSanitize: (value: any) => value === null ? undefined : value,
    fnSanitize: (row: any) => { row.fullname = `${row.name} ${row.lastname}`; return row; }
} );
// ✔️ -> customRow
// ❌ -> false

(await .query) Format: rowStrict

await sqlClient.query<T>(query: string, opts: OMysqlQueryRowStrictOpts): Promise<T | undefined | false>

interface OMysqlQueryRowStrictOpts {
  format: 'rowStrict';
  valueKey?: number;
}

// OR

await sqlClient.query<T>(query: string, opts: OMysqlQueryRowStrictFnOpts<T>): Promise<T | undefined | false>

interface OMysqlQueryRowStrictFnOpts<T> {
  format: 'rowStrict';
  valueKey?: number;
  fnSanitize?: (object: Record<string, any>) => T;
  fnValueSanitize?: (value: any, key: string) => any;
}

✔️ When query is valid, it returns the row object without columns with falsy values as result.       · valueKey (default 0) is the position number of the query-array. ⚠️ When query is valid and there aren't affected rows, result is undefined. ⚠️ When query is valid, there are affected rows, but valueKey is higher than query-array.length, result is undefined. ⚠️ When query is valid and the row has all the column-values as falsy, result is {}. ✔️ When query is valid with fnSanitize:       · function-input is the row. ✔️ When query is valid with fnValueSanitize:       · function-input is every column-value, column-key of the row. ❌ When query fails, result is false.

Javascript falsy values:

• false
• 0 (zero)
• '' or “” (empty string)
• null.
• undefined.
• NaN (number).

const row = await sqlClient.query( "SELECT * FROM table WHERE id = $id", {
    format: 'rowStrict',
} );
// ✔️ -> row without-falsy-columns
// ❌ -> false

// OR

const row = await sqlClient.query( "SELECT * FROM table WHERE category = $category", {
    format: 'rowStrict',
    valueKey: 1,
} );
// ✔️ -> row2 without-falsy-columns
// ❌ -> false

// OR

const row = await sqlClient.query( "SELECT * FROM table WHERE category = $category", {
    format: 'rowStrict',
    valueKey: 999,
} );
// ✔️ -> undefined
// ❌ -> false

// OR

const value = await sqlClient.query( "SELECT * FROM table WHERE id = $id", {
    format: 'row',
    fnValueSanitize: (value: any) => Ofn.isStringJson(value) ? JSON.parse(value) : value,
    fnSanitize: (row: any) => { row.fullname = `${row.name} ${row.lastname}`; return row; }
} );
// ✔️ -> customRow without-falsy-columns
// ❌ -> false

Testing

If you want to run npm run test, you can create your own ./test/config.json (you can copypaste it from ./test/config-default.json).

{
  "host": "localhost",
  "database": null,
  "user": "root",
  "password": ""
}

ADVISE: When running the testing process, the system automatically generates and deletes the 'test*oromysql' database, so if config.user has not permission to create database, you should create the database test_oromysql manually.

On the other hand, if in your mysql already exist test_oromysql and it's required for you, avoid to run test.