wrapsequel

WrapSQL ( wrapsequel )

A MySQL wrapper that allows you to perform basic CRUD updates on a DB without writing any SQL. Results are returned as promises allowing the uses of 'await' when synchronous request are required.

Getting Started

npm install wrapsequel 

You can either pass Wrapsql an active MySQL connection or just the connection settings and Wrapsql will create it's own.

Wrapsql builds MySQL connection

const Wrapsql = require('wrapsequel')

const config = {
    host: '127.0.0.1',
    port: '8889',
    user: 'user',
    password: 'password',
    database: 'database'
}

const wsql = new Wrapsql(config,true)

let result = await wsql.selectAll('testTable')
console.log( result )

MySQL connection is built and passed to Wrapsql

const mysql = require('mysql')
const Wrapsql = require('wrapsequel')

const sql = mysql.createConnection({
    host: '127.0.0.1',
    port: '8889',
    user: 'user',
    password: 'password',
    database: 'database'
}) 

const wsql = new Wrapsql(sql,true)

let result = await wsql.selectAll('testTable')
console.log( result )

CRUD Example

Below is an example for of how to insert, select, update, and delete a record.

let insertResult = await wsql.insert("customers",{firstName:"Bob",lastName:"Smith",favoriteAnimal:"dog"})
// Equivalent SQL: INSERT INTO customers (firstName, lastName, favoriteAnimal) VALUES ('Bob', 'Smith', 'dog') 

// Insert Result:  {
//   fieldCount: 0,
//   affectedRows: 1,
//   insertId: 1,
//   serverStatus: 2,
//   warningCount: 0,
//   message: '',
//   protocol41: true,
//   changedRows: 0
// }


// Results can be returned either using async/await or then/catch promise syntax 

let selectResult = {}

try {
    selectResult = await wsql.select("customers","*",{firstName:"Bob",lastName:"Smith"})
    // Equivalent SQL: SELECT  * FROM customers WHERE firstName = 'Bob' AND lastName = 'Smith' 
} catch(error) {
    console.log(error)
}

console.log(selectResult)

// OR 

wsql.select("customers","*",{firstName:"Bob",lastName:"Smith"}).then(
        result=>{selectResult=result}
    ).catch(error=>{
        console.log(error)
    })


// Select Result: [
//   {
//     id: 1,
//     firstName: 'Bob',
//     lastName: 'Smith',
//     favoriteAnimal: 'dog'
//   }
// ]


let updateResult = await wsql.update("customers",{favoriteAnimal:"cat"},{id:1})
// Equivalent SQL: UPDATE customers SET favoriteAnimal = 'cat' WHERE id = 1 

// Update Result: {
//   fieldCount: 0,
//   affectedRows: 1,
//   insertId: 0,
//   serverStatus: 2,
//   warningCount: 0,
//   message: '(Rows matched: 1  Changed: 1  Warnings: 0',
//   protocol41: true,
//   changedRows: 1
// }


let deleteResult = await wsql.delete("customers",{id:1})
// Equivalent SQL: DELETE FROM customers WHERE id = 1 

// Delete Result: {
//   fieldCount: 0,
//   affectedRows: 1,
//   insertId: 0,
//   serverStatus: 2,
//   warningCount: 0,
//   message: '',
//   protocol41: true,
//   changedRows: 0
// }

    

Functions

selectAll(tableName)

Select all results from a table.

tableName: Name of table

Example

let result = await wsql.selectAll('testTable')

select( table, columns, where, orderBy=false, order='ASC', limit=false, offset=false )

Select data from a table.

table: Table to select from.
columns: Accepts either an array of columns to return or '*' to return all columns.
where: Object of where conditions, Array defining custom comparison, or string of custom where conditions. Default comparison is 'AND' default operator '='. See examples below for details.). May Be False to exclude.
orderBy: Column you would like to order by. May Be False to exclude.
order: Order of results ('ASC','DESC'). May Be False to exclude.
limit: Number of results to return. May Be False to exclude.
offset: Number of rows to offset before return results. May Be False to exclude.
groupBy: Column to group results by. May Be False to exclude.

where: comparisons can be represented the following ways.

// Default 'AND' comparison
{column1:value,colum2:value}
// SQL Result: WHERE column1=value AND column2:value

// Defined 'AND' comparison 
["AND",{column1:value,colum2:value}]
// SQL Result: WHERE column1=value AND column2:value

// Defined 'OR' comparison 
["OR",{column1:value,colum2:value}]
// SQL Result: WHERE column1=value OR column2:value

// Defined 'IN' comparison 
["IN",{column1:[value1,value2]}]
// SQL Result: WHERE column1 IN ('value1','value2')

// Defined operator 
{column1:[">",value],colum2:["<",value]}
// SQL Result: WHERE column1>value AND column2<value

// Customer WHERE string 
`column1>value AND column2 IS NOT null OR column2 = 'test'`
// SQL Result: WHERE column1>value AND column2 IS NOT null OR column2 = 'test'

Example

 // Equivalent SQL: SELECT 'value' FROM 'testTable' WHERE value='testValue' GROUP BY 'value' ORDER BY 'id' DESC LIMIT 10
 let result = await wsql.select('testTable','value',{value:"testValue"},"id","DESC",10,offset=false,value)

insert(table,insert)

Insert data into a table.

table: Table to select from.
insert: Object of values to insert {column:"value"} or array of Objects to insert multiple rows.

Example

// Single row insert.
let result = await wsql.insert('insertTest',{testData:"testInsert"})
// Multiple row insert.
let result2 = await wsql.insert('insertTest',[{testData:"testInsert1"},{testData:"testInsert2"}])

update(table,set,where=false)

Update records

table: Table to update.
set: Object of values to set {column:"value"}
where: Object of where conditions. May Be False

Example

let result = await wsql.update('insertTest',{value:'updated'},{value:'1'})

delete(table,where=false)

Delete records.

table: Table to delete records from.
where: Object of where conditions. May Be False

Example

let result = await wsql.delete('insertTest',{value:'1'})

count(table,where=false,label)

Count rows in result.

table: Table to delete records from.
where: Object of where conditions. May Be False
label: Label for count results.

Example

let result = await wsql.count('testTable',{value:'testRow2'},'theCount')

transaction(queries)

Submit an array of dependant SQL queries to be executed in one request. If one fails they are all rolled back. Results is returned as array of arrays.

queries: Array of SQL queries.

Example

let queries = [
    "SELECT * FROM testTable ORDER BY id DESC",
    "SELECT * FROM testTable",
]

let result = await wsql.transaction(queries)

// result[0] first queries results. 
// result[1] second queries results.   

query(query)

Pass through a raw SQL query.

query: SQL query

Example

let query = "SELECT * FROM testTable ORDER BY id DESC"

let result = await wsql.transaction(query)