JAMM-PROMISE

JAMM but with promises instead

Installation

$ npm install jamm-promise --save

Example

Create few files below:

• database.js

module.exports={
	host     : DB_HOSTNAME,
	user     : DB_USERNAME,
	password : DB_PASSWORD,
	dbname:DB_DATABASE
}

• author.js

function author(){
	const table={
		name:'tbl_author',
		column:[
			{
				name:'id',
				type:'VARCHAR',
				len:16,
				primary:true,
			},
			{
				name:'email',
				type:'VARCHAR',
				len:255,
				unique:['email']
			},
			{
				name:'name',
				type:'TEXT',
				ext:''
			},
			{
				name:'score',
				type:'INT',
			},
			{
				name:'posttime',
				type:'TIMESTAMP',
				ext:'NOT NULL DEFAULT CURRENT_TIMESTAMP',
				index:true
			}
		]
	}

	const database = require('./database.js');
	const mysql = require('jamm-promise');
	const model = new mysql(table,database);
	return model;	
}
module.exports=author();

• index.js

const authorModel = require("./author.js");

once done, you can run the script with

node index.js

if there's no error, you can check your database that the table "tbl_author" is created.

Table schema

const table ={
    name:,
    column:[
        {
            name:,
            type:,
            ext:,
            primary:,
            index:,
			unique:,
            foreign:{
                table:,
                column:
                }
            }
        }
    ]
}

• table.name :[string][required], the name of the Table
• table.column : [array][required], list of column inside the table
• table.column.name : [string][required], the name of column
• table.column.type : [string][required], sql data type (ie: TEXT, VARCHAR, INT). Please refer to sql data types for all available option.
• table.column.len : [number][optional], sql data size.
• table.column.ext:[string],[optional], sql extended option for data type, such as length, or unique index. Please refer to sql data types for all available option.
• table.column.index :[boolean][optional], Determines if column will have INDEX KEY
• table.column.primary : [boolean][optional], Determines if column will have PRIMARY KEY index
• table.column.unique :[array][optional], Determines if column will have UNIQUE KEY
• table.column.foreign:[object][optional],Determines if column FOREIGN KEY
• table.column.foreign.table :[string][required], the table for FOREIGN KEY
• table.column.foreign.column:[string][required],the column for FOREIGN KEY.
• table.column.foreign.ext:[string][optional],the extended option for FOREIGN KEY.

Performing queries

Once created, here's the available method for the authorModel from above example as a shortcut to execute a query.

Query(query,escape)

Perform the usual SQL Query. Inputs are:

• query : [string][required], the full sql query, you can use ? characters as placeholders for values you would like to have escaped,
• escape : [array][optional], the value of the escaped string

example:

    authorModel.Query("SELECT * FROM another_table_that_exist_in_database WHERE id=?",['myId'])
    .then((res)=>{
       // res will have the result of the query
    })
    .catch((e)=>{
        console.log(e);
    })

Find(condition,escape)

A shorthand to perform SELECT * FROM query for the table. Inputs are:

• condition : [string], the sql query after a WHERE clause , you can use ? characters as placeholders for values you would like to have escaped,
• escape : [array], the value of the escaped string

performing

authorModel.Find("id=?",["myId"])
.then((res)=>{
    // res is array of record, empty array if not found
})
.catch((e)=>{
    console.log(e);
})

is the same as executing query like this

SELECT * FROM tbl_author WHERE id='myId'

FindOne(condition,escape)

A shorthand to perform SELECT * FROM query for the table with LIMIT 1 appended at the end of query. Inputs are:

• condition : [string], the sql query after a WHERE clause , you can use ? characters as placeholders for values you would like to have escaped,
• escape : [array], the value of the escaped string

performing

authorModel.FindOne("id=?",["myId"])
.then((res)=>{
    // res is object of a record, null if not found
})
.catch((e)=>{
    console.log(e);
})

is the same as executing query like this

SELECT * FROM tbl_author WHERE id='myId' LIMIT 1

FindTop(condition,escape,order,limit)

