fadab-mysql-helper
v1.4.2
Published
fadab-mysql-helper is the mysql helper package.
Downloads
36
Maintainers
Readme
Fadab MySQL Helper
A lightweight Promise-based wrapper and helper for felixge's node-Mysql.
Features:
- Very slim library, only 2 dependancies (app-root-path and felixge's node-mysql).
- Convenience functions for record selecting, inserting, updating and insert (on duplicate) updating.
- Connection pooling.
- Everything based on Promises.
Install
npm i fadab-mysql-helper --save
yarn add fadab-mysql-helper
Usage
Fadab Config File
The fadab.config file should be created in the project root directory. Format you connection options based on felixge's options.
Javascript File Example
module.exports = {
mysql: {
host: 'host',
user: 'user',
password: 'password',
database: 'database_name'
}
};
Typescript File Example
export default {
mysql: {
host: 'host',
user: 'user',
password: 'password',
database: 'database_name'
}
};
Defining Library
Javascript Example
const FadabMysql = require('fadab-mysql-helper');
Typescript Example
import * as FadabMysql from 'fadab-mysql-helper';
Selecting a record
FadabMysql.selectAsync('tableName')
.then(function (record) {
console.log(record);
})
.catch(function (err) {
console.log('Error fetching record, mysql error:', err.message);
});
// or
const record = await FadabMysql.selectAsync('tableName');
console.log(record);
Options can be added for the select operation.
- where: A condition is created with the data sent as object.
- distinct: The data taken with this parameter receiving the Boolean value is unique.(Default:false)
- fields: The names of the columns whose data are required in the table are sent in array format and listed.
- orderBy: Specifies by which columns the listed data should be sorted and how;
- fields: The names of the table columns that need to be sorted are sent as an array.
- ranking: ASC or DESC property is specified and sorting is performed.
- limit: The amount of data to be listed is specified.
- offset: Specifies from which index to start the data to be listed.
- isRandom: Lists random data.
const where = {
FirstName: 'ismet'
};
// Creates a where query with the data sent in the where object.
// List the users whose name is ismet with this operation.
const records = await FadabMysql.selectAsync('tableName', { where });
console.log(records);
const orderBy = {
field: 'FirstName',
ranking: 'ASC'
};
// Sort by First Name column.
const records = await FadabMysql.selectAsync('tableName', { orderBy });
console.log(records);
const fields = ['FirstName', 'LastName'];
// It lists data from the table according to the desired columns.
const records = await FadabMysql.selectAsync('tableName', { fields });
console.log(records);
const records = await FadabMysql.selectAsync('tableName', {
limit: 2,
offset: 1
});
console.log(records);
Advanced Query Examples
const orderBy = {
field: "FirstName",
ranking: "ASC"
};
const where = [
{
key: 'FirstName',
value: 'ismet',
conditionType: 'eq'
},
{
key: 'LastName',
value: 'kizgin',
conditionType: 'eq'
},
{
key: 'Age',
value: 18,
conditionType: 'gte'
}
];
const records = await FadabMysql.selectAsync('tableName', { orderBy, where, limit: 2, offset: 1 });
console.log(records);
const orderBy = [
{
field: "FirstName"
ranking: "ASC"
},
{
field: "LastName",
ranking: "ASC"
}
];
const where = {
_and: {
FirstName: 'ismet',
LastName: 'kizgin'
},
_or: [
{
key: 'Age',
value: 18,
conditionType: 'gte'
},
{
key: 'Age',
value: 10,
conditionType: 'lte'
}
]
};
const records = await FadabMysql.selectAsync('tableName', { orderBy, where, limit: 2, offset: 1 });
console.log(records);
Count
const total = await FadabMysql.countAsync('tableName');
console.log(total);
const where = {
_and: {
FirstName: 'ismet',
LastName: 'kizgin'
},
_or: [
{
key: 'Age',
value: 18,
conditionType: 'gte'
},
{
key: 'Age',
value: 10,
conditionType: 'lte'
}
]
};
const total = await FadabMysql.countAsync('tableName', { where });
console.log(total);
Find One
// Returns a single record as object.
const records = await FadabMysql.findOneAsync('tableName', { id: 1 });
console.log(records);
Advanced Example
const orderBy = [
{
field: "FirstName"
ranking: "ASC"
},
{
field: "LastName",
ranking: "ASC"
}
];
const where = {
FirstName: "ismet"
};
const fields = ["FirstName", "LastName"];
const records = await FadabMysql.selectAsync('tableName', { orderBy, where, fields, limit: 2, offset: 1 });
console.log(records);
Inserting a record
const insert = {
EmailAddress: '[email protected]',
FirstName: 'İsmet',
LastName: 'Kizgin'
};
FadabMysql.insertAsync('tblUser', insert)
.then(function (info) {
console.log('New User Entered!', info);
})
.catch(function (err) {
console.log('Error creating new user, mysql error:', err.message);
});
// or
const info = await FadabMysql.insertAsync('tblUser', insert);
console.log(info);
//info is an object with affectedRows and insertId
There is also a boolean 3rd argument, true if you want "INSERT IGNORE"
Multi insert records
const insert = [
{
EmailAddress: '[email protected]',
FirstName: 'İsmet',
LastName: 'Kizgin'
},
{
EmailAddress: '[email protected]',
FirstName: 'İsmet',
LastName: 'Kizgin'
}
];
FadabMysql.bulkInsertAsync('tblUser', insert)
.then(function (info) {
console.log('New User Entered!', info);
})
.catch(function (err) {
console.log('Error creating new user, mysql error:', err.message);
});
// or
const info = await FadabMysql.bulkInsertAsync('tblUser', insert);
console.log(info);
//info is an object with affectedRows and insertId
There is also a boolean 3rd argument, true if you want "INSERT IGNORE"
Updating a record
var where = {
Id: 1
};
var update = {
EmailAddress: '[email protected]',
FirstName: 'İsmet',
LastName: 'Kizgin'
};
FadabMysql.updateAsync('tblUser', update, where)
.then(function (info) {
console.log('User Updated!', info);
})
.catch(function (err) {
console.log('Error updating record, mysql error:', err.message);
});
// or
const info = await FadabMysql.updateAsync('tblUser', update, where);
console.log(info);
//info is an object with affectedRows, changedRows
Upsert a record
var user = {
EmailAddress: '[email protected]',
FirstName: 'İsmet',
LastName: 'Kizgin'
};
FadabMysql.upsertAsync('tblUser', user)
.then(function (info) {
console.log('User Updated!', info);
})
.catch(function (err) {
console.log('Error updating record, mysql error:', err.message);
});
// or
const info = await FadabMysql.upsertAsync('tblUser', update);
console.log(info);
//info is an object with affectedRows, changedRows
Deleting a record
FadabMysql.deleteAsync('tblUser', { id: 1 })
.then(function (record) {
console.log(record);
})
.catch(function (err) {
console.log('Error deleting record, mysql error:', err.message);
});
// or
const where = {
id: 1
};
const record = await FadabMysql.deleteAsync('tblUser', where);
console.log(record);
Custom Queries
Don't forget to release the pooled connection so another process can use it.
//query has sql structure
//values will be placed in the query when escaped, and are optional
FadabMysql.query(query, values)
.then(function (results) {
console.log('my query results', results);
})
.catch(function (err) {
reject(err);
});
// or
const results = await FadabMysql.queryAsync(query, values);
console.log(results);
The query values are used in the same way felixge's module expects it. They are also optional.
Utilities
// Formatting sql query
var query = FadabMysql.format(query, values);
//Escape a database,table or column name
var value = FadabMysql.escapeId(values);
//Escape a string
var noSqlInject = FadabMysql.escapeId(value);
Helper Class
Class content
import { SelectOptions, Where, DynamicObject, WhereAdvancedObject, CountOptions } from '../models';
export declare class FadabHelper {
protected baseTable: string;
constructor();
queryAsync: (query: string, values?: object | object[] | undefined) => Promise<unknown>;
selectAsync(options: SelectOptions): Promise<unknown>;
findOneAsync(where: Where | DynamicObject): Promise<object>;
insertAsync(values: DynamicObject, ignore?: boolean): Promise<unknown>;
updateAsync(values: DynamicObject, where: Where | DynamicObject | Array<WhereAdvancedObject>): Promise<unknown>;
deleteAsync(where: Where | DynamicObject): Promise<unknown>;
countAsync(options?: CountOptions): Promise<number>;
bulkInsertAsync(values: Array<DynamicObject>, ignore?: boolean): Promise<unknown>;
upsertAsync(values: DynamicObject): Promise<unknown>;
}
Usage
const { FadabHelper } = require('fadab-mysql-helper');
// or
// import { FadabHelper } from 'fadab-mysql-helper';
class MysqlTransaction extends FadabHelper {
constructor() {
super();
this.baseTable = 'tableName';
}
}
Coming Soon
- fadab-mssql-helper
- fadab-postgresql-helper
Support fadab-mysql-helper
fadab-mysql-helper is completely free and open-source. If you find it useful, you can show your support by 🌟 it or sharing it in your social network.