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.
npm install wrapsequelA 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.
``
npm install wrapsequel
You can either pass Wrapsql an active MySQL connection or just the connection settings and Wrapsql will create it's own.
`
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 )
`
`
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 )
`
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
// }
`
Select all results from a table.
tableName: Name of table
`
let result = await wsql.selectAll('testTable')
`
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 // Customer WHERE string `
{column1:[">",value],colum2:["<",value]}
// SQL Result: WHERE column1>value AND column2
column1>value AND column2 IS NOT null OR column2 = 'test'
// SQL Result: WHERE column1>value AND column2 IS NOT null OR column2 = 'test'$3
`
// 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 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.
`
// 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 records
table: Table to update.
set: Object of values to set {column:"value"}
where: Object of where conditions. May Be False
`
let result = await wsql.update('insertTest',{value:'updated'},{value:'1'})
`
Delete records.
table: Table to delete records from.
where: Object of where conditions. May Be False
`
let result = await wsql.delete('insertTest',{value:'1'})
`
Count rows in result.
table: Table to delete records from.
where: Object of where conditions. May Be False
label: Label for count results.
`
let result = await wsql.count('testTable',{value:'testRow2'},'theCount')
`
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.
`
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.
`
Pass through a raw SQL query.
query: SQL query
`
let query = "SELECT * FROM testTable ORDER BY id DESC"
let result = await wsql.transaction(query)
``