A shorthand to perform SELECT * FROM query for the table with ORDER BY and LIMIT appended at the end of query. Inputs are:

• condition : [string], the sql query after a WHERE clause , you can use ? characters as placeholders for values you would like to have escaped,
• escape : [array], the value of the escaped string
• order : [string], column name that use for sorting
• limit : [integer], the number of record requested

performing

authorModel.FindTop("posttime<?",["2019-01-01"],`posttime DESC`,10)
.then((res)=>{
    // res is an array of record
})
.catch((e)=>{
    console.log(e)
})

is the same as executing query like this

SELECT * FROM tbl_author WHERE posttime<'2019-01-01' ORDER BY posttime DESC LIMIT 10

FindPage(condition,escape,page,max)

Shortcut to perform SELECT * FROM query for the table with LIMIT appended at the end of query. Option are:

• condition : [string], the sql query after a WHERE clause , you can use ? characters as placeholders for values you would like to have escaped,
• escape : [array], the value of the escaped string
• page: [integer], offset number
• max:[integer], max record

performing

authorModel.FindPage("posttime<?",["2019-01-01"],0,10)
.then((res)=>{
    // res is an array of record
 })
 .catch((e)=>{
    console.log(e)
})

is the same as executing query like this

SELECT * FROM tbl_author WHERE posttime<'2019-01-01' LIMIT 0,10

FindTopPage(condition,escape,order,page,max)

Shortcut to perform SELECT * FROM query for the table with ORDER BY and LIMIT appended at the end of query. Option are:

• condition : [string], the sql query after a WHERE clause , you can use ? characters as placeholders for values you would like to have escaped,
• escape : [array], the value of the escaped string
• order : [string], column name that use for sorting
• page: [integer], offset number
• max:[integer], max record

performing

authorModel.FindTopPage("posttime<?",["2019-01-01"],`posttime DESC`,0,10)
.then((res)=>{
    // res is an array of record
 })
 .catch((e)=>{
    console.log(e)
})

is the same as executing query like this

SELECT * FROM tbl_author WHERE posttime<'2019-01-01' ORDER BY posttime DESC LIMIT 0,10

Delete(condition,escape)

A shorthand to perform DELETE FROM query for the selected table. Option are:

• condition : [string], the sql query after a WHERE clause , you can use ? characters as placeholders for values you would like to have option
• escape : [array], the value of the escaped string

performing

authorModel.Delete("id=?","myId")
.then((res)=>{
    console.log('success')
})
.catch((e)=>{
    console.log(e)
})

is the same as executing query like this

DELETE FROM tbl_author WHERE id='myId'

DeleteLimit(condition,escape,limit)

A shorthand to perform DELETE FROM query for the selected model with LIMIT appended at the end of query. Option are:

• condition : [string], the sql query after a WHERE clause , you can use ? characters as placeholders for values you would like to have escaped
• escape : [array], the value of the escaped string
• limit : [integer], number of record deleted

performing

authorModel.Delete("id=?""myId",1)
.then((res)=>{
    console.log('success')
})
.catch((e)=>{
    console.log(e)
})

is the same as executing query like this

DELETE FROM tbl_author WHERE id='myId' LIMIT 1

Count(condition,escape)

A shorthand to perform SELECT IFNULL(COUNT(*),0) FROM query for the table. Inputs are:

• condition : [string], the sql query after a WHERE clause , you can use ? characters as placeholders for values you would like to have escaped,
• escape : [array], the value of the escaped string

performing

authorModel.Count("posttime<?",["2019-01-01"])
.then((res)=>{
    // res is the count result
})
.catch((e)=>{
    console.log(e);
})

is the same as executing query like this

SELECT IFNULL(COUNT(*),0) FROM tbl_author WHERE posttime<'2019-01-01';

Sum(column,condition,escape)

A shorthand to perform SELECT IFNULL(SUM(column),0) FROM query for the table. Option are:

• column : [string], the column name,
• condition : [string], the sql query after a WHERE clause , you can use ? characters as placeholders for values you would like to have escaped,
• escape : [array], the value of the escaped string

