Share your code. npm Orgs help your team discover, share, and reuse code. Create a free org »

    sqlmwpublic

    Middleware Framework for SQL

    Description

    This is a simple framework to combine multiple async SQL operations into usable callback functions.

    Installation for Node.js

    Simplest way to install is to use npm, just simply npm install sqlmw.

    License

    MIT-style license, see INSTALL.txt.

    Backends

    Examples for MySQL

    Initializing the sql object for MySQL:

    var config = {'host': 'localhost', 'user': 'user', 'password': 'hello', 'database': 'dbname'},
        sql = require('sqlmw')('mysql', config);
    

    You can create saved callback functions like this:

    var delete_player = sql.query('DELETE FROM player WHERE game_id=:game_id AND number=:number LIMIT 1');
    

    And use delete_player like this:

    var connect = sql.connect();
    connect(function(err) {
    	if(err) {
    		console.log('Failed to connect: ' + err);
    	} else {
    		delete_player({'game_id':1,'number':1}, function(err) {
    			if(err) console.log('Failed to add player: ' + err);
    			else console.log('Successfully added player');
    		});
    	}
    });
    

    Middleware sql.connect() can be executed more than once. It creates new connections only if connection is disconnected.

    You can also group multiple middlewares into one single callback function this way:

    var insert_player = sql.group(
    	sql.connect(),
    	sql.query('SELECT COUNT(number)+1 AS number FROM player WHERE game_id=:game_id'),
    	sql.query('INSERT INTO player (number, game_id) VALUES (:number, :game_id)'),
    	sql.disconnect()
    );
    

    And use insert_player simply like this:

    insert_player({'game_id':1}, function(err) {
    	if(err) console.log('Failed to add player: ' + err);
    	else console.log('Successfully added player');
    });
    

    You can also use groups like insert_player as a middleware when grouping:

    var useless_operation = sql.group(insert_player, delete_player);
    useless_operation(function(err) {
    	if(err) console.log('Failed: ' + err);
    	else console.log('Successfully added AND removed a player');
    });
    

    Examples for PostgreSQL

    Initializing the sql object for PostgreSQL:

    var config = 'tcp://postgres:1234@localhost/postgres',
        sql = require('sqlmw')('pg', config);
    

    Interface for sqlmw with PostgreSQL is the same as with MySQL except obviously actual query strings might not be always compatible for both backends.

    See more from unit tests and examples/.

    Calling middlewares

    Every returned callable middleware works the same way and can be called in the following standard ways.

    With options:

    var fn = sql.query('INSERT INTO table (a,b) VALUES (:a, :b)');
    fn({'a':1, 'b':2}, function(err) {
    	if(err) console.log('Error: ' + err);
    });
    

    Without options:

    var fn = sql.query('DELETE FROM table');
    fn(function(err) {
    	if(err) console.log('Error: ' + err);
    });
    

    Without user defined callback (errors are printed to stderr):

    var fn = sql.query('DELETE FROM table');
    fn();
    

    With options but without user defined callback (errors are printed to stderr):

    var fn = sql.query('DELETE FROM table WHERE id = :id');
    fn({'id':2});
    

    Built-in Middlewares

    sql.connect()

    Returns callable middleware to connect our backend to the server if disconnected.

    sql.disconnect()

    Returns callable middleware to disconnect our backend from the server.

    sql.query(str)

    Returns callable middleware for generic SQL query.

    sql.group(a[, b[, ...]])

    Returns set of middlewares grouped as one callable middleware. You can group other groups, too.

    sql.assign(key, value)

    Returns middleware to assign key in the current state object as value.

    User-defined Middlewares

    Create a file named ourMiddleware.js:

    module.exports = function(sql) {
    	sql.insertArticle = function() {
    		var sql = this,
    		    insert = sql.query('INSERT INTO article (title, text, created) VALUES (:title, :text, :created)')
    		return function(options, next) {
    			insert({'title':'Hello world', 'text':'This is a test article.', 'created':new Date()}, next);
    		};
    	};
    };
    

    And use it like this:

    var ourMiddleware = require('./ourMiddleware.js');
    sql.use(ourMiddleware);
    
    var cb = sql.insertArticle();
    cb(function(err, state) {
    	if(err) console.log('Failed to insert row: ' + err);
    });
    

    sql object members

    sql.debug

    Debug flag. If set to true middlewares are allowed to output debug messages with console.log().

    sql.use(mw [, ...])

    Loads new user-defined middlewares.

    sql.backend

    Current backend object in use.

    sql.backend.type

    Returns backend type as String:

    • pg for PostgreSQL
    • mysql for MySQL

    Middlewares in TODO

    These middlewares are NOT IMPLEMENTED but might be in the future. You can also implement your own middlewares.

    sql.create(name)

    Returns middleware to create a new database. This should be portable for all backends.

    sql.insert(table)

    Returns middleware to insert values to a table. This should be portable for all backends.

    sql.del(table)

    Returns middleware to remove rows from a table. This should be portable for all backends.

    sql.update(table)

    Returns middleware to update row(s) in table. This should be portable for all backends.

    Running lint and unit tests

    To run our lint test just execute command ./run-lint.sh:

    + node-lint --no-colors --config=lint.json lib/
    ✓ Valid » 7 files ∙ 0 error
    

    To run our nodeunit tests just execute command npm test:

    > sqlmw@0.1.1 test /home/users/jhh/git/node-sqlmw
    > ./run-test.sh
    
    + node test/run.js
    
    test-mysql.js
    ✔ backend
    ✔ query_insert_with_callback
    ✔ query_select_with_empty_options_callback
    ✔ query_select_without_options_with_callback
    ✔ query_select_single_with_options_callback
    ✔ query_delete
    ✔ query_update
    ✔ query_delete_with_options_without_callback
    ✔ query_delete_without_options_callback
    
    test-pg.js
    ✔ backend
    ✔ query_insert_with_options_callback
    ✔ query_select_with_emptyoptions_callback
    ✔ query_select_single_with_options_callback
    ✔ query_delete_with_options_callback
    ✔ query_update_with_options_callback
    ✔ query_select_without_options_with_callback
    ✔ query_select_single_without_options_callback
    
    OK: 305 assertions (901ms)
    

    Keywords

    none

    install

    npm i sqlmw

    Downloadsweekly downloads

    17

    version

    0.1.3

    license

    none

    repository

    githubgithub

    last publish

    collaborators

    • avatar