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("' OR 1 = 1");
console.log(valInjection);
// -> `'\' OR 1 = 1'`

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:

query: String "SELECT * FROM table".
format: String, Allowed values: default,id,bool,count,value,values,valuesById,array,arrayById,rowStrict,row.
valueKey: String|Number, name or position of the column to get the value.
valueId: String|Number, name or position of the column to use as param.
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:

format: String, Allowed values: default,id,bool,count,value,values,valuesById,array,arrayById,row,rowStrict.
valueKey: String | Number, name or position of the column to get the value.
valueId: String | Number, name or position of the column to use as param.
fnSanitize: Null | Function, function to map every value.
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: `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^*️⃣ (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.

*️⃣ Notes:

If valueKey is type string, then it references to the column name.
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^*️⃣ 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.

*️⃣ Notes:

If valueKey is type string, then it references to the column name.
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^*️⃣ as key,
      · valueKey column-value^*️⃣ 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.

*️⃣ Notes:

If valueKey or valueId is type string, then it references to the column name.
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^*️⃣ 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.

*️⃣ Notes:

If valueKey is type string, then it references to the column name.
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 'testoromysql' 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.

Name		Name	Last commit message	Last commit date
Latest commit History 7 Commits
.github/workflows		.github/workflows
.husky		.husky
src		src
.eslintrc.yml		.eslintrc.yml
.gitignore		.gitignore
.npmignore		.npmignore
.prettierignore		.prettierignore
.prettierrc.yml		.prettierrc.yml
CHANGELOG.md		CHANGELOG.md
LICENSE		LICENSE
README.md		README.md
babel.config.cjs		babel.config.cjs
coverage.open.js		coverage.open.js
jest.config.js		jest.config.js
nodemon.json		nodemon.json
package-lock.json		package-lock.json
package.json		package.json
tsconfig.json		tsconfig.json
tsup.config.ts		tsup.config.ts

License

oropesa/oro-mysql

Folders and files

Latest commit

History

Repository files navigation

Oro Mysql

Overview

Installation

Example:

Methods

new OMysql()

await .poolOpen()

await .poolClose()

.getClient()static OMysql.getClient()

.getDB()

.getInfo()

.getStatus().status

.getAllQueries()

.clearQueries()

.getLastQuery()

.getFirstQuery()

.getAffectedRows()

.sanitize()static Omysql.sanitize()

await .pqueryOnce()

await .pquery()

await .queryOnce()

await .query()

ResultArray

(await .query) Format uses

(await .query) Format: default

(await .query) Format: id

(await .query) Format: bool

(await .query) Format: count

(await .query) Format: value

(await .query) Format: values

(await .query) Format: valuesById

(await .query) Format: array

(await .query) Format: arrayById

(await .query) Format: row

(await .query) Format: rowStrict

Testing

About

Resources

License

Stars

Watchers

Forks

Languages

.getClient()
static OMysql.getClient()

.getStatus()
.status

.sanitize()
static Omysql.sanitize()

(await .query) Format: `default`

(await .query) Format: `id`

(await .query) Format: `bool`

(await .query) Format: `count`

(await .query) Format: `value`

(await .query) Format: `values`

(await .query) Format: `valuesById`

(await .query) Format: `array`

(await .query) Format: `arrayById`

(await .query) Format: `row`

(await .query) Format: `rowStrict`