mysql-no-query

A middleware tool to use mysql databases' tables as JavaScript objects without writing any queries

Installation

$ npm install mysql-no-query

Quick Example

var express = require('express')
var app = express()

// Including 'mysql-no-query' package.
var mysqlNoQuery = require('mysql-no-query') 

// Connecting to database.
var db = new mysqlNoQuery();
db.connect({
  host: 'localhost',
  user: '<username>',
  password: '<password>',
  database: '<database>'
})

// Specify app port as you like.
app.listen(8888);

app.get('/api/comments', (req, res) => {

  // This code line replaces 'SELECT * FROM comments' query, It gets all rows of 'comments' table.
  db.schema.comments.get({}, (error, results, fields) => {
    res.end(JSON.stringify(results))
  })

})

Usage

schema.<table>

All tables in the database can be accessed like this database.schema.<table>, names of the objects will be the same as the names of the tables in the database's schema. So, if we want to access users table, we access it in the code like this database.schema.users

schema.<table>.get(options, callback)

Retrieves data from <table> depending on specific options and triggers a callback when it ends. options is required, if you don't need it set it to {}

options

Option | Type | Required | Description --- | --- | --- | --- select |Array<string> | No | list of columns that will be selected by the query. defaults to * where |Object or string| No | adds custom WHERE to SELECT statement sort |string | No | adds custom SORT BY to SELECT statement limit |string | No | adds custom LIMIT to SELECT statement offset |string | No | adds custom OFFSET to SELECT statement join |Array<Join> or string| No | adds JOIN to SELECT statement. Assign 'all' to it instead of object to join all foreign keys of the table with its references

Example

/* 
This will select first 3 comments' `text`s and their writer's `first_name`s of a post which has id = 5

Equivalent to 
SELECT comments.text, writer.first_name FROM comments JOIN users AS writer ON writer.id = comments.user_id WHERE comments.post_id = 5 LIMIT 3;

*/
db.schema.comments.get({
    select: ['text'],
    where: {
      post_id: 5
    } // You also can use it like this: (where: 'post_id = 5')
    limit: 3,
    join: [{ 
      with: 'users', 
      as: 'writer', 
      on: 'writer.id = comments.user_id',  
      select: ['first_name']
    }]
  }, (error, results, fields) => {
    res.end(JSON.stringify(results))
  })

schema.<table>.insert(row, callback)

Inserts a new record in the database table. row is required

row

A Row object of the table's structure. see schema.<table>.row() for more details

schema.<table>.edit(options, callback)

Updates records that are already in the database table. row is required

options

Option | Type | Required | Description --- | --- | --- | --- set |string | Yes | specifies the edited columns with its new values where |string | No | adds custom WHERE to UPDATE statement. If not specified, all records will be updated

schema.<table>.delete(options, callback)

Deletes records from the database table. row is required

options

Option | Type | Required | Description --- | --- | --- | --- where |string | No | adds custom WHERE to DELETE statement. If not specified, all records will be deleted

schema.<table>.row(init)

Returns a Row object of the table's structure with default values

init

An object to initialize the returned Row object with it

Prototypes

Join Prototype

property | Type | Required | Description --- | --- | --- | --- with |string | Yes | Specifies which table to join with on |string | Yes | Condition to which columns the join will be applied as |string | No | Specifies alias to the joined table select |Array<string> | No | specifies which columns to select from the joined table. defaults to * type |enum | No | Specifies JOIN type. applicable values are LEFT, RIGHT, INNER or FULL OUTER. defaults to FULL OUTER

Row Prototype

Each schema.<table>.row() returns a different object depends on the table, but in general each one has a list of properties represents COLUMNS_NAMEs of the table and each property equals to the default value for its column in the database.

console.log(db.schema.comments.row())
/*  Outputs

	{ id: null,
	  user_id: null,
	  post_id: null,
	  text: null,
	  date_added: null,
	  update: [Function],
	  delete: [Function] }
  */

insert(callback)

Inserts this record in the database

// Creates a new row initialized with `text`, `user_id`, `post_id`.
  var newRow = db.schema.comments.row({ text: 'Hello from Row.insert()', user_id: 29, post_id: 72 });

  // Inserts the row in the database.
  newRow.insert((error, results, fields) => {
    console.log(results.insertId);
  })

update(callback)

Updates this record in the database using the primary keys of the table

// Gets comment with id = 5.
  db.schema.comments.get({ where: 'id = 5' }, (error, results, fields) => {

    // Creates a Row object and initializing it with the data of the retrieved record 
    var record = db.schema.comments.row(results[0])

    // Let's change its text
    record.text = 'New Text!'

    // this commits the record in the database
    record.update();
    
  })

delete(callback)

Deletes this record from the database using the primary keys of the table

// Gets comment with id = 5.
  db.schema.comments.get({ where: 'id = 5' }, (error, results, fields) => {

    // Creates a Row object and initializing it with the data of the retrieved record 
    var record = db.schema.comments.row(results[0])
    
    // this deletes the record from the database
    record.delete();
    
  })

callback

A function to be triggered when main function is finished processing. It passes 3 parameters:

• error: will be an Error if one occurred during the query
• results: will contain the results of the query
• fields: will contain information about the returned results fields (if any)

What's new

• v1.0.7: Now you can assign an object to options.where, properties of the object will be treated as a column name and its value. All conditions are connected by AND. You still can use options.where as a string to provide a customized WHERE to the query.

• v1.1.2: Using SQL Functions in SELECT is available now by starting the string with a space character, for example select: [' COUNT(*)', 'Name'].