New Case Study:See how Anthropic automated 95% of dependency reviews with Socket.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

mappifysql

Package Overview
Dependencies
Maintainers
0
Versions
17
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

mappifysql - npm Package Compare versions

Comparing version 1.1.3 to 1.1.4

460

app.d.ts

@@ -0,5 +1,7 @@

import { ErrorPacketParams, OkPacketParams, Pool, PoolConnection, Prepare, PrepareStatementInfo } from "mysql2";
/**
* A module that provides a database connection and a model class for interacting with the database.
* @example const mappifysql = require('mappifysql');
* @example const { Database, MappifyModel } = require('mappifysql');
* @example const { Database, MappifyModel, query, connection } = require('mappifysql');
* @module mappifysql

@@ -49,3 +51,3 @@ */

* This method is used to define a one-to-one relationship between two models.
* @param {Model} relatedModel - The related model.
* @param {MappifyModel} relatedModel - The related model.
* @param {object} options - The options for the association.

@@ -64,3 +66,3 @@ * @param {string} options.as - The alias for the association.

*/
hasOne(relatedModel: Class, options: object): void;
hasOne(relatedModel: MappifyModel, options: { as: string, foreignKey: string }): void;

@@ -70,3 +72,3 @@

* This method is used to define a one-to-one relationship between two models.
* @param {Model} relatedModel - The related model.
* @param {MappifyModel} relatedModel - The related model.
* @param {object} options - The options for the association.

@@ -103,3 +105,3 @@ * @param {string} options.as - The alias for the association.

*/
belongsTo(relatedModel: Class, options: object): void;
belongsTo(relatedModel: typeof MappifyModel, options: { as: string, key: string, foreignKey: string }): void;

@@ -109,3 +111,3 @@

* This method is used to define a one-to-many relationship between two models.
* @param {Model} relatedModel - The related model.
* @param {MappifyModel} relatedModel - The related model.
* @param {object} options - The options for the association.

@@ -124,3 +126,3 @@ * @param {string} options.as - The alias for the association.

*/
hasMany(relatedModel: Class, options: object): void;
hasMany(relatedModel: MappifyModel, options: { as: string, foreignKey: string }): void;

@@ -130,3 +132,3 @@

* This method is used to define a many-to-many relationship between two models.
* @param {Model} relatedModel - The related model.
* @param {MappifyModel} relatedModel - The related model.
* @param {object} options - The options for the association.

@@ -165,3 +167,3 @@ * @param {string} options.as - The alias for the association.

*/
belongsToMany(relatedModel: Class, options: object): void;
belongsToMany(relatedModel: MappifyModel, options: { as: string, through: MappifyModel, key: string, foreignKey: string, otherKey: string }): void;

@@ -192,3 +194,3 @@

*/
populate(relation: string, options?: object): Promise<this>;
populate(relation: string, options?: { attributes?: Array<string>, exclude?: Array<string> }): this;

@@ -198,3 +200,3 @@

* This method attaches a new record to the related model and associates it with the current instance.
* @param {Model} target - The record to attach to the relation.
* @param {MappifyModel} target - The record to attach to the relation.
* @param {string} relation - The name of the relation to attach to.

@@ -217,3 +219,3 @@ * @param {object} options - The options for the query. - (optional)

*/
attach(target: Model, relation: string, options?: object): Promise<this>;
attach(target: MappifyModel, relation: string, options?: { attributes?: Array<string>, exclude?: Array<string> }): Promise<this>;

@@ -260,4 +262,4 @@ /**

* @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 {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 } });

@@ -280,3 +282,3 @@ * @example var product = await Product.findOne({ where: { name: 'Product 1' }, exclude: ['created_at', 'updated_at'] });

*/
static findOne(options: object): Promise<Object<MappifyModel>>;
static findOne(options: { where: object, exclude?: Array<string>, attributes?: Array<string> }): Promise<MappifyModel | null>;

@@ -291,3 +293,3 @@

*/
static findById(id: number): Promise<Object<MappifyModel>>;
static findById(id: number): Promise<MappifyModel | null>;

@@ -297,10 +299,10 @@

* 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.
* @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();

@@ -327,3 +329,3 @@ * @example var products = await Product.findAll({ where: { price: { gt: 100 } } });

*/
static findAll(options?: object): Promise<Array<MappifyModel>>;
static findAll(options?: { where?: object, exclude?: Array<string>, attributes?: Array<string>, limit?: number, offset?: number, order?: string, group?: string }): Promise<Array<MappifyModel>>;

@@ -334,2 +336,5 @@

* @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 {object} data - The data to create the record with.

@@ -340,3 +345,3 @@ * @example var user = await User.findOrCreate({ where: { email: 'user@example.com' } }, { name: 'John Doe', password: 'password' });

*/
static findOrCreate(options: object, data: object): Promise<{ instance: MappifyModel, created: boolean }>;
static findOrCreate(options: { where: object, exclude?: Array<string>, attributes?: Array<string> }, data: object): Promise<{ instance: MappifyModel, created: boolean }>;

@@ -359,22 +364,21 @@