performing

authorModel.Sum("score","posttime<?",["2019-01-01"])
.then((res)=>{
    // res the sum results
})
.catch((e)=>{
    console.log(e)
})

is the same as executing query like this

SELECT IFNULL(SUM(score),0) FROM tbl_author WHERE posttime<'2019-01-01';

Update(data,condition,escape)

Simplified form to perform UPDATE query for the selected table. Option are:

• data : [object], updated data object
• condition : [string], the sql query after a WHERE clause , you can use ? characters as placeholders for values you would like to have option
• escape : [array], the value of the escaped string

performing

let updatedData={
	name:"new name",
	email:"[e-mail]"
}
authorModel.Update(updatedData,"id=?","myId")
.then((res)=>{
    // success
})
.catch((e)=>{
    console.log(e);
})

is the same as executing query like this

UPDATE tbl_author SET name='new name', email='[e-mail]' WHERE id='myId'

Insert(data)

Perform INSERT INTO query for the selected table, performing

let newAuthor={
    id:"newId",
	name:"new Author",
	email:"[e-mail]"
}
authorModel.Insert(newAuthor)
.then((res)=>{
    //success
})
.catch((e)=>{
    console.log(e);
})

is the same as executing query like this

INSERT INTO tbl_author SET (id,name,email) VALUES ('newId','new Author','[e-mail]')

InsertIgnore(data)

Perform INSERT IGNORE INTO query for the selected table, performing

let newAuthor={
    id:"newId",
	name:"new Author",
	email:"[e-mail]"
}
authorModel.InsertIgnore(newAuthor))
.then((res)=>{
    //success
})
.catch((e)=>{
    console.log(e);
})

is the same as executing query like this

INSERT IGNORE INTO tbl_author SET (id,name,email) VALUES ('newId','new Author','[e-mail]')

InsertDuplicate(data,dup)

A shorthand to Perform INSERT INTO query for the selected table with ON DUPLICATE KEY UPDATE appended at the end of query, performing

let newAuthor={
    id:"newId",
	name:"new Author",
	email:"[e-mail]"
}
authorModel.InsertDuplicate(newAuthor,["name","email"])
.then((res)=>{
    //success
})
.catch((e)=>{
    console.log(e);
})

is the same as executing query like this

INSERT INTO tbl_author SET (id,name,email) VALUES ('newId','new Author','[e-mail]') ON DUPLICATE KEY UPDATE name = VALUES (name),email = VALUES (email)

InsertMultiple(data)

Perform INSERT INTO query for the selected table for multiple data record, performing

let newAuthorList=[
    {
        id:"newId",
	    name:"new Author",
	    email:"[e-mail]"
    },
    {
        id:"newerId",
	    name:"newer Author",
	    email:"[e-mail]"
    }
]
authorModel.InsertMultiple(newAuthorList)
.then((res)=>{
    //success
})
.catch((e)=>{
    console.log(e);
})

is the same as executing query like this

INSERT INTO tbl_author SET (id,name,email) VALUES ('newId','new Author','[e-mail]'),('newerId','newer Author','[e-mail]')

InsertMultipleWithDuplicate(data,dup)

Perform INSERT INTO query for the selected table for multiple data record with ON DUPLICATE KEY UPDATE appended at the end of query, performing

let newAuthorList=[
    {
        id:"newId",
	    name:"new Author",
	    email:"[e-mail]"
    },
    {
        id:"newerId",
	    name:"newer Author",
	    email:"[e-mail]"
    }
]
authorModel.InsertMultipleWithDuplicate(newAuthorList,["name","email"])
.then((res)=>{
    //success
})
.catch((e)=>{
    console.log(e);
})

is the same as executing query like this

INSERT INTO tbl_author SET (id,name,email) VALUES ('newId','new Author','[e-mail]'),('newerId','newer Author','[e-mail]') ON DUPLICATE KEY UPDATE name = VALUES (name),email = VALUES (email)

License

MIT