Smart MySQL smart-mysql made to build queries by hand waste your time.

So let's build it with JavaScript.

How to Install

$ npm install smart-mysql

How to connect

Let’s connect with smart_mysql class with calling class name. You can make a new object of smart_mysql class as we have done here .

host, user , password , charset , database is required .

var smart_mysql = require('smart-mysql');

var db = new smart_mysql({
    host: 'localhost',
    user: 'root',
    password: '',
    charset: 'utf8_unicode_ci',
    database: 'avl'
}, false);

Specify your table

It’s easy to define which table you wants to work with. You can specify your table name as we have done below.

db.collection('table_name');

Inserting Rows

We'll show you, That's simple to insert some records.

db.collection('user').insert({a: 1});
db.collection('user').insert({a:1, b:2});

Or you can insert many rows in a command.

db.collection('user').Insert({[{a:1}, {a:2}, {a:3}], b:1});

You can use callback () function.

db.collection('user').Insert({a:1}, function(count){
    console.log('Number of inserted rows : ' + count);
});

What does callback () function do ? You can retrieve results of your action with this class. We show you how to.

db.collection('user').insert({a:1}, function(done) {
    if (done) {
        console.log ("done!");
    };
});

Updating Rows

Update your table simply. This line update all of your rows without any condition. Use put new data in first parameter. Next line will update all of your culomns named a.

db.collection('user').Update({a: "your data"}, function(done){
    if (done) {
        console.log ("done!");
    };
});

This sql query happen for last command:

Update tbl set a: "your data"

And you can chose condition if you want to, Put it in second parameter.

db.collection('user').Update({a:"your data"}, {a:"my data"} , function(done){
    if (done) {
        console.log ("done!");
    };
});

In last line this query string will happen.

Update tbl set a : "your data" where a : "my data"

Delete a Row

It’s simple to delete a row from table with that condition you want or Without any condition as we have done below.

db.collection('user').delete({id:5}, function(done){
    if (done) {
        console.log ("done!");
    };
});

Last command return this query string:

Delete * from tablename where id = 5

Or in other way we can use an array in condition.

db.collection('user').delete({id:[5, 7, 10, 14, 17]}, function(done){
    if (done) {
        console.log ("done!");
    };
});

Selecting Rows

There is three way to use select function

1. table
2. record
3. field

1. In table function you will take all of rows that you want to be. You can use this parameters for table function.

db. Table ( field, filter, order, group, limit, page, callback ) Field can be a string or an array. db.table("id") OR db.table(["id", "name"]) OR db.table(false) FALSE will return * or all rows in select You can set condition in second parameter for select

db.collection('user').table(["id","username"], {id:1}, function(data){
    // Process the result data
});

OR use array condition

db.collection('user').table([{id:1}, {age:25}], function(data){
    // Process the result data
 });

In next parameter you can use ORDER in next parameter to condition; order can be an array or a string

db.collection('user').table(false, [{id : 1}, {age: 25}], "id desc", function(data){
    // Process the result data
});

Or use an array for order

db.collection('user').table(["id", "name"], [{id: 1}, {age: 25], ["id desc", "name asc"], function(data){
    // Process the result data
});

Record

2. in record function you take only one row of table as we have done below. You can use this function like table function. But different is this function fetch first row.

record(field, filter, order, callback);

db.collection('user').record(["id", "name"], {id: 5}, (rec)=>{console.log(rec);});

3. field function only return first field of that row we fetch db.field ( [ "name" ] , { id : 5 } );

Last command return this query, string. Select name from tablename where id=5, First parameter is that field we want get and second parameter Is our condition.