provide-mssql
v0.4.4
Published
Convenience layer for MSSQL/Tedious DB in Express app
Downloads
63
Readme
provide-mssql
JSON model layer for Microsoft SQL Server database (via Tedious) in Express apps.
BREAKING CHANGES in v0.4.0:
- model definitions and instantiation strategy
- query comparators are static members of MSSQL class
BREAKING CHANGES in v0.3.0:
params
passed to a string-literal query should be passed as an explicit array instead of as individual arguments (see below)- the (undocumented)
filter
functions are now expected to be an object rather than an array with a single (object) element.
Installation
npm i --save provide-mssql
Usage
Model (called from Express handler--for which consider provide-handler):
MSSQL = require 'provide-mssql'
class TicketModel extends MSSQL
table: # DB table definition
name: 'ticket' # table name in DB
primary_key: '_id' # optional but important for INSERT/UPDATE queries
schema: # whitelist of valid table columns (with datatypes)
_id: 'Int'
customer_id: 'VarChar'
received_date: 'Date'
hours: 'Decimal': precision: 4, scale: 2 # specify additional param options
# and now the 'vanilla' Promise-based model methods...
methodA: (args...) ->
i_return_a_normal_promise args...
# ...and model methods (under the 'generators' key) which should be treated as ES6 generator functions....
generators:
all: (filters...) ->
# JSON object will be built into SQL query...
params =
select:
customer: ['name: customer_name', 'region']
join: [
['@.customer_id', 'customer._id']
]
yield @request params
find_by_customer: (id) ->
params =
select: {}
join: [
['@.customer_id', 'customer._id']
]
where: [
['@.customer_id', MSSQL.eq id]
]
yield @request params
module.exports = new TicketModel
Note that methods defined under the generators
key are still mounted at the
root level of the model! So for the example above your handler should still call Ticket.find_by_customer
(not Ticket.generators.find_by_customer
).
Notice how we define a primary_key
in the schema--this is to exclude that
column (_id
in this example) from the fields affected by INSERT/UPDATE
queries, as we don't actually want to change that value.
Tedious config
Additional Tedious configuration options may be passed as the final (fifth)
argument to the model constructor. Including a pool
object will pass those
options to the ConnectionPool constructor:
options =
encrypt: false
requestTimeout: 20000
pool:
log: false
module.exports = new MyModel null, null, null, null, options
Query Builder
select:
table1: ['columnA', 'columnD']
table2: ['columnA: othername', 'columnB', 'columnC']
...becomes....
SELECT <base_table>.*, table1.columnA, table1.columnD, table2.columnA AS othername, table2.columnB, table2.columnC
A table name of @
will be replaced with the model's table.name
property (its
"base table").
An empty select
object will default to @.*
(all columns of model's base
table). Likewise if select
is defined but doesn't include the base table.
Essentially, if you don't specify columns from the model base table, you're
getting @.*
.
Defining a field as COLUMN: ALIAS
will use the AS
statement to assign an
alias to that column.
join: [
['@.other_id', 'othertable._id']
['RIGHT', 'othertable.third_id', 'thirdtable._id']
]
(FROM <base_table>)
LEFT JOIN othertable ON <base_table>.other_id = othertable._id
RIGHT JOIN thirdtable ON othertable.third_id = thirdtable._id
You can pass a JOIN
direction as an optional first array element in a join
item; default is LEFT
.
where: [
['@.some_id', MSSQL.eq 1234]
['table2.first_name', MSSQL.contains 'sephina']
]
WHERE <base_table>.some_id = 1234
AND table2.first_name LIKE '%sephina%'
where: [
['@.some_id', MSSQL.eq 1234]
['OR', 'table2.first_name', MSSQL.contains 'sephina']
]
WHERE <base_table>.some_id = 1234
OR table2.first_name LIKE '%sephina%'
You can pass 'OR' as an optional first array element in a where subclause;
default is AND
.
You can CAST columns in the where clause using MSSQL.cast(condition, type)
, e.g.:
where: [
['@.some_id', MSSQL.cast MSSQL.eq(1234), 'tinyint']
]
WHERE CAST(<base_table>.some_id AS tinyint) = 1234
Here are the available comparator functions:
contains: LIKE '%___%'
ends_with: LIKE '%___'
eq: =
gt: >
gte: >=
in: IN (<array>)
is_not_null: IS NOT NULL
is_null: IS NULL
lt: <
lte: <=
neq: <>
nin: NOT IN (<array>)
starts_with: LIKE '___%'
See test/mssql.coffee
for more query examples. If you run into a query that
won't build properly, just pass it as a string (with optional params):
method_with_challenging_query: (item_id, other_id) =>
query = """SELECT *
FROM table1
LEFT JOIN table2 ON table1.field1 = table2.field1
AND table1.field2 = table2.field2
WHERE table1.id = @id
AND table2.other_id = @other"""
# version 0.3.x param syntax (see note below)
yield @request query, [
@build_param('id', 'Int', item_id),
@build_param('other', 'Int', other_id)
]
NOTE: in versions < 0.3.x, params
for string-literal queries should be passed
as individual arguments, not as an array.
Transactions
You may pass an array of query objects to perform as a transaction:
q1 =
update: { success: 1 }
where: [
['id', MSSQL.eq item_id]
]
q2 =
select: {}
where: [
['@.item_id', MSSQL.eq item_id]
]
yield @transaction [{ name: 'some_query', query: q1 }, { name: 'another_query', query: q2 }]
...returned result in such a case would look like:
{
some_query: [results of q1]
another_query: [results of q2]
}
Query results will be "named" with the array index they are passed in with if no
name
property is provided.
You may also pass string-literal queries like:
q1 = "SELECT * FROM items WHERE id = @id AND field2 = @other"
p1 = [@build_params('id', 'Int', item_id), @build_params('other', 'Int', other_id)]
yield @transaction [{ name: 'put', query: q1, params: p1 }, ...]
Note in that case that the params
property for each query should be an array.
You can also perform transactions as a series of requests in your model:
tx = yield @start_transaction() # start transaction
yield @some_write_query tx
item = yield Item.get id, tx
unless item?
return tx.done 'No such item' # roll back transaction
another_query =
select: {}
where: [
['@.item_id', MSSQL.eq item.id]
]
yield @request another_query, null, tx # pass explicit null PARAMS arg
tx.done() # commit transaction
Note that you must pass the transaction (the return value of
start_transaction()
) to each request so the handler knows to use that rather
than acquiring a new pool
connection. Neglecting to do this may cause the
request to hang, which is bad because transactions place a write-lock on the DB.
This means no other requests can be processed while the transaction is open!
It's also important not to forget calling transaction.done()
when you're
finished (to commit), or transaction.done(err)
(to roll back) if you're
handing errors in your model method (errors thrown in the request
plumbing are
already handled this way).
Testing
npm test