my-jsql
Easy to use SQL query builder
Install
npm install my-jsql
About
This module allows you to run basic SQL queries by passing JavaScript objects. You also don't need to worry about escaping the variables you pass.
ES8 Ready
(async () => {
const jsql = new MyJsql(con)
let first = 'John'
let last = 'Doe'
const results = await jsql
.s()
.t('users')
.w({ first, last })
.run()
console.log(results)
})()
Basic Usage
Here are some examples
const mysql = require('mysql')
const MyJsql = require('my-jsql')
const con = mysql.createConnection({
host: 'localhost',
user: 'me',
password: 'secret',
database: 'my_db'
})
con.connect()
const jsql = new MyJsql(con)
jsql.t('users')
jsql
.s()
.w({last: 'Doe'})
.o({first: 'desc'})
.l(10, 5)
.run((err, results, fields) => {
if (err) throw err
console.log('Result is: ', results[0])
})
jsql
.i({
first: 'John',
last: 'Doe',
email: 'email@email.com'
})
.run()
jsql
.u({email: 'email@email.com'})
.w({email: null}, {not: {first: 'John'}})
.run()
jsql
.d()
.w({
first: 'John',
not: {email: null}
})
.run()
jsql
.s()
.w('email LIKE ?', ['e%'])
.run((err, results, fields) => {
if (err) throw err
jsql.each(results, (index, value) => {
console.log('Result ' + index + ' is: ', value)
})
})
jsql.run('SELECT * FROM users WHERE id=? AND first=?', [1,'John'], (err, results, fields) => {
if (err) throw err
console.log('Result is: ', results[0])
})
API
This section is devoted to the API documentation.
MyJsql(connection[, options])
Pass a database connection. Currently only supports the mysql module.
const mysql = require('mysql')
const con = mysql.createConnection({
host: 'localhost',
user: 'me',
password: 'secret',
database: 'my_db'
})
con.connect()
const jsql = new MyJsql(con)
Refer to mysql's docs to view which connections you can pass.
Optionally pass an options object if you want to auto clear after calling run()
:
const jsql = new MyJsql(con, { autoClear: true })
.t(table)
Pass a string of the table name.
jsql.t('users')
jsql.s().run()
jsql
.i({first: 'John'})
.t('users')
.run()
.s([columns[, column]])
Pass a string (or multiple strings) or an array of strings in order to SELECT
certain columns. If nothing is passed, all the columns will be returned SELECT * FROM...
.
jsql
.s('first', 'email')
.t('users')
.run((err, results, fields) => {
if (err) throw err
})
jsql
.s(['first', 'email'])
.t('users')
.run((err, results, fields) => {
if (err) throw err
})
jsql
.s()
.t('users')
.run((err, results, fields) => {
if (err) throw err
})
.i(data)
Pass an object of the data you want to INSERT
, with the keys being the table columns.
jsql
.i({
first: 'John',
last: 'Doe',
email: 'email@email.com'
})
.t('users')
.run((err, results, fields) => {
if (err) throw err
})
.u(data)
Pass an object of the data you want to UPDATE
, with the keys being the table columns.
jsql
.u({first: 'Jane'})
.t('users')
.w({id: 1})
.run((err, results, fields) => {
if (err) throw err
console.log('Results updated: ', results.affectedRowed)
})
.d()
Nothing needs to be passed for a DELETE FROM
.
jsql
.d()
.t('users')
.w({id: 1})
.run((err, results, fields) => {
if (err) throw err
console.log('Results deleted: ', results.affectedRowed)
})
.w([conditions1[, conditions2[, ...]]])
Pass one or more objects as conditional WHERE
statements. Statements in the same object are separated by AND
. If you pass multiple objects, they are separated by OR
. If nothing is passed, there will be no WHERE...
statement. MyJsql also saves the last where statement, so you don't have to call it again, but this also means you need to clear if you don't want to reuse your previous statement.
jsql
.s()
.w({id: 1, name: 'John'})
.run()
jsql
.s()
.w({id: 1, email: null}, {name: 'John'})
.run()
jsql
.u({first: 'Jane'})
.run()
If you want to use the NOT
statement, pass a nested object with not
as it's key.
jsql
.s()
.w({not: {id: 1}})
.run()
Currently, when passing objects, this function only uses the =
operator. If you need to use other operators, you will need to use the API below.
.w([condition[, values]])
Pass the WHERE
condition as a string. If you want to escape the values using the ?
replacement, then pass the values as an array.
jsql
.s()
.t('products')
.w('price>=? AND name LIKE ?', [99.99, 'a%'])
.run()
.o([orderBy])
Pass the ORDER BY
statement as an object with the keys being the column names and the value being asc
or desc
. Pass nothing to clear the previous order by statement.
jsql
.s()
.o({first: 'asc', last: 'desc'})
.run()
.l([limit[, offset]])
Pass the LIMIT
and OFFSET
statements as ints. Pass nothing to clear the previous statement.
jsql
.s()
.l(10)
.run()
jsql
.s()
.l(10, 5)
.run()
.run([query[, values[, callback]]])
You can manually pass a query as a string. If you want to use ?
to escape values in the query, just pass an array of values. Pass a callback function to be able to access the return values. More documentation of the callback function can be found here.
jsql.run('SELECT * FROM users WHERE id=? AND first=?', [1,'John'], (err, results, fields) => {
if (err) throw err
console.log('Result is: ', results[0])
})
promise .run()
The .run
function also returns a promise if you prefer to use async await
or .then
and .catch
(async () => {
try {
const results = await jsql.run()
console.log(results)
} catch (err) {
throw err
}
})()
jsql.run()
.then(results => {
console.log(results)
})
.catch(err => {
throw err
})
.clear()
Clears all statements (including the table). If this isn't called, all of your previous conditions are used again.
jsql
.s()
.w({id: 1})
.l(1)
.run()
jsql.s().run()
jsql.clear()
jsql.s().t('users').run()
.getQuery()
Returns the current SQL query as a string.
jsql.s().w({id: 1})
console.log(jsql.getQuery())
.getValues()
Returns the current values as an array.
jsql.s().w({id: 1, first: 'John'})
console.log(jsql.getValues())
.each(variable, function)
Similar to jQuery's each function, you can pass either an array as the first argument, or you can pass an object. The function you pass should have 2 arguments. The first is the index or key. The second is the value. This will make it easier to cycle through returned rows after you run a query.
jsql
.s()
.t('users')
.run((err, results, fields) => {
jsql.each(results, (index, value) => {
})
})