node-mysql-model

This is a simple model for MySQL which you can manage your own connection as you want. Support a single connection, pool connection or cluster connection.

This module support callback and promise

For example, you create a mysql connection with mysql module and you want to use pool connection. You just create the connection then pass that connection to this module. you will never need to create the mysql connection again and again.

Note The connection need to have method query

Installation

npm install @soulmaneller-dev/nodejs-model-mysql

Usage

Simple use

cosnt mysql     = require( 'mysql' );
const { Model } = require( '@soulmaneller-dev/nodejs-model-mysql' );

const db = mysql.createConnection({
    host     : 'host',
    user     : 'user',
    password : 'secret'
});

const users = new Model( db, options );

/*********************
 * Using method find
 *********************/
users.find( id ).then( result => {
    // do something
});

// or callback
users.find( id, ( err, result ) => {
    // do something
});

/*********************
 * Using method select
 *********************/
users.select( 'id', 'name' ).exec().then( result => {
    // do something
});

// or callback
users.select( 'id', 'name' ).exec(( err, result ) => {
    // do something
});

Extending

You can also use extend

cosnt mysql     = require( 'mysql' );
const { MySQLModel } = require( '@soulmaneller-dev/nodejs-model-mysql' );

const db = mysql.createConnection({
    host     : 'host',
    user     : 'user',
    password : 'secret'
});
const Model = new MySQLModel( db );

const users = Model.extend({ table: 'users', primaryKey: 'user_id' });
users.find( 1 ).then( result => {
    // do something
});

const movies = Model.extend({ table: 'movies', primaryKey: 'movie_id' });
movies.select( 'movie_id', 'movie_name' ).exec().then( result => {
    // do something
});

Options

• table: Table name [Required]
• primaryKey: Primary key name

Methods

find( value [, callback ] )

Find a row from primary key [Required primaryKey].

Note: This method no need to call exec()

sql( queryString [, values ] [, callback ] )

Query data by custom query string

Note: This method no need to call exec()

select([ fieldname [, fieldname... ] ]).exec([ callback ])

Select only some field or leave it empty for *

insert([ fieldname [, fieldname... ] ]).values( value [, value... ]).exec([ callback ])

Insert 1 or more row to database. You can leave fieldname as empty for insert follow table schema sequence.

If you want to insert more than 1 row you can add .values( ... )

update( fieldname, [, op ], value ).exec([ callback ])

Update row(s). You can use .set( fieldname, [, op ], value ) for many fields

op is an operator if you don't set it will be = by default.

delete( fieldname, [, op ], value ).exec([ callback ])

Delete row(s). op is an operator if you don't set it will be = by default.

Additional

where( fieldname, [, op ], value )

Add condition in WHERE.

op is an operator if you don't set it will be = by default.

whereOr( fieldname, [, op ], value )

Add condition in WHERE with OR

op is an operator if you don't set it will be = by default.

whereIn( fieldname, value )

Add condition in WHERE using operator IN

Note: This must be the first condition

orderBy( fieldname [, fieldname... ])

Query by order by fields

limit( number [, to ])

Query by limit the rows

Example

const users = new Model( db, { table: 'users', primaryKey: 'user_id' });

// Select user where user_id = 1 and user_status = 'active'
users.select()
    .where( 'user_id', 1 )
    .where( 'user_status', 'active' )
    .exec()
    .then( result => {
        // do something
    });

// Select user where user_status = 'active' and user_age > 10 order by user_name limit 5
users.select()
    .where( 'user_status', 'active' )
    .where( 'user_age', '>', 10 )
    .orderBy( 'user_name' )
    .limit( 5 )
    .exec()
    .then( result => {
        // do something
    });