mappifysql - npm Package Compare versions

Comparing version 1.0.6 to 1.0.9

app.js

 const mappifysql = require('./lib/');
 
 module.exports = mappifysql;
+
 module.exports.default = mappifysql;
@@ -9,3 +10,3 @@ module.exports.mappifysql = mappifysql;
 module.exports.Database = mappifysql.Database;
-module.exports.Model = mappifysql.Model;
+module.exports.MappifyModel = mappifysql.MappifyModel;
 

lib/database.js

@@ -6,3 +6,15 @@ const mysql = require('mysql2');
 
+/**
+ * Database class for managing MySQL connections.
+ * @example const db = new Database();
+ * db.createConnection().then((connection) => {
+ * console.log('Connection created successfully');
+ * } catch (err) {
+ * console.error(err);
+ * }
+ */
 class Database {
+    /**
+     * Constructor for the Database class.
+     */
     constructor() {
@@ -19,3 +31,12 @@ this.config = {
 
-    // create connection for createConnection on mysql
+    /**
+    * Creates a new MySQL connection using the configuration.
+    * @example const db = new Database();
+    * db.createConnection().then((connection) => {
+    *  console.log('Connection created successfully');
+    * } catch (err) {
+    * console.error(err);
+    * }
+    * @returns {Promise} A promise that resolves to the connection if successful, otherwise rejects with an error.
+    */
     createConnection() {
@@ -36,3 +57,13 @@ return new Promise((resolve, reject) => {
 
-    // create connection for createPool on mysql
+    /**
+     * Creates a new MySQL connection pool using the configuration.
+     * This method should be used when multiple connections are required.
+     * @example const db = new Database();
+     * db.createPool().then((pool) => {
+     *   console.log('Pool created successfully');
+     * } catch (err) {
+     *  console.error(err);
+     * }
+     * @returns {Promise} A promise that resolves to the connection pool if successful, otherwise rejects with an error.
+     */
     createPool() {
@@ -68,2 +99,14 @@ return new Promise((resolve, reject) => {
 
+    /**
+     * Gets a promisified version of the query method from the connection.
+     * This method should be used to query the database.
+     * @example const db = new Database();
+     * const query = db.getQuery();
+     * query('SELECT * FROM users').then((results) => {
+     *  console.log(results);
+     * } catch (err) {
+     * console.error(err);
+     * }
+     * @returns {Function} The promisified query method.
+     */
     getQuery() {
@@ -70,0 +113,0 @@ return this.query = util.promisify(this.connection.query).bind(this.connection);

lib/index.js

@@ -0,7 +1,15 @@
+const { between } = require('./condition');
 const Database = require('./database');
-const Model = require('./model');
+const MappifyModel = require('./model');
 
+class User extends MappifyModel {
+    static get tableName() {
+        return 'users';
+    }
+
+}
+
 module.exports = {
     Database,
-    Model
+    MappifyModel
 };

lib/model.js

 const { query } = require('../lib/connection');
 const pluralize = require('pluralize');
+const conditions = require('./condition');
 
+// This function prepares a WHERE clause for a SQL query based on the provided conditions
+function prepareWhereClause(where, conditions) {
+  // Initialize the whereClause and whereValues
+  let whereClause = '';
+  let whereValues = [];
 
-class Model {
+  // Check if there are any conditions to process
+  if (Object.keys(where).length) {
+    // Initialize an array to hold the individual conditions
+    let conditionsArray = [];
+
+    // Loop over each condition
+    for (let key in where) {
+      // Check if the condition is an array (i.e., it's a complex condition with multiple parts)
+      if (Array.isArray(where[key])) {
+        // Process each part of the complex condition
+        let subConditions = where[key].map(subWhere => {
+          // Extract the key and value from the sub-condition
+          let subKey = Object.keys(subWhere)[0];
+          let subValue = subWhere[subKey];
+
+          // Check if the value is an operator
+          let isOperator = typeof subValue === 'object' && subValue !== null && Object.keys(subValue).some(key => conditions[key]);
+
+          // If the value is an operator, process it accordingly
+          if (isOperator) {
+            // Check if the operator is 'in' or 'notIn'
+            if (Object.keys(subValue)[0] === 'in' || Object.keys(subValue)[0] === 'notIn') {
+              // Extract the array of values for the 'in' or 'notIn' operator
+              let arr = Object.values(subValue)[0];
+
+              // Check if the values are indeed an array
+              if (!Array.isArray(arr)) {
+                throw new Error('Value of in or notIn must be an array');
+              }
+
+              // Format the values correctly
+              arr = arr.map(value => typeof value === 'string' ? `'${value}'` : value);
+              let str = '(' + arr.join(',') + ')';
+
+              // Return the condition string
+              return `${subKey} ${Object.keys(subValue)[0]} ${str}`;
+            } else if (Object.keys(subValue)[0] === 'between' || Object.keys(subValue)[0] === 'notBetween') {
+              // Extract the array of values for the 'between' or 'notBetween' operator
+              let arr = Object.values(subValue)[0];
+
+              // Check if the values are indeed an array of length 2
+              if (!Array.isArray(arr) || arr.length !== 2) {
+                throw new Error('Value of between must be an array of length 2');
+              }
+
+              // Format the values correctly
+              arr = arr.map(value => typeof value === 'string' ? `'${value}'` : value);
+              let str = arr.join(' AND ');
+
+              // Return the condition string
+              return `${subKey} ${Object.keys(subValue)[0]} ${str}`;
+            } else if (Object.keys(subValue)[0] === 'isNull' || Object.keys(subValue)[0] === 'isNotNull') {
+              // Check if the value is true for the 'isNull' or 'isNotNull' operator
+              if (subValue[Object.keys(subValue)[0]] !== true) {
+                throw new Error('Value of isNull or isNotNull must be true');
+              }
+
+              // Return the condition string
+              return `${subKey} ${Object.keys(subValue)[0]}`;
+            } else {
+              // If the operator is not 'in' or 'notIn', process it accordingly
+              if (conditions[Object.keys(subValue)[0]] === 'AND' || conditions[Object.keys(subValue)[0]] === 'OR') {
+                throw new Error(`Invalid operator '${Object.keys(subValue)[0]}'`);
+              }
+
+              // Extract the value for the operator
+              let value = Object.values(subValue)[0];
+
+              // If the value is a string, wrap it in quotes
+              if (typeof value === 'string') {
+                value = `'${value}'`;
+              }
+
+              // Return the condition string
+              return `${subKey} ${conditions[Object.keys(subValue)[0]]} ${value}`;
+            }
+          } else if (conditions[subKey]) {
+            // If the subKey is a valid condition, add it to the whereValues and return the condition string
+            whereValues.push(subValue);
+            return `${subKey} ${conditions[subKey]} ?`;
+          } else {
+            // If the subKey is not a valid condition, check if the subValue is an object
+            if (typeof subValue === 'object') {
+              throw new Error(`Invalid operator '${Object.keys(subValue)[0]}'`);
+            }
+
+            // Add the subValue to the whereValues and return the condition string
+            whereValues.push(subValue);
+            return `${subKey} = ?`;
+          }
+        });
+
+        //if the condition is not AND or OR, throw an error
+        if (conditions[key] !== 'AND' && conditions[key] !== 'OR') {
+          throw new Error(`Invalid operator '${key}'`);
+        }
+
+        // Add the processed complex condition to the conditionsArray
+        conditionsArray.push(`(${subConditions.join(` ${conditions[key] || 'AND'} `)})`);
+      } else if (typeof where[key] === 'object' && where[key] !== null) {
+        // If the condition is an object, process each operator in it
+        for (let operator in where[key]) {
+          if (conditions[operator]) {
+            // If the operator is 'in', 'notIn', 'between', or 'notBetween', process it accordingly
+            if (operator === 'in' || operator === 'notIn') {
+              let arr = where[key][operator];
+              if (!Array.isArray(arr)) {
+                throw new Error('Value of in or notIn must be an array');
+              }
+              arr = arr.map(value => typeof value === 'string' ? `'${value}'` : value);
+              let str = arr.join(',');
+              conditionsArray.push(`${key} ${conditions[operator]} (${str})`);
+            } else if (operator === 'between' || operator === 'notBetween') {
+              let arr = where[key][operator];
+              if (!Array.isArray(arr) || arr.length !== 2) {
+                throw new Error('Value of between must be an array of length 2');
+              }
+              arr = arr.map(value => typeof value === 'string' ? `'${value}'` : value);
+              let str = arr.join(' AND ');
+              conditionsArray.push(`${key} ${conditions[operator]} ${str}`);
+            } else if (operator === 'isNull' || operator === 'isNotNull') {
+              if (where[key][operator] !== true) {
+                throw new Error('Value of isNull or isNotNull must be true');
+              }
+              conditionsArray.push(`${key} ${conditions[operator]}`);
+            } else if (operator === 'and' || operator === 'or') {
+              throw new Error(`Invalid operator '${operator}'`);
+            }
+            else {
+              // If the operator is not 'in', 'notIn', 'between', or 'notBetween', isNull or isNotNull , add it to the whereValues and conditionsArray
+              if (conditions[operator] === 'AND' || conditions[operator] === 'OR') {
+                throw new Error(`Invalid operator '${operator}'`);
+              }
+              conditionsArray.push(`${key} ${conditions[operator]} ?`);
+              whereValues.push(where[key][operator]);
+            }
+          }
+          else {
+            if (key == 'not') {
+              conditionsArray.push(`NOT (${operator} = ?)`);
+              whereValues.push(Object.values(where[key])[0]);
+            }
+          }
+        }
+      } else {
+        // If the condition is not an array or an object, add it to the whereValues and conditionsArray
+        conditionsArray.push(`${key} = ?`);
+        whereValues.push(where[key]);
+      }
+    }
+
+    // Join all the conditions with 'AND' to form the whereClause
+    whereClause = `WHERE ${conditionsArray.join(' AND ')}`;
+  }
+
+  // Return the whereClause and whereValues
+  return { whereClause, whereValues };
+}
+
+/**
+ * Model class for managing database records.
+ * @example class User extends MappifyModel {}
+ */
+class MappifyModel {
+  /**
+   * This static getter returns the table name for the model, which is the pluralized, lowercased class name.
+   * @example User.tableName // returns 'users'
+   * @example using in static functions method -> this.tableName
+   * @example using in instance functions method -> this.constructor.tableName
+   * @returns {string} The table name.
+   */
   static get tableName() {
@@ -10,2 +186,7 @@ return pluralize(this.name.toLowerCase());
 
+  /**
+   * This method sets properties on the instance from a given object.
+   * @example user.setProperties({ name: 'John Doe', email: '[e-mail]' });
+   * @param {object} properties - The properties to set.
+   */
   setProperties(properties) {
@@ -17,4 +198,7 @@ for (let key in properties) {
 
-
-
+   /**
+   * The constructor sets properties on the instance from a given object.
+   * @example const user = new User({ name: 'John Doe', email: '[e-mail]' });
+   * @param {object} properties - The properties to set.
+   */
   constructor(properties = {}) {
@@ -24,16 +208,32 @@ this.setProperties(properties);
 
-  static async save() {
+  /**
+   * This method saves the instance to the database.
+   * @example const product = new Product({ name: 'Product 1', price: 100 });
+   * await product.save();
+   * @returns {Promise<number>} The ID of the inserted record.
+   */
+  async save() {
     var columns = Object.keys(this); // get all the keys of the object
     var values = Object.values(this); // get all the values of the object
 
-    // create the sql query
-    var sql = `INSERT INTO ${this.tableName} (${columns.join(', ')}) VALUES (${'?'.repeat(columns.length).split('').join(', ')})`; 
+    // create the sql query and replace the values with question marks
+    var sql = `INSERT INTO ${this.constructor.tableName} (${columns.join(', ')}) VALUES (${'?'.repeat(columns.length).split('').join(', ')})`;
+
     // execute the query
     var result = await query(sql, values);
-    this.id = result.insertId; // set the id of the object
-    this.created_at = result.created_at; // set the created_at of the object
-    this.updated_at = result?.updated_at; // set the updated_at of the object
+
+    // Set the id, created_at, and updated_at properties of the model instance
+    this.id = result.insertId;
+    this.created_at = result.created_at;
+    this.updated_at = result?.updated_at;
     return result.insertId;
   }
 
+  /**
+   * This method updates the instance in the database.
+   * @example var product = await Product.findById(1);
+   * product.price = 200;
+   * await product.update();
+   * @returns {Promise<boolean>} A promise that resolves to true if the record was updated, otherwise false.
+   */
   async update() {
@@ -48,2 +248,8 @@ var columns = Object.keys(this);
 
+  /**
+   * This method deletes the instance from the database.
+   * @example var product = await Product.findById(1);
+   * await product.delete();
+   * @returns {Promise<boolean>} A promise that resolves to true if the record was deleted, otherwise false.
+   */
   async delete() {
@@ -55,3 +261,8 @@ var sql = `DELETE FROM ${this.constructor.tableName} WHERE id = ?`;
 
-  static async fetch(){
+  /**
+   * This method fetches all records from the database.
+   * @example var products = await Product.fetch();
+   * @returns {Promise<Array<Model>>} An array of instances.
+   */
+  static async fetch() {
     var sql = `SELECT * FROM ${this.tableName}`;
@@ -62,21 +273,37 @@ var rows = await query(sql);
 
+  /**
+   * This static method fetches one record from the table based on the provided options.
+   * @param {object} options - The options for the query.
+   * @param {object} options.where - The WHERE clause for the query.
+   * @param {Array} options.exclude - The columns to exclude from the result.
+   * @param {Array} options.attributes - The columns to include in the result.
+   * @example var product = await Product.findOne({ where: { id: 1 } });
+   * @example var product = await Product.findOne({ where: { name: 'Product 1' }, exclude: ['created_at', 'updated_at'] });
+   * @example var product = await Product.findOne({ where: { price: { gt: 100 } }, attributes: ['name', 'price'] });
+   * @example var product = await Product.findOne({ where: { price: { gt: 100, lt: 200 } } });
+   * @example var product = await Product.findOne({ where: { price: { in: [100, 200] } } });
+   * @example var product = await Product.findOne({ where: { price: { notIn: [100, 200] } } });
+   * @example var product = await Product.findOne({ where: { price: { between: [100, 200] } } });
+   * @example var product = await Product.findOne({ where: { price: { notBetween: [100, 200] } } });
+   * @example var product = await Product.findOne({ where: { name: { like: 'Product%' } } });
+   * @example var product = await Product.findOne({ where: { name: { notLike: 'Product%' } } });
+   * @example var product = await Product.findOne({ where: { name: { isNull: true } } });
+   * @example var product = await Product.findOne({ where: { name: { isNotNull: true } } });
+   * @example var product = await Product.findOne({ where: { not: { name: 'Product 1' } } });
+   * @example var product = await Product.findOne({ where: { and: [{ name: 'Product 1' }, { price: 100 }] } });
+   * @example var product = await Product.findOne({ where: { or: [{ name: 'Product 1' }, { price: 100 }] } });
+   * @returns {Promise<Array|null>} An instance of an array or null if no record was found.
+   */
   static async findOne(options = {}) {
     // Destructure options
-    const { where = {}, exclude = [], attributes = ['*'], operation } = options;
+    const { where = {}, exclude = [], attributes = ['*'] } = options;
 
     // Prepare SELECT clause
     const selectedAttributes = attributes[0] === '*' ? '*' : attributes.join(', ');
-      
-    // check if where clause has multiple conditions and operation is not provided
-    if (Object.keys(where).length > 1 && !operation) {
-      throw new Error('Operation must be provided when where clause has multiple conditions');
-    }else if(Object.keys(where).length > 1 && operation !== 'AND' && operation !== 'OR'){
-      throw new Error('Operation must be AND or OR');
-    } else if(Object.keys(where).length === 0){
+
+    // Prepare WHERE clause
+    if (Object.keys(where).length === 0) {
       throw new Error('Where clause must be provided');
     }
-    
-    // Prepare WHERE clause
-    const whereClause = Object.keys(where).length ? `WHERE ${Object.keys(where).map(key => `${key} = ?`).join(` ${operation || 'AND'} `)}` : '';
-    const whereValues = Object.values(where);
+    const { whereClause, whereValues } = prepareWhereClause(where, conditions);
 
@@ -93,2 +320,9 @@ // Construct SQL query
 
+  /**
+   * This static method fetches one record from the table based on the provided ID.
+   * @param {number} id - The ID of the record to fetch.
+   * @example var product = await Product.findById(1);
+   * console.log(product);
+   * @returns {Promise<Array|null>} An instance of an array or null if no record was found.
+   */
   static async findById(id) {
@@ -103,4 +337,35 @@ var sql = `SELECT * FROM ${this.tableName} WHERE id = ?`;
 
+  /**
+   * This static method fetches all records from the table based on the provided options.
+   * @param {object} options - The options for the query.
+   * @param {object} options.where - The WHERE clause for the query.
+   * @param {Array} options.exclude - The columns to exclude from the result.
+   * @param {Array} options.attributes - The columns to include in the result.
+   * @param {number} options.limit - The maximum number of records to fetch.
+   * @param {number} options.offset - The number of records to skip.
+   * @param {string} options.order - The column to order the results by.
+   * @param {string} options.group - The column to group the results by.
+   * @example var products = await Product.findAll();
+   * @example var products = await Product.findAll({ where: { price: { gt: 100 } } });
+   * @example var products = await Product.findAll({ where: { price: { gt: 100, lt: 200 } } });
+   * @example var products = await Product.findAll({ where: { price: { in: [100, 200] } } });
+   * @example var products = await Product.findAll({ where: { price: { notIn: [100, 200] } } });
+   * @example var products = await Product.findAll({ where: { price: { between: [100, 200] } } });
+   * @example var products = await Product.findAll({ where: { price: { notBetween: [100, 200] } } });
+   * @example var products = await Product.findAll({ where: { name: { like: 'Product%' } } });
+   * @example var products = await Product.findAll({ where: { name: { notLike: 'Product%' } } });
+   * @example var products = await Product.findAll({ where: { name: { isNull: true } } });
+   * @example var products = await Product.findAll({ where: { name: { isNotNull: true } } });
+   * @example var products = await Product.findAll({ where: { not: { name: 'Product 1' } } });
+   * @example var products = await Product.findAll({ where: { and: [{ name: 'Product 1' }, { price: 100 }] } });
+   * @example var products = await Product.findAll({ where: { or: [{ name: 'Product 1' }, { price: 100 }] } });
+   * @example var products = await Product.findAll({ exclude: ['created_at', 'updated_at'] });
+   * @example var products = await Product.findAll({ attributes: ['name', 'price'] });
+   * @example var products = await Product.findAll({ limit: 10, offset: 0 });
+   * @example var products = await Product.findAll({ order: 'price DESC' });
+   * @example var products = await Product.findAll({ group: 'category' });
+   * @returns {Promise<Array<Model>>} An array of instances.
+   */
   static async findAll(options = {}) {
-    const { attributes = ['*'], exclude = [], where = {}, limit, offset, order, operation } = options;
+    const { attributes = ['*'], exclude = [], where = {}, limit, offset, order, group } = options;
 
@@ -110,19 +375,10 @@ // Prepare SELECT clause
 
-    // check if where clause has multiple conditions and operation is not provided
-    if (Object.keys(where).length > 1 && !operation) {
-      throw new Error('Operation must be provided when where clause has multiple conditions');
-    }else if(Object.keys(where).length > 1 && operation !== 'AND' && operation !== 'OR'){
-      throw new Error('Operation must be AND or OR');
-    }
-
     // Prepare WHERE clause
-    const whereClause = Object.keys(where).length ? `WHERE ${Object.keys(where).map(key => `${key} = ?`).join(` ${operation || 'AND'} `)}` : '';
-    const whereValues = Object.values(where);
+    const { whereClause, whereValues } = prepareWhereClause(where, conditions);
 
+
     // Prepare LIMIT and OFFSET clauses
-    if ((offset && !limit) || (!offset && limit)) {
-      throw new Error('Both limit and offset must be provided together');
-    }
+
     const limitClause = limit ? `LIMIT ${limit}` : '';
-    const offsetClause = offset ? `OFFSET ${(offset - 1) * limit}` : '';
+    const offsetClause = offset && limit ? `OFFSET ${(offset - 1) * limit}` : '';
 
@@ -132,5 +388,8 @@ // Prepare ORDER BY clause
 
+    // Prepare GROUP BY clause
+    const groupClause = group ? `GROUP BY ${group}` : '';
+
     // Construct SQL query
-    const sql = `SELECT ${selectedAttributes} FROM ${this.tableName} ${whereClause} ${orderClause} ${limitClause} ${offsetClause}`;
-
+    const sql = `SELECT ${selectedAttributes} FROM ${this.tableName} ${whereClause} ${groupClause} ${orderClause} ${limitClause} ${offsetClause}`;
+    console.log(sql);
     // Execute query
@@ -148,17 +407,33 @@ let result = await query(sql, whereValues);
 
-  static async findOrCreate(options = {}, defaults) {
-    var instance = await this.findOne(options);
-    if (instance) {
-      return instance;
+  /**
+   * This static method finds a record based on the provided options, or creates a new record if no record is found.
+   * @param {object} options - The options for the query.
+   * @param {object} data - The data to create the record with.
+   * @example var user = await User.findOrCreate({ where: { email: '[e-mail]' } }, { name: 'John Doe', password: 'password' });
+   * @returns {Promise<{ instance: Model, created: boolean }>} An object containing the instance and a boolean indicating if the record was created.
+   * @throws {Error} Throws an error if the where clause is not provided.
+   */
+  static async findOrCreate(options = {}, data) {
+    var record = await this.findOne(options);
+    if (record) {
+      return record;
     }
     var { where } = options;
     if (Object.keys(where).length === 0) {
-      throw new Error('Defaults must be provided');
+      throw new Error('Where clause must be provided');
     }
-    instance = new this({ ...where, ...defaults });
-    await instance.create();
-    return { instance, created: true }
+    record = new this({ ...where, ...data });
+    await record.create();
+    return { instance: record, created: true }
   }
 
-  static async findAndDestroyOne(id) {
+  /**
+    * This static method deletes a record from the table based on its ID.
+    * @param {number} id - The ID of the record to delete.
+    * @example await User.findByIdAndDelete(1);
+    * @returns {Promise<boolean>} A promise that resolves to true if the record was deleted, otherwise false.
+    * @throws {Error} Throws an error if the ID is not provided or if no record is found.
+    * @example await User.findByIdAndDelete(1);
+    */
+  static async findByIdAndDelete(id) {
     if (!id) {
@@ -175,9 +450,39 @@ throw new Error('ID must be provided');
 
+  /**
+   * This static method updates a record in the table based on the provided options.
+   * @param {object} options - The options for the query.
+   * @param {object} options.where - The WHERE clause for the query.
+   * @param {object} data - The new data for the record.
+   * @example await Product.findOneAndDelete({ where: { name: 'Product 1' } });
+   * @returns {Promise<Model|null>} The updated instance or null if no record was found.
+   * @throws {Error} Throws an error if the where clause is not provided or if no record is found.
+   */
+  static async findOneAndDelete(options = {}) {
+    if (Object.keys(options).length > 1) {
+      throw new Error('Only where clause must be provided');
+    }
+    var instance = await this.findOne(options);
+    if (instance) {
+      await instance.delete();
+      return true;
+    }
+    throw new Error('No record found');
+  }
 
-
-  static async findOneAndUpdate(options = {}, defaults) {
+  /**
+   * This static method updates a record in the table based on the provided options.
+   * @param {object} options - The options for the query.
+   * @param {object} options.where - The WHERE clause for the query.
+   * @param {object} options.attributes - The columns to include in the result.
+   * @param {object} options.exclude - The columns to exclude from the result.
+   * @param {object} data - The new data for the record.
+   * @example await Product.findOneAndUpdate({ where: { id: 1 } }, { price: 200 });
+   * @returns {Promise<Model|null>} The updated instance or null if no record was found.
+   * @throws {Error} Throws an error if the where clause is not provided or if no record is found.
+   */
+  static async findOneAndUpdate(options = {}, data) {
     var instance = await this.findOne(options);
     if (instance) {
-      for (let key in defaults) {
-        instance[key] = defaults[key];
+      for (let key in data) {
+        instance[key] = data[key];
       }
@@ -190,7 +495,15 @@ await instance.update();
 
-  static async findByIdAndUpdate(id, defaults) {
+  /**
+   * This static method updates a record in the table based on the provided ID.
+   * @param {number} id - The ID of the record to update.
+   * @param {object} data - The new data for the record.
+   * @example await Product.findByIdAndUpdate(1, { price: 200 });
+   * @returns {Promise<Model|null>} The updated instance or null if no record was found.
+   * @throws {Error} Throws an error if the ID is not provided or if no record is found.
+   */
+  static async findByIdAndUpdate(id, data) {
     var instance = await this.findById(id);
     if (instance) {
-      for (let key in defaults) {
-        instance[key] = defaults[key];
+      for (let key in data) {
+        instance[key] = data[key];
       }
@@ -203,40 +516,33 @@ await instance.update();
 
-  static async findByIdAndDelete(id) {
-    var instance = await this.findById(id);
-    if (instance) {
-      await instance.delete();
-      return true;
-    }
-    throw new Error('No record found');
-  }
 
-  static async findByEmail(option = {}) {
-    const { where = {} } = option;
-    if (!where.email) {
-      throw new Error('Email must be provided');
-    }
-    return this.findOne(option);
 
-  }
+  // static async findByEmail(option = {}) {
+  //   const { where = {} } = option;
+  //   if (!where.email) {
+  //     throw new Error('Email must be provided');
+  //   }
+  //   return this.findOne(option);
 
-  static async findByUsername(options = {}) {
-    const { where = {} } = options;
-    if (!where.username) {
-      throw new Error('Username must be provided');
-    }
-    return this.findOne(options);
-  }
+  // }
 
-  static async findByEmailOrUsername(options = {}) {
-    const { where = {} } = options;
-    if (!where.email && !where.username) {
-      throw new Error('Email or username must be provided');
-    } else if (Object.keys(where).length > 1) {
-      throw new Error('Only email or username must be provided');
-    }
-    return this.findOne(options);
-  };
+  // static async findByUsername(options = {}) {
+  //   const { where = {} } = options;
+  //   if (!where.username) {
+  //     throw new Error('Username must be provided');
+  //   }
+  //   return this.findOne(options);
+  // }
 
+  // static async findByEmailOrUsername(options = {}) {
+  //   const { where = {} } = options;
+  //   if (!where.email && !where.username) {
+  //     throw new Error('Email or username must be provided');
+  //   } else if (Object.keys(where).length > 1) {
+  //     throw new Error('Only email or username must be provided');
+  //   }
+  //   return this.findOne(options);
+  // };
+
 }
 
 module.exports = Model;

package.json

 {
   "name": "mappifysql",
-  "version": "1.0.6",
+  "version": "1.0.9",
   "description": "MappifySQL is a lightweight, easy-to-use Object-Relational Mapping (ORM) library for MySQL databases, designed for use with Node.js. It provides an intuitive, promise-based API for interacting with your MySQL database using JavaScript or TypeScript.",
@@ -32,3 +32,7 @@ "repository": {
     "JavaScript",
-    "TypeScript"
+    "TypeScript",
+    "Database",
+    "SQL",
+    "MappifySQL",
+    "Mappify"
   ],
@@ -35,0 +39,0 @@ "author": "Walid Adebayo",

README.md

@@ -1,2 +0,2 @@
-# MappifySQL:A MySQL ORM for Node.js
+# MappifySQL: A MySQL ORM for Node.js
 
@@ -9,4 +9,10 @@ MappifySQL is a lightweight, easy-to-use Object-Relational Mapping (ORM) library for MySQL databases, designed for use with Node.js. It provides an intuitive, promise-based API for interacting with your MySQL database using JavaScript or TypeScript.
 - **CRUD Operations**: Easily perform Create, Read, Update, and Delete operations on your database.
-- **Transactions**: Safely execute multiple database operations at once with transaction support.
-- **Relationships**: Define relationships between your tables to easily fetch related data.
+<!-- - **Transactions**: Safely execute multiple database operations at once with transaction support. -->
+<!-- - **Relationships**: Define relationships between your tables to easily fetch related data. -->
+- **Model Class**: Define a model class for each table in your database to encapsulate database operations.
+- **Environment Variables**: Use environment variables to store database connection details securely.
+- **TypeScript Support**: Use MappifySQL with TypeScript for type-safe database interactions.
+- **Custom Queries**: Execute custom SQL queries using the query method.
+- **SQL Injection Protection**: Protect your application from SQL injection attacks with parameterized queries.
+- **Pagination**: Implement pagination for large datasets with the limit and offset options.
 
@@ -39,3 +45,3 @@
 DB_DATABASE=mydatabase
-DB_PORT=3306 ##(optional)
+DB_PORT=3306 ## (optional) default is 3306
 ```
@@ -98,36 +104,3 @@
 
-### Using the Query Builder
 
-Using the query and the connection object you exported from the connection.js file, you can now perform various database operations using the query builder provided by MappifySQL.
-
-```javascript
-const { connection, query } = require('./connection');
-
-// Example: Insert a new record into a table
-
-let addUserData = async (data) => {
-    const data = { name: 'John Doe', email: '[e-mail]' };
-    try {
-        let result = await query('INSERT INTO users SET ?', data);
-        console.log('New record inserted successfully');
-    } catch (err) {
-        console.error(err);
-    }
-};
-
-// Example: Fetch all records from a table
-
-let fetchAllUsers = async () => {
-    try {
-        let results = await connection.query('SELECT * FROM users');
-        console.log('Fetched records:', results);
-    } catch (err) {
-        console.error(err);
-    }
-};
-
-```
-
-**Note**: The query method returns a promise that resolves with the result of the query. You can use async/await to handle the asynchronous nature of the database operations.
-
 ### Using the Model Class
@@ -141,9 +114,9 @@
 ```javascript
-const { Model } = require('mappifysql');
+const { MappifyModel } = require('mappifysql');
 
-class Users extends Model {
+class User extends MappifyModel {
 
 }
 
-module.exports = Users;
+module.exports = User;
 
@@ -155,11 +128,11 @@ ```
 ```javascript
-const { Model } = require('mappifysql');
+const { MappifyModel } = require('mappifysql');
 
-class Users extends Model {
+class User extends MappifyModel {
     static get tableName() {
-        return 'my_users_table';
+        return 'my_user_table_name';
     }
 }
 
-module.exports = Users;
+module.exports = User;
 
@@ -173,8 +146,8 @@ ```
 ```javascript
-const Users = require('path/to/Users');
+const User = require('path/to/user.js')
 
-// Example: Fetch all records from the users table
+// Example: Fetch all records from the table
 
-let fetchAllUsers = async () => {
-    Users.findAll().then((results) => {
+let fetchAll = async () => {
+    User.findAll().then((results) => {
         console.log('Fetched records:', results);
@@ -188,5 +161,5 @@ }).catch((err) => {
 
-let addUserData = async () => {
-    const data = { name: 'John Doe', email: '[e-mail]' };
-    Users.create(data).then(() => {
+let addData = async () => {
+    let newUser = new User({ name: 'John Doe', email: '[e-mail]' });
+    User.save().then(() => {
         console.log('New record inserted successfully');
@@ -200,7 +173,10 @@ }).catch((err) => {
 
-let updateUserData = async () => {
-    const id = 1;
-    const data = { name: 'Jane Doe' };
-    Users.findByIdAndUpdate(id, data).then(() => {
-        console.log('Record updated successfully');
+let updateData = async () => {
+    User.findById(1).then((record) => {
+        record.setProperties({ name: 'Jane Doe', email: '[e-mail]' });
+        record.update().then(() => {
+            console.log('Record updated successfully');
+        }).catch((err) => {
+            console.error(err);
+        });
     }).catch((err) => {
@@ -213,5 +189,4 @@ console.error(err);
 
-let deleteUserData = async () => {
-    const id = 1;
-    Users.findByIdAndDelete(id).then(() => {
+let deleteData = async () => {
+    User.findByIdAndDelete(1).then(() => {
         console.log('Record deleted successfully');
@@ -222,5 +197,3 @@ }).catch((err) => {
 };
-
 ```
-
 # Model Class
@@ -232,3 +205,3 @@
 
-### `save()`
+### MappifySQL save Method
 
@@ -239,7 +212,13 @@ This method inserts a new record into the database. It uses the properties of the instance to determine the column names and values.
 ```javascript
-let user = new User({name: 'John', email: '[e-mail]'});
-await user.save();
+let user = new User({ name: 'John Doe', email: '[e-mail]' });
+user.save().then(() => {
+    console.log('New record inserted successfully');
+}).catch((err) => {
+    console.error(err);
+});
+
+// save returns the id of the newly inserted record
 ```
 
-### `update()`
+### MappifySQL Update Method
 
@@ -250,9 +229,19 @@ This method updates the record associated with the instance in the database. It uses the properties of the instance to determine the column names and values.
 ```javascript
-let user = await User.findById(1);
-user.name = 'John Doe';
-await user.update();
+User.findById(1).then((record) => {
+    record.setProperties({ name: 'Jane Doe', email: '[e-mail]' });
+    record.update().then(() => {
+        console.log('Record updated successfully');
+    }).catch((err) => {
+        console.error(err);
+    });
+}).catch((err) => {
+    console.error(err);
+});
+
+// update returns the true if the record was updated successfully
 ```
 
-### `delete()`
 
+### MappifySQL delete Method
+
 This method deletes the record associated with the instance from the database.
@@ -262,7 +251,16 @@
 ```javascript
-let user = await User.findById(1);
-await user.delete();
+User.findById(1).then((record) => {
+    record.delete().then(() => {
+        console.log('Record deleted successfully');
+    }).catch((err) => {
+        console.error(err);
+    });
+}).catch((err) => {
+    console.error(err);
+});
+
+// delete returns the true if the record was deleted successfully
 ```
 
-### `fetch()`
+### MappifySQL fetch Method
 
@@ -273,20 +271,133 @@ This method fetches all the records associated with the instance from the database.
 ```javascript
-let users = await User.fetch();
+User.fetch().then((records) => {
+    console.log('Fetched records:', records);
+}).catch((err) => {
+    console.error(err);
+});
+
+// fetch returns an array of records
 ```
 
-### `findOne()`
+### MappifySQL findOne Method
 
-This method finds one record in the database that matches the specified conditions. The `options` parameter is an object that can contain the following properties:
+This method finds one record in the database that matches the specified conditions. The parameter is an object that can contain the following properties:
 
-- `where`: An object specifying the conditions for the query. (where: {my_column: 'my_value'})
-- `exclude`: An array of column names to exclude from the result. (exclude: ['password'])
-- `attributes`: An array of column names to include in the result. (attributes: ['id', 'name', 'email'])
-- `operation`: A string specifying the logical operator to use when combining conditions if multiple conditions are specified in the `where` property. (Available options: 'AND', 'OR')(optional);
+- `where`: An object specifying the conditions for the query. - <span style="color: red;"> **required** </span>
+- `exclude`: An array of column names to exclude from the result.
+- `attributes`: An array of column names to include in the result.
 
 ```javascript
-let user = await User.findOne({where: {email: '[e-mail]'}});
+// Fetch a user with all columns from the database using the email
+const user = await User.findOne({ where: { email: '[e-mail]' } });
+
+// Fetch a product with the id 1 and exclude the 'description' column from the result
+const product = await Product.findOne({ where: { id: 1 }, exclude: ['description'] });
+
+// Fetch a user with the role 'admin' and only include the 'id', 'name', and 'email' columns in the result
+const admin = await User.findOne({ where: { role: 'admin' }, attributes: ['id', 'name', 'email'] });
+
+// Fetch a record using operations
+
+// Equal to
+const user = await User.findOne({ where: { age: { eq: 18 } } });
+// run this query: SELECT * FROM users WHERE age = 18;
+
+// Greater than
+const user = await User.findOne({ where: { age: { gt: 17 } } });
+// run this query: SELECT * FROM users WHERE age > 17;
+
+// Less than
+const user = await User.findOne({ where: { age: { lt: 10 } } });
+// run this query: SELECT * FROM users WHERE age < 10;
+
+// Greater than or equal to
+const user = await User.findOne({ where: { age: { gte: 18 } } });
+// run this query: SELECT * FROM users WHERE age >= 18;
+
+// Less than or equal to
+const user = await User.findOne({ where: { age: { lte: 10 } } });
+// run this query: SELECT * FROM users WHERE age <= 10;
+
+// Not equal to
+const user = await User.findOne({ where: { age: { ne: 18 } } });
+// run this query: SELECT * FROM users WHERE age <> 18;
+
+//greater than and less than
+const user = await User.findOne({ where: { age: { gt: 10, lt: 20 } } });
+// run this query: SELECT * FROM users WHERE age > 10 AND age < 20;
+
+//like
+const product = await Product.findOne({ where: { name: { like: '%apple%' } } });
+// run this query: SELECT * FROM products WHERE name LIKE '%apple%';
+
+//not like
+const product = await Product.findOne({ where: { name: { notLike: '%apple%' } } });
+// run this query: SELECT * FROM products WHERE name NOT LIKE '%apple%';
+
+//in
+const product = await Product.findOne({ where: { category: { in: ['electronics', 'clothing'] } } });
+// run this query: SELECT * FROM products WHERE category IN ('electronics', 'clothing');
+
+//not in
+const product = await Product.findOne({ where: { category: { notIn: ['electronics', 'clothing'] } } });
+// run this query: SELECT * FROM products WHERE category NOT IN ('electronics', 'clothing');
+
+//between
+const product = await Product.findOne({ where: { price: { between: [10, 20] } } });
+// run this query: SELECT * FROM products WHERE price BETWEEN 10 AND 20;
+
+//not between
+const product = await Product.findOne({ where: { price: { notBetween: [10, 20] } } });
+// run this query: SELECT * FROM products WHERE price NOT BETWEEN 10 AND 20;
+
+//is null
+const product = await Product.findOne({ where: { description: { isNull: true } } });
+// run this query: SELECT * FROM products WHERE description IS NULL;
+
+//is not null
+const product = await Product.findOne({ where: { description: { isNotNull: true } } });
+// run this query: SELECT * FROM products WHERE description IS NOT NULL;
+
+//and
+const product = await Product.findOne({ where: { category: 'electronics', price: { gt: 10 } } });
+// run this query: SELECT * FROM products WHERE category = 'electronics' AND price > 10;
+
+const product = await Product.findOne({ where: { and: [{ category: 'electronics' }, { price: { gt: 10 } }] } });
+// run this query: SELECT * FROM products WHERE (category = 'electronics' AND price > 10);
+
+const product = await Product.findOne({ where: { name: { like: '%apple%' }, and: [{ category: 'electronics' }, { price: { gt: 10 } }] } });
+// run this query: SELECT * FROM products WHERE name LIKE '%apple%' AND (category = 'electronics' AND price > 10);
+
+//or
+const product = await Product.findOne({ where: { or: [{ category: 'electronics' }, { price: { gt: 10 } }] } });
+// run this query: SELECT * FROM products WHERE category = 'electronics' OR price > 10;
+
+const product = await Product.findOne({ where: { name: { like: '%apple%' }, or: [{ category: 'electronics' }, { price: { gt: 10 } }] } });
+// run this query: SELECT * FROM products WHERE name LIKE '%apple%' AND (category = 'electronics' OR price > 10);
+
+//not
+const product = await Product.findOne({ where: { not: { category: 'electronics' } } });
+// run this query: SELECT * FROM products WHERE NOT category = 'electronics';
+
+const product = await Product.findOne({attributes: ['id', 'name', 'price'], where: { not: { category: 'electronics' } }});
+// run this query: SELECT id, name, price FROM products WHERE (NOT category = 'electronics');
 ```
 
-### `findById(id)`
+Here is a table for the LIKE operators in the where clause:
 
+| Operator | Description |
+| --- | --- |
+| `%apple%` | Finds any values that have "apple" in any position |
+| `apple%` | Finds any values that start with "apple" |
+| `%apple` | Finds any values that end with "apple" |
+| `_pple` | Finds any values that have "pple" in the second position |
+| `a%e` | Finds any values that start with "a" and end with "e" |
+| `a%o` | Finds any values that start with "a" and ends with "o" |
+| `a__%` | Finds any values that start with "a" and are at least 3 characters in length |
+| `a_%` | Finds any values that start with "a" and are at least 2 characters in length |
+| `_r%` | Finds any values that have "r" in the second position |
+
+
+### MappifySQL findById Method
+
 This method finds one record in the database with the specified id.
@@ -296,120 +407,496 @@
 ```javascript
-let user = await User.findById(1);
+User.findById(1).then((record) => {
+    console.log('Fetched record:', record);
+}).catch((err) => {
+    console.error(err);
+});
 ```
 
-### `findAll(options)`
+### MappifySQL findAll Method
 
 This method finds all records in the database that match the specified conditions. The `options` parameter is an object that can contain the following properties:
 
-- `where`: An object specifying the conditions for the query. (where: {is_active: 1, role: 'admin'})
-- `exclude`: An array of column names to exclude from the result. (exclude: ['password'])
-- `attributes`: An array of column names to include in the result. (attributes: ['id', 'name', 'email'])
-- `limit`: The maximum number of records to return. (limit: 10)
-- `offset`: The number of records to skip before starting to return records. (offset: req.query.page || 1)
-- `order`: A string specifying the order in which to return the records. (order: 'created_at DESC')
-- `operation`: A string specifying the logical operator to use when combining conditions if multiple conditions are specified in the `where` property. (Available options: 'AND', 'OR')(optional); default: 'AND'
+- `where`: An object specifying the conditions for the query. 
+- `exclude`: An array of column names to exclude from the result.
+- `attributes`:An array of column names to include in the result. Default is ['*'] which selects all
+- `limit`: The maximum number of records to return.
+- `offset`: The number of records to skip before starting to return records.
+- `order`: A string specifying the order in which to return the records.
+- `group`: A string specifying the column to group the records by. (column_name ASC/DESC);
 Example:
 ```javascript
-let users = await User.findAll({attributes: ['id', 'name', 'email'], limit: 10, offset: 0, order: 'created_at DESC'});
+// Fetch all products from the database
+const products = await Product.findAll();
+//run this query: SELECT * FROM products;
+
+// Fetch all products with specific properties
+const products = await Product.findAll(attributes: ['id', 'name', 'price']);
+//run this query: SELECT id, name, price FROM products;
+
+// Fetch all products and exclude specific properties
+const products = await Product.findAll(exclude: ['description']);
+
+// Fetch the first 10 products
+const products = await Product.findAll({ limit: 10 });
+//run this query: SELECT * FROM products LIMIT 10;
+
+// Fetch the second set of 10 products
+const products = await Product.findAll({ limit: 10, offset: 2 });
+//run this query: SELECT * FROM products LIMIT 10 OFFSET 2;
+
+/* 
+offset: 2 will skip the first 10 records and return the next 10 records.
+This is particularly useful for implementing pagination. The offset can be set dynamically like so: offset: req.query.page
+*/
+
+// Fetch products with the 'electronics' category
+const products = await Product.findAll({ where: { category: 'electronics' } });
+//run this query: SELECT * FROM products WHERE category = 'electronics';
+
+// Fetch products with the 'electronics' category and exclude the 'description' column from the result
+const products = await Product.findAll({ where: { category: 'electronics' }, exclude: ['description'] });
+
+// Fetch the total number of products for each category
+const products = await Product.findAll({ attributes: ['category', 'COUNT(*) AS total'], group: 'category' });
+//run this query: SELECT category, COUNT(*) AS total FROM products GROUP BY category;
+
+// Fetch all products grouped by category and ordered by price in descending order	
+const products = await Product.findAll({ group: 'category', order: 'price DESC' });
+//run this query: SELECT * FROM products GROUP BY category ORDER BY price DESC;
+
+//Fetch records using operations
+
+// Equal to
+const products = await Product.findAll({ where: { price: { eq: 1000 } } });
+// run this query: SELECT * FROM products WHERE price = 1000;
+
+// Greater than
+const products = await Product.findAll({ where: { price: { gt: 1000 } } });
+// run this query: SELECT * FROM products WHERE price > 1000;
+
+// Less than
+const products = await Product.findAll({ where: { price: { lt: 1000 } } });
+// run this query: SELECT * FROM products WHERE price < 1000;
+
+// Greater than or equal to
+const products = await Product.findAll({ where: { price: { gte: 1000 } } });
+// run this query: SELECT * FROM products WHERE price >= 1000;
+
+// Less than or equal to
+const products = await Product.findAll({ where: { price: { lte: 1000 } } });
+// run this query: SELECT * FROM products WHERE price <= 1000;
+
+// Not equal to
+const products = await Product.findAll({ where: { price: { ne: 1000 } } });
+// run this query: SELECT * FROM products WHERE price <> 1000;
+
+//greater than and less than
+const products = await Product.findAll({ where: { price: { gt: 500, lt: 1000 } } });
+// run this query: SELECT * FROM products WHERE price > 500 AND price < 1000;
+
+//like
+const products = await Product.findAll({ where: { name: { like: '%apple%' } } });
+// run this query: SELECT * FROM products WHERE name LIKE '%apple%';
+
+//not like
+const products = await Product.findAll({ where: { name: { notLike: '%apple%' } } });
+// run this query: SELECT * FROM products WHERE name NOT LIKE '%apple%';
+
+//in
+const products = await Product.findAll({ where: { category: { in: ['electronics', 'clothing'] } } });
+// run this query: SELECT * FROM products WHERE category IN ('electronics', 'clothing');
+
+//not in
+const products = await Product.findAll({ where: { category: { notIn: ['electronics', 'clothing'] } } });
+// run this query: SELECT * FROM products WHERE category NOT IN ('electronics', 'clothing');
+
+//between
+const products = await Product.findAll({ where: { price: { between: [500, 1000] } } });
+// run this query: SELECT * FROM products WHERE price BETWEEN 500 AND 1000;
+
+//not between
+const products = await Product.findAll({ where: { price: { notBetween: [500, 1000] } } });
+// run this query: SELECT * FROM products WHERE price NOT BETWEEN 500 AND 1000;
+
+//is null
+const products = await Product.findAll({ where: { description: { isNull: true } } });
+// run this query: SELECT * FROM products WHERE description IS NULL;
+
+//is not null
+const users = await User.findAll({ where: { is_subscribed: { isNotNull: true } } });
+// run this query: SELECT * FROM users WHERE is_subscribed IS NOT NULL;
+
+//and
+const products = await Product.findAll({ where: { category: 'electronics', price: { gt: 500 } } });
+// run this query: SELECT * FROM products WHERE category = 'electronics' AND price > 500;
+
+const products = await Product.findAll({ where: { and: [{ category: 'electronics' }, { price: { gt: 500 } }] }});
+// run this query: SELECT * FROM products WHERE (category = 'electronics' AND price > 500);
+
+const products = await Product.findAll({ where: { name: { like: '%apple%' }, and: [{ category: 'electronics' }, { price: { gt: 500 } }] }});
+// run this query: SELECT * FROM products WHERE name LIKE '%apple%' AND (category = 'electronics' AND price > 500);
+
+//or
+const products = await Product.findAll({ where: { or: [{ category: 'electronics' }, { price: { gt: 500 } }] } });
+// run this query: SELECT * FROM products WHERE category = 'electronics' OR price > 500;
+
+const products = await Product.findAll({ where: { name: { like: '%apple%' }, or: [{ category: 'electronics' }, { price: { gt: 500 } }] }});
+// run this query: SELECT * FROM products WHERE name LIKE '%apple%' AND (category = 'electronics' OR price > 500);
+
+//not
+const products = await Product.findAll({ where: { not: { category: 'electronics' } } });
+// run this query: SELECT * FROM products WHERE NOT category = 'electronics';
+
+const products = await Product.findAll({attributes: ['id', 'name', 'price'], where: { not: { category: 'electronics' } }});
+// run this query: SELECT id, name, price FROM products WHERE (NOT category = 'electronics');
 ```
+#### Operations
+| Operation | Description |
+| --- | --- |
+| eq | Equal to `=` |
+| gt | Greater than `>` |
+| lt | Less than `<` |
+| gte | Greater than or equal to `>=` |
+| lte | Less than or equal to `<=` |
+| ne | Not equal to `<>` |
+| like | Like `%value%` |
+| notLike | Not Like `%value%` |
+| in | In `('value1', 'value2')` |
+| notIn | Not In `('value1', 'value2')` |
+| between | Between `value1 AND value2` |
+| notBetween | Not Between `value1 AND value2` |
+| isNull | Is Null |
+| isNotNull | Is Not Null |
+| and | Logical AND |
+| or | Logical OR |
+| not | Logical NOT |
 
-### `findOrCreate(options, defaults)`
 
-This method finds one record in the database that matches the specified conditions, or creates a new record if no matching record is found. The `defaults` parameter is an object specifying the values to use when creating a new record. The `options` parameter is an object that can contain the following properties:
+### MappifySQL findOrCreate Method
 
-- `where`: An object specifying the conditions for the query. (where: {email: '[e-mail]'})
-- `exclude`: An array of column names to exclude from the result. (exclude: ['password'])
-- `attributes`: An array of column names to include in the result. (attributes: ['id', 'name', 'email'])
-- `operation`: A string specifying the logical operator to use when combining conditions if multiple conditions are specified in the `where` property. (Available options: 'AND', 'OR')(optional);
+This method finds one record in the database that matches the specified conditions, or creates a new record if no matching record is found. This function returns a object with two properties: `record` and `created`. The `record` property contains the record found or created, and the `created` property is a boolean value indicating whether the record was created or not. This function can be useful implementing a third-party login system where you want to find a user by their email or create a new user if they don't exist.
 
+**Parameters**:
+There are two parameters for this method:
+- `options`: This is the first parameter and is an object that specifies the conditions for the record to find. It can contain the following properties:
+    - `where`: An object specifying the conditions for the query.  <span style="color: red;"> **required** </span>
+    - `exclude`: An array of column names to exclude from the result.
+    - `attributes`: An array of column names to include in the result.
+
+- `defaults`: This is the second parameter and is an object that specifies the values to use when creating a new record. If a record is found, these values are ignored. 
+
 Example:
 ```javascript
-let user = await User.findOrCreate({where: {email: '[e-mail]'}}, {name: 'John'});
+// Find a user with the email and create a new user if not found
+let { record, created } = await User.findOrCreate({ where: { email: '[e-mail]' } }, { name: 'John Doe', picture: 'default.jpg', role: 'user' });
+
+if (created) {
+    console.log('New user created:', record);
+} else {
+    console.log('User found:', record);
+}
+
+// Find a user using operations
+let { record, created } = await User.findOrCreate({ where: { or: [{ email: '[e-mail]' }, { username: 'user' }] } }, { name: 'John Doe', picture: 'default.jpg', role: 'user' });
 ```
 
-### `findAndDestroyOne(id)`
+### MappifySQL findByIdAndDelete Method
 
-This method finds one record in the database with the specified id and deletes it.
-The `id` parameter is the id of the record to delete.
+The `findByIdAndDelete` method finds a single record in the database that matches the specified `id` and deletes it. The parameter is the id of the record to delete.
 
 Example:
 ```javascript
-await User.findAndDestroyOne(1);
+User.findByIdAndDelete(1).then(() => {
+    console.log('Record deleted successfully');
+}).catch((err) => {
+    console.error(err);
+});
 ```
 
-### `findOneAndUpdate(options, defaults)`
+### MappifySQL findOneAndDelete Method
 
-This method finds one record in the database that matches the specified conditions and updates it.  The `defaults` parameter is an object specifying the values to update. The `options` parameter is an object that can contain the following properties:
+This method finds one record in the database that matches the specified conditions and deletes it.
 
-- `where`: An object specifying the conditions for the query. (where: {email: '[e-mail]'})
-- `exclude`: An array of column names to exclude from the result. (exclude: ['password'])
-- `attributes`: An array of column names to include in the result. (attributes: ['id', 'name', 'email'])
-- `operation`: A string specifying the logical operator to use when combining conditions if multiple conditions are specified in the `where` property. (Available options: 'AND', 'OR')(optional);
+**Parameters**:
+There are two parameters for this method:
+- `options`: This is the first parameter and is an object that specifies the conditions for the record to find. It can contain the following properties:
+    - `where`: An object specifying the conditions for the query. <span style="color: red;"> **required** </span>
 
-
 Example:
 ```javascript
-let user = await User.findOneAndUpdate({where: {email: '[e-mail]'}}, {name: 'John Doe'});
+User.findOneAndDelete({ where: { email: '[e-mail]' } }).then(() => {
+    console.log('Record deleted successfully');
+}).catch((err) => {
+    console.error(err);
+});
 ```
 
-### `findByIdAndUpdate(id, defaults)`
 
-This method finds one record in the database with the specified id and updates it. The `defaults` parameter is an object specifying the values to update. The `id` parameter is the id of the record to update.
+### MappifySQL findOneAndUpdate Method
 
+This method finds one record in the database that matches the specified conditions and updates it. 
+
+**Parameters**:
+There are two parameters for this method:
+- `options`: This is the first parameter and is an object that specifies the conditions for the record to find. It can contain the following properties:
+    - `where`: An object specifying the conditions for the query. <span style="color: red;"> **required** </span>
+    - `exclude`: An array of column names to exclude from the result after the update.
+    - `attributes`: An array of column names to include in the result after the update.
+
+- `data`: This is the second parameter and is an object that specifies the values to update.
+
 Example:
 ```javascript
-let user = await User.findByIdAndUpdate(1, {name: 'John Doe'});
+User.findOneAndUpdate({ where: { email: '[e-mail]' } }, { name: 'Jane Doe', picture: 'profile.jpg' }).then(() => {
+    console.log('Record updated successfully');
+}).catch((err) => {
+    console.error(err);
+});
 ```
 
-### `findByIdAndDelete(id)`
+### MappifySQL findByIdAndUpdate Method`
 
-This method finds one record in the database with the specified id and deletes it. The `id` parameter is the id of the record to delete.
+This method finds one record in the database with the specified id and updates it. 
 
+**Parameters**:
+There are two parameters for this method:
+- `id`: This is the first parameter and is the id of the record to update.
+- `data`: This is the second parameter and is an object that specifies the values to update.
+
 Example:
 ```javascript
-await User.findByIdAndDelete(1);
+User.findByIdAndUpdate(1, { name: 'Jane Doe', picture: 'profile.jpg' }).then(() => {
+    console.log('Record updated successfully');
+}).catch((err) => {
+    console.error(err);
+});
 ```
 
-### `findByEmail(options)`
+### Custom Queries
 
-This method finds one record in the database with the specified email. The `options` parameter is an object that must contain the `where` property with the email value.
+You can execute custom SQL queries using the query method provided by MappifySQL. This method allows you to execute any SQL query and returns a promise that resolves with the result of the query.
 
-- `where`: An object specifying the conditions for the query. (where: {email: '[e-mail]'})
-- `exclude`: An array of column names to exclude from the result. (exclude: ['password'])
-- `attributes`: An array of column names to include in the result. (attributes: ['id', 'name', 'email'])
-
 Example:
 ```javascript
-let user = await User.findByEmail({where: {email: '[e-mail]'}});
+const { connection, query } = require('./connection');
+
+let customQuery = async () => {
+    try {
+        let results = await query('SELECT * FROM users WHERE role = ?', ['admin']);
+        console.log('Fetched records:', results);
+    } catch (err) {
+        console.error(err);
+    }
+};
+
+// you can also use the connection object directly
+let customQuery = async () => {
+    try {
+        let results = await connection.query('SELECT * FROM products WHERE name LIKE ?', ['%apple%']);
+        console.log('Fetched records:', results);
+    } catch (err) {
+        console.error(err);
+    }
+};
+
 ```
+<span style="color:red;"><b>Note</b></span>: The query method returns a promise that resolves with the result of the query. You can use async/await to handle the asynchronous nature of the database operations.
 
-### `findByUsername(options)`
+### Pagination
 
-This method finds one record in the database with the specified username. The `options` parameter is an object that must contain the `where` property with the username value.
+You can implement pagination for large datasets using the limit and offset options in the findAll method. The limit option specifies the maximum number of records to return, and the offset option specifies the number of records i.e. the page number you are on.
 
-- `where`: An object specifying the conditions for the query. (where: {username: 'adeal'})
-- `exclude`: An array of column names to exclude from the result. (exclude: ['password'])
-- `attributes`: An array of column names to include in the result. (attributes: ['id', 'name', 'email'])
+Example:
+By passing the offset dynamically using query parameters, you can fetch the next set of records for each page.
+```javascript
+// Fetch the 10 records for each page
+var page = req.query.page;
+const products = await Product.findAll({ limit: 10 , offset: page });
+```
+### Creating a custom function for a model class to perform a database operation
 
+You can create a custom function for a model class to perform a database operation. This function can be used to encapsulate complex queries or operations that are specific to the model.
 
 Example:
 ```javascript
-let user = await User.findByUsername({where: {username: 'adeal'}});
+const { MappifyModel } = require('mappifysql');
+
+class Product extends MappifyModel {
+    static async findElectronics() {
+        try {
+            let sql = `SELECT * FROM ${this.tableName} WHERE category = ?`;
+            let results = await this.query(sql, ['electronics']);
+            if (results.length > 0) {
+                return results.map(result => new this(result));
+            }
+            return [];
+        } catch (err) {
+            throw err;
+        }
+    }
+
+    // create a custom function using functions in the model class
+    static async findElectronics() {
+        try {
+            let results = await this.findAll(attributes: ['id', 'name', 'price'], and: [{ category: 'electronics' }, { price: { between: [500, 1000] } }]);
+            return results;
+        } catch (err) {
+            throw err;
+        }
+    }
+}
+module.exports = User;
+
 ```
+Usage:
+```javascript
+const Product = require('path/to/product.js');
 
-### `findByEmailOrUsername(options)`
+Product.findElectronics().then((products) => {
+    console.log('Electronics products:', products);
+}).catch((err) => {
+    console.error(err);
+});
+```
 
-This method finds one record in the database with the specified email or username. The `options` parameter is an object that must contain the `where` property with the email or username value.
 
-- `where`: An object specifying the conditions for the query. (where: {username: 'adeal'})
-- `exclude`: An array of column names to exclude from the result. (exclude: ['password'])
-- `attributes`: An array of column names to include in the result. (attributes: ['id', 'name', 'email'])
+### Using TypeScript
 
+#### Connecting to a Database
+
+To connect to a MySQL database using MappifySQL with TypeScript, you need to create a .env file in the root directory of your project and add the following environment variables:
+
+```bash
+DB_HOST=localhost
+DB_USER=root
+DB_PASSWORD=password
+DB_DATABASE=mydatabase
+DB_PORT=3306 ## (optional) default is 3306
+```
+
+Then, create a new TypeScript file (e.g., connection.ts) and add the following code:
+
+```typescript
+import { Database } from 'mappifysql';
+
+const db = new Database();
+
+db.createConnection().then(() => {
+    console.log('Database connected successfully');
+}).catch((err) => {
+    console.error(err);
+});
+
+var connection = db.connection;
+var query = db.getQuery();
+
+export { connection, query };
+```
+
+#### Using the Model Class
+
+MappifySQL can be used with TypeScript for type-safe database interactions. You can define interfaces for your models to ensure that the properties of your objects match the columns in your database tables.
+
 Example:
-```javascript
-let user = await User.findByEmailOrUsername({where: {username: 'john'}});
+```typescript
+import { MappifyModel } from 'mappifysql';
+
+interface ProductData {
+    name: string;
+    price: number;
+}
+
+class Product extends MappifyModel {
+    id: number;
+    name: string;
+    price: number;
+
+    constructor(data: ProductData) {
+        super();
+        this.name = data.name;
+        this.price = data.price;
+    }
+    async save() {
+        await super.save();
+    }
+
+    async update() {
+        await super.update();
+    }
+
+    async delete() {
+        await super.delete();
+    }
+
+    static async findAll() {
+        let results = await super.findAll();
+        return results.map(result => new Product(result));
+    }
+
+    static async findById(id: number){
+        let result = await super.findById(id);
+        return new Product(result);
+    }
+
+    static async findOne(options: { where: object }) {
+        let result = await super.findOne(options);
+        return new Product(result);
+    }
+    
+    static async findOrCreate(options: object, defaults: object) {
+        let { record, created } = await super.findOrCreate(options, defaults);
+        return { record: new Product(record), created };
+    }
+
+    static async findByIdAndDelete(id: number) {
+        await super.findByIdAndDelete(id);
+    }
+
+    static async findOneAndDelete(options: { where: object }) {
+        await super.findOneAndDelete(options);
+    }
+
+    static async findOneAndUpdate(options: { where: object }, data: object) {
+        await super.findOneAndUpdate(options, data);
+    }
+
+    static async findByIdAndUpdate(id: number, data: object) {
+        await super.findByIdAndUpdate(id, data);
+    }
+
+    static async customQuery() {
+        let sql = `SELECT * FROM ${super.tableName} WHERE category = ?`;
+        let results = await super.query(sql, ['electronics']);
+        return results.map(result => new Product(result));
+    }
+
+    static async findElectronics() {
+        let results = await super.findAll(attributes: ['id', 'name', 'price'], and: [{ category: 'electronics' }, { price: { between: [500, 1000] } }]);
+        return results.map(result => new Product(result));
+    }
+}
+
+export default Product;
 ```
 
+```typescript
+import Product from 'path/to/product.ts';
 
+let product = new Product({ name: 'Samsung S24', price: 1000 });
 
+product.save().then(() => {
+    console.log('New product inserted successfully');
+}).catch((err) => {
+    console.error(err);
+});
+
+Product.findAll().then((products) => {
+    console.log('Fetched products:', products);
+}).catch((err) => {
+    console.error(err);
+});
+```
+
+
+
 <!-- ### Transactions
@@ -445,5 +932,5 @@
 ```javascript
-const { Model } = require('mappifysql');
+const { MappifyModel } = require('mappifysql');
 
-class Users extends Model {
+class Users extends MappifyModel {
     constructor() {
@@ -455,3 +942,3 @@ super('users');
 
-class Addresses extends Model {
+class Addresses extends MappifyModel {
     constructor() {
@@ -458,0 +945,0 @@ super('addresses');

New alerts

Unpublished package

Supply chain risk

Package version was not found on the registry. It may exist on a different registry and need to be configured to pull from that registry.

Found 1 instance in 1 package

Fixed alerts

Unpopular package

Quality

This package is not very popular.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

64922

increased by137.27%

Number of package files

8

increased by14.29%

Lines of code

627

increased by131.37%

Number of medium quality alerts

0

decreased by-100%

Number of lines in readme file

953

increased by104.51%

Worsened metrics

Number of medium supply chain risk alerts

1

increased byInfinity%

No dependency changes