alanzhao-mysql-orm-async v2.2.3
Usage
Summary
Running sequential SQL statements in NODEJS is not as straightforward and easy to read as using the procedural programing language such as PHP. Using nested callbacks or Promises clutters up your code. This is where Async/Await comes to the rescue.
To further clean and speed up the database query procedures, I added two database abstraction layers that wrap all the NPM mysql2
functionality.
Database.js
provides the common methods for all database needs. DbObject.js
further abstracts the methods to provide one-to-one mapping of a database table to an object.
Installation
yarn add alanzhao-mysql-orm-async
or
npm install alanzhao-mysql-orm-async
Example: main.js
main = async () => {
// Parse your enviornment variables saved in .env file
require('dotenv').config();
// Main database class
const Database = require('alanzhao-mysql-orm-async/Database');
// DbUser extends from DbObject
const DbUser = require('./DbUser');
// Construct database configs
const dbConfigs = {
'dbHost': process.env.DB_ENDPOINT,
'dbUser': process.env.DB_USER,
'dbPassword': process.env.DB_PASSWORD,
'dbName': process.env.DB_NAME,
'dbPort': process.env.DB_PORT,
'dbConnectTimeout': process.env.DB_CONNECT_TIMEOUT
};
// Instantiate database
const database = new Database(dbConfigs);
// Connect to database
await database.connect();
//
// Examples on using the main Database class
//
// Basic query
const query = 'SELECT * FROM users ORDER BY ID ASC';
const users = await database.query(query);
for (let i = 0; i < users.length; i++) {
let user = users[i];
console.log(user.firstName + ' ' + user.lastName);
}
// Output total users in Database
const totalUsers = await database.getAllCount('users');
console.log('Total users: ' + totalUsers);
// Delete user ID of 10
await database.delete('users', 10);
console.log('Deleted a user #10');
//
// Examples on using the DbObject extended class
//
// Instantiate DbUser, pass the database connection
const dbUser = new DbUser(database);
// Call method on the DbUser
const specialUsers = await dbUser.getSomeVerySpecialUsers();
for (let i = 0; i < specialUsers.length; i++) {
let user = users[i];
console.log(user.firstName + ' ' + user.lastName);
}
// Use the inherited methods
// User ID #10 exists?
const userExists = await dbUser.exists(10);
console.log('User #10 exists: ' + userExists);
// Update an user
await dbUser.update(10, { firstName: 'New First Name', lastName: 'New Last Name' });
console.log('User #10 has been updated');
}
main().catch(error => {
// All errors will get caught here
console.log('Main error: ' + error.message);
});
Example: DbUser.js
const DbObject = require('alanzhao-mysql-orm-async/DbObject');
module.exports = class DbUser extends DbObject {
constructor(db) {
super(db);
// Users table
this.tableName = 'users';
}
async getSomeVerySpecialUsers() {
const query = "SELECT * FROM users WHERE status = 'special'";
const users = await this._db.query(query);
return users;
}
}
API
Database
Kind: global class
Params: array configs The database connection configurations
- Database
- new Database()
- .dbClasses ⇒ void
- .dbHost ⇒ string
- .dbPort ⇒ number
- .dbConnectTimeout ⇒ number
- .dbUser ⇒ string
- .dbPassword ⇒ string
- .dbName ⇒ string
- .insertedId ⇒ number
- .lastResults ⇒ array
- .lastQuery ⇒ string
- .affectedRows ⇒ number
- .changedRows ⇒ number
- .connect(ssl, sslCerts) ⇒ boolean
- .close() ⇒ boolean
- .escape(value) ⇒ string
- .escapeId(value) ⇒ string
- .format(query, values) ⇒ string
- .execute(query, values) ⇒ array
- .query(query, [values]) ⇒ array
- .get(table, id) ⇒ Object
- .getAll(table, orderBy) ⇒ array
- .getAllCount(table) ⇒ integer
- .getBy(table, criteria, [limit], [orderBy]) ⇒ array
- .insert(table, values) ⇒ boolean
- .update(table, id, values) ⇒ boolean
- .updateBy(table, criteria, values) ⇒ boolean
- .delete(table, id) ⇒ boolean
- .deleteBy(table, criteria) ⇒ boolean
- .exists(table, id) ⇒ boolean
- .existsBy(table, criteria, [excludeId]) ⇒ boolean
- .array(query, [column]) ⇒ array
- .kvObject(query, key, value) ⇒ Object
- .row(query) ⇒ array
- .scalar(query) ⇒ string | number | boolean
- .bool(query) ⇒ boolean
- .integer(query) ⇒ number
- .decimal(query, [decimal]) ⇒ number
- .tableExists(The) ⇒ boolean
- .transaction(queries) ⇒ boolean
- .duplicateTable(from, to) ⇒ boolean
- .truncate(table) ⇒ boolean
- .drop(table) ⇒ boolean
- .setEnvVar(name, value) ⇒ boolean
- .getEnvVar(name) ⇒ array
- .getTableColumns(name, [ignoreColumns]) ⇒ array
- .getTableColumnDefaultValues(name, [ignoreColumns]) ⇒ Object
- .getTableColumnDataTypes(name, [ignoreColumns]) ⇒ Object
- .export(results) ⇒ array
- .saveCache(cacheId, value) ⇒ void
- .clearCache(cacheId) ⇒ void
- .clearAllCache() ⇒ void
- .getCache(cacheId) ⇒ array
- .clearConnection() ⇒ void
- .getDb(args) ⇒ array
new Database()
Construct database connection
database.dbClasses ⇒ void
Set dbClasses
Kind: instance property of Database
Param | Type | Description |
---|---|---|
dbClasses | array | The DbObject mapping to set |
Example
// Example for `dbClasses` parameter
let dbClasses = {
'User': DbUser,
'Job': DbJob
};
database.dbHost ⇒ string
Get dbHost variable
Kind: instance property of Database
database.dbPort ⇒ number
Get dbPort variable
Kind: instance property of Database
database.dbConnectTimeout ⇒ number
Get dbConnectTimeout variable
Kind: instance property of Database
database.dbUser ⇒ string
Get dbUser variable
Kind: instance property of Database
database.dbPassword ⇒ string
Get dbPassword variable
Kind: instance property of Database
database.dbName ⇒ string
Get dbName variable
Kind: instance property of Database
database.insertedId ⇒ number
Get last inserted ID
Kind: instance property of Database
database.lastResults ⇒ array
Get last results
Kind: instance property of Database
database.lastQuery ⇒ string
Get last query
Kind: instance property of Database
database.affectedRows ⇒ number
Get number of affected rows
Kind: instance property of Database
database.changedRows ⇒ number
Get number of updated rows
Kind: instance property of Database
database.connect(ssl, sslCerts) ⇒ boolean
Connect to database
Kind: instance method of Database
Returns: boolean - Returns true on successful connection
Throws:
- Database connection error
Param | Type | Default | Description |
---|---|---|---|
ssl | boolean | false | Using SSL connection? |
sslCerts | array | The SSL certificate paths |
database.close() ⇒ boolean
Close database connection
Kind: instance method of Database
Returns: boolean - Returns true on successful close
Throws:
- Database close error
database.escape(value) ⇒ string
Escape string value
Kind: instance method of Database
Returns: string - Escaped value
Param | Type | Description |
---|---|---|
value | string | Value to escape |
database.escapeId(value) ⇒ string
Escape identifier(database/table/column name)
Kind: instance method of Database
Returns: string - Escaped value
Param | Type | Description |
---|---|---|
value | string | Value to escape |
database.format(query, values) ⇒ string
Prepare a query with multiple insertion points, utilizing the proper escaping for ids and values
Kind: instance method of Database
Returns: string - The formatted query
Param | Type | Description |
---|---|---|
query | string | Query to format |
values | array | The array of values |
Example
var query = "SELECT * FROM ?? WHERE ?? = ?";
var values = ['users', 'id', userId];
db.format(query, values);
database.execute(query, values) ⇒ array
Prepare and run query Differences between execute() and query():
Kind: instance method of Database
Returns: array - Results of query
See: https://github.com/sidorares/node-mysql2/issues/382
Param | Type | Description |
---|---|---|
query | string | Query to execute |
values | array | The values of the query |
Example
var query = "SELECT * FROM ?? WHERE ?? = ?";
var values = ['users', 'id', userId];
await db.execute(query, values);
database.query(query, values) ⇒ array
Run a query
Kind: instance method of Database
Returns: array - Results of query
Param | Type | Default | Description |
---|---|---|---|
query | string | Query to execute | |
values | array | [] | The values of the query, optional |
Example
var query = "SELECT * FROM ?? WHERE ?? = ?";
var values = ['users', 'id', userId];
await db.query(query, values);
// or
var query = "SELECT * FROM users WHERE id = 10";
await db.query(query);
database.get(table, id) ⇒ Object
Get one record by ID
Kind: instance method of Database
Returns: Object - The row as an object
Param | Type | Description |
---|---|---|
table | string | The table name |
id | number | The primary ID |
database.getAll(table, orderBy) ⇒ array
Get all records from a table
Kind: instance method of Database
Returns: array - The result array
Param | Type | Default | Description |
---|---|---|---|
table | string | The table name | |
orderBy | string | null | The order by syntax, example "id DESC" |
database.getAllCount(table) ⇒ integer
Get all record count of a table
Kind: instance method of Database
Returns: integer - The total count of the table
Param | Type | Description |
---|---|---|
table | string | The table name |
database.getBy(table, criteria, limit, orderBy) ⇒ array
Construct a SELECT query and execute it
Kind: instance method of Database
Returns: array - The result array
Param | Type | Default | Description |
---|---|---|---|
table | string | The table name | |
criteria | Object | The criteria, example: { id: 10, status: 'expired' } | |
limit | number | The number of results to return, optional | |
orderBy | string | null | The order by syntax, example "id DESC", optional |
database.insert(table, values) ⇒ boolean
Construct single or multiple INSERT queries and execute
Kind: instance method of Database
Returns: boolean - Returns true on successful insertion
Param | Type | Description |
---|---|---|
table | string | The table name |
values | array | Object | The data to insert as a single object or array of objects |
Example
// Example for `values` parameter
{
id: 10,
firstName: 'John',
lastName: 'Doe',
status: 'active'
}
// or
[{
id: 10,
firstName: 'John',
lastName: 'Doe',
status: 'active'
}, ... ]
database.update(table, id, values) ⇒ boolean
Construct an UPDATE by ID query and execute
Kind: instance method of Database
Returns: boolean - Returns true on successful update
Param | Type | Description |
---|---|---|
table | string | The table name |
id | number | The primary ID of the record |
values | Object | The data to update |
database.updateBy(table, criteria, values) ⇒ boolean
Construct an update by criteria query and execute
Kind: instance method of Database
Returns: boolean - Returns true on successful update
Param | Type | Description |
---|---|---|
table | string | The table name |
criteria | Object | The criteria used to match the record |
values | Object | The data to update |
database.delete(table, id) ⇒ boolean
Construct delete by ID query and execute
Kind: instance method of Database
Returns: boolean - Returns true on successful deletion
Param | Type | Description |
---|---|---|
table | string | The table name |
id | number | The primary ID of the record |
database.deleteBy(table, criteria) ⇒ boolean
Construct delete by criteria query and execute
Kind: instance method of Database
Returns: boolean - Returns true on successful delete
Param | Type | Description |
---|---|---|
table | string | The table name |
criteria | Object | The criteria used to match the record |
database.exists(table, id) ⇒ boolean
Check if a record exists by the ID
Kind: instance method of Database
Returns: boolean - Returns true if record exists
Param | Type | Description |
---|---|---|
table | string | The table name |
id | number | The primary ID of the record |
database.existsBy(table, criteria, excludeId) ⇒ boolean
Check if a record matching the criteria exists
Kind: instance method of Database
Returns: boolean - Returns true if record exists
Param | Type | Default | Description |
---|---|---|---|
table | string | The table name | |
criteria | Object | The criteria used to match the record | |
excludeId | number | The ID to exclude |
database.array(query, column) ⇒ array
Execute a query and return column result as array
Kind: instance method of Database
Returns: array - Returns the result as array
Param | Type | Default | Description |
---|---|---|---|
query | string | The query to execute | |
column | string | null | The column of the result set. If not provided, first column will be used |
database.kvObject(query, key, value) ⇒ Object
Return results as custom key and value pair object
Kind: instance method of Database
Returns: Object - Returns the result as object
Param | Type | Description |
---|---|---|
query | string | The query to execute |
key | string | The column of the result to use as key of the object |
value | string | The column of the result to use as value of the object |
database.row(query) ⇒ array
Return first row of the result set
Kind: instance method of Database
Returns: array - Returns the result as array
Param | Type | Description |
---|---|---|
query | string | The query to execute |
database.scalar(query) ⇒ string | number | boolean
Return scalar value
Kind: instance method of Database
Returns: string | number | boolean | decimal - Returns the result as scalar
Param | Type | Description |
---|---|---|
query | string | The query to execute |
database.bool(query) ⇒ boolean
Return boolean value
Kind: instance method of Database
Returns: boolean - Returns the result as boolean
Param | Type | Description |
---|---|---|
query | string | The query to execute |
database.integer(query) ⇒ number
Return integer value
Kind: instance method of Database
Returns: number - Returns the result as integer
Param | Type | Description |
---|---|---|
query | string | The query to execute |
database.decimal(query, decimal) ⇒ number
Return decimal value
Kind: instance method of Database
Returns: number - Returns the result as decimal
Param | Type | Default | Description |
---|---|---|---|
query | string | The query to execute | |
decimal | number | 2 | The number of decimal places |
database.tableExists(The) ⇒ boolean
Whether or not a table exists
Kind: instance method of Database
Returns: boolean - Returns true if table exists
Param | Type | Description |
---|---|---|
The | string | table name |
database.transaction(queries) ⇒ boolean
Run queries in transaction
Kind: instance method of Database
Returns: boolean - Returns true if transaction is successful
Param | Type | Description |
---|---|---|
queries | array | An array of queries to run in transaction |
database.duplicateTable(from, to) ⇒ boolean
Duplicate content to a new table
Kind: instance method of Database
Returns: boolean - Returns true if duplication is successful
Param | Type | Description |
---|---|---|
from | string | The table to copy from |
to | string | The table to copy to |
database.truncate(table) ⇒ boolean
Truncate a table
Kind: instance method of Database
Returns: boolean - Returns true if table is truncated
Param | Type | Description |
---|---|---|
table | string | The table to truncate |
database.drop(table) ⇒ boolean
Drop a table
Kind: instance method of Database
Returns: boolean - Returns true if table is dropped
Param | Type | Description |
---|---|---|
table | string | The table to drop |
database.setEnvVar(name, value) ⇒ boolean
Set an environment variable
Kind: instance method of Database
Returns: boolean - Returns true if table is truncated
Param | Type | Description |
---|---|---|
name | string | Name of the environment variable |
value | string | Value of the environment variable |
database.getEnvVar(name) ⇒ array
Get an environment variable
Kind: instance method of Database
Returns: string - The environment variable
Param | Type | Description |
---|---|---|
name | string | Name of the environment variable to get |
database.getTableColumns(name, ignoreColumns) ⇒ array
Get table columns
Kind: instance method of Database
Returns: array - Returns names of the table as array
Param | Type | Default | Description |
---|---|---|---|
name | string | Name of the table | |
ignoreColumns | string | null | Columns to ignore |
database.getTableColumnDefaultValues(name, ignoreColumns) ⇒ Object
Get column default values
Kind: instance method of Database
Returns: Object - Returns an object with column names and their default values
Param | Type | Default | Description |
---|---|---|---|
name | string | Name of the table | |
ignoreColumns | string | null | Columns to ignore |
database.getTableColumnDataTypes(name, ignoreColumns) ⇒ Object
Get column data types
Kind: instance method of Database
Returns: Object - Returns an object with column names and their data types
Param | Type | Default | Description |
---|---|---|---|
name | string | Name of the table | |
ignoreColumns | string | null | Columns to ignore |
database.export(results) ⇒ array
Export results
Kind: instance method of Database
Returns: array - Returns cleaned up results
Param | Type | Description |
---|---|---|
results | array | Results to export |
database.saveCache(cacheId, value) ⇒ void
Save value to cache
Kind: instance method of Database
Param | Type | Description |
---|---|---|
cacheId | string | The cache ID |
value | string | The value to cache |
database.clearCache(cacheId) ⇒ void
Clear a cache
Kind: instance method of Database
Param | Type | Description |
---|---|---|
cacheId | string | The ID of the cache to clear |
database.clearAllCache() ⇒ void
Clear all cache
Kind: instance method of Database
database.getCache(cacheId) ⇒ array
Get cache by ID
Kind: instance method of Database
Returns: array - Returns the cached result set
Param | Type | Description |
---|---|---|
cacheId | string | The ID of the cache to get |
database.clearConnection() ⇒ void
Clear connection
Kind: instance method of Database
database.getDb(args) ⇒ array
Call method(s) on multiple DbObjects at the same time
Kind: instance method of Database
Returns: array - Returns an array of results
Param | Type | Description |
---|---|---|
args | array | Object | The arguments |
Example
// Example for `args` parameter
let args = [{
entity: 'User',
method: 'get',
args: [
// querying row # 1
1
]
}, {
entity: 'User',
method: 'get',
args: [
// querying row # 2
2
]
}];
// or
let args = {
entity: 'User',
method: 'get',
args: [
// querying row # 1
1
]
};
DbObject
Kind: global class
- DbObject
- new DbObject(db)
- .tableName ⇒ void
- .tableName ⇒ string
- .get(id) ⇒ array
- .getAll([orderBy]) ⇒ array
- .getAllCount() ⇒ number
- .find(criteria, [limit], [orderBy]) ⇒ array
- .findOne(criteria, [orderBy]) ⇒ Object
- .findColumn(criteria, columnName, [orderBy]) ⇒ string | number | boolean
- .create(values) ⇒ boolean
- .update(id, values) ⇒ boolean
- .updateBy(criteria, values) ⇒ boolean
- .delete(id) ⇒ boolean
- .deleteBy(criteria) ⇒ boolean
- .exists(id) ⇒ boolean
- .existsBy(criteria, [excludeId]) ⇒ boolean
- .updatePositionColumnById(values) ⇒ boolean
- .saveCache(cacheId, value) ⇒ void
- .clearCache(cacheId) ⇒ void
- .clearAllCache() ⇒ void
- .getCache(cacheId) ⇒ array
- .escape(value) ⇒ string
- .escapeId(value) ⇒ string
new DbObject(db)
Construct the DbObject
Param | Type | Description |
---|---|---|
db | Objct | The database object |
dbObject.tableName ⇒ void
Set tableName of this object
Kind: instance property of DbObject
Params: string tableName The table name
dbObject.tableName ⇒ string
Get tableName of this object
Kind: instance property of DbObject
Returns: string - The table name
dbObject.get(id) ⇒ array
Get entity by ID
Kind: instance method of DbObject
Returns: array - The entity array
Param | Type | Description |
---|---|---|
id | number | The primary ID of entity |
dbObject.getAll(orderBy) ⇒ array
Get all entities
Kind: instance method of DbObject
Returns: array - All the result sets as an array
Param | Type | Default | Description |
---|---|---|---|
orderBy | string | null | The order by string |
dbObject.getAllCount() ⇒ number
Get all entity count
Kind: instance method of DbObject
Returns: number - Total number of entities
dbObject.find(criteria, limit, orderBy) ⇒ array
Find entities
Kind: instance method of DbObject
Returns: array - The result array
Param | Type | Default | Description |
---|---|---|---|
criteria | Object | The criteria | |
limit | number | The number of results to return, optional | |
orderBy | string | null | The order by syntax, example "id DESC", optional |
Example
// Example for `criteria` parameter
{
id: 10,
status: 'expired'
}
dbObject.findOne(criteria, orderBy) ⇒ Object
Find one entity
Kind: instance method of DbObject
Returns: Object - The entity as object
Param | Type | Default | Description |
---|---|---|---|
criteria | Object | The criteria | |
orderBy | string | null | The order by syntax, example "id DESC", optional |
dbObject.findColumn(criteria, columnName, orderBy) ⇒ string | number | boolean
Find a column from an entity
Kind: instance method of DbObject
Returns: string | number | boolean - The column value
Param | Type | Default | Description |
---|---|---|---|
criteria | Object | The criteria. If multiple rows matching the criteria are found, only the first row will be used | |
columnName | string | The column to return | |
orderBy | string | null | The order by syntax, example "id DESC", optional |
dbObject.create(values) ⇒ boolean
Create an entity
Kind: instance method of DbObject
Returns: boolean - Returns true on successful creation
Param | Type | Description |
---|---|---|
values | array | Object | The data to insert as a single object or array of objects |
Example
// Example for `values` parameter
{
id: 10,
firstName: 'John',
lastName: 'Doe',
status: 'active'
}
// or
[{
id: 10,
firstName: 'John',
lastName: 'Doe',
status: 'active'
}, ... ]
dbObject.update(id, values) ⇒ boolean
Update an entity by ID
Kind: instance method of DbObject
Returns: boolean - Returns true on successful update
Param | Type | Description |
---|---|---|
id | number | The primary ID of the entity |
values | Object | The data to update |
dbObject.updateBy(criteria, values) ⇒ boolean
Update entity with multiple matching criteria
Kind: instance method of DbObject
Returns: boolean - Returns true on successful update
Param | Type | Description |
---|---|---|
criteria | Object | The criteria used to match the record |
values | Object | The data to update |
dbObject.delete(id) ⇒ boolean
Delete an entity by ID
Kind: instance method of DbObject
Returns: boolean - Returns true on successful deletion
Param | Type | Description |
---|---|---|
id | number | The primary ID of the record |
dbObject.deleteBy(criteria) ⇒ boolean
Delete entity with multiple matching criteria
Kind: instance method of DbObject
Returns: boolean - Returns true on successful delete
Param | Type | Description |
---|---|---|
criteria | Object | The criteria used to match the record |
dbObject.exists(id) ⇒ boolean
Does entity ID exist?
Kind: instance method of DbObject
Returns: boolean - Returns true if record exists
Param | Type | Description |
---|---|---|
id | number | The primary ID of the record |
dbObject.existsBy(criteria, excludeId) ⇒ boolean
Does entity exists matching multiple criteria
Kind: instance method of DbObject
Returns: boolean - Returns true if record exists
Param | Type | Default | Description |
---|---|---|---|
criteria | Object | The criteria used to match the record | |
excludeId | number | The ID to exclude |
dbObject.updatePositionColumnById(values) ⇒ boolean
Update entities' position column
Kind: instance method of DbObject
Returns: boolean - Returns true on successful update
Param | Type | Description |
---|---|---|
values | Object | The position values to update |
Example
// Example for `values` parameter
{
100: 5, // entity #100 gets a new `position` value of 5
101: 6,
102: 7,
103: 8
}
dbObject.saveCache(cacheId, value) ⇒ void
Save cache
Kind: instance method of DbObject
Param | Type | Description |
---|---|---|
cacheId | string | The cache ID |
value | string | The value to cache |
dbObject.clearCache(cacheId) ⇒ void
Clear cache
Kind: instance method of DbObject
Param | Type | Description |
---|---|---|
cacheId | string | The ID of the cache to clear |
dbObject.clearAllCache() ⇒ void
Clear all cache
Kind: instance method of DbObject
dbObject.getCache(cacheId) ⇒ array
Get cache by ID
Kind: instance method of DbObject
Returns: array - Returns the cached result set
Param | Type | Description |
---|---|---|
cacheId | string | The ID of the cache to get |
dbObject.escape(value) ⇒ string
Escape string value
Kind: instance method of DbObject
Returns: string - Escaped value
Param | Type | Description |
---|---|---|
value | string | Value to escape |
dbObject.escapeId(value) ⇒ string
Escape identifier(database/table/column name)
Kind: instance method of DbObject
Returns: string - Escaped value
Param | Type | Description |
---|---|---|
value | string | Value to escape |