osql v0.4.1
osql
A simple object based interface to SQL data sources. Maybe this will evolve into an ORM in the future, but probably not as there are some great ORMs for none-mssql databases available already, and I'm not going to be doing enough with mssql to justify creating one.
The other nice thing this exposes is a wrapper for restify, to enable you to create REST routes for your given tables.
This project is simply designed to make interaction with SQL database tables a little simpler, in a nice when
promise based library.
Currently supported drivers are:
- sqlite3
- mssql
The language syntax used in the "many" statements below, that is used to generate the sql required is https://github.com/hiddentao/squel.
Getting Started
Install the module with: npm install osql
and then one of the following drivers:
npm install sqlite3
npm install mssql
var osql = require('osql');
var db = new osql.Database('sqlite3', ':memory:');
or
var osql = require('osql');
var db = new osql.Database('mssql', {
user: 'example_user',
password: 'example_user_password',
server: '127.0.0.1',
database: 'example_database'
});
Examples
Once you've got you db object and driver, define a table and start manipulating it:
var table = new osql.Table({
table: 'example_table',
identity: 'id', // Optional
database: db
});
Inserting a Single Row, presuming the identity is auto generated
table.insert({
column1: 'value'
}).then(function(model) {
// model.id will be set to the inserted identity
});
Inserting a Single Row where you set the identity
table.insert({
id: id,
column1: 'value'
}).then(function(model) {
// done
});
Inserting a bunch of records, with a single insert statement
table.insert([
{ column1: 'val1'},
{ column1: 'val2'}
]).then(function() {
// done
});
Selecting a single row from the table
table.select(rowId)
.then(function(model) {
// done
});
Selecting mutliple rows from the table using squel syntax
table.selectMany()
.where('id > ?', 0)
.execute()
.then(function(models) {
// done
});
Updating a single row from the table
table.update(rowId, {
column1: 'updated value'
})
.then(function(model) {
// done
});
Updating mutliple rows from the table using squel syntax
table.updateMany({
column1: 'updated value'
})
.where('id > ?', 0)
.execute()
.then(function() {
// done
});
Deleting a single row from the table
table.delete(rowId)
.then(function() {
// done
});
Deleting mutliple rows from the table using squel syntax
table.deleteMany()
.where('id > ?', 0)
.execute()
.then(function() {
// done
});
REST
To make life even easier, you can wrap a restify server with a table object, which will in turn expose the rest routes.
NOTE: This is NOT a complete solution, i've done a quick pragmatic implementation to enable an on going project.
// Create your restify server
var server = require('restify').createServer({
name: 'osql-rest',
version: '1.0.0'
});
server.use(restify.acceptParser(server.acceptable));
server.use(restify.queryParser());
server.use(restify.bodyParser());
// Define your table
var table = new osql.Table({
table: 'example_table',
identity: 'id',
database: db
});
// Wrap the server
table.wrap('/api', server);
// Profit
server.listen(port, done);
From there, you will get a bunch of URLs exposed on the server:
POST /api/example_table // Create an entity
GET /api/example_table // Get all entities
GET /api/example_table/id // Get a single entity by id
GET /api/example_table/id/field // Get a field from a single entity by id
PUT /api/example_table/id // Update an entity by id
DELETE /api/example_table/id // Delete an entity by id
Have a look in test/rest.test.js to see how to query it using Restler
Contributing
In lieu of a formal styleguide, take care to maintain the existing coding style. Add unit tests for any new or changed functionality. Lint and test your code using Grunt.
Release History
- 0.1.0 Initial release
- 0.1.1-2 Bug fixes
- 0.1.3 Encapsulation bug fix, inserting of an object returns new instance of object with id
- 0.1.4 Bug fix
- 0.1.5 underlying db is now exposed via the dal
- 0.1.6 Will now escape single quotes by default
- 0.1.7 Concurrency fix for insert + get ID statements
- 0.1.8 Dependency Updates
- 0.1.9 Fixed concurrency issues on INSERT + get ID
- 0.2.0 Fixed concurrency issues on MSSQL driver, as well as fixing an issue around IDENTITY_INSERT
- 0.2.1 Updated MSSQL to 1.2.1
- 0.2.2 table.options.identity is no longer a required paramter as tables dont always have identities
- 0.2.4 Version Bump
- 0.2.5 Changed a console warn to a log, so as not to break jenkins pipelines
- 0.3.0 REST wrapping for Restify
- 0.3.1 Updated dependencies
- 0.3.2 Stored proc work
- 0.4.0 Added callback support as well as promises
- 0.4.1 Fixed an unhandled rejection
License
Copyright (c) 2014 Karl Stoney
Licensed under the MIT license.
9 years ago
9 years ago
9 years ago
9 years ago
9 years ago
9 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago