object_mysql

Module: object_mysql Retrieve databases in Javascript object format With this module synchronize the Mysql database in a more dynamic way to implement in your project created with NodeJs

🚀 Installation

npm i object_mysql

Usage

Initial setup

The way to use this module is by declaring the following attributes in the environment variables.

Variable	Definition
DB_HOST	IPv4 addresses and host names (default:'localhost')
DB_USER	User name (default:'root')
DB_PASS	Database password (default:'')
DB_TABLE	Schema name
DB_PORT	Port connection
DB_MULTIPLE_STATEMENT	Multiple statement (default:true)
DB_CONNECTION_LIMIT	Connection limit (default:5000)
DB_CONNECTION_TIMEOUT	Connection timeout (default:30000)
DB_CHARSET	Charset (default:'utf8mb4')

Module Additions Method

Name	Method	Definition	Return data
db	db.query (sql:string,params:object)	Make an inquiry directly	{ error, result }

You can pass parameters to the query so that the data to the query is parsed

Example: direct query

import Dotenv from 'dotenv'
import ObjectDB from 'object_mysql'

Dotenv.config()

const exec = async () => {

    // Retrieve object from database
    const { ...NameToTable, db } = await ObjectDB() 

    // ==========================================
    // "db" is an instance of "new Database()"
    // ==========================================

    // Directly run a query
    const sql = 'SELECT * FROM nametotable WHERE id = :attribute_name'
    const { error, result } = await db.query(sql, {attribute_name:1})
}

exec()

📚 Method of using the objects

Method	Parameters	Definition	Return data
add	params:object	Add data to the table	{ error, result }
get	params:object	Recover data	{ error, result }
update	id:number-string, params:object	Update data	{ error, result }
remove	id:number-string	Delete data	{ error, result }
getByPk	id:number-string, pk: any = 'id'	Recover data based on its primary key	{ error, result }
getByAttr	nameAttr:string, attr: string-number-null, expresion:string ("=")	Retrieve data according to its attributes	{ error, result }
count	row:string, params:object	Retrieve register total	int
getTotal	---	Retrieve register total	int
isExist	params:object	Check if record exists based on attributes	boolean

📚 Listening events

Now you can listen to the events that occur in the module

Method	Definition	Return data
monitor	Recover insert, update, delete, select processes	object
error	Recover errors	object

Example

import Dotenv from 'dotenv'
import ObjectDB from 'object_mysql'

Dotenv.config()

const exec = async () => {

    // Retrieve object from database
    const { ...NameToTable, on } = await ObjectDB() 

    on('monitor', data => {
        console.log(data)
    })

    // Add data to the table
    await NameToTable.add({name:"Testing data"})

    // Recover data
    await NameToTable.get({id})
}

exec()

/*
console.log -> Add data to the table
{
    startTime: 2955.893799999729,
    endTime: 2958.500699999742,
    executionTime:  0.0026069000000134112,
    executionTimeFormat: '0.003s',
    model: 'NameToTable',
    type: 'insert',
    query: 'INSERT INTO `name_to_table` (`name`) VALUES (:name)',
    params: { name: 'Testing data' },
    result:  OkPacket 
    {
        fieldCount: 0,
        affectedRows: 1,
        insertId: 1,
        serverStatus: 2,
        warningCount: 0,
        message: '',
        protocol41: true,
        changedRows: 0
    }
}

console.log -> Recover data
{
    startTime: 2977.9382999995723,
    endTime:  2975.734899999574 ,
    executionTime: 0.001358299999497831,
    executionTimeFormat: '0.001s',
    model: 'NameToTable',
    type: 'insert',
    query: 'SELECT * FROM `name_to_table` WHERE hotel.name = :name',
    params: { name: 'Testing data' },
    result: [
        RowDataPacket {
            id: 1,
            name: 'Testing data',
            created: '2022-10-31 15:42:33',
            updated: '2022-10-31 15:42:33'
        }
    ]
}
*/

Example: directly attack the table object

import Dotenv from 'dotenv'
import ObjectDB from 'object_mysql'

Dotenv.config()

const exec = async () => {

    // Retrieve object from database
    const { ...NameToTable } = await ObjectDB() 

    // Add data to the table
    const { error, result } = await NameToTable.add({name:"Testing data"})

    // Muliple add data to the table
    const { error, result } = await NameToTable.add([{name:"Testing data"},{name:"Other data"},{name:"more data"}])

    if(error) return false 

    //Unique identifier of the inserted data
    const { insertId:id } = result

    // Update data
    const { error, result } = await NameToTable.update(id,{name:"Testing data 2"})

    if(error) return false

    // Recover data
    const { error, result } = await NameToTable.get({id})

    if(error) return false

    // Delete data
    const { error, result } = await NameToTable.remove(id)
}

exec()

String Function

check	Method	Description
✅	ascii	function returns the ASCII value for the specific character
✅	char_length	function return the length of a string (in characters)
✅	length	function returns the length of a string (in bytes)
✅	lower	function converts a string to lower-case
✅	trim	function removes leading and trailing spaces from a string
✅	ltrim	function removes leading spaces from a string
✅	rtrim	function removes trailing spaces from a string
✅	reverse	function reverses a string and returns the result
✅	upper	function converts a string to upper-case

Numeric Function

check	Method	Description
✅	count	function returns the number of records returned by a select query
✅	sum	function calculates the sum of a set of values
✅	avg	function returns the average value of an expression
✅	max	function returns the maximum value in a set of values
✅	min	function returns the minimum value in a set of values

🗒️ Roadmap

• String Functions
• Numeric Functions
• Date Functions
• Join tables

Last test

Tests have been carried out to check the operation of the connections, create, read, update, remote and also additional functions