Skip to content

Latest commit

 

History

History
151 lines (112 loc) · 4.45 KB

README.md

File metadata and controls

151 lines (112 loc) · 4.45 KB
Raw

AMK-SQL

CircleCI codecov Known Vulnerabilities

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:

  1. use dotenv to set the variables
  2. issue the command export AMK_SQL_USERNAME=username
  3. put it on the .bashrc or .bash_profile file

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.

License

MIT