diet-mysql

Dead simple no-sql like mysql wrapper. This MySQL runs on Diet.

npm install diet-mysql
19 downloads in the last week
35 downloads in the last month

Diet: MySQL

In Diet you interact with MySQL like it's a No-SQL database. MySQL is the default database engine for Diet. It's included in every GET and POST request unless you use the app.get.simple or app.post.simple methods.

Philosophy

  • With this module you don't have to worry about sql injection and string escaping.
  • Every table is mapped into a CRUD ready object oriented JSON environment when the Diet Application initializes.
  • You can use MySQL in a simple NO-SQL style with the Mapped tables inside the routes, but you can still have access to advanced custom queries with the mysql('query') scheme.
  • The module is embedded inside every GET and POST route, for easy access, but it can be turned off for performance tuning.

Example Usage:

Let's say you have a database awesome_project, with these tables: users, posts, tags. You can access and modify them like this in an app.get or app.post:

app.get('/users', function(request, response, mysql){ // <-- mysql is included in every route
    // GET user_id from the url
    var user_id = request.query.id; // from: /users?id=42

    // GET users
    mysql.users.getBy('id', user_id, function(rows){
        response.end(JSON.stringify(rows));
    });
});

Handling multiple asynchronous mysql requests with next

app.get('/call', function(request, response, mysql){
    // NEXT async handler
    var next = new Next(3, finish);
    var data = {};

    // GET users
    mysql.users.get('id', user_id, function(rows, onerror){
        console.log('users:', rows);
        data.users = rows;
        next();
    });

    // GET posts
    mysql.posts.get('id', user_id, function(rows, onerror){
        console.log('posts:', rows);
        data.posts = rows;
        next();
    });

    // GET tags
    mysql.tags.get('name', 'tag_name', function(rows, onerror){
        console.log('tags:', rows);
        data.tags = rows;
        next();
    });

    // Finishing function
    function finish(){
        console.log('finished', data); // outputs: { users: [..], posts: [..], tags: [..] }
        response.end();
        mysql.end();
    }
});

MySQL Functions

  • mysql.table.create(row, callback):
// create a user
mysql.users.create({ 
    username: 'audrey', 
    passoword: '123456'
}, function(rows, onerror){
    // ...
});
  • mysql.table.save(row, callback):
// this either creates or updates the user, depending on the unique keys
mysql.users.save({ 
    username: 'audrey', 
    passoword: '999999' // <-- we changed the password for audrey
}, function(rows, onerror){
    // ...
});
  • mysql.table.saveMore(row, callback):
// SAVE MORE - save multiple rows at once
mysql.posts.saveMore([
    { id: 1, title: 'Hello World!' },
    { id: 2, title: 'Hello Again!' }
], function(rows, onerror){
    // ...
});
  • mysql.table.update(where, value, whatToUpdate, callback):
// UPDATE user
mysql.users.update('id', '42', {
    username: 'adam',
    password: 123123
}, function(rows, onerror){
    // ...
});
  • mysql.table.get(by, value, callback):
// GET a row with by a parameter like ID or slug
// 'by'            : the parameter to get with
// 'what`         : the parameter value
// 'option1'     : either `callback` or `options`
// 'option2'     : optional `callback` if `option1` is `options`  

// Find users with id 42
// SQL: SELECT * FROM users WHERE id = 42
mysql.users.get('id', 42, function(rows, onerror){
    console.log(rows);
});

// Find all users who live in Budapest then order ascending by city and limit result matches to 5
// SQL: SELECT * FROM users WHERE `city` = 'Budapest' ORDER BY `city` ASC, LIMIT 5
mysql.users.get('city', 'Budapest', { 
    order: 'city ASC', 
    limit: 5 
}, function(rows, onerror){
    console.log(rows);
});
  • mysql.table.delete(by, value, callback):
// Delete users with id 21
// SQL: DELETE FROM users WHERE `id` = 21
mysql.users.delete('id', 21, function(rows, onerror){
    // ...
});

// DELETE with MULTI where
// this deletes every disabled user from San Francisco
// SQL: DELETE FROM users WHERE `city` = 'San Francisco' AND disabled = true
mysql.users.delete({
    city      : 'San Francisco',
    disabled : true
}, function(rows, onerror){
    // ...
});

Calling MySQL independently

This is useful if you want to create custom modules etc..

var MySQL = new MySQLClient({
    host      : 'localhost',
    user      : 'root',
    password : '123123',
    database : 'my_database'
});

MySQL.connect(function(mysqlObject, error){

    // Append MySQL Error to MySQL Object
    mysqlObject.error = error;

    // MySQL Query Wrapper
    var sql = mysql_wrapper({}, {}, mysqlObject, {});

    // SELECT dictionary table from the database
    sql.query('SELECT * FROM dictionary', function(errors, rows){
        console.log(errors, rows);
        sql.end();
    });
});
npm loves you