Connect to Your Database Using MySQL Proxy with Fixed IP Address
Lapps provide MySQL and Promise-MySQL node packages to connect with MySql host. Many a times the database configuration is set in order to accept incoming queries only from whitelisted IPs. This ensures that requests are coming from a known source, and not an attempt of misuse. In such cases the name packages (MySQL and Promise-MySQL) won’t help.
We have exposed a new function, ls.lib.mysqlproxy.query, in order to make MySql queries from whitelisted IPs. Below is the signature of the function –
ls.lib.mysqlproxy.query(connectionOptions, queryOptions,callback=null)
Below are the parameters described in detail –
connectionOptions
Defines the connection configuration to make a database connection. Below are the four properties expected –
let connectionOptions = {
"host" : "mysql host",
"user" : "user value",
"password": "password for user",
"database" : "database name"
}
queryOptions
Defines the query details to be executed. Below are the three properties available –
let queryOptions = {
"sql" : "query to be executed",
"values" : [<array of values to be passed for sql statement>],
"timeout" : 10000
}
- values: Though this parameter is optional, you are advised to be used in order to safeguard from SQL injection. It should be used to pass values to the SQL statement that will replace placeholders at the time of execution.
- timeout: It is the max timeout for query execution in a millisecond. This also an optional parameter and has the default value (as well as max possible value) as 3000 ms.
callback
callback is optional. It returns two parameters, “error” and “response”. In case callback is not passed, the function returns a “Promise”.
General Rules for Passing Parameter to Query
- Use ? (single question mark) character to replace placeholder with value as the datatype of value.
- Use ?? (double question mark) characters as placeholders for identifiers like column or table name.
Sample Query Syntax
The below examples assumes the following:
- Table name: sampledb
- Columns: id(int; autoincrement), name (varchar), category(varchar)
//Passing one value in where clause
let queryOptions = {
"sql" : "select id,name,category from sampledb where id = ?",
"values" : [2]
}
//Passing 2 values in where clause
let queryOptions = {
"sql" : "select id,name,category from sampledb where name = ? and id = ?",
"values" : ['nameA','nameB']
}
//Passing columns and table name; column and column value in where clause
let queryOptions = {
"sql" : "select ?? from ?? where ?? = ?",
"values" : [['id','name','company'],'sampledb','name','nameB']
}
Response
The function supports executing all types of queries, one connection at a time and without any database transaction. The maximum execution time for the query can be no more than 3 seconds.
Response returned are described in different categories as below –
- select query: returns JSON with an array of row objects
- insert query: return properties:
- affectedRows: count of rows created
- insertId: if primary key is autoincrement, insertId contains the primary key of the newly created row
- update query: return properties
- affectedRows: # of rows matching conditions
- changedRows: # of rows modified
- delete query: return properties
- affectedRows: # of rows matching conditions
- changedRows: 0
IP Addresses
Note: To obtain the fixed IP address for your Lapps, please reach out to your account manager, or write to support@leadsquared.com.
Complete Sample Code
callback based approach
let connectionOptions = {
"host" : *****************.ap-south-1.rds.amazonaws.com",
"user" : ls.SETTINGS.DBUSER,
"password": ls.SETTINGS.DBPASSWORD,
"database" : "*****_mxradon"
}
let queryOptions = {
"sql" : "select ?? from sampledb where ?? = ?,
"values" : [["name","category"],
"id",
"07b7e7f0-520e-11e9-9b91-069b743e848f"]
}
ls.lib.mysqlproxy.query(connectionOptions,queryOptions,(error,res)=>{
if(error){
//handle error here
ls.log.Error("error occured",error)
} else {
// handle successful response here
ls.log.Info("got data",res)
}
});
Promise Based Approach
let connectionOptions = {
"host" : *****************.ap-south-1.rds.amazonaws.com",
"user" : ls.SETTINGS.DBUSER,
"password": ls.SETTINGS.DBPASSWORD,
"database" : "*****_mxradon"
}
let queryOptions = {
"sql" : "select ?? from sampledb where ?? = ?,
"values" : [["name","category"],
"id",
"07b7e7f0-520e-11e9-9b91-069b743e848f"]
}
ls.lib.mysqlproxy.query(connectionOptions,queryOptions)
.then((res)=> {
// handle successful response here
ls.log.Info("got data",res)
})
.catch((error)=> {
//handle error here
ls.log.Error("error occured",error)
})
