Flex.Query Processor Library
Table of Contents
- Introduction
- Important Notes
- Installation
- Define Connection
- Syntax Introduction
- Methods Introduction
- Preset Connection
- Retrieve From Database
- Insert, Update and Delete
- Transaction
Introduction
Objective
- The Company - FLEX-SOLVER PTE LTD
To create a clean and standardized format for mysql query call when writing APIs.
Hence, this library was created on top of the OFFICIAL MYSQL LIBRARY.
We recommend you to try out mysql library first before you start with this wrapper.
Features
- Connect to Mysql database.
- Easy query to the database.
- Easy parse data from the database withuot introducing models.
Important Notes
Important Notes #1 -About Promise (Latest 20/05/2018)
In this library, most of the functions return promises. We highly encourage developers to practice on await-async (read me to find out why).
Important Notes #2 -About Returns (Latest 20/05/2018)
As mentioned, the library is written on top of the offical library, we only did minimal changes in the returned data to ease the data parsing.
Apart from that, you may refer to
Installation
This is a Node.js module available through the npm registry.
Before installing, download and install Node.js. Node.js 8.0 or higher is required.
Installation is done using the npm install command:
$ npm install flexqp
Define Connection
The recommended way to define connection is to create a json file like this:
{
"host" : "localhost",
"user" : "admin",
"password" : "p@55w0rd",
"database" : "world"
}
Alternatively, you can also create an connection object which serve the same purpose upon establishing connection like this:
let dbConnection = {
host : "localhost",
user : "admin",
password : "p@55w0rd",
database : "world"
}
In order to standardize the code in the following example, we will use the first method by using: require('dbConnection.json');
Syntax Introduction
You can simply find out the purpose of all APIs using the definition table here:
| Syntax | Definition | |
|---|---|---|
execute |
Send this SQL query to the database |
|
AndFetch |
Perform a select or call statement call |
|
Update |
Perform update, delete, insert statement call |
|
First |
Retrieve the first result if exists else null
|
|
Promise |
This returns as a promise |
|
Ignore |
Even if this query did not pass, this Promise will always resolve by itself |
|
End |
|
Methods Introduction
| Method Name | Usage |
|---|---|
presetConnection |
Preset Connection |
connectWithTbegin |
Creates a temporary connection that begins a transaction - see Transaction |
executeAndFetchPromise |
Perform a select or call statement call |
executeAndFetchFirstPromise |
Perform a select or call statement call and return the first result |
executeUpdatePromise |
Perform a query and returns database result such as affected rows, insertId back |
executeUpdateIgnorePromise |
Perform a query and returns database result or error object back |
Preset Connection
If you are only using one database and one connection, you are recommended to preset connection upon running the server.
With the preset connection, you no longer need to define your connection before you perform any sql query call via the following example:
var qp = require('flexqp');
var dbConnection = require('./dbConnection.json');
qp.presetConnection(dbConnection);
Just remember, if you did not declare the database connection in the following method, this library will always be using the default/preset configuration for you.
In other words, you are not required to define database credential after you have preset the connection.
Retrieve From Database
If you would like to write a function that performs select statement, you may do so like the following example.
var qp = require('flexqp');
var dbConnection = require('./dbConnection.json');
qp.presetConnection(dbConnection);
async function getUsers(){
//select the whole array of users
var result1 = await qp.executeAndFetchPromise('select * from users');
return result1;
}
async function getActiveUsers(){
//select the whole array of active users
var result2 = await qp.executeAndFetchPromise('select * from users where status = ?', ['ACTIVE']);
return result2;
}
async function getSpecific(_name){
//select a specific user
var result3 = await qp.executeAndFetchFirstPromise('select * from users where name like ?', [_name]);
return result3;
}
About StoredProcedure
Coming Soon....
About Options
Coming Soon....
JSON Data Type
Coming Soon....
Timestamp Data Type
Coming Soon....
Insert, Update and Delete
If you would like to write a function that performs insert/update/delete statement, you may do so like the following example.
var qp = require('flexqp');
var dbConnection = require('./dbConnection.json');
qp.presetConnection(dbConnection);
async function insertUser(user){
//select the whole array of users
var result1 = await qp.executeUpdatePromise('insert into users set ?', [user]);
return result1;
}
async function updateUser(user, originalId){
//select the whole array of active users
var result2 = await qp.executeUpdatePromise('update users set ? where id = ?', [user, originalId]);
return result2;
}
async function deleteUser(_name){
//select a specific user
var result3 = await qp.executeUpdatePromise('delete from users where name like ?', [_name]);
return result3;
}
Transaction
If you would like to write multiple query statements but within a transaction, you may do so like the following example.
var qp = require('flexqp');
var dbConnection = require('./dbConnection.json');
qp.presetConnection(dbConnection);
async function aTransaction(user, originalId, _name) {
var con;
try {
//Create a temporary connection that begins a transaction using the above declared database connection
con = await qp.connectWithTbegin();
//Do some changes to the database with execute - allows us to pass our con object as the connection we want to connect to our database with
await qp.execute('insert into users set ?', [user], con);
await qp.execute('update users set ? where id = ?, [user, originalId], con);
await qp.execute('delete from users where name like ?', [_name], con);
//If all is fine, commit the changes and close the connection
await qp.commmitAndCloseConnection(con);
} catch (err) {
//If an error occurs during the transaction, catch it and roll back the changes made to the database
await qp.rollbackAndCloseConnection(con);
}
}
Do note that if there is an error after commitAndCloseConnection(con) in the try block, calling rollbackAndCloseConnection(con) in the error block will result in the error where the query cannot be enqueued after the connection has already been closed. Therefore, try to use transactions as a stand-alone function.