SQLite
DESCRIPTION
General
Installation
npm install eden-sqlite
Usage
var sqlite = require('eden-sqlite');
var database = sqlite(':memory:')
Methods
collection
eden/sqlite/collection collection(String);
Returns collection
Parameters
- string
Returns
eden/sqlite/collection
Example
Code
database.collection('user')
.add({})
.add({})
.setFoo('bar')
.setUserName('Bobby2')
.setUserEmail('bobby2@gmail.com')
.setUserFacebook(123);
collection[0].foo;
collection[1].user_email;
Outputs
'bar'
'bobby2@gmail.com'
connect
this connect(Array);
Connects to the database
Parameters
- array - the connection options
Returns
this
Example
Code
sqlite().connect();
Outputs
RESULTS
getConnection
connection resource getConnection(Array);
Returns the connection object if no connection has been made it will attempt to make it
Parameters
- array - connection options
Returns
connection resource
Example
Code
var database = sqlite(':memory:');
database.getConnection();
Outputs
null
getColumns
this getColumns(String, Function);
Returns the columns and attributes given the table name
Parameters
-
string - the name of the table
-
function - callback
Returns
this
Example
Code
database.getColumns('user', function(error, row));
typeof columns === 'string';
Outputs
false
getRow
this getRow(String, String, String, Function);
Returns a row result given the column name and the value
Parameters
-
string - table
-
string - name
-
string - value
-
function - callback
Returns
this
Example
Code
database.getRow('user', 'user_email', 'bob@gmail.com', function(error, row));
row.user_name;
database.getRow('user', 'user_email', 'dayle@gmail.com', function(error, row));
row;
Outputs
'bobby'
null
insert
eden/sqlite/insert insert(String);
Returns the insert query builder
Parameters
- string
Returns
eden/sqlite/insert
Example
Code
var query = database.insert('user')
.set('user_name', 'chris')
.set('user_age', 21)
.getQuery();
query = database.insert('user')
.set({user_name: 'chris', user_age: 21})
.getQuery();
query = database.insert('user')
.set('user_name', 'chris', 0)
.set('user_age', 21, 0)
.set('user_name, '?', 1)
.set('user_age', 22, 1)
.getQuery();
query = database.insert('user')
.set({user_name: 'chris', user_age: 21})
.set({user_name: 'dan', user_age:22}, 1)
.getQuery();
Outputs
'INSERT INTO user (user_name, user_age) VALUES (\'chris\', 21);'
'INSERT INTO user (user_name, user_age) VALUES (\'chris\', 21);'
'INSERT INTO user (user_name, user_age) VALUES (\'chris\', 21), (?, 22);'
'INSERT INTO user (user_name, user_age) VALUES (\'chris\', 21), (\'dan\', 22);'
insertRow
this insertRow(String, Object, Array|bool|null, Function);
Inserts data into a table and returns the ID
Parameters
-
string - table
-
object - setting
-
array|bool|null
-
function - callback
Returns
this
Example
Code
database.insertRow('user', {
user_name: 'bob',
user_email: 'bob@gmail.com',
uer_facebook: 123 }, function(error, rows, meta));
meta.lastID > 1;
Outputs
true
insertRows
this insertRows(String, Object, Array|bool|null, Function);
Inserts multiple rows into a table
Parameters
-
string - table
-
object - settings
-
array|bool|null
-
function - callback
Returns
this
Example
Code
database.insertRows('user', [{
user_name: 'bob',
user_email: 'bob@gmail.com',
user_facebook: 123
}, {
user_name: 'chris',
user_email: 'bob@gmail.com',
user_facebook: 312 }]);
meta.lastID > 1;
Outputs
true
model
eden/sqlite/search model(String);
Returns model
Parameters
- string
Returns
eden/sqlite/search
Example
Code
database.search('post')
.innerJoinOn('user', 'post_user=user_id')
.filterByUserName('Christian Blanquera')
.sortByUserId('DESC')
.getModel(function(error, model, meta) });
model.getUserName();
Outputs
'Christian Blanquera'
query
this query(String, Array, Function);
Queries the database
Parameters
-
string - query
-
array - binded value
-
function - callback
Returns
this
Example
Code
database.query('SELECT * FROM user', function(error, rows));
rows.length > 0;
Outputs
true
remove
eden/sqlite/delete remove(String);
Returns the delete query builder
Parameters
- string
Returns
eden/sqlite/delete
Example
Code
var query = database
.remove('user')
.where('user_id = ?')
.getQuery();
query = database
.remove('user')
.where(['user_id = ?', 'user_name = ?'])
.getQuery();
query = database
.remove('user')
.where('user_id = ?')
.where('user_name = ?')
.getQuery();
Outputs
'DELETE FROM user WHERE user_id = ?'
'DELETE FROM user WHERE user_id = ? AND user_name = ?;'
'DELETE FROM user WHERE user_id = ? AND user_name = ?;'
removeRows
this removeRows(String, Array, Function);
Removes rows that match a filter
Parameters
-
string - table
-
array - filter
-
function - callback
Returns
this
Example
Code
database.removeRows('user',
[['user_email = ?', 'bob@gmail.com']],
function(error, row);
typeof row === 'object';
Outputs
true
select
eden/sqlite/select select(String);
Returns the select query builder
Parameters
- string
Returns
eden/sqlite/select
Example
Code
var query = database.select('*')
.from('user')
.innerJoin('post', 'post_user=user_id', false)
.where('user_name = ?')
.sortby('user_name')
.groupBy('user_id')
.limit(1, 2)
.getQuery();
query = database.select('*')
.from('user')
.leftJoin('post', 'post_user=user_id', false)
.where('user_name = ?')
.sortBy('user_name', 'DESC')
.groupBy('user_id')
.limit(1, 2)
.getQuery();
query = database.select('*')
.from('user')
.rightJoin('post', 'post_user=user_id', false)
.where('user_name = ?')
.sortBy('user_name')
.getQuery();
query = database.select('*')
.from('user')
.outerJoin('post', 'post_user')
.where('user_name = ?')
.groupBy('user_id')
.limit(1, 2)
.getQuery();
Outputs
'SELECT * FROM user INNER JOIN post ON (post_user=user_id) '
+ 'WHERE user_name = ? GROUP BY user_id ORDER BY user_name ASC LIMIT 1, 2;'
'SELECT * FROM user LEFT JOIN post ON (post_user=user_id) '
+ 'WHERE user_name = ? GROUP BY user_id ORDER BY user_name DESC LIMIT 1, 2;'
'SELECT * FROM user RIGHT JOIN post ON (post_user=user_id) '
+ 'WHERE user_name = ? ORDER BY user_name ASC;'
'SELECT * FROM user OUTER JOIN post USING (post_user) '
+ 'WHERE user_name = ? GROUP BY user_id LIMIT 1, 2;'
search
eden/sqlite/search search();
Returns search
Parameters
Returns
eden/sqlite/search
Example
Code
database.search('user').getRows(function(error, rows));
typeof rows.length;
database.search('user')
.addFilter('user_name = ?', 'Christian Blanquera')
.getRow(function(error, row, meta));
row.user_email;
Outputs
'number'
'cblanquera@gmail.com'
setRow
this setRow(String, String, String, Object, Function);
Sets only 1 row given the column name and the value
Parameters
-
string - table
-
string - name
-
string - value
-
object - setting
-
function - callback
Returns
this
Example
Code
database.setRow('user', 'user_name', 'bob', {
user_email: 'bob@gmail.com'
}, function(error, row));
row.user_email;
Outputs
'bob@gmail.com'
update
eden/sqlite/update update(String);
Returns the update query builder
Parameters
- string
Returns
eden/sqlite/update
Example
Code
var query = database.update('user')
.set('user_name', 'chris')
.set('user_age', 21)
.where('user_id = ?')
.where('user_name = ?')
.getQuery();
query = database.update('user')
.set({user_name: 'chris', user_age: 21})
.where(['user_id = ?', 'user_name = ?'])
.getQuery();
Outputs
'UPDATE user SET user_name = \'chris\', user_age = 21 WHERE user_id = ? AND user_name = ?;'
'UPDATE user SET user_name = \'chris\', user_age = 21 WHERE user_id = ? AND user_name = ?;'
updateRows
this updateRows(String, Object, Array, Array|bool|null, Function);
Updates rows that match a filter given the update settings
Parameters
-
string - table
-
object - setting
-
array - filter
-
array|bool|null
-
function - callback
Returns
this
Example
Code
database.updateRows('user', {
user_name: 'bobby'
} [['user_email = ?', 'bob@gmail.com']], function(error, row));
typeof row === 'object';
Outputs
true