AMK-SQL
AMK-SQL is a plugin for express using knex to simplify the usage of SQL databases
Setup
Set the following environment variables
- AMK_SQL_USERNAME
- AMK_SQL_PASSWORD
- AMK_SQL_HOST
- AMK_SQL_DATABASE
- AMK_SQL_CLIENT
- AMK_SQL_POOL_MIN (connection pool, default = 2)
- AMK_SQL_POOL_MAX (connection pool, default = 10)
you can achieve this by choosing one of the options below:
- use dotenv to set the variables
- issue the command
export AMK_SQL_USERNAME=username - put it on the
.bashrcor.bash_profilefile
Usage
After setting up environment variables, inherit from this amk-sql. refer to code snipet below:
users.js
const SQL = require('amk-sql');
class Users extends SQL {
constructor() {
super('users', 'u')
}
}
module.exports = Users;
using users.js
Users = require('./users');
const users = new Users();
// this will give you a list of 20 users that are active
const rs = await users.get({active: 1}, { limit=20, orderBy='email'})
For more advance usage, please refer to this
API
find(param1, param2, param3)
Simple query with filters
Arguments
- param1 (string or object) - can be an object to filter
- param2 (string) - can be ('param', 'value')
- param3 (string) - can be ('param', 'in', 'value')
Returns
- (Array|Promise) - result set in an array or query object
get(params, { limit, offset, groupBy, orderBy})
Querying dataset with pagination
Arguments
- param (string) - query parameter
- limit (number) - limit
- offset (number) - offset (limit is required if offset is set)
- groupBy (string) - group by statement
- orderBy (string|Object) - order by or order by and direction
Returns
- (Array|Promise) - result set in an array or query object
count(params, { groupBy })
Count the number of entry in a table
Arguments
- param (string) - query parameter
- groupBy (string) - group by statement
Returns
-
(Array|Promise) - count in an array or query object. suggest to use destructuring like
let [count] = model.count()to get the value
ins(params, returning)
function to insert a single row of data
Arguments
- params (object) - the row you are going to insert
- returning (Array|Promise) - the row value you want returned i.e. primary keys or the query object
Returns
- (Array) - returns the number of rows inserted or the return value specified on the arguments
upd(updateValue, params, returning)
function to update rows of data
Arguments
- updateValue (object) - value you want to change
- params (object) - the criteria of which row to update
- returning (string) - the row value you want returned i.e. primary keys
Returns
- (Array|Promise) - returns the number of rows inserted or the return value specified on the arguments or the query object
del(params)
function to delete rows of data
Arguments
- params (object) - the criteria of which row to delete
Returns
- (Array|Promise) - returns the number of rows deleted or the query object
getDB()
returns the knex object with table name
Returns
-
(Promise) - similar to
knex(TABLE_NAME)that can be chained
getJoinDB()
returns the knex object table name with alias to make it easier to join
Returns
-
(Promise) - similar to
knex(TABLE_NAME).as(alias)that can be chained
getConn()
returns the knex object
Returns
-
(Promise) - similar to
knex()
Testing
Work in progress
Feedback
All bugs, feature requests, pull requests, feedback, etc., are welcome. Create an issue.