less-sql

A minimal DB layer based on MoSQL[https://github.com/goodybag/mongo-sql]. (postgresql only now)

Install

npm install less-sql --save

Usage

export DATABASE_URL=postgres://localhost/dbname

All functions return promises. Works best in combination with with co;

For more Query syntax, please check the MoSQL document Conditional Helpers[https://github.com/goodybag/mongo-sql/blob/master/docs/conditional-helpers.md]

var lessSQL = require('less-sql')

// Create Table
var table = "User"
var definition = {
  id: { type: 'serial', primaryKey: true },
  name: { type: 'text'}
}
yield lessSQL.createTableIfNotExists(table, definition)


// Drop Table 
yield lessSQL.dropTable(table)


// Create Index
yield lessSQL.createIndexIfNotExists(table, field)
yield lessSQL.createIndexIfNotExists(table, [field1,field2])

yield lessSQL.createIndexIfNotExists(table, [field1,field2], true) // unique
// or
yield lessSQL.createUniqueIndexIfNotExists(table, [field1,field2]) // unique index


// Find
// by query
yield lessSQL.find(table, {id: {$gt: 5}})
// return [{ id:6, name: "tim"}, {id:7}, name:"john"}]


// FindOne
yield lessSQL.find(table, {id: 5})
// return {id:5 , name: "Mary"}


// Count
yield lessSQL.count(table, {id: {$gt: 5}})
// return 10


// Insert
yield lessSQL.insert(table, {name:"bob"})
// return {id:10, name:"bob"}



// Update
yield lessSQL.update(table, {id: {$gt: 5}}, {name:"tim"})
// return [{ id:6, name: "tim"}, {id:7}, name:"tim"}]


// Remove
yield lessSQL.remove(table, {id: {$gt: 5}})
// return [{ id:6, name: "tim"}, {id:7}, name:"john"}]


// Transaction
// create a new transaction
var tx = yield lessSQL.transaction()

//do some works in the transanction , update , delete, ..
yield tx.update(table, {id: {$gt: 5}}, {name:"tim"})

// commit
yield tx.commit(client)

// or rollback
yield tx.rollback()

// warning: You should not use the transaction after it commit or rollback. The connection to postgres server of the transaction is closed after that.


// Json Query builder
var usersQuery = {
  type: 'select'
, table: 'users'
, where: { $or: { id: 5, name: 'Bob' } }
};

var result = yield lessSQL.sql(usersQuery);

result.toString() 
// select "users".* from "users" where "users.id" = $1 or "users"."name" = $2

result.values
// Array of values