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

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