Database and REST (dare)

Dare is an API for generating SQL, it can be used internally to build and execute SQL. As well as lathered with request handlers for layering per table rules and security, just the thing for maintaining data integrity and developing REST interfaces.

Install

npm i dare --save

Example usage...

This is a simple setup to get started with, it'll make a basic SELECT query.

// Require the module
const Dare = require('dare');

// Initiate it
const dare = new Dare();

// Define a module for connecting
dare.execute = (sql, callback) => {
    // Connect to DB, and execute the `sql`,
    // Execute `callback(errorResponse, successResponse)`;
};

// Make a request
dare.get('users', ['name'], {id: 1}).then((resp) => {

    // This would have run...
    // SELECT id, name FROM users WHERE id = 1 LIMIT 1;
    // And returned a single record as the response...

    console.log(`Hi ${resp.name}');
});

Setup

dare = new Dare(options)

Create an instance of Dare with some options

const options = {
    schema
}
const dare = new Dare(options);

Options

The options Object is a set of properties to apply at the point of calling any methods. Initially it's used to define default properties. However every method creates its own instance inheritting its parent options as well as defining it's own. See dare.use(options) for more.

The options themselves are a set of properties used to interpret and manipulate the request.

The basic options are:

• schema

Additional Options include:

• table_alias
• table_conditions

Schema

The schema is used to define the structure of your SQL database. You can refer to it as options.schema. It's each property in the schema pertains to a database table. And defines the fields within the table.

e.g.

{
    users: {Field Definition's,...},
    country: {Field Definition's,...}
}

Relationships

In the example below the fields users.country_id defines a relationship with country.id which is used to construct SQL JOIN Conditions.

    ...
    schema : {
        users: {
            // table columns
            country_id: 'country.id'
        },
        country: {...}
    }
    ...

Field Definition

Fields dont need to be explicitly defined in the options.schema.*tbl*. Fields which are defined can give hints as to how to handle them.

field reference

Fields can reference other table fields, this is used to construct relationships as we've seen earlier.

field type

Defining the type introduces additional features.

currently this is limited to datetime

E.g. in the example the type is defined as 'datetime', a conditional filter short hand for created_time: 2017 would be expanded too `created_time BETWEEN '2017-01-01T00:00:00' AND '2017-12-31T23:59:59'

    ...
    schema : {
        users: {
            created_time: {
                type: 'datetime'
            }
        },
    ...

field handler

When the value is a function, the function will be invoked when interpretting the request as part of a field value. The response of this function can either be a static value or it can be an additional function which is optionally run on all items in the response, to return a generated field.

E.g.

This will manipulate the request and response to create the property avatar_url on the fly.

    ...
    schema : {
        users: {
            avatar_url(fields) {

                fields.push('id'); // require additional field from users table.

                return (item) => `/images/avatars/${item.id}`;
            }
        },
    ...

Methods

dare.get(table[, fields][, filter][, options])

The dare.get method is used to build and execute a SELECT ... SQL statement.

e.g.

dare.get('table', ['name'], {id: 1});
// SELECT name FROM table WHERE id = 1 LIMIT 1;

dare.get(options Object)

Alternatively a options Object can be used instead.

e.g.

dare.get({
    table: 'users',
    fields: ['name'],
    filter: {
        id: 1
    }
});

Fields Array

The fields array is defined in dare.get(...[,fields]...) only and says what fields from the matching resultset to return.

Items (strings)

In its simplest form it is an Array of Strings, e.g. ['id', 'name', 'created_date']. This creates a very simple query.

SELECT id, name, created_date FROM ....

The array items can also be Objects.

Aliased Items (objects)

Object entries whose value are strings, may define SQL functions. E.g.

    [
      'name',
      {
      	'_date': 'DATE(created_date)'
      }
    ]

    // sql: SELECT name, DATE(created_date) AS _date ...

Nesting Fields

Objects entries which have Objects as value. In this case they shall attempt to get data from accross multiple tables.

    [
        'name',
        'country': {
            'name'
        }
    ]

    // sql: SELECT [users.]name, county.name

The SQL this creates renames the fields and then recreates the structured format that was requested. So with the above request: a typical response would have the following structure...

    {
        name: 'Andrew',
        country: {
            name: 'UK'
        }
    }

• At the moment this only supports n:1 mapping.
• The relationship between the tables must be defined in the scheme.

Filter

The Filter Object is a Fields=>Value object literal, defining the SQL condition to attach to a statement.

e.g.

    {
        id: 1,
        is_hidden: 0
    }


    // ... WHERE id = 1 AND is_hidden = 0 ...

The filter object can contain nested objects (Similar too the Fields Object). Nested objects define conditions on Relational tables.

    {
        country: {
            name: 'UK'
        }
    }

Creates the following SQL JOIN Condition

    ... WHERE country.name = 'UK' ...

Filter Syntax

The type of value affects the choice of SQL Condition syntax to use. For instance an array will create an IN (...) condition, the presence of % will create a LIKE condition. If the property name is prefixed with a hyhen it will negate the filter. See examples below...

Join

The Join Object is a Fields=>Value object literal. It accepts similar syntax to the Filter Object, and defines those conditions on the SQL JOIN Condition.

e.g.

    join: {
        county: {
            is_hidden: 0
        }
    }

    // ... LEFT JOIN county b ON (b.id = a.country_id AND b.is_hidden = 0)

The JOIN object is useful when restricting results in the join table without affecting the results returned in the primary table.

To facilitate scenarios where the optional JOIN tables records are dependent on another relationship we can define this also in the JOIN Object, by passing though an special prop _required: true (key=>value)

The following statement includes all results from the main table, but does not append the country data unless it is within the continent of 'Europe'

    join: {
        county: {
            continent: {
                _required: true,
                name: 'Europe'
            }
        }
    }

    // ...
    // LEFT JOIN county b ON (b.id = a.country_id)
    // LEFT JOIN continent c ON (c.id = b.continent_id)
    // WHERE (c.id = b.continent_id OR b.continent_id IS NULL)
    // ...

Additional Options

Table Alias

Table can have alias's this is useful when the context changes.

E.g. Define 'author' as an alternative for 'users'

    table_alias: {
        author: 'users'
    }

Example implementation...

dare.get({
    table: comments,
    fields: {
        id,
        text,
        author: {
            id,
            name
        }
    }
});

Table Conditions

options.table_conditions[method] => handler|reference

Table conditions defines a list of handlers to trigger when the table is used in a Request. This is useful to assign additional filters on a table during access.

Table Handler

E.g. if there is a label to hide delete records, or showing only content available to a user.

    {
        users(item, options) {
            // Set the scope of the table
            item.join = {is_deleted: 0};
        }
    }

The handler takes two parameters the item is the Table definition within the options Object, the second is the options object for this request.

Table Dependency

If one table is always dependent on another, i.e. Show users only in current country

    {
        users: 'country',
        country(item, options) {
            // Here the HTTP `req` object was included as a property to options, it includes session data.
            item.join = {
                id: options.req.session.country_id
            };
        }
    }

Method Table Handlers

options[method][table] = handler Function

Essentially enables methods to intercept requests to particular tables for given methods and apply business rules.

E.g. prevent a user from being deleted if they dont match the same as the passed through req object.

    del: {
        users(options) {
            if (options.filter.id !== options.req.session.user_id) {
                throw "You can't delete this user"
            }
        }
    }

After Handlers

options.after*Method*[table] = handler(resp)

This handler is executed after the request and is useful for logging or manipulating the response. If this returns undefined statically or via a Promise then the original response is not altered. Anything else will alter the response.

E.g. log an update to the users table

afterPatch: {
    users(resp) {
        // Get the original request filter...
        const ref_id = this.options.filter.id;

        // Post to changelog...
        date.post('changelog', {
            message: 'User updated',
            type: 'users',
            ref_id
        });
        // do not return anything...
    }
}

Catch in the before handler and overwrite after method

Of course there are scenarios where you want to capture a previous existing value. For that you might like to define the after handler before the patch operation is complete.

E.g. here is an example using the before handlers to capture the original value of a field and redefine define the after handler on this instance....

...
patch: {
    users(options) {

        // Clone the request, and add fields to query
        const opts = Object.assign(
            {},
            options,
            {fields: ['id', 'name']}
        );

        return dare.get(opts).then(resp => {

            const previous_name = resp.name;
            const ref_id = resp.id;

            // Set the after handler
            this.after = () => {
                dare.post('changelog', {
                    message: 'User updated',
                    type: 'users',
                    ref_id,
                    previous_name
                })
            };
        });
    }
}
...