Amk-sql NPM | npm.io

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: 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