Database wrapper for MySql
#### 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 bigg-mysql-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 'bigg-mysql-db-wrapper'
import { connections } from '../config/dbConfig.js'
const db = new sqlBuilder();
#### Useage
##### Selecting records
db.conn(connections.connection1);
db.where('Foo', 'Bar');
db.limit(1);
db.order_by('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(String _field_, String _condition_)
Set a where statement. Can be used multiple times e.g.
db.where('first_name', 'joe');
db.where('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(String _tablename_, String _joinCondition_, String _type_)
Join another table. Defaults to left outer join if join type not specified e.g.
db.join('orders', 'orders.customerId = customers.Id', 'left');
#####limit()
Limit the results of a select statement e.g.
db.limit(10);
#####group_by()
Group the results e.g.
db.group_by('firstName');
#####order_by()
Order the results of a select statement String fieldName e.g.
db.order_by('first_name', 'desc');
#####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');