@bhar2254/mysql NPM

A comprehensive MySQL utility package for Node.js applications. This package provides essential tools for interacting with MySQL databases, including query construction, metadata handling, object-based CRUD operations, and custom extensions like caching.

Features

SQL Helpers: Functions for constructing common SQL queries (e.g., INSERT, UPDATE, SELECT, DELETE), working with database metadata, and more.
SQL Extensions: Custom extensions such as caching to enhance database interaction.
SQLObject Class: A class to interact with MySQL database objects using CRUD operations. Supports automatic data mapping, reads, creates, updates, and deletes.
Modular and Extensible: Easily extend the functionality by adding custom helpers or overriding existing methods.

Installation

To install the package, use npm:

npm install @bhar2254/mysql

Usage

Basic SQL Operations

You can import the necessary functions from @bhar2254/mysql for query construction, object CRUD operations, and metadata fetching.

Example: Building Queries

const { buildInsertQuery, buildSelectQuery, buildUpdateQuery } = require('@bhar2254/mysql');

const insertQuery = buildInsertQuery('users', { name: 'John Doe', age: 30 });
const selectQuery = buildSelectQuery('users', ['id', 'name'], { age: 30 });
const updateQuery = buildUpdateQuery('users', { age: 31 }, { name: 'John Doe' });

console.log(insertQuery);
console.log(selectQuery);
console.log(updateQuery);

Example: Using SQLObject

const { SQLObject } = require('@bhar2254/mysql');

// Create a new SQLObject instance for a 'users' table
const user = new SQLObject({
  table: 'users',
  key: 'id',
  datum: { name: 'John Doe', age: 30 }
});

// Create the user in the database
await user.create();

// Read the user data by ID
await user.read({ id: 1 });

// Update the user data
await user.update({ name: 'Jane Doe' });

// Delete the user data
await user.destroy();

Example: Using Cache with `cacheFetch`

const { cacheFetch } = require('@bhar2254/mysql');

const data = await cacheFetch('user_1', '/api/users/1');
console.log(data);

API

SQL Helpers

buildInsertQuery(table, data): Generates an INSERT SQL query for the provided table and data.
buildUpdateQuery(table, data, where): Generates an UPDATE SQL query for the provided table, data, and conditions.
buildSelectQuery(table, columns, where): Generates a SELECT SQL query for the provided table, columns, and conditions.
buildDeleteQuery(table, where): Generates a DELETE SQL query for the provided table and conditions.
buildPaginationQuery(table, page, pageSize): Generates a paginated SELECT query with LIMIT and OFFSET.
queryPromise(query): Executes a raw SQL query and returns the result.

SQL Extensions

cacheFetch(ref, url): Fetches data from the specified URL and caches it in the database, returning cached or newly fetched data.

SQLObject Class

SQLObject: A class that provides CRUD operations for interacting with database tables.
- create(args): Creates a new record in the table.
- read(args): Reads records from the table based on conditions.
- update(datum): Updates existing records in the table.
- destroy(): Deletes a record from the table.
- readOrCreate(data): Reads a record, and if none exists, creates it.

Additional Functions

getEmptyObjectFromDB(table): Loads an empty object with columns set to null for the specified table.
getBaseViewTables(): Retrieves the list of base tables and views in the database.
getColumnKeysFromDB(table): Retrieves the column names for the specified table.
getPropertiesFromDB(table): Loads the properties (column names and data types) of the table.
getEnumFromDB(table, column): Retrieves the enum values for a specified column in the table.
getTablesFromDB(): Retrieves a list of all table names in the database.
getColumnAttributesFromDB(table, column): Retrieves the attributes of a specified column (e.g., primary key, foreign key).
getRowCount(table): Retrieves the row count for the specified table.
getForeignKeyRelationships(table): Retrieves foreign key relationships for the specified table.
doesTableExist(table): Checks if a table exists in the database.
isViewTable(table): Checks if a table is a view.
escapeSQL(str): Escapes special characters in SQL queries to prevent SQL injection.