levity-MySQL

Node SQL Operations

Installation and Usage

$ npm install levity-mysql

import mysql from 'mysql';
import {createDBPool, DBEnd, dbOp} from 'levity-validator';

// creating MySql Database Pool
createDBPool({
	connectionLimit : 50,
	host			: 'localhost',
	user			: 'root',
	password	: 'root',
	database	: 'test',
	charset		: 'utf8mb4_unicode_ci',
	timezone	: 'UTC',
	multipleStatements: true
})

async function addUser(){
	// table name, data to insert
	await dbOp.insert('users', {first_name: 'David', last_name: 'Dobrik', email: '[email protected]'});
}

async function getUserName(){
	// table name, fields to select, where conditions, parameters to assign
	let user = await dbOp.get({
		table: 'users',
		fields: ['first_name','last_name'],
		where: "email=?",
		params: ['[email protected]']
	});
	
	return `${user.first_name} ${user.last_name}`
}

await addUser();
await getUser();

// end database Connection
await DBEnd();

Documentation

createDBPool(options={}, poolName='default')

options (Object) [required]: for all options see https://github.com/mysqljs/mysql#connection-options.

poolName (String) [optional]: pool name. default: 'default'

example

createDBPool({
	connectionLimit : 50,
	host			: 'localhost',
	user			: 'root',
	password	: 'root',
	database	: 'test',
	charset		: 'utf8mb4_unicode_ci',
	timezone	: 'UTC',
	multipleStatements: true
})

Operations

Every Operation function has two optional parameters

database (String) [optional]: the database name if not specified in the connection options
poolName (String) [optional]: the pool name only if there are multiple pools

• Low Operations
• High Operations

Low-Operations

insert({table='', fields=[], data={}, database, poolName})

table (String): table name to insert fields (Array) Optional: fields/columns names. example: ['first_name','email'] data (Object || Array): data to insert, when fields is ignored the data is an object or array to add multiple rows but fields are required. examples: {first_name: 'David', last_name: 'Dobrik'}, [['David','Dobrik'], ['Felix','shellberg']]

example

dbOp.insert({table:'users', data: {first_name: 'David', last_name: 'Dobrik'}});
// adds a record to the users table with first_name='David' and last_name='Dobrik'

dbOp.insert({table:'users', fields: ['first_name','last_name'], data: [['David','Dobrik'], ['Felix','shellberg']]});
// adds two records to the users table

select({table='', fields=[], where='', params=[], additions='', database, poolName})

table (string): table name to select from fields (array): fields to select where (string | object): where condition - Where Examples params (array): parameters to bind orderby (Object): ORDER BY, example: {name:'DESC', age:'ASC'} additions (string): additional conditions. example: ORDER BY, LIMIT

example

dbOp.select('users', ['first_name','last_name'], "email=?", ['[email protected]'], 'LIMIT 1');
// or with where as object
dbOp.select('users', ['first_name','last_name'], {email: '[email protected]'}, 'LIMIT 1');
// select the user with the email that is equal to '[email protected]'

update({table='', fields={}, where='', params=[], additions='', database, poolName})

table (string): table name to select from fields (object): fields to update {field_name: field_value} where (string || object): where condition - Where Examples params (array): parameters to bind additions (string): additional conditions. example: ORDER BY, LIMIT

example

dbOp.update({
	table: 'users',
	fields: {first_name:'Felix', last_name:'shellberg'},
	where: {email: '[email protected]'},
	additions: 'LIMIT 1'
});
// update the user with the '[email protected]' email first and last name to felix shellberg

delete({table='', where='', params=[], additions='', database, poolName})

table (string): table name to select from where (string || object): where condition - Where Examples params (array): parameters to bind additions (string): additional conditions. example: ORDER BY, LIMIT

example

dbOp.delete({
	table: 'users',
	where: {email=?},
	params: ['[email protected]'],
	additions: 'LIMIT 1'
});
// deletes the user with the '[email protected]' email

High-Operations

get({table='', fields=[], where='', params=[], additions"", database, poolName})

gets only one record, adds 'LIMIT 1' to the end of the query

table (string): table name to select from fields (array): fields to select where (string || object): where condition - Where Examples params (array): parameters to bind additions (string): additional conditions. example: ORDER BY (note: get already adds LIMIT 1 to the end of the query)

example

dpOp.get({
	table: 'users',
	fields: ['first_name','last_name'],
	where: {email: '[email protected]'},
});
// return {first_name: 'David', last_name: 'Dobrik'}

doesExist({table='', where='', database, poolName})

gets only one record

table (string): table name to select from where (string || object): where condition - Where Examples

returns: boolean

example

doesExist({
	table: 'users',
	where: {email: '[email protected]'}
})
// return true

createTables(dbSchema={}, tablesIgnored, database, poolName)

Creates Tables in the database based on a schema

dbSchema (Object): The Database Schema tablesIgnored (Array): Array of tables names to ignore and will not be added

