AMK-SQL is a plugin for express using knex to simplify the usage of SQL databases
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
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
Simple query with filters
- param1 (string or object) - can be an object to filter
- param2 (string) - can be ('param', 'value')
- param3 (string) - can be ('param', 'in', 'value')
- (Array|Promise) - result set in an array or query object
Querying dataset with pagination
- 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
- (Array|Promise) - result set in an array or query object
Count the number of entry in a table
- param (string) - query parameter
- groupBy (string) - group by statement
- (Array|Promise) - count in an array or query object. suggest to use destructuring like
let [count] = model.count()to get the value
function to insert a single row of data
- 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
- (Array) - returns the number of rows inserted or the return value specified on the arguments
function to update rows of data
- 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
- (Array|Promise) - returns the number of rows inserted or the return value specified on the arguments or the query object
function to delete rows of data
- params (object) - the criteria of which row to delete
- (Array|Promise) - returns the number of rows deleted or the query object
returns the knex object with table name
- (Promise) - similar to
knex(TABLE_NAME)that can be chained
returns the knex object table name with alias to make it easier to join
- (Promise) - similar to
knex(TABLE_NAME).as(alias)that can be chained
returns the knex object
- (Promise) - similar to
knex()
Work in progress
All bugs, feature requests, pull requests, feedback, etc., are welcome. Create an issue.