DBConnection Namespace Reference

wilton/DBConnection
Connect to relational databases. More...

Functions
Undefined	close (Function|Undefined callback)
	Close database connection.
Object	DBConnection (String url, Function|Undefined callback)
	Open connection to database.
Any	doInSyncTransaction (String lockChannelName, Function operations, Function|Undefined callback)
	Perform a set of DB operations inside the synchronized transaction.
Any	doInTransaction (Function operations, Function|Undefined callback)
	Perform a set of DB operations inside the transaction.
Undefined	execute (String sql, Object|Undefined params, Function|Undefined callback)
	Execute DML (insert or update) or DDL (create or drop) query.
Number	executeFile (String filePath, Function|Undefined callback)
	Execute all queries from file.
Number	executeModuleResource (String moduleId, Function|Undefined callback)
	Execute all queries from RequireJS module.
static Object	loadQueryFile (String path, Function|Undefined callback)
	Load queries from an SQL file.
static Object	loadQueryModuleResource (String moduleId, Function|Undefined callback)
	Load queries from an SQL module.
Array	queryList (String sql, Object|Undefined params, Function|Undefined callback)
	Execute select query returning result as a list.
Object	queryObject (String sql, Object|Undefined params, Function|Undefined callback)
	Execute select query returning result as an object.

Detailed Description

This module allows to work with relational databases.

It implements a lightweight ORM - object-relational mapping, allows to map JavaScript objects to query parameters and to map query results to JavaScript objects.

SQLite and PostgreSQL databases are supported out of the box.

Support for Oracle, MSSQL (through ODBC), MySQL and Firebird can be added in custom builds.

DB connection can be closed manually to release system resource, otherwise it will be closed during the shutdown.

Usage example:

// open connection
var conn = new DBConnection("postgresql://host=127.0.0.1 port=5432 dbname=test user=test password=test");

// execute DDL
conn.execute("create table t1 (foo varchar, bar int)");

// execute DQL
var res = conn.query("select foo, bar from t1 where foo = :foo or bar = :bar order by bar", {
    foo: "ccc",
    bar: 42
});

// execute DML in transaction
conn.doInTransaction(function() {
    conn.execute("insert into t1 values(:foo, :bar)", {
        foo: "bbb",
        bar: 42
    });
});

// close connection
conn.close();

Function Documentation

Undefined DBConnection::close

(

Function|Undefined

callback

)

Closes DB connection releasing system resources. Connections left open will be closed on shutdown.

Parameters

callback

Function|Undefined callback to receive result or error

Returns

Undefined

Object DBConnection::DBConnection	(	String	url,
		Function|Undefined	callback
	)

Opens connection to database.

Parameters

url	String backend-specific connection URL, postgres example: postgresql://host=127.0.0.1 port=5432 dbname=test user=test password=test, sqlite example: sqlite://test.db
callback	Function|Undefined callback to receive result or error

Returns

Object DBConnection instance

Any DBConnection::doInSyncTransaction	(	String	lockChannelName,
		Function	operations,
		Function|Undefined	callback
	)

This method runs specified operations inside the transaactional block (using doInTransaction) additionally wrapping it with synchroinized block (using Channel.synchronize()).

Parameters

lockChannelName	String name of the channel to use for synchronization it must be existing empty channel with maxSize = 1
operations	Function function performing DB operations
callback	Function|Undefined callback to receive result or error

Returns

Any value returned by operations function

Any DBConnection::doInTransaction	(	Function	operations,
		Function|Undefined	callback
	)

Performs a set of DB operations inside the transaction. Trnsaction will be committed on success and rolled back on error.

Parameters

operations	Function function performing DB operations
callback	Function|Undefined callback to receive result or error

Returns

Any value returned by operations function

Undefined DBConnection::execute	(	String	sql,
		Object|Undefined	params,
		Function|Undefined	callback
	)

Executes DML (insert or update) or DDL (create or drop) query

Parameters

sql	String SQL query
params	Object|Undefined query parameters object
callback	Function|Undefined callback to receive result or error

Returns

Undefined

Number DBConnection::executeFile	(	String	filePath,
		Function|Undefined	callback
	)

Queries are parsed from file splitting it by ; symbols and then executed one by one.

Comment-only lines are ignored;

Parameters

filePath	String path to SQL file
callback	Function|Undefined callback to receive result or error

Returns

Number number of queries executed

Number DBConnection::executeModuleResource	(	String	moduleId,
		Function|Undefined	callback
	)

Queries are parsed from module splitting it by ; symbols and then executed one by one.

Comment-only lines are ignored;

Parameters

moduleId	String module ID that points to SQL file
callback	Function|Undefined callback to receive result or error

Returns

Number number of queries executed

static Object DBConnection::loadQueryFile ( String  path, Function|Undefined  callback  )

static

Parses a file with SQL queries as query_name: sql object.

Each query must start with /(STAR)(STAR) myQuery (STAR)/ header.

Lines with comments are preserved, empty lines are ignored.

Parameters

path	String path to file with queries
callback	Function|Undefined callback to receive result or error

Returns

Object loaded queries.

static Object DBConnection::loadQueryModuleResource ( String  moduleId, Function|Undefined  callback  )

static

Parses a module with SQL queries as query_name: sql object.

Each query must start with /(STAR)(STAR) myQuery (STAR)/ header.

Lines with comments are preserved, empty lines are ignored.

Parameters

moduleId	String module ID that points to file with queries
callback	Function|Undefined callback to receive result or error

Returns

Object loaded queries.

Array DBConnection::queryList	(	String	sql,
		Object|Undefined	params,
		Function|Undefined	callback
	)

Executes select query and returns its result as a list.

Parameters

sql	String SQL query
params	Object|Undefined query parameters object
callback	Function|Undefined callback to receive result or error

Returns

Array list of objects, one object per returned row

Object DBConnection::queryObject	(	String	sql,
		Object|Undefined	params,
		Function|Undefined	callback
	)

Executes select query and returns its result as an object.

Query must return no more than one row, Error will be thrown, if more rows returned.

Parameters

sql	String SQL query
params	Object|Undefined query parameters object
callback	Function|Undefined callback to receive result or error

Returns

Object object converted from the single row returned, or null if no rows returned