* @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.
* @returns {Promise<MappifyModel|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 findOneAndDelete(options: object): Promise<MappifyModel>;
static findOneAndDelete(options: { where: object }): Promise<MappifyModel>;
/**
* This static method updates a record in the table based on the provided options.
* @param options The options for the query.
* @param options.where The WHERE clause for the query.
* @param options.attributes The columns to include in the result.
* @param options.exclude The columns to exclude from the result.
* @param data The new data for the record.
* @example await Product.findOneAndUpdate({ where: { id: 1 } }, { price: 200 });
* @returns The updated instance or null if no record was found.
* @throws Throws an error if the where clause is not provided or if no record is found.
*/
static findOneAndUpdate(options: { where?: object, attributes?: object, exclude?: object }, data: object): Promise<Model | null>;
/**
* 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 {Array} [options.exclude] - The columns to exclude from the result.
* @param {Array} [options.attributes] - The columns to include in the result.
* @param data The new data for the record.
* @example await Product.findOneAndUpdate({ where: { id: 1 } }, { price: 200 });
* @returns The updated instance or null if no record was found.
* @throws Throws an error if the where clause is not provided or if no record is found.
*/
static findOneAndUpdate(options: { where: object, attributes?: object, exclude?: object }, data: object): Promise<MappifyModel | null>;

@@ -393,79 +397,74 @@

/**
* 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);
* }
*/
export class Database {
/**
* 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(): Promise<any>;
/**
* 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(): Promise<any>;
/**
* 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();
* Executes a MySQL query.
* @example const { query } = require('mappifysql');
* query('SELECT * FROM users').then((results) => {
* console.log(results);
* console.log(results);
* } catch (err) {
* console.error(err);
* }
* @returns {Function} The promisified query method.
* @param {string} sql - The SQL query string.
* @param {any} [values] - Optional values for parameterized SQL queries.
* @returns {Promise<any>} The results of the query.
*/
getQuery(): Function;
export function query(sql: string, values?: any): Promise<any>;
/**
* Gets the current connection.
* @example const db = new Database();
* db.createConnection().then(() => {
* }).catch((err) => {
* console.log(err);
* });
* const connection = db.getConnection();
* @returns {Connection} The current connection
/**
* Begins a new transaction.
* @example const { beginTransaction } = require('mappifysql');
* await beginTransaction();
* @returns {Promise<void>}
*/
export function beginTransaction(): Promise<void>;
/**
* Commits the current transaction.
* @example const { commit } = require('mappifysql');
* await commit();
* @returns {Promise<void>}
*/
export function commit(): Promise<void>;
/**
* Rolls back the current transaction.
* @example const { rollback } = require('mappifysql');
* await rollback();
* @returns {Promise<void>}
*/
export function rollback(): Promise<void>;
/**
* The current MySQL connection.
* @example const { connection } = require('mappifysql');
* connection.query('SELECT * FROM users').then((results) => {
* console.log(results);
* } catch (err) {
* console.error(err);
* }
*/
getConnection(): Connection;
export const connection: Connection;
}
interface Connection {
/**
* Begins a transaction.
* @param {function} [callback] - Optional callback function.
*/
* Begins a transaction. This allows you to execute multiple queries as part of a single transaction.
* @param {function} [callback] - Optional callback function.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.beginTransaction((err, connection) => {
* if (err) {
* console.error(err);
* }
* });0.4
*/
beginTransaction(callback?: (err: any) => void): void;
/**
* Commits the current transaction.
* Commits the current transaction. This saves all changes made since the last call to beginTransaction.
* @param {function} [callback] - Optional callback function.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.commit((err) => {
* if (err) {
* console.error(err);
* }
*/

@@ -475,4 +474,12 @@ commit(callback?: (err: any) => void): void;

/**
* Rolls back the current transaction.
* Rolls back the current transaction. This undoes all changes made since the last call to beginTransaction.
* @param {function} [callback] - Optional callback function.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.rollback((err) => {
* if (err) {
* console.error(err);
* }
* });
* @example connection.rollback();
*/

@@ -483,5 +490,13 @@ rollback(callback?: (err: any) => void): void;

* Sends a SQL query to the database.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.query('SELECT * FROM users', (err, results) => {
* if (err) {
* console.error(err);
* }
* @type {Function}
* @param {string} sql - The SQL query string.
* @param {any} [values] - Optional values for parameterized SQL queries.
* @param {function} [callback] - Optional callback function.
* @returns {void}
*/

@@ -491,4 +506,13 @@ query(sql: string, values?: any, callback?: (error: any, results: any, fields: any) => void): void;

/**
* Ends the connection.
* Ends the connection. This allows the connection to be closed and removed from the pool.
* @param {function} [callback] - Optional callback function.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.end((err) => {
* if (err) {
* console.error(err);
* }
* });
* @example connection.end();
* @returns {void}
*/

@@ -498,3 +522,7 @@ end(callback?: (err: any) => void): void;

/**
* Destroys the connection.
* Destroys the connection. This allows the connection to be closed and removed from the pool.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.destroy();
* @returns {void}
*/

@@ -504,3 +532,7 @@ destroy(): void;

/**
* Pauses the connection.
* Pauses the connection. This allows the connection to be temporarily disabled.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.pause();
* @returns {void}
*/

@@ -514,26 +546,146 @@ pause(): void;

/**
* Escapes a value for SQL.
* @param {any} value - The value to escape.
* @returns {string} The escaped value.
* Changes the user for the current connection.
* @param {any} options - The options for changing user.
* @param {function} [callback] - Optional callback function.
*/
escape(value: any): string;
changeUser(options: any, callback?: (err: any) => void): void;
/**
* Executes a SQL query and returns a promise.
* @param {string} sql - The SQL query string.
* @param {Function} [callback] - Optional callback function.
* @returns {Promise<any>} The results of the query.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* const results = await conn.execute('SELECT * FROM users');
* console.log(results);
*/
execute(sql: string, callback?: any): Promise<any>;
/**
* Synchronously calls each of the listeners registered for the event named eventName, in the order they were registered, passing the supplied arguments to each.
*Returns `true` if the event had listeners, `false` otherwise.
*/
emit(event: string | symbol, ...args: any[]): boolean;
/**
* Adds a **one-time** `listener` function for the event named `eventName`. The
* next time `eventName` is triggered, this listener is removed and then invoked.
*
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.once('connection', (stream) => {
* console.log('Ah, we have our first user!');
* });
*
* Returns a reference to the `EventEmitter`, so that calls can be chained.
* @returns {Function} The promisified query method.
* @param {string | symbol} event The name of the event.
* @param {Function} listener The callback function.
*/
once(event: string | symbol, listener: (...args: any[]) => void): this;
/**
* Adds the `listener` function to the end of the listeners array for the event
* named `eventName`. No checks are made to see if the `listener` has already
* been added. Multiple calls passing the same combination of `eventName` and
* `listener` will result in the `listener` being added, and called, multiple times.
*
*
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.on('connection', (stream) => {
* console.log('someone connected!');
* });
*
* Returns a reference to the `EventEmitter`, so that calls can be chained.
* @returns {Function} The promisified query method.
* @param {string | symbol} event The name of the event.
* @param {Function} listener The callback function.
*/
on(event: string | symbol, listener: (...args: any[]) => void): this;
/**
* unprepares a previously prepared statement.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.unprepare('SELECT * FROM users WHERE id = ?', (err) => {
* if (err) {
* console.error(err);
* }
* });
* @param {string} sql - The SQL query string.
* @returns {void}
*/
unprepare(sql: string): PrepareStatementInfo
/**
* Prepares a SQL statement.
* @example const { connection } = require('mappifysql');
* const statement = connection.prepare('SELECT * FROM users WHERE id = ?');
* @param {string} sql - The SQL query string.
* @param {function} [callback] - Optional callback function.
* @returns {Prepare} The prepared statement.
* @throws {Error} Throws an error if the SQL statement is not provided.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* const statement = con.prepare('SELECT * FROM users WHERE id = ?');
* console.log(statement);
* @example const { connection } = require('mappifysql');
*/
prepare(sql: string, callback?: (err: any, statement: PrepareStatementInfo) => void): Prepare;
/**
* Escapes an identifier for SQL.
* @param {any} value - The identifier to escape.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* const identifier = conn.escapeId('id');
* console.log(identifier);
* @param {string} value - The identifier to escape.
* @returns {string} The escaped identifier.
* @example const { connection } = require('mappifysql');
* const identifier = connection.escapeId('id');
* console.log(identifier);
*/
escapeId(value: any): string;
escapeId(value: string): string;
/**
* Formats a SQL query string.
* Escapes a value for SQL.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* const value = conn.escape('John Doe');
* console.log(value);
* @param {any} value - The value to escape.
* @returns {string}
*/
escape(value: any): string;
/**
* Formats a SQL query string. This is used to escape values for SQL.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* const sql = conn.format('SELECT * FROM users WHERE id = ?', [1]);
* console.log(sql);
* @param {string} sql - The SQL query string.
* @param {any} [values] - Optional values for parameterized SQL queries.
* @param {any | any[] | { [param: string]: any }} values - The values for the query.
* @returns {string} The formatted SQL query string.
*/
format(sql: string, values?: any): string;
format(sql: string, values: any): string;
/**
* Pings the server.
* Pings the server. This is used to check if the server is still connected.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.ping((err) => {
* if (err) {
* console.error(err);
* }
* });
* @param {function} [callback] - Optional callback function.

@@ -543,9 +695,75 @@ */

/**
* Changes the user for the current connection.
* @param {any} options - The options for changing user.
* @param {function} [callback] - Optional callback function.
* The server handshake. This is used to establish a connection to the server.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.serverHandshake((err) => {
* if (err) {
* console.error(err);
* }
* });
* @param {any} args - The arguments for the handshake.
*/
changeUser(options: any, callback?: (err: any) => void): void;
serverHandshake(args: any): any;
/**
* Returns a promise that resolves to the connection. This allows you to use async/await with the connection.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* const promise = conn.promise();
* const results = await promise.query('SELECT * FROM users');
* console.log(results);
* @param {PromiseConstructor} [promiseImpl] - Optional values for parameterized SQL queries.
* @returns {Promise<any>} The results of the query.
*/
promise(promiseImpl?: PromiseConstructor): Pool;
// authorized
/**
* The authorized status of the connection.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* const authorized = conn.authorized;
* if (authorized) {
* console.log('Authorized');
* } else {
* console.log('Not authorized');
* }
* @returns {boolean} The authorized status.
*/
authorized: boolean;
/**
* The thread ID of the connection.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* const threadId = conn.threadId;
* console.log(threadId);
* @returns {number} The thread ID.
*/
threadId: number;
/**
* The sequence ID of the connection.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* const sequenceId = conn.sequenceId;
* console.log(sequenceId);
* @returns {number} The sequence ID.
*/
sequenceId: number;
/**
* Releases the connection back to the pool. This allows the connection to be reused.
* @note This method is only available when using a pool.
* @example const { connection } = require('mappifysql');
* const conn = await connection;
* conn.release();
* @returns {void}
*/
release(): void;
}
}

34

app.js

@@ -26,13 +26,37 @@ /**

// Re-export for ESM support
/**
* Exports the query function from the mappifysql module.
* @type {Function}
*/
module.exports.query = mappifysql.query;
/**
* Exports the Database class from the mappifysql module.
* @type {Class}
* Exports the connection object from the mappifysql module.
* @type {Object}
*/
module.exports.Database = mappifysql.Database;
module.exports.connection = mappifysql.connection;
/**
* Exports the beginTransaction function from the mappifysql module.
* @type {Function}
*/
module.exports.beginTransaction = mappifysql.beginTransaction;
/**
* Exports the commit function from the mappifysql module.
* @type {Function}
*/
module.exports.commit = mappifysql.commit;
/**
* Exports the rollback function from the mappifysql module.
* @type {Function}
*/
module.exports.rollback = mappifysql.rollback;
/**
* Exports the MappifyModel class from the mappifysql module.
* @type {Class}
*/
module.exports.MappifyModel = mappifysql.MappifyModel;
module.exports.MappifyModel = mappifysql.MappifyModel;

50

lib/connection.js
const { Database } = require('./database');
const db = new Database();
db.createConnection().then(() => {
let connectionPromise;
if (process.env.DB_USE_POOL === 'true') {
console.log('Using connection pool');
connectionPromise = db.createPool();
} else {
connectionPromise = db.createConnection();
}
connectionPromise.then(async () => {
console.log('Connection created successfully');
}).catch((err) => {

@@ -8,4 +18,38 @@ console.log(err);

var query = db.getQuery();
let query = async (sql, values) => {
const queryFunction = await db.getQuery();
return queryFunction(sql, values);
};
module.exports = { query };
let connection;
let beginTransaction = async () => await db.beginTransaction();
let commit = async () => await db.commit();
let rollback = async () => await db.rollback();
if (process.env.DB_USE_POOL === 'true') {
connection = new Promise((resolve, reject) => {
db.getConnectionFromPool((err, conn) => {
if (err) {
db.pool.emit('error', err);
reject(err);
} else {
resolve(conn);
}
});
});
} else {
connection = new Promise((resolve, reject) => {
db.getConnection((err, conn) => {
if (err) {
reject(err);
} else {
resolve(conn);
}
});
});
}
module.exports = { query, connection, beginTransaction, commit, rollback };

184

lib/database.js
const mysql = require('mysql2');
const util = require('util');
const dotenv = require('dotenv');
const { ne } = require('./condition');
dotenv.config();
/**

@@ -21,3 +21,3 @@ * Database class for managing MySQL connections.

constructor() {
this.config = {
this.connectionConfig = {
host: process.env.DB_HOST,

@@ -27,7 +27,26 @@ user: process.env.DB_USER,

database: process.env.DB_NAME,
port: process.env.DB_PORT || 3306,
connectTimeout: 5000000,
port: Number(process.env.DB_PORT) || 3306,
connectTimeout: Number(process.env.DB_CONNECT_TIMEOUT) || 10000,
};
this.connection = this.createConnection() || this.createPool();
this.poolConfig = {
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
port: Number(process.env.DB_PORT) || 3306,
waitForConnections: true || (process.env.DB_WAIT_FOR_CONNECTIONS === 'true'), // default to true
connectionLimit: Number(process.env.DB_CONNECTION_LIMIT) || 10, // default to 10
idleTimeout: Number(process.env.DB_IDLE_TIMEOUT) || 60000, // default to 60 seconds
queueLimit: Number(process.env.DB_QUEUE_LIMIT) || 0, // default to 0
enableKeepAlive: true || (process.env.DB_ENABLE_KEEP_ALIVE === 'true'), // default to true
keepAliveInitialDelay: Number(process.env.DB_KEEP_ALIVE_INITIAL_DELAY) || 0, // default to 0
};
// Assign maxIdle after poolConfig is defined
this.poolConfig.maxIdle = Number(process.env.DB_MAX_IDLE) || this.poolConfig.connectionLimit; // default to connection limit
this.connection = null;
this.pool = null;
this.currentPoolConnection = null;
}

@@ -47,3 +66,3 @@

return new Promise((resolve, reject) => {
this.connection = mysql.createConnection(this.config);
this.connection = mysql.createConnection(this.connectionConfig);
this.connection.connect((err) => {

@@ -74,4 +93,4 @@ if (err) {

return new Promise((resolve, reject) => {
this.connection = mysql.createPool(this.config);
this.connection.getConnection((err, connection) => {
this.pool = mysql.createPool(this.poolConfig);
this.pool.getConnection((err, connection) => {
if (err) {

@@ -82,4 +101,4 @@ reject(new Error('error connecting: ' + err.stack));

connection.release();
this.initializeConnection();
resolve(this.connection);
this.initializePool();
resolve(this.pool);
}

@@ -91,10 +110,7 @@ })

// initialize connection when connection is lost
initializeConnection() {
this.query = util.promisify(this.connection.query).bind(this.connection);
async initializeConnection() {
this.connection.on('error', (err) => {
if (err.code === 'PROTOCOL_CONNECTION_LOST' || err.code === 'ECONNRESET') {
console.log('Database connection lost. Reconnecting...');
if (this.connection.connect) {
this.connect();
}
this.createConnection().catch(console.error);
} else {

@@ -104,19 +120,63 @@ throw new Error('Database connection error: ' + err);

});
}
}
initializePool() {
this.pool.on('error', (err) => {
if (err.code === 'PROTOCOL_CONNECTION_LOST' || err.code === 'ECONNRESET') {
console.log('Database connection lost. Reconnecting...');
this.createPool().catch(console.error);
} else {
throw new Error('Database connection error: ' + err);
}
});
}
/**
* Gets the current connection.
* @example const db = new Database();
* db.createConnection().then(() => {
* }).catch((err) => {
* console.log(err);
* });
* const connection = db.getConnection();
* @returns {any} The current connection.
* Gets connection from the connection
* @param {Function} callback - The callback function to handle the connection or error.
*/
getConnection() {
return this.connection;
getConnection(callback) {
if (!this.connection) {
callback(new Error('No connection available'), null);
} else {
callback(null, this.connection);
}
}
/**
* Gets a connection from the pool.
* @param {Function} callback - The callback function to handle the connection or error.
*/
getConnectionFromPool(callback) {
if (!this.pool) {
callback(new Error('No pool available'), null);
} else {
this.pool.getConnection((err, connection) => {
if (err) {
this.pool.emit('error', err);
callback(err, null);
} else {
connection.on('error', (err) => {
this.pool.emit('error', err);
});
callback(null, connection);
}
});
}
}
getConnectionFromPoolAsync() {
return new Promise((resolve, reject) => {
this.pool.getConnection((err, connection) => {
if (err) {
reject(err);
} else {
resolve(connection);
}
});
});
}
/**

@@ -134,8 +194,72 @@ * Gets a promisified version of the query method from the connection.

*/
getQuery() {
return this.query = util.promisify(this.connection.query).bind(this.connection);
async getQuery() {
if (this.connection) {
return this.createQueryFunction(this.connection);
} else if (this.pool) {
if (!this.currentPoolConnection) {
this.currentPoolConnection = await this.getConnectionFromPoolAsync();
}
return this.createQueryFunction(this.currentPoolConnection);
} else {
throw new Error('No connection or pool available');
}
}
createQueryFunction(connection) {
return async (sql, values) => {
try {
const query = util.promisify(connection.query).bind(connection);
return await query(sql, values);
} catch (err) {
throw new Error('Error executing query: ' + err);
}
};
}
async beginTransaction() {
if (this.connection) {
return util.promisify(this.connection.beginTransaction).bind(this.connection)();
} else if (this.pool) {
if (!this.currentPoolConnection) {
this.currentPoolConnection = await this.getConnectionFromPoolAsync();
}
return util.promisify(this.currentPoolConnection.beginTransaction).bind(this.currentPoolConnection)();
} else {
throw new Error('No connection or pool available');
}
}
async commit() {
if (this.connection) {
return util.promisify(this.connection.commit).bind(this.connection)();
} else if (this.pool) {
if (!this.currentPoolConnection) {
throw new Error('No active transaction');
}
const result = await util.promisify(this.currentPoolConnection.commit).bind(this.currentPoolConnection)();
this.currentPoolConnection.release();
this.currentPoolConnection = null;
return result;
} else {
throw new Error('No connection or pool available');
}
}
async rollback() {
if (this.connection) {
return util.promisify(this.connection.rollback).bind(this.connection)();
} else if (this.pool) {
if (!this.currentPoolConnection) {
throw new Error('No active transaction');
}
const result = await util.promisify(this.currentPoolConnection.rollback).bind(this.currentPoolConnection)();
this.currentPoolConnection.release();
this.currentPoolConnection = null;
return result;
} else {
throw new Error('No connection or pool available');
}
}
}
module.exports.Database = Database;
module.exports.Database = Database;

78

lib/index.js

@@ -1,31 +0,32 @@

const { connection } = require('./connection');
const { Database } = require('./database');
const { connection, query, beginTransaction, commit, rollback } = require('./connection');
const { MappifyModel } = require('./model');
// class User extends MappifyModel {
// }
/**
* Model class for managing database records.
* @example class User extends MappifyModel {}
*/
module.exports.MappifyModel = MappifyModel;
/**
* Function for executing MySQL queries.
* @example const { query } = require('mappifysql');
* query('SELECT * FROM users').then((results) => {
* console.log(results);
* } catch (err) {
* console.error(err);
* }
*
* const results = await query('SELECT * FROM users');
* console.log(results);
* @param {string} sql - The SQL query string.
* @param {any} [values] - Optional values for parameterized SQL queries.
* @returns {Promise<any>} The results of the query.
*/
module.exports.query = query;
// (async () => {
// try {
// connection.beginTransaction();
// let user = new User({ first_name: 'John', last_name: 'Doe' });
// await user.save();
// let user2 = new User({ firstname: 'Jane', last_name: 'Doe' });
// await user2.save();
// connection.commit();
// } catch (err) {
// connection.rollback();
// console.error(err);
// }
// })();
/**
* Database class for managing MySQL connections.
* @example const db = new Database();
* db.createConnection().then((connection) => {
* console.log('Connection created successfully');
* The current MySQL connection.
* @example const { connection } = require('mappifysql');
* connection.query('SELECT * FROM users').then((results) => {
* console.log(results);
* } catch (err) {

@@ -35,8 +36,27 @@ * console.error(err);

*/
module.exports.Database = Database;
module.exports.connection = connection;
/**
* Model class for managing database records.
* @example class User extends MappifyModel {}
* Begins a new transaction.
* @example const { beginTransaction } = require('mappifysql');
* await beginTransaction();
* @returns {Promise<void>}
*/
module.exports.MappifyModel = MappifyModel;
module.exports.beginTransaction = beginTransaction;
/**
* Commits the current transaction.
* @example const { commit } = require('mappifysql');
* await commit();
* @returns {Promise<void>}
*/
module.exports.commit = commit;
/**
* Rolls back the current transaction.
* @example const { rollback } = require('mappifysql');
* await rollback();
* @returns {Promise<void>}
*/
module.exports.rollback = rollback;

68

lib/model.js

@@ -391,3 +391,3 @@ const { query } = require('../lib/connection');

*/
async populate(relation, options = {}) {
async populate(relation, options = { attributes: ['*'], exclude: [] }) {
// Check if the relation is defined

@@ -479,4 +479,3 @@ if (!this.associations[relation]) {

*/
async attach(target, relation, options = {}) {
async attach(target, relation, options = { attributes: ['*'], exclude: [] }) {
if (!this.associations[relation]) {

@@ -582,8 +581,8 @@ throw new Error(`Relation "${relation}" is not defined`);

/**
/**
* 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.
* @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 } });

@@ -604,5 +603,5 @@ * @example var product = await Product.findOne({ where: { name: 'Product 1' }, exclude: ['created_at', 'updated_at'] });

* @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.
* @returns {Promise<Object|null>} An instance of an array or null if no record was found.
*/
static async findOne(options = {}) {
static async findOne(options = {where: {}, exclude: [], attributes: ['*']}) {
// Destructure options

@@ -648,10 +647,10 @@ const { where = {}, exclude = [], attributes = ['*'] } = options;

* 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.
* @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();

@@ -678,3 +677,3 @@ * @example var products = await Product.findAll({ where: { price: { gt: 100 } } });

*/
static async findAll(options = {}) {
static async findAll(options = {where: {}, exclude: [], attributes: ['*'], limit: null, offset: null, order: null, group: null}) {
const { attributes = ['*'], exclude = [], where = {}, limit, offset, order, group } = options;

@@ -688,2 +687,3 @@

var { whereClause, whereValues } = prepareWhereClause(where, conditions);
}

@@ -720,2 +720,5 @@

* @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 {object} data - The data to create the record with.

@@ -726,3 +729,3 @@ * @example var user = await User.findOrCreate({ where: { email: 'user@example.com' } }, { name: 'John Doe', password: 'password' });

*/
static async findOrCreate(options = {}, data) {
static async findOrCreate(options = {where: {}, exclude: [], attributes: ['*']}, data) {
var record = await this.findOne(options);

@@ -737,3 +740,3 @@ if (record) {

record = new this({ ...where, ...data });
await record.create();
await record.save();
return { instance: record, created: true }

@@ -762,7 +765,6 @@ }

/**
/**
* 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' } });

@@ -772,3 +774,3 @@ * @returns {Promise<MappifyModel|null>} The updated instance or null if no record was found.

*/
static async findOneAndDelete(options = {}) {
static async findOneAndDelete(options = {where: {}}) {
if (Object.keys(options).length > 1) {

@@ -786,13 +788,13 @@ throw new Error('Only where clause 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} 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<MappifyModel|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) {
* 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 {Array} [options.exclude] - The columns to exclude from the result.
* @param {Array} [options.attributes] - The columns to include in the result.
* @param data The new data for the record.
* @example await Product.findOneAndUpdate({ where: { id: 1 } }, { price: 200 });
* @returns The updated instance or null if no record was found.
* @throws Throws an error if the where clause is not provided or if no record is found.
*/
static async findOneAndUpdate(options = {where: {}, exclude: [], attributes: ['*']}, data) {
var instance = await this.findOne(options);

@@ -799,0 +801,0 @@ if (instance) {

15

package.json
{
"name": "mappifysql",
"version": "1.1.3",
"version": "1.1.4",
"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.",

@@ -20,4 +20,12 @@ "repository": {

"main": "app.js",
"files": [
"app.js",
"lib/",
"LICENSE",
"README.md",
"package.json",
"app.d.ts"
],
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"test": "jest",
"start": "node app.js",

@@ -56,3 +64,6 @@ "dev": "nodemon app.js"

"pluralize": "^8.0.0"
},
"devDependencies": {
"jest": "^29.7.0"
}
}

370

README.md

@@ -16,2 +16,18 @@ <div align="center">

## Table of Contents
- [Features](#features)
- [Why MappifySQL?](#why-mappifysql)
- [Installation](#installation)
- [Getting Started](#getting-started)
- [Connecting to a Database](#connecting-to-a-database)
- [Class, Function, and Object available in MappifySQL](#class-function-and-object-available-in-mappifysql)
- [Using the Query Method](#using-the-query-method)
- [Using the Connection Object](#using-the-connection-object)
- [Using the Model Class](#using-the-model-class)
- [Performing CRUD Operations](#performing-crud-operations)
- [Transactions](#transactions)
- [Relationships](#relationships)
- [Issues](#issues)
- [License](#license)
- [References](#references)

@@ -21,6 +37,8 @@ ## Features

- **Object-Relational Mapping**: Map your database tables to JavaScript or TypeScript objects for easier and more intuitive data manipulation.
- **Auto Connection**: Automatically connect to your database using environment variables.
- **Connection Pooling**: Use connection pooling to improve performance and scalability in your application.
- **Model Class**: Define a model class for each table in your database to encapsulate database operations.
- **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.
- **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.

@@ -48,5 +66,4 @@ - **TypeScript Support**: Use MappifySQL with TypeScript for type-safe database interactions.

### Importing the Library
#### import and use the library in your JavaScript or TypeScript file:
### import and use the library in your JavaScript or TypeScript file:

@@ -56,3 +73,2 @@ ```javascript

const Database = mappifysql.Database;
const MappifyModel = mappifysql.MappifyModel;

@@ -64,3 +80,2 @@ ```

const Database = mappifysql.Database;
const MappifyModel = mappifysql.MappifyModel;

@@ -72,7 +87,7 @@ ```

```javascript
const { Database, MappifyModel } = require('mappifysql');
const { MappifyModel } = require('mappifysql');
```
```typescript
import { Database, MappifyModel } from 'mappifysql';
import { MappifyModel } from 'mappifysql';
```

@@ -92,145 +107,138 @@

DB_PORT=3306 ## (optional) default is 3306
```
Then, create a new JavaScript file (e.g., connection.js) and one of the following code snippets:
DB_USE_POOL=true ## (optional) default is false ## For pool connection
# For connection pooling
DB_WAIT_FOR_CONNECTIONS=true ## (optional) default is true
DB_CONNECTION_LIMIT=10 ## (optional) default is 10
DB_MAX_IDLE=10 ## (optional) default is set to the connection limit
DB_IDLE_TIMEOUT=60000 ## (optional) default is 60000
DB_QUEUE_LIMIT=0 ## (optional) default is 0
DB_ENABLE_KEEP_ALIVE=true ## (optional) default is true
DB_KEEP_ALIVE_INITIAL_DELAY=0 ## (optional) default is 0
#### Create a single connection to the database
Create a new instance of the Database class and call the createConnection method to establish a single connection to the database
```
```javascript
### Class, Function, and Object available in MappifySQL
const { Database } = require('mappifysql');
| Class/Function/Object | Description |
| --- | --- |
| `MappifyModel` | Base class for defining models that represent database tables. |
| `query` | Function for executing SQL queries. |
| `connection` | Object representing the current MySQL connection. |
| `beginTransaction` | Function to begin a new transaction. |
| `commit` | Function to commit the current transaction. |
| `rollback` | Function to roll back the current transaction. |
const db = new Database();
db.createConnection().then(() => {
console.log('Database connected successfully');
}).catch((err) => {
console.error(err);
});
### Using the Query Method
var connection = db.getConnection();
var query = db.getQuery();
The query method allows you to execute SQL queries and returns a promise that resolves with the result of the query. You can use this method to perform CRUD operations, fetch data, and more.
module.exports = { connection, query };
```
Here's an example of how to use the query method to fetch all records from a table:
** Using TypeScript **
```javascript
const { query } = require('mappifysql');
// import { query } from 'mappifysql';
```typescript
import { Database } from 'mappifysql';
let fetchAll = async () => {
try {
let results = await query('SELECT * FROM users');
console.log('Fetched records:', results);
} catch (err) {
console.error(err);
}
};
const db = new Database();
db.createConnection().then(() => {
console.log('Database connected successfully');
}).catch((err) => {
console.error(err);
});
var connection = db.getConnection();
var query = db.getQuery();
export { connection, query };
let addData = async () => {
try {
let result = await query('INSERT INTO users (first_name, email) VALUES (?, ?)', ['John Doe', 'example@gmail.com']);
console.log('New record - inserted id:', result.insertId);
} 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 or then and catch to handle the asynchronous nature of the database operations.
### Using the Connection Object
<div align="center">
<img src="https://i.ibb.co/NptYQGf/createsingleconnection.png" alt="createSingleConnection" border="0">
</div>
The connection object provides methods for interacting with the database. You can use the query method to execute SQL queries and the other methods to query the database, manage transactions, ping the server, and more.
#### Create a pool of connections to the database
Call the createPool method to establish a pool of connections to the database. This is useful for managing multiple concurrent database queries, improving performance.
```javascript
const { connection } = require('mappifysql');
// import { connection } from 'mappifysql';
const { Database } = require('mappifysql');
(async () => {
let conn = await connection;
console.log('Connected to database:', conn.threadId);
const db = new Database();
// Perform database operations here
conn.query('SELECT * FROM users', (err, results) => {
if (err) {
console.error(err);
} else {
console.log('Fetched records:', results);
}
});
db.createPool().then(() => {
console.log('Database connected successfully');
}).catch((err) => {
console.error(err);
});
//ping the server
conn.ping((err) => {
if (err) {
console.error(err);
} else {
console.log('Server pinged successfully');
}
});
var connection = db.getConnection();
var query = db.getQuery();
// End the connection
conn.end(); // or conn.release() to release the connection back to the pool
module.exports = { connection, query };
```
conn.pause();
```typescript
setTimeout(() => {
conn.resume();
}, 5000);
import { Database } from 'mappifysql';
const db = new Database();
db.createPool().then(() => {
console.log('Database connected successfully');
}).catch((err) => {
console.error(err);
});
var connection = db.getConnection();
var query = db.getQuery();
export { connection, query };
})();
```
<div align="center">
<img src="https://i.ibb.co/6r0npjy/createpoolconnection.png" alt="createPoolConnection" border="0">
</div>
Here's a list of the methods available in the connection object:
you can also use other methods available in the connection object that are not listed here but are available in the mysql library.
| Method | Description | Parameters |
| --- | --- | --- |
| `authorized` | The authorized status of the connection. | None |
| `beginTransaction` | Begins a transaction. | `callback?: (err: any) => void` |
| `changeUser` | Changes the user for the current connection. | `options: any`, `callback?: (err: any) => void` |
| `commit` | Commits the current transaction. | `callback?: (err: any) => void` |
| `destroy` | Destroys the connection. | None |
| `emit` | Synchronously calls each of the listeners registered for the event named eventName. | `event: string | symbol`, `...args: any[]` |
| `end` | Ends the connection. | `callback?: (err: any) => void` |
| `escape` | Escapes a value for SQL. | `value: any` |
| `escapeId` | Escapes an identifier for SQL. | `value: any` |
| `execute` | Executes a SQL query and returns a promise. | `sql: string`, `callback?: any` |
| `format` | Formats a SQL query string. | `sql: string`, `values?: any` |
| `once` | Adds a one-time listener function for the event named eventName. | `event: string | symbol`, `listener: (...args: any[]) => void` |
| `on` | Adds the listener function to the end of the listeners array for the event named eventName. | `event: string | symbol`, `listener: (...args: any[]) => void` |
| `pause` | Pauses the connection. | None |
| `ping` | Pings the server. | `callback?: (err: any) => void` |
| `prepare` | Prepares a SQL statement. | `sql: string`, `callback?: (err: any, statement: PrepareStatementInfo) => void` |
| `promise` | Returns a promise that resolves to the connection. | `promiseImpl?: PromiseConstructor` |
| `query` | Sends a SQL query to the database. | `sql: string`, `values?: any`, `callback?: (error: any, results: any, fields: any) => void` |
| `release` | Releases the connection back to the pool. | None |
| `releaseConnection` | Releases a connection back to the pool. | `connection: PoolConnection` |
| `resume` | Resumes the connection. | None |
| `rollback` | Rolls back the current transaction. | `callback?: (err: any) => void` |
| `sequenceId` | The sequence ID of the connection. | None |
| `serverHandshake` | The server handshake. | `args: any` |
| `threadId` | The thread ID of the connection. | None |
| `unprepare` | Unprepares a previously prepared statement. | `sql: string` |
Methods available in the connection object:
| Method | Description | Parameters | Supported by |
| --- | --- | --- | --- |
| `beginTransaction` | Begins a transaction. | `callback?: (err: any) => void` | `createConnection` |
| `commit` | Commits the current transaction. | `callback?: (err: any) => void` | `createConnection` |
| `rollback` | Rolls back the current transaction. | `callback?: (err: any) => void` | `createConnection` |
| `query` | Sends a SQL query to the database. | `sql: string`, `values?: any`, `callback?: (error: any, results: any, fields: any) => void` | `createConnection`, `createPool` |
| `end` | Ends the connection. | `callback?: (err: any) => void` | `createConnection`, `createPool` |
| `destroy` | Destroys the connection. | None | `createConnection` |
| `pause` | Pauses the connection. | None | `createConnection` |
| `resume` | Resumes the connection. | None | `createConnection` |
| `escape` | Escapes a value for SQL. | `value: any` | `createConnection`, `createPool` |
| `escapeId` | Escapes an identifier for SQL. | `value: any` | `createConnection`, `createPool` |
| `format` | Formats a SQL query string. | `sql: string`, `values?: any` | `createConnection`, `createPool` |
| `ping` | Pings the server. | `callback?: (err: any) => void` | `createConnection`, `createPool` |
| `changeUser` | Changes the user for the current connection. | `options: any`, `callback?: (err: any) => void` | `createConnection` |
Example:
```javascript
const { connection } = require('./connection');
connection.query('SELECT * FROM users', (err, results, fields) => {
if (err) {
throw err;
}
console.log('Fetched records:', results);
});
```
** Using TypeScript **
```typescript
import { connection } from './connection';
connection.query('SELECT * FROM users', (err, results, fields) => {
if (err) {
throw err;
}
console.log('Fetched records:', results);
});
```
### Using the Model Class

@@ -857,3 +865,3 @@

### Custom Queries
<!-- ### Custom Queries

@@ -864,3 +872,3 @@ 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.

```javascript
const { connection, query } = require('./connection');
const { connection, query } = require('mappifysql');

@@ -890,5 +898,5 @@ let customQuery = async () => {

```
<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.
``` -->
### Pagination

@@ -1010,16 +1018,16 @@

<span style="color:red;"><b>Note</b></span>: Transactions are only supported when created a single connection using the createConnection method. Transactions are not supported in pool because a pool consists of multiple connections to the database.
#### Starting a Transaction using the beginTransaction, commit, and rollback methods
```javascript
const { connection, query } = require('./connection');
const { query, beginTransaction, commit, rollback } = require('mappifysql');
let performTransaction = async () => {
try {
connection.beginTransaction();
var user = await query('INSERT INTO users SET ?', { name: 'John Doe'});
await beginTransaction();
const user = await query('INSERT INTO users SET ?', { name: 'John Doe'});
await query('INSERT INTO addresses SET ?', { user_id: user.insertId, address: '123 Main St' });
connection.commit();
await commit();
console.log('Transaction completed successfully');
} catch (err) {
connection.rollback();
await rollback();
console.error(err);

@@ -1033,3 +1041,3 @@ }

try {
connection.beginTransaction();
await beginTransaction();
let user = new User({ name: 'John Doe' });

@@ -1039,6 +1047,6 @@ await user.save();

await address.save();
connection.commit();
await commit();
console.log('Transaction completed successfully');
} catch (err) {
await connection.rollback();
await rollback();
console.error(err);

@@ -1050,3 +1058,85 @@ }

#### Starting a Transaction using the query or connection object directly
<span style="color:red;"><b>Note</b></span>: When using the connection object directly, you must release the connection after the transaction is completed in the finally block and you can't use the connection object directly with the model class as it is not exposed to the model class.
If you wan to use transaction with the model class, you must use the query method or <a href="#starting-a-transaction-using-the-begintransaction-commit-and-rollback-method">using beginTransaction commit, and rollback methods from mappifysql</a>
```javascript
const { query, connection } = require('mappifysql');
let performTransaction = async () => {
try {
await query('START TRANSACTION');
const user = await query('INSERT INTO users SET ?', { name: 'John Doe'});
await query('INSERT INTO addresses SET ?', { user_id: user.insertId, address: '123 Main St' });
await query('COMMIT');
console.log('Transaction completed successfully');
} catch (err) {
await query('ROLLBACK');
console.error(err);
}
};
// using query function with the Model class
let performTransaction = async () => {
try {
await query('START TRANSACTION');
let user = new User({ name: 'John Doe' });
await user.save();
let address = new Address({ user_id: user.id, address: '123 Main St' });
await address.save();
await query('COMMIT');
console.log('Transaction completed successfully');
} catch (err) {
await query('ROLLBACK');
console.error(err);
}
};
// using the connection object directly
// Note: You can't use the connection object directly with the model class as it is not exposed to the model class and the connection must be released after the transaction is completed in the finally block.
let performTransaction = async () => {
let conn = await connection;
try {
conn.beginTransaction();
const user = conn.query('INSERT INTO users SET ?', { first_name: 'John Doe' });
conn.query('INSERT INTO addresses SET ?', { user_id: user.insertId, address: '123 Main St' });
conn.commit();
console.log('Transaction completed successfully');
} catch (err) {
conn.rollback();
console.error(err);
} finally {
conn.release();
}
};
// using the connection object query method
let performTransaction = async () => {
let conn = await connection;
try {
conn.query('START TRANSACTION');
const user = conn.query('INSERT INTO users SET ?', { first_name: 'John Doe' });
conn.query('INSERT INTO addresses SET ?', { user_id: user.insertId, address: '123 Main St' });
conn.query('COMMIT');
console.log('Transaction completed successfully');
} catch (err) {
conn.query('ROLLBACK');
console.error(err);
} finally {
conn.release();
}
};
```
### Relationships

@@ -1123,2 +1213,3 @@ MappifySQL allows you to define relationships between your tables, making it easier to fetch related data.

Usage:

@@ -1128,6 +1219,8 @@ ```javascript

Order.findByOne({ where: { id: 1 }}).then(async (order) => {
await order.populate('shippingAddress', {exclude: ['created_at', 'updated_at']}).then((order) => {
console.log('Order with shipping address:', order);
});
Order.findOne({ where: { id: 1 }}).then(async (order) => {
if (order) {
await order.populate('shippingAddress', {exclude: ['created_at', 'updated_at']}).then((order) => {
console.log('Order with shipping address:', order);
});
}
}).catch((err) => {

@@ -1361,6 +1454,7 @@ console.error(err);

<span style="color:red;"><b>Note</b></span>: When using the `belongsToMany` method, import the through model at the bottom of the file to avoid circular dependencies.
```javascript
const { MappifyModel } = require('mappifysql');
const Category = require('path/to/Category');
const ProductCategory = require('path/to/ProductCategory');

@@ -1380,2 +1474,3 @@ class Product extends MappifyModel {

module.exports = Product;
const ProductCategory = require('path/to/ProductCategory');

@@ -1402,3 +1497,2 @@ ```

import Product from 'path/to/Product';
import ProductCategory from 'path/to/ProductCategory';

@@ -1434,3 +1528,3 @@ interface CategoryAttributes {

export default Category;
import ProductCategory from 'path/to/ProductCategory';
```

@@ -1540,3 +1634,3 @@

```typescript
import Enrollment from 'path/to/Enrollment
import Enrollment from 'path/to/Enrollment';

@@ -1543,0 +1637,0 @@ let enroll = async () => {

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc