npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

my-jsql

v2.2.3

Published

Easy to use SQL query builder

Downloads

61

Readme

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'

  // SELECT * FROM users WHERE first='John' AND last='Doe'
  const results = await jsql
    .s()
    .t('users')
    .w({ first, last })
    .run()

  console.log(results)
})()

Basic Usage

Here are some examples

// As you can see, we are using the mysql module to create a connection
const mysql = require('mysql')
const MyJsql = require('my-jsql')

// Create your connection and pass it to MyJsql
const con = mysql.createConnection({
  host: 'localhost',
  user: 'me',
  password: 'secret',
  database: 'my_db'
})
con.connect()
const jsql = new MyJsql(con)

// Set the table
jsql.t('users')

// SELECT * FROM users WHERE last='Doe' ORDER BY first DESC LIMIT 10 OFFSET 5 
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])
  })

// INSERT INTO users (first, last, email) VALUES ('John','Doe','[email protected]')
jsql
  .i({
    first: 'John',
    last: 'Doe',
    email: '[email protected]'
  })
  .run()

// UPDATE users SET email='[email protected]' WHERE email IS NULL OR NOT first='John'
jsql
  .u({email: '[email protected]'})
  .w({email: null}, {not: {first: 'John'}})
  .run()

// DELETE FROM users WHERE first='John' AND email IS NOT NULL
jsql
  .d()
  .w({
    first: 'John',
    not: {email: null}
  })
  .run()

// Manually write a WHERE condition
// SELECT * FROM users WHERE email LIKE 'e%'
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)
    })
  })

// Manually write a query
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.

// Set the table for the following queries
jsql.t('users')

// SELECT * FROM users
jsql.s().run()

// Set the table while building a query
// INSERT INTO users (first) VALUES ('John')
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....

// Passing strings
// SELECT first, email FROM users
jsql
  .s('first', 'email')
  .t('users')
  .run((err, results, fields) => {
    if (err) throw err
    // These results will only return with the 'first' and 'email' columns
  })

// Passing an array
// SELECT first, email FROM users
jsql
  .s(['first', 'email'])
  .t('users')
  .run((err, results, fields) => {
    if (err) throw err
    // These results will only return with the 'first' and 'email' columns
  })

// SELECT * FROM users
jsql
  .s()
  .t('users')
  .run((err, results, fields) => {
    if (err) throw err
    // These results will return all columns
  })

.i(data)

Pass an object of the data you want to INSERT, with the keys being the table columns.

// INSERT INTO users (first, last, email) VALUES ('John', 'Doe', '[email protected]')
jsql
  .i({
    first: 'John',
    last: 'Doe',
    email: '[email protected]'
  })
  .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.

// UPDATE users SET first='Jane' WHERE id=1
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.

// DELETE FROM users WHERE id=1
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.

// SELECT * FROM users WHERE id=1 AND name='John'
jsql
  .s()
  .w({id: 1, name: 'John'})
  .run()

// SELECT * FROM users WHERE (id=1 AND email IS NULL) OR name='John'
jsql
  .s()
  .w({id: 1, email: null}, {name: 'John'})
  .run()

// This will use the last WHERE statement
// UPDATE users SET name='Jane' WHERE (id=1 AND email IS NULL) OR name='John'
jsql
  .u({first: 'Jane'})
  .run()

If you want to use the NOT statement, pass a nested object with not as it's key.

// SELECT * FROM users WHERE NOT id=1
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.

// SELECT * FROM products WHERE price>=99.99 AND name LIKE 'a%'
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.

// SELECT * FROM users ORDER BY first ASC, last DESC
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.

// Only passing a limit
// SELECT * FROM users LIMIT 10
jsql
  .s()
  .l(10)
  .run()

// Passing an offset too
// SELECT * FROM users LIMIT 10 OFFSET 5
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
  }
})()

// or

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.

// SELECT * FROM users WHERE id=1 LIMIT 1
jsql
  .s()
  .w({id: 1})
  .l(1)
  .run()

// Where and limit statements are saved 
// SELECT * FROM users WHERE id=1 LIMIT 1
jsql.s().run()

// Clears everything
jsql.clear()

// SELECT * FROM users
jsql.s().t('users').run()

.getQuery()

Returns the current SQL query as a string.

jsql.s().w({id: 1})

console.log(jsql.getQuery())
// 'select * from users where id=?'

.getValues()

Returns the current values as an array.

jsql.s().w({id: 1, first: 'John'})

console.log(jsql.getValues())
// [1, 'John']

.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.

// SELECT * FROM users
jsql
  .s()
  .t('users')
  .run((err, results, fields) => {
    jsql.each(results, (index, value) => {
      // Cycles through all of your results
    })
  })