mysql-js-wrapper

Bigg MySql DB Wrapper

A fully featured database wrapper for MySql, based on the wrapper included in Codeigniter.

Using this database wrapper with Node frameworks enables data objects to be passed and the Sql queries automatically constructed based on the key:value pairs, meaning less time defining what data should be inserted, updated or deleted.

Installation

npm install mysql-js-wrapper

Prerequisites

Config File

Database connection settings should be held in a config file and then imported to your code.

 // dbConfig.js
 
 export const connections = {
 
 connection1:
     {
         host: 'Localhost',
         user: 'local_user1',
         password: 'my_password1',
         port: 3306,
         database: 'my_db1'
     },
 connection2:
     {
         host: 'Localhost',
         user: 'local_user2',
         password: 'my_password2',
         port: 3306,
         database: 'my_db2'
     },
 };

Importing

import sqlBuilder from 'mysql-js-wrapper'
import { connections } from '../config/dbConfig.js' 
const db = new sqlBuilder();

Useage

Selecting records

db.conn(connections.connection1);						
db.where({field1: 'value1', field2: 'value2'});									
db.limit(1);											
db.orderBy({createdDate: 'ASC'});						
db.get('my_table').then((result) => {					

	// Do something with the result

}).catch((error) => {

    // Do something with the error
});

Updating records

const dataObject = JSON.parse(event.body); 				

db.conn(connections.connection1); 						
db.data(dataObject); 									
db.where({id: 2}); 										
db.update('my_table').then((result) => { 				

	// Do something with the result

}).catch((error) => {

    // Do something with the error
});

Inserting records

const dataObject = JSON.parse(event.body); 				

db.conn(connections.connection1); 						
db.data(dataObject); 									
db.insert('my_table').then((result) => { 				

	// Do something with the result

}).catch((error) => {

    // Do something with the error
});

Deleting records

db.conn(connections.connection1); 						
db.where({id:, 3});										
db.delete('my_table').then((result) => { 				
        
   // Do something with the result

}).catch((error) => {

    // Do something with the error
});

Functions List

#####conn(MySQLConnection conn) Set the connection to use from your config file

#####data(Object key/value) Set a data object containing key/value pairs to match table fields e.g,

db.data({
    first_name:   'joe',
    last_name:    'blogs'
})

#####where(Object key/value) Set a where statement. Can be used multiple times e.g.

db.where({first_name:, 'joe', last_name: 'blogs'});

#####select(String select) Not required unless you want to manually choose what records to select. Defaults to * e.g.

db.select('first_name as firstName, last_name as lastName');

#####join(Object key/value) Join another table. Defaults to left outer join if join type not specified e.g.

db.join({table: orders, condition: 'order.id = customers.id'})

or

db.join({table: orders, condition: 'order.id = customers.id', type: 'inner'})

or

db.join(
    [
        {
            table: orders, condition: 'order.id = customers.id'
        },
        {
            table: addresses, condition: 'customers.id = addresses.id'
        }
    ]
)

#####limit() Limit the results of a select statement e.g.

db.limit(10);

#####groupBy() Group the results e.g.

db.groupBy('firstName');

or

db.groupBy(['firstName', 'age']);

#####orderBy() Order the results of a select statement String fieldName e.g.

db.orderBy({'first_name': 'desc'});

or

db.orderBy(
    [
        {
            'first_name': 'desc'
        },
        {
            'age': 'asc'
        }
    ]
);

#####get() Get results from a table e.g.

db.get('customers');

#####update() Update the data in a table e.g.

db.update('customers');

#####insert() Insert data into a table e.g.

db.insert('customers');

#####delete() Delete table rows. Be sure to include a where statement first e.g.

db.where('customerId', 1);
db.delete('customers');