sqlcmd

Coarse-grained partially composable SQL.

npm install --save sqlcmd

Basic queries

import {Connection} from 'sqlcmd'
const db = new Connection()

Select(), Insert(), Update(), and Delete() are all functions of db that take a single argument, the name of the table they are operating on, and return a different type of query object.

The examples below assume a connection is available somewhere, which is not shown. See sqlcmd-pg for a complete example.

I've elided error handling below, for concision, but in practice that's a recipe for disaster, or at least a really awful debugging experience.

var user_name = 'Chris'

db.Select('friendships')
.where('source = ? OR target = ?', user_name, user_name)
.execute((err, friendships) => {
  console.log("All of Chris's connections: %j", friendships)
})

db.Insert('friendships')
.set({source: 'Chris', target: 'Jess'})
.returning('*')
.execute((err, rows) => {
  // we've asked for "RETURNING *", so we get back a list of one row.
  console.log('inserted row: %j', rows[0])
})

TODO: more documentation. (When is that not a TODO?)

Ideas

Every SQL command is an instance of a general SQL Command class, and can be modified (mutated) or built upon to create new instances. SQL syntax is regular, but we want to be able to build commands (especially queries) incrementally.

Most SQL databases loosely adhere to a standard, but every implementation has extensions or flexibility for better compatibility. This library does not make provisions for different implementations. Instead, it is string-based, so if you use it to create a table, you need to know what types and constraints your database implementation supports.

Commands

SQL parameterization embeds references to external objects in plain strings. Strings are just arrays of characters, so a parameterized string is just an array of strings interspersed with parameter objects. We could model those sequences as an array of stuff like "SqlString" and "SqlParameter" instances, but since pg and sqlite3 both distinguish between the query string and a parameters object/array when performing queries, we'll use a similar model. When

sqlcmd comes with several built-in commands. These classes have both mutable and immutable interfaces. The mutable interface uses method names prefixed by _. These methods modify the command instance's statement and/or parameters and return this, so that they're chainable. The non-prefixed methods clone the command instance first, and then perform the mutable command on the newly created copy, returning the copy and leaving the original command untouched. Every command must also implement the Command#toSQL(), which takes no arguments and returns a single string. It does not modify the command.

License

Copyright 2014-2015 Christopher Brown. MIT Licensed.

3.0.1 (2018-03-05)

• Replace Object#hasOwnProperty invocation with Object.prototype.hasOwnProperty call.

2.0.1 (2015-12-05)

• Merge testDependencies into devDependencies so that mocha actually gets installed.

2.0.0 (2015-12-05)

• Add UpdateOne command.
• Rewrite in TypeScript.
• Remove singleton module/Connection polymorphism.
  • Replace var db = require('sqlcmd'); with import {Connection} from 'sqlcmd'; var db = new Connection();

1.3.0 (2015-11-30)

• Remove util-enhanced dependency.
• Refactor executePatches functionality to sql-patch.

1.2.1 (2015-06-27)

• Reduce util-enhanced usage.

1.2.0 (2015-06-27)

• Add InsertOne command, exactly like Insert, but calls the callback with the first row if there are result rows.

1.1.1 (2015-06-25)

• Export SelectOne command.

1.1.0 (2015-06-25)

• Add SelectOne command, similar to Select, but automatically adds a LIMIT 1, and calls the callback with a single row.

1.0.6 (2015-01-17)

• Throw a more descriptive error when Command#interpolateQuestionMarks(string, args) is called improperly.
• Bump util-enhanced version to 0.1.2, which fixes a bug that involved cloning null as {}.

1.0.5 (2015-01-07)

• Calling Database#all(sql_string, null, callback) would fail in SQLite when called in the Connection#executePatches(...) method. Using [] to denote empty parameters instead of null when calling Connection#executeSQL(sql_string, [], callback) solves this problem in SQLite as well as being cross-compatible with PostgreSQL.

1.0.4 (2015-01-07)

• Remove overloaded function call Connection#executeSQL(sql_string, callback), so that only the parameterized version is allowed.

1.0.3 (2015-01-02)

• Add missing error-handling conditional in the Connection#executeSQL(...) callback inside the Connection#executePatches(...) method.

1.0.2 (2015-01-02)

• SQLite does not support RETURNING * SQL syntax in INSERT and UPDATE queries. This patch removes the RETURNING * default in the db.Insert(...) and db.Update(...) commands, and adds Insert#returning(...columns) and Update#returning(...columns) commands (which are also overloaded as Insert#returning(columns) and Update#returning(columns)).

1.0.1 (2015-01-02)

• Add CreateTable#ifNotExists() method.
• Make Connection inherit from EventEmitter (for logging purposes).

1.0.0 (2015-01-02)

• Refactor PostgreSQL-dependent functionality into the sqlcmd-pg library, allowing the sqlcmd query construction logic to be used across various SQL database engines, such as SQLite and raw strings.
• Restructure Command classes to store all of the query's structural data in a statement property, enabling simpler and more concise cloning when using the immutable modality.
• Rename Update#set(mapping) to Update#setEqual(mapping) and add a new Update#set(sql, args) command, similar to Select#where(sql, args), since SQL UPDATE queries are not restricted to key-value settings.