@firstfleet/ffsql

MSSQL Task and Promise Wrapper Library

Stats

stars 🌟issues ⚠️updated 🛠created 🐣size 🏋️‍♀️
@firstfleet/ffsql
Minified + gzip package size for @firstfleet/ffsql in KB

Readme

FFSQL

This is our libary that wraps database calls for MSSQL. We support both (task-based) and (promise-based) calls. Task calls still work, but should be considered deprecated.

It relies on four npm packages.

  1. mssql - used to make promise-based calls to SQL
  2. data.task - used to create a task oriented work flow
  3. ffErrorHandler - used for error handling and logging
  4. md5 - used to compare versions of the db config file (if you are using one for user and password)

Installation

npm install @firstfleet/ffsql --save

Configuration Variables

Process Environment Variables (process.env)

Key Description Default Value
PAPERTRAIL_PROGRAM App name, used in logging 'UNKNOWN'
PAPERTRAIL_HOST Host for syslog transport (using papertrail) undefined
PAPERTRAIL_PORT Port for syslog transport (using papertrail) undefined
NODE_ENV Node environment setting, used to control where we log, can see console logs if not in production undefined
DB_CONFIG_FILE File path to your db configuration file. This file (MAY) hold the DB_USER and DB_PASSWORD. If you do not include a filepath, the library will look for DB_USER and DB_PASSWORD in the environment variables, rather than the file. undefined
DB_SERVER Name of the sql server you wish to connect to undefined
DB_USER Username of sql account used to access the DB. Not needed if using DB_CONFIG_FILE. undefined
DB_PASSWORD Password of sql account used to access DB. Not needed if using DB_CONFIG_FILE. undefined
DB_DATABASE Default DB to use when executing procs or queries. undefined
DB_ENABLE_READ_ONLY Tells the library if you wish to try to use a read only connection when possible. If you wish to use this, see the Using Read Only seciton below false
DB_REQ_TIMEOUT Request max timeout in ms 180000
DB_POOL_MAX Max number of pools 50
DB_READ_ONLY_REFRESH_INTERVAL How often to refresh read only cache in ms 1000 * 60 * 5
APP_NAME Can be used as a fallback if PAPERTRAIL_PROGRAM is not present undefined
NODE_HEARTBEAT_INTERVAL_MINUTES How often to log a heartbeat to the DB in mins undefined
SlackPosterURL Slack webhook URL, will send errors here undefined

DB_CONFIG_FILE vs (DB_USER and DB_PASSWORD)

There are two ways you can feed the sql account username and password to this library. The first is to simply set DB_USER and DB_PASSWORD in your process.env variables.

The second is too instead, leave DB_USER and DB_PASSWORD undefined, and set DB_CONFIG_FILE instead. DB_CONFIG_FILE should be a file path to JSON file. Inside you should have a JSON object with two keys and values.

{
  "DB_USER": "SQLUsername",
  "DB_PASSWORD": "SQLPassword"
}

But why would you want to store them in a text file, and not just use them in the process.env? This is because in some use cases, you may want to be abel to have process automatically rotate the password, and have the app magically swap it out without a restart, or any intervention.

If you choose to use the DB_CONFIG_FILE, the library will automatically monitor the file for changes, and update the password for the sql connection if a change is detected.


Using Read Only

To use read only intents, and have the library automatically call with a read only intent, you need to create a procedure in your default database DB_DATABASE called RO_GetProcs. This procedure needs to return rows with only one column. That column must have a name of name and in that column should be your read only procedures.

Example of RO_GetProcs:


CREATE PROCEDURE [dbo].[RO_GetProcs] AS
SELECT LOWER(dbname + '.dbo.' + ProcName) name
FROM ReadOnlyProcs
WHERE Active = 1
    go

This will give you a list of proces you wish to be executed in a read only context, and the library will attempt any procs in that list as read only, and will fall back to the regular intent if it fails.


Promises

ExecMsProc (procedure, params, options)

Inputs
  • procedure: name of SQL stored procedure, can include full db path if not in default DB_DATABASE.
  • params: object containing key/value options that must match the parameters of the procedure
  • options
    • firstOnly - if set to true, the method will only return the first record found (instead of an array)
Special notes
  • If your query result is a single value, it will automatically be unpacked to a simple scaler value (instead of an array)
Examples:
const sql = require('@firstfleet/ffsql')
sql.ExecMsProc('ESig_GetFieldsToSave', {DocID: docId, SignSequence: signSequence}, {firstOnly: true})
sql.ExecMsProc('ESig_GetEmployeeDocs', {empId: empId})
sql.ExecMsProc('ESig_GetDocsMissingPDFData');

ExecMsQuery (queryText, params, options)

Inputs
  • queryText: Any SQL text. Param placeholders should be in the string prefixed with @ followed by an incrementing integer (similar to .NET PetaPoco syntax)
  • params: an array of parameters that will slide into the queryText placeholders
  • options
    • firstOnly - if set to true, the method will only return the first record found (instead of an array)
Special notes
  • If your query result is a single value, it will automatically be unpacked to a simple scaler value (instead of an array)
