query processor as a wrapper on top of mysql
npm install flexqp
$ 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 | :star:
------------- | ------------- | -------------
execute | Send this SQL query to the database | :star::star::star::star::star:
AndFetch | Perform a select or call statement call | :star::star::star::star:
Update | Perform update, delete, insert statement call | :star::star::star::star:
First | Retrieve the first result if exists else null | :star::star::star:
Promise | This returns as a promise | :star::star::star::star::star:
Ignore | Even if this query did not pass, this Promise will always resolve by itself | :star::star:
~~End~~ | ~~End the http request straight away~~ (Consider to deprecate this soon) | :star:
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;
}
`
$3
Coming Soon....
$3
Coming Soon....
$3
Coming Soon....
$3
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);
}
}
``