mysql-no-query v1.1.8

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

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

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

Deletes records from the database table. row is required

options

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

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