Examples:
const sql = require('@firstfleet/ffsql')
sql.ExecMsQuery(`select count(*) from ESigEmpDocs where EmpId = @0`, [empId], {firstOnly: true});
sql.ExecMsQuery('update ESigEmpDocs set SignDate = getdate(), SignStatus = @0 where Id = @1', [signStatus, empDocId])

ExecMsBulkInsert (dbName, tableName, columns, data, options)

Inputs

  • dbName: Name of the database where the table that you wish to insert into is located.
  • tableName: Name of the table you wish to insert into.
  • columns: array of column names (in order) that data should be inserted into.
  • data: Array of arrays, where each nested array is the values you want to insert, representing a row
  • options: {purgeTableFirst: true/false}. Flag to truncate the table before insert (true === truncate)

Example

router.post('/raddecs', (req, res) => {
    try {
        let data = req.body;
        if (data) {
            console.log(data);
            console.log(data.packets);
            if (data.packets && data.packets.length) {
                const payload = advlib.process(data.packets, PROCESSORS)
                // One "row" of data, where each key is column name
                const sqlRow = {
                    hexString: data.packets.join('|'),
                    macAddress: data.transmitterId,
                    beaconType: '',
                    txPower: _getTxPower(payload, data),
                    loggedAtUtc: moment.utc().format()
                }
                // Another "row" of data, again, each key is a column name
                const AnothersqlRow = {
                    hexString: data.packets.join('|'),
                    macAddress: data.transmitterId,
                    beaconType: '',
                    txPower: _getTxPower(payload, data),
                    loggedAtUtc: moment.utc().format()
                }
                sql.ExecMsBulkInsert('FFObjects', 'bleData', Object.keys(sqlRow), [sqlRow, AnothersqlRow])
                    .then(() => {
// End the request
                        res.end('post');
                    })
                    .catch(console.error);
            }
        }
    } catch (error) {
        console.error(error)
    }
})

createBulkTableObject (dbName, tableName, columns)

Inputs

  • dbName: Name of the database that holds the table that will be used for bulk insertion
  • tableName: Name of the table you are going to bulk insert into
  • columns: Array where each item in the array is a column name (in order) data should be inserted into

Example

// This creates a bulk table object we can reuse, rather than having to create it each time
bulkTableConfig = await sql.createBulkTableObject('FFObjects', 'PS_hosLogStaging',
    ['PSMessageId', 'LogId', 'LogGUID', 'DriverId', 'TractorId', 'Activity', 'StartTime', 'Duration', 'Edit', 'State', 'Odometer',
        'Distance', 'Location', 'EditReason', 'EventType', 'OriginalLogId', 'TrailerId', 'CoDriver', 'EventTimeTerminal', 'TimezoneTerminal', 'UTCOffset'])

// Now, you simply have to feed it the data
const logsToInsert = data.attributes.events
    .map(event => (
        {
            PSMessageId: data.data.id,
            LogId: event.id.toString(),
            LogGUID: event.guid,
            DriverId: data.user.external_id,
            TractorId: event.power_unit_number,
            Activity: event.duty_status ? event.duty_status.name : '',
            StartTime: time.formatUtc(event.event_timestamp_utc),
            Duration: event.duty_status ? event.duty_status.duration_seconds : 0,
            Edit: event.edit_count === 0 ? 0 : 1,
            State: event.location_state_code,
            Odometer: event.odometer_total_decimal,
            Distance: event.odometer_distance,
            Location: event.location_description,
            EditReason: event.edit_reason,
            EventType: HOSEventLookup.getHosEventType(event.event_type, event.event_code),
            OriginalLogId: event.origin_guid,
            TrailerId: data.attributes.trailer_numbers.length > 0 ? data.attributes.trailer_numbers.join(',') : undefined,
            CoDriver: data.attributes.co_drivers.length > 0 ? parseCoDrivers(data.attributes.co_drivers) : undefined,
            EventTimeTerminal: time.formatLocalTimestamp(event.event_timestamp_terminal),
            TimezoneTerminal: event.event_timezone,
            UTCOffset: event.event_utc_offset
        }));

await sql.ExecMsBulkInsert('', '', '', logsToInsert, {tableConfig: bulkTableConfig});

Tasks

Each of the exposed module methods return Task from data.task. You need to fork the task in order to get you Task.rejected(error) or your Task.of(data)

Require the module
cosnt sql = require('@firstfleet/ffsql')

ReturnSqlResults

sql.ReturnSqlResults(procedure, params)

Takes in a prodcedure name {string} and params {Object}. Returns a Task.rejected(error) || Task.of(data)

Example

sql.ReturnSqlResults("Database.dbo.getSqlRecords", {user: userId, option: 1})

ExecSql

sql.ExecSql(procedure, params)

Takes in a procedure name {string} and a params {Object}. Returns either a Task.rejected(error) || Task.of('Success') Used to execute a sql procedure when not expecting any data to return.

ExecDynamicSql

sql.ExecDynamicSql(procdedure || sql string, params)

Takes in either a sql procedure, or a string of sql text like "select * from table". Returns either a Task.rejected( error) || Task.of(data)

If you find any bugs or have a feature request, please open an issue on github!

The npm package download data comes from npm's download counts api and package details come from npms.io.