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
void
database.dbClasses ⇒ 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
};
string
database.dbHost ⇒ Get dbHost variable
Kind: instance property of Database
number
database.dbPort ⇒ Get dbPort variable
Kind: instance property of Database
number
database.dbConnectTimeout ⇒ Get dbConnectTimeout variable
Kind: instance property of Database
string
database.dbUser ⇒ Get dbUser variable
Kind: instance property of Database
string
database.dbPassword ⇒ Get dbPassword variable
Kind: instance property of Database
string
database.dbName ⇒ Get dbName variable
Kind: instance property of Database
number
database.insertedId ⇒ Get last inserted ID
Kind: instance property of Database
array
database.lastResults ⇒ Get last results
Kind: instance property of Database
string
database.lastQuery ⇒ Get last query
Kind: instance property of Database
number
database.affectedRows ⇒ Get number of affected rows
Kind: instance property of Database
number
database.changedRows ⇒ Get number of updated rows
Kind: instance property of Database
boolean
database.connect(ssl, sslCerts) ⇒ 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 |
boolean
database.close() ⇒ Close database connection
Kind: instance method of Database
Returns: boolean
- Returns true on successful close
Throws:
- Database close error
string
database.escape(value) ⇒ Escape string value
Kind: instance method of Database
Returns: string
- Escaped value
Param | Type | Description |
---|---|---|
value | string |
Value to escape |
string
database.escapeId(value) ⇒ Escape identifier(database/table/column name)
Kind: instance method of Database
Returns: string
- Escaped value
Param | Type | Description |
---|---|---|
value | string |
Value to escape |
string
database.format(query, values) ⇒ 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);
array
database.execute(query, values) ⇒ 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);
array
database.query(query, [values]) ⇒ 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);
Object
database.get(table, id) ⇒ 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 |
array
database.getAll(table, orderBy) ⇒ 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" |
integer
database.getAllCount(table) ⇒ 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 |
array
database.getBy(table, criteria, [limit], [orderBy]) ⇒ 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 |
boolean
database.insert(table, values) ⇒ 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'
}, ... ]
boolean
database.update(table, id, values) ⇒ 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 |
boolean
database.updateBy(table, criteria, values) ⇒ 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 |
boolean
database.delete(table, id) ⇒ 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 |
boolean
database.deleteBy(table, criteria) ⇒ 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 |
boolean
database.exists(table, id) ⇒ 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 |
boolean
database.existsBy(table, criteria, [excludeId]) ⇒ 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 |
array
database.array(query, [column]) ⇒ 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 |
Object
database.kvObject(query, key, value) ⇒ 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 |
array
database.row(query) ⇒ 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 |
string
| number
| boolean
database.scalar(query) ⇒ 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 |
boolean
database.bool(query) ⇒ Return boolean value
Kind: instance method of Database
Returns: boolean
- Returns the result as boolean
Param | Type | Description |
---|---|---|
query | string |
The query to execute |
number
database.integer(query) ⇒ Return integer value
Kind: instance method of Database
Returns: number
- Returns the result as integer
Param | Type | Description |
---|---|---|
query | string |
The query to execute |
number
database.decimal(query, [decimal]) ⇒ 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 |
boolean
database.tableExists(The) ⇒ 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 |
boolean
database.transaction(queries) ⇒ 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 |
boolean
database.duplicateTable(from, to) ⇒ 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 |
boolean
database.truncate(table) ⇒ 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 |
boolean
database.drop(table) ⇒ 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 |
boolean
database.setEnvVar(name, value) ⇒ 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 |
array
database.getEnvVar(name) ⇒ 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 |
array
database.getTableColumns(name, [ignoreColumns]) ⇒ 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 |
Object
database.getTableColumnDefaultValues(name, [ignoreColumns]) ⇒ 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 |
Object
database.getTableColumnDataTypes(name, [ignoreColumns]) ⇒ 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 |
array
database.export(results) ⇒ Export results
Kind: instance method of Database
Returns: array
- Returns cleaned up results
Param | Type | Description |
---|---|---|
results | array |
Results to export |
void
database.saveCache(cacheId, value) ⇒ Save value to cache
Kind: instance method of Database
Param | Type | Description |
---|---|---|
cacheId | string |
The cache ID |
value | string |
The value to cache |
void
database.clearCache(cacheId) ⇒ Clear a cache
Kind: instance method of Database
Param | Type | Description |
---|---|---|
cacheId | string |
The ID of the cache to clear |
void
database.clearAllCache() ⇒ Clear all cache
Kind: instance method of Database
array
database.getCache(cacheId) ⇒ 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 |
void
database.clearConnection() ⇒ Clear connection
Kind: instance method of Database
array
database.getDb(args) ⇒ 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 |
void
dbObject.tableName ⇒ Set tableName of this object
Kind: instance property of DbObject
Params: string
tableName The table name
string
dbObject.tableName ⇒ Get tableName of this object
Kind: instance property of DbObject
Returns: string
- The table name
array
dbObject.get(id) ⇒ Get entity by ID
Kind: instance method of DbObject
Returns: array
- The entity array
Param | Type | Description |
---|---|---|
id | number |
The primary ID of entity |
array
dbObject.getAll([orderBy]) ⇒ 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 |
number
dbObject.getAllCount() ⇒ Get all entity count
Kind: instance method of DbObject
Returns: number
- Total number of entities
array
dbObject.find(criteria, [limit], [orderBy]) ⇒ 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'
}
Object
dbObject.findOne(criteria, [orderBy]) ⇒ 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 |
string
| number
| boolean
dbObject.findColumn(criteria, columnName, [orderBy]) ⇒ 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 |
boolean
dbObject.create(values) ⇒ 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'
}, ... ]
boolean
dbObject.update(id, values) ⇒ 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 |
boolean
dbObject.updateBy(criteria, values) ⇒ 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 |
boolean
dbObject.delete(id) ⇒ 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 |
boolean
dbObject.deleteBy(criteria) ⇒ 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 |
boolean
dbObject.exists(id) ⇒ 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 |
boolean
dbObject.existsBy(criteria, [excludeId]) ⇒ 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 |
boolean
dbObject.updatePositionColumnById(values) ⇒ 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
}
void
dbObject.saveCache(cacheId, value) ⇒ Save cache
Kind: instance method of DbObject
Param | Type | Description |
---|---|---|
cacheId | string |
The cache ID |
value | string |
The value to cache |
void
dbObject.clearCache(cacheId) ⇒ Clear cache
Kind: instance method of DbObject
Param | Type | Description |
---|---|---|
cacheId | string |
The ID of the cache to clear |
void
dbObject.clearAllCache() ⇒ Clear all cache
Kind: instance method of DbObject
array
dbObject.getCache(cacheId) ⇒ 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 |
string
dbObject.escape(value) ⇒ Escape string value
Kind: instance method of DbObject
Returns: string
- Escaped value
Param | Type | Description |
---|---|---|
value | string |
Value to escape |
string
dbObject.escapeId(value) ⇒ Escape identifier(database/table/column name)
Kind: instance method of DbObject
Returns: string
- Escaped value
Param | Type | Description |
---|---|---|
value | string |
Value to escape |