dbSchema Properties: {columnName: {type, default, isID, autoIncrement, primaryKey, allowNull, dbIgnore}}

columnName: any valid sql column name

type (string): any valid sql data type, ('INT(11)', 'VARCHAR(255)', 'DOUBLE(12,2)', 'JSON', etc..)

isID (boolean): if true then the column is 'AUTO_INCREMENT PRIMARY KEY NOT NULL', default: false

autoIncrement (boolean): if true then the column is AUTO_INCREMENT, default: false

primaryKey (boolean): if true then the column is primaryKey, default: false

allowNull (boolean): if true then null is now allowed in this column, default: true

dbIgnore (boolean): if true then the column will be ignored and not added to the table, default: false

example

const dbSchema = {
	users: {
		id: {type: 'INT(11)', isID: true},
		first_name: {type: 'VARCHAR(255)'},
		last_name: {type: 'VARCHAR(255)'},
		email: {type: 'VARCHAR(255)'},
		money: {type: 'INT(11)', default: 0},
		details: {type: 'JSON', default: '[]'},
		extra: {type: 'INT(11)', dbIgnore: true},
	},
	users_categories: {
		id: {type: 'int(11)', isID: true},
		name: {type: 'VARCHAR(255)'}
	},
	stats: {
		id: {type: 'int(11)', isID: true},
		name: {type: 'VARCHAR(255)'}
	}
}

const tablesIgnored = ['stats'];

await createTables(dbSchema, tablesIgnored);

Where

1- Where can be String ('email=? AND first_name=?') and the query values sent separately in the params array

2- Where can be Object with just key value ({email: '[email protected]'}), don't worry the query values are escaped

The value can be String or Object If the value is an Object, then it should have "op" and "value" properties op can be one of ('<','>','<=','>=','!=','IN') If the 'op' is 'IN' then the value must be an Array

{balance: {op:'<', value: 1500} }
// balance < 1500
{balance: {op:'>', value: 1500} }
// balance > 1500
{balance: {op:'<=', value: 1500} }
// balance <= 1500
{balance: {op:'>=', value: 1500} }
// balance >= 1500
{balance: {op:'!=', value: 1500} }
// balance != 1500
{balance: {op:'IN', value: [1500,2000]} }
// balance IN (1500,2000)
{balance: {op:'BETWEEN', value: [1000,1500]} }
// balance BETWEEN 1000 AND 1500
{first_name: {op:'LIKE', value: '%Cas%'} }
// first_name LIKE '%Cas%'

3- Where can be Object with complex structure Every "AND" or "OR" Must have and Array value In this array you can have one or multiple objects Every Object can have another ("AND" or "OR") or just a key value object

{
	'OR': [
		{id: 1},
		{'AND': [
				{email: '[email protected]'},
				{money: {op:'<=', value'[email protected]'}},
				{'OR': [{first_name: 'Casey', last_name: 'Neistat'}]}
			]
		}
	]
}

don't worry the query values are escaped

All of the below are examples of valid Where Conditions

const where0 = {
	id: 1
}
// where id=1


const where1 = {
	'AND': [
		{id: 1, email: '[email protected]'}
	]
}
// WHERE id=1 AND email='[email protected]'


//======= deprecated
// const where2 = {
// 	'AND': [
// 		{id: 1},
// 		{'OR': [ {first_name: ['Casey','Felix']} ]}
// 	]
// }
//==================

const where2 = {
	'AND': [
		{id: 1},
		{first_name: {op:'IN', value:['Casey','Felix']} }
	]
}
// where: id=1 AND first_name IN ('Casey','Felix')

const where3 = {
	'OR': [
		{id: 1},
		{'AND': [
				{email: '[email protected]'},
				{money: {op:'<', value:2000} },
				{'OR': [{first_name: 'Casey', last_name: 'Neistat'}]}
			]
		}
	]
}
// WHERE id=1 OR ( email='[email protected]' AND money<2000 AND ( first_name='Casey' OR last_name='Neistat' ) )


// ============ deprecated
// const where4 = {
// 	'OR': [{first_name: ['Casey','Felix']}]
// }
// ==============

const where4 = {
	first_name: {op:'IN', value:['Casey','Felix']}
}
// where: first_name IN ('Casey','Felix')

const where5 = {
	money: {op:'<', value: 1500}
	// money: {op:'>', value: 1500}
	// money: {op:'<=', value: 2000}
	// money: {op:'>=', value: 1500}
	// money: {op:'!=', value: 2000}
	// money: {op:'IN', value: [1000,2000]}
	// money: {op:'BETWEEN', value: [1000,1500]}
}
// where: money < 1500

const where6 = {
	first_name: {op:'LIKE', value:'%Fel%'}
}
// where: first_name LIKE %Fel%

const where7 = {
	money: {op:'BETWEEN', value: [1000,1500]}
}
// where: money BETWEEN 1000 AND 1500

To Be Continued...