Database and REST (dare)

Dare is a brave API for generating SQL out of structured Javascript objects.

Install

npm i dare --save

Setup

Import the appropriate entry point for your database engine. The versions listed are minimum supported versions Dare has been tested against - more recent database versions are expected to be backwards compatible with the versions listed below.

Database Engine	Import Path
MySQL 8.0+, MariaDB	`dare`
MariaDB 11+ (alias of `dare`)	`dare/mariadb11`
MySQL 8.0+ (alias of `dare`)	`dare/mysql80`
MySQL 5.7+, MySQL 5.6+	`dare/mysql57`
PostgreSQL 16+	`dare/postgres16`
SQLite 3+	`dare/sqlite3`

Example

Create a dare connection file, e.g.

// ./dareconn.jsimportDarefrom'dare';// OR 'dare/postgres16', etc...importdbconnfrom'./dbConn.js';// <- your script for executing queriesconstdare=newDare({// setting engine: makes for finer granular version handling used within the engine importsengine: 'mysql:8.0.40',});// Define the handler dare.execute for handing database requestsdare.execute=asyncrequest=>{// Execute query using prepared statements// use request.sql, whilst in postgres use request.textreturndbconn.query(request.sql,request.values);};

Around the code base, e.g.

importdarefrom'./dareconn.js'// Make a requestconstresp=awaitdare.get('users',['name'],{id: 1});// SELECT id, name FROM users WHERE id = 1 LIMIT 1;console.log(`Hi ${resp.name}');

Connect

The setup needs to define a execution handler dare.execute(SqlRequest) : Promise<Array | Object<{insertId, affectedRows}>

The integration tests illustrates how a setup of a dare instance connects to different clients...

MySQL (5.6, 5.7, 8.0,...) and MariaDB (11) See connection with mysql2
Postgres (16+) See connection with pg
SQLite (3+) See connection with node:sqlite

Methods

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

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

property	Type	Description
table	string	Name of the table to access
fields	Array strings	Fields Array
filter	Hash (key=>Value)	Query Object
options	Hash (key=>Value)	Additional Options

e.g.

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

dare.get(options Object)

Alternatively an options Object can be used instead.

e.g.

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

Fields Array `fields`

The fields property is the second argument in the shorthand request dare.get(table,fields[], ...). It is an Array of the fields from the matching table to return.

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

awaitdare.get('users',['id','name','created_date'], ...);// SELECT id, name, created_date FROM ....

The array items can also be Objects.

Aliased fields and Formatting (objects)

Aliasing fields

It's sometimes appropriate to return a field by another name, this is called aliasing.

To achieve that, instead of having a string item in the fields array, an object is provided instead. The object has one property where the key of that property defines the new name, and the value the db field.

e.g. here we rename email to emailAddress

awaitdare.get('users',['name',// also including a regular string field alongside for comparison{// label : db fieldemailAddress: 'email',},]);// sql: SELECT email AS emailAddress FROM users ...

Applying SQL Formatting

The object structure used for aliasing can also be used to label a response including a SQL Function.

E.g. Below we're using the DATE function to format the created_date, and we're aliasing it so it will be returned with prop key date.

awaitdare.get('users',[{date: 'DATE(created_date)',},]);// sql: SELECT name, DATE(created_date) AS date ...

Supported SQL Functions:

SQL Functions have to adhere to a pattern.

note: It is currently limited to defining just one table field, we hope this will change

FUNCTION_NAME([FIELD_PREFIX]? field_name [MATH_OPERATOR MATH_VALUE]?[, ADDITIONAL_PARAMETERS]*)

FUNCTION_NAME: uppercase, no spaces
FIELD_PREFIX: optional, uppercase
field_name: db field reference
MATH_OPERATORMATH_VALUE: optional
ADDITIONAL_PARAMETERS: optional, prefixed with ,, (uppercase, digit or quoted string)

e.g.

Field Defition	Description
`FORMAT(field, 2, 'de_DE')`	Rounding to 2 decimal places and convert to a string with German formatting.
`CONCAT(ROUND(field * 100), '%')`	Multiplying a number by 100. Rounding to 2 decimal places and appending a '%' to the end to convert a decimal value to a percentage.
`DATE_FORMAT(field, "%Y-%m-%dT%T.%fZ")`	Format date field
`!ISNULL(field)`	Function prefixed with negation operator `!`

Nesting Fields

Nesting can return data structures from across tables.

note: a Model Field Attribute reference, which defines the join between the two tables, is required to make nested joins.

Request nested data with an object; where the key is the name of the table to be joined, and the value is the Array of fields to return from the joined table.

// fields attribute...['name',{country: ['name'],},];// sql: SELECT 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...

// Example response{name: 'Andrew',country: {name: 'UK'}}

At the moment this only supports n:1 mapping.
The relationship between the tables must be defined in a model field reference.

Filter `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 to the Fields Object). Nested objects define conditions on Relational tables.

note: a Model Field Attribute reference, which defines the join between the two tables, is required to make nested joins.

{
country: {
name: 'UK';}}

OR shorthand, nested subkeys are represented with a '.'

	{
'country.name': 'UK'
}

Creates the following SQL JOIN Condition

	... WHEREcountry.name='UK' ...

Filter Syntax

The type of value affects the choice of SQL Condition syntax to use. For example an array will create an IN (...) condition.

Prefixing the prop with:

%: creates a LIKE comparison (or ILIKE in postgres)
-: hyhen negates the value
~: creates a range

Key	Value	Type	= SQL Condition
id	1	number	`id = 1`
name	'Andrew'	string	`name = 'Andrew'`
%name	'And%'	Pattern	`name LIKE 'And%'`
-%name	'And%'	Pattern	`name NOT LIKE 'And%'`
name$1	any	any	e.g. `name LIKE '%And%` $suffixing gives `name` alternative unique object key values, useful when writing `name LIKE %X% AND name LIKE %Y%`
tag	[1, 'a']	Array values	`tag IN (1, 'a')`
-tag	[1, 'a']	Array values	`tag NOT IN (1, 'a')`
-status	['deleted', null]	Array values	`(status NOT IN ('deleted') AND status IS NOT NULL)` Mixed type including `null`
~date	'2016-03-04T16:08:32Z..'	Greater than	`date > '2016-03-04T16:08:32Z'`
~date	'2016-03-04..2016-03-05'	Between	`date BETWEEN '2016-03-04' AND '2016-03-05'`
-~date	'2016-03-04..'	!Greater than	`(NOT date > '2016-03-04T00:00:00' OR date IS NULL)`
flag	null	null	`flag IS NULL`
-flag	null	null	`flag IS NOT NULL`
*text	'+And*'	Pattern	`MATCH(text) AGAINST('+And*' IN BOOLEAN MODE)`
name,email	'hello'	any	`(name = 'hello' OR email = 'hello')`

Negate entire joins (i.e. NOT EXISTS)

note: a Model Field Attribute reference, which defines the join between the two tables, is required to make nested joins.

If there is a nested section on a filter which should act to exclude items from the resultset. Then it can be appropriate to use - in front of the table name.

Example: Retrieve all users who are not in the 'admin' team....

awaitdare.get({table: 'users',fields: ['name'],filter: {-team: {name: 'admin'}}});// SELECT u.name FROM users u WHERE NOT EXISTS (SELECT 1 FROM team t WHERE name = 'admin' AND t.user_id = u.id)...

note: this is very different from having the negation on the field definition, i.e. -name:'admin', which is described in Filter Syntax.

Group by `groupby`

groupby accepts the same format as a single field expression. It can be a single value or an array of multiple expressions. I.e.

groupby: ['type','YEAR_MONTH(created_date)'];

Generates

GROUP BY type, YEAR_MONTH(created_date)

Order By `orderby`

orderby accepts the same format as a single field expression. It can be a single value or an array of multiple expressions. I.e.

orderby: ['type','YEAR_MONTH(created_date)'];

Generates

ORDER BY type, YEAR_MONTH(created_date)

Join

note: a Model Field Attribute reference, which defines the join between the two tables, is required to make nested joins.

The Join Object is a Fields=>Value object literal. It accepts the same 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 a special prop _required: true (key=>value)

The following statement includes all rows as before, but the nested country data is filtered separatly.

	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)// ...

Pagination `limit` and `start`

The limit and start properties are simply applied to the SQL query and can be used to paginate the resultset.

awaitdare.get({table: 'table',fields: ['name'],limit: 10,// Return only 10 rowsstart: 20,// Start in the 20th});// SELECT name FROM table LIMIT 10 OFFSET 20;

No `limit` set and `notfound`

Dare returns a single item when no limit is set. When the item is not found Dare rejects the request with DareError.NOT_FOUND. To override this default behaviour simply set the notfound to the value to respond with in the event of a notfound event being triggered. This can be a simple value or if a function is provided, then that function will be called e.g.

constresp=awaitdare.get({table: 'table',fields: ['name'],filter: {name: 'Nameless'}notfound: null});// SELECT name FROM table WHERE name = 'Nameless' LIMIT 1;// -- found 0 rowsconsole.log(resp);// null

dare.getCount(table[, filter][, options])

The dare.getCount method like the dare.get method builds and executes a SELECT ... SQL statement. It differs from the get in that it does not operate on the fields option. It merely calculates and returns the number of results which match the request options. It is intended to be used when constructing pagination, or other summaries.

property	Type	Description
table	string	Name of the table to access
filter	Hash (key=>Value)	Query Object
options	Hash (key=>Value)	Additional Options

e.g.

constcount=awaitdare.getCount('profile',{first_name: 'Andrew'});// SELECT COUNT(DISTINCT id) FROM profile WHERE name = 'Andrew' LIMIT 1;

dare.getCount(options Object)

Using an options Object allows for date.getCount(options) to be paired with a request to dare.get(options).

e.g.

constrequestOptions={table: 'profile',filter: {first_name: 'Andrew'},limit: 10};// Get the first 10 items, and the number of possible rowsconst[items,foundRows]=awaitPromise.all([// Make a request for members matching the conditiondare.get(requestOptions)// Get the number of possible resultsdare.getCount(requestOptions)]);

dare.post(table, body[, options])

The dare.post method is used to build and execute an INSERT ... SQL statement.

property	Type	Description
table	string	Name of the table to insert into
body	Object	Post Object or Array of Post Objects
options	Hash (key=>Value)	Additional Options

e.g.

awaitdare.post('user',{name: 'Andrew',profession: 'Mad scientist'});// INSERT INTO table (name, profession) VALUES('Andrew', 'Mad scientist')

dare.post(options Object)

Alternatively a options Object can be used instead.

e.g.

awaitdare.post({table: 'user',body: {name: 'Andrew',profession: 'Mad scientist',},});

dare.post(options Object) with multiple values

The body can be an Array of objects.

e.g.

awaitdare.post({table: 'user',body: [{name: 'Andrew',profession: 'Mad scientist',},{name: 'Peppa',},],});

This generates INSERT INTO user (name, profession) VALUES ('Andrew', 'Mad Scientist'), ('Peppa', DEFAULT). Note where the key's differ between items in the Array the DEFAULT value is inserted instead.

Post `options` (additional)

Prop	Type	Description
duplicate_keys	'ignore'	Appends `ON DUPLICATE KEYS UPDATE _rowid=_rowid`
duplicate_keys_update	Array(field1, field2, ...)	Appends `ON DUPLICATE KEYS UPDATE field1=VALUES(field1)`
query	Get Options	See the dare.get options

query

The query is used to create an INSERT...SELECT statement in place of a body property. The object is the same as the Get options, see above.

e.g.

/* * Run query to record all users with a session_date in the year 2021 * INSERT INTO logs (id, name) SELECT id, name FROM user WHERE session_date BETWEEN '2021-01-01' AND '2021-12-31' */awaitdare.post({table: 'log',query: {table: 'user',fields: ['id','name'],filter: {session_date: '2021-01-01..2021-12-31'}}]});

dare.patch(table, filter, body[, options])

Updates records within the table with the body object when they match filter.

property	Type	Description
table	string	Name of the table to insert into
filter	Object	Filter object of the results
body	Object	Post Object to apply
options	Hash (key=>Value)	Additional Options

Patch `options` (additional)

Prop	Type	Description
duplicate_keys	'ignore'	Adds keyword `IGNORE`, e.g. `UPDATE IGNORE table ...`
limit	number	Default: `1`. Limit the number of results which can be affected by patch
notfound	*	Value to return when there are no affected rows. If it's a function the function will be called. Default throws `DareError.NOT_FOUND`

dare.del(table, filter[, options])

Deletes records within the table when they match filter.

property	Type	Description
table	string	Name of the table to insert into
filter	Object	Filter object of the results
options	Hash (key=>Value)	Additional Options

Del `options` (additional)

Prop	Type	Description
notfound	*	Value to return when there are no affected rows. If it's a function the function will be called. Default throws `DareError.NOT_FOUND`
limit	number	Default: `1`. Limit the number of results which can be affected by delete

`options` Object

The options Object is used to define properties on the current and descendent contexts. In other words, every method in Dare, creates a new instances 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.

// Create an options Objectconstoptions={// Some options...};// Apply options at the point where Dare is invoked...constdare=newDare(options);// OR Apply options when creating an instance off another instance...constdare2=dare.use(options);// OR Apply options at the point of calling a method...awaitdare.get({table: 'sometable',fields: ['id','name'],
...options,});

option name	type	description
`engine`	`string`	Database engine, e.g. `mysql:5.7:40`, `postgres:16.3`
`models`	`Object<ModelName, Model>`	An object where the keys are the model names to which models can be referred.
`validateInput`	`Function(fieldAttributes, field, value)`	Validate input on patch and post operations
`getFieldKey`	`Function(field, schema)`	Override the default function for retrieving schema fields, this is useful if you want to support altenative case (camelCase and/or snakeCase)
`state`	`any`	Arbitary data which can be used within the Method handlers to set additional filters or formatting

Model

Models define many things, the underling db table, the schema and any handler functions which are invoked on any operation (post, patch, del, get, getCount).

Models are applied to the options.models object e.g...

constdare=newDare({models: {mymodel : {/** model properties */}.}});

Available properties which can be defined on a model are...

constmyModel={
table,// this is the db table name, if omitted Dare will assume the models label instead
schema,// A schema object defining fields, as well as their relationship to other models.
get,// Function to modify the request when accessing data
post,// Function to modify the request when posting data
patch,// Function to modify the request when patching data
del,// Function to modify the request when deleting data};

Model Table `table`

The underlying Database SQL Table to use when querying this model, if omitted Dare will assume the models label instead

constmyModel={table: 't_mytable',// an example table name: some dba's do like to prefix table names, however it's not a convention which makes for a nice api.// ...};

Model Schema `schema`

The schema property defines an object, containing field attribute references in key=>value pair, i.e. fieldName (key) => field attributes (value).

constmySchema={
id,// id field attributes
name,// name field attributes//etc...};

Field Attributes

Can define how a field corresponds to a DB table field, whether it's readable/writable, is it a generated field, as well as relationships between models.

Defining a field attribute, can be verbose using an object with special keys, or can be shorthanded with specific datatypes

Property	Attr Example	Shorthand DataType	ShortHand Example	Description
`references`	e.g. `{references: ['country.id']}`	`Array`	`county_id: ['country.id']`	Relationship with other models
`alias`	e.g. `{alias: 'email'}`	`String`	`emailAddress: 'email'`	Alias a field with a DB Table field
`handler`	e.g. `{handler: Function}`	`Function`	`url: urlFunction`	Generated Field
`type`	e.g. `{type: 'json'}`	na	na	Type of data in field, this has various uses.
`defaultValue`	e.g. `{defaultValue: 'active'}`	na	na	Default Value to insert during post, and filter with get/patch/del
`readable`	e.g. `{readable: false}`	na	na	Disables/Enables request access to a field
`writeable`	e.g. `{writeable: false}`	na	na	Disables/Enables write access to a field
na	e.g. `{writeable: false: readable: false}`	`Boolean`	`{password: false}`	Disables/Enables both write and read access to a field
`get`, `post`, `patch`, `del`	e.g. `{get: {defaultValue: 'active'}, patch: {writeable: false}}`	na		Extends the existing fieldDefinition based upon the method used

Fields dont need to be explicitly defined in the options.models.*tbl*.schema where they map one to one with a DB table fields the request will just go through verbatim.

Field attribute: `reference`

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

constdare=newDare({models: {users: {schema: {// users fields...country_id: ['country.id'],},},country: {schema: {// country fields...id: {},},},},});

Field attribute: `type`

Defining the type introduces additional features.

datatime

Setting value to 'datetime', a conditional filter short hand for created_time: 2017 would be expanded to created_time BETWEEN '2017-01-01T00:00:00' AND '2017-12-31T23:59:59

constdare=newDare({models: {users: {schema: {created_time: {type: 'datetime',},},},},});

date

With type=date; Instance of the Date objects are converted to YYYY-MM-DD format for insertion

constdare=newDare({models: {members: {schema: {start_date: {type: 'date',},},},},});// Example POSt => SQLdare.post('members',{start_date: newDate()});// 'INSERT INTO members (`start_date`) VALUES ('2025-01-02');`

json

Serializes Objects and Deserializes JSON strings in get, post and patch operations. Setting this value also enables the ability to filter results by querying within the JSON values

i.e.

Schema: field definition...

constdare=newDare({models: {users: {schema: {meta: {// Define a field meta with data type of jsontype: 'json',},},},},});

Example set and get

// Arbitary object...constmeta={prop1: 1,prop2: 2,};// For a field named `meta`const{insertId: id}=awaitdare.post('users',{meta});// The value is run through JSON.stringify before insertion// INSERT INOT users (meta) VALUES('{"prop1": 1, "prop2": 2}')// The filter will find records where the JSON meta field has a `prop1 = 1` (For MySQL 5.7+)// If stored as TEXT type field the field is deserialisedconst{meta}=awaitdare.get('users',['meta'],{meta: {prop1: 1}});// i.e. meta === {prop1: 1, prop2: 2);

Many of the same search operators as used in filtering regular fields will work in comparing against properties within JSON objects. i.e. (=), - (!=), % (LIKE), '~' (BETWEEN) etc... will work if your database engine support it.

Field attribute: `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.

constdare=newDare({models: {users: {schema: {avatar_url(fields){fields.push('id');// require additional field from users table.returnitem=>`/images/avatars/${item.id}`;},},},},});

Field attribute: `alias`

To alias a field, so that you can use a name different to the db column name, assign it a string name of the field in the current table. e.g. emailAddress: 'email'

constdare=newDare({models: {users: {schema: {emailAddress: 'email',},},},});

For example this will enable the use of emailAddress in our api (see below), but the SQL generated will refer to it with it's true field name "email".

awaitdare.get('users',['emailAddress'],{emailAddress: 'andrew@%'});// SELECT email AS emailAddress FROM users WHERE email LIKE 'andrew@%'awaitdare.post('users',{emailAddress: 'andrew@example.com'});// INSERT INTO users (email) VALUES ('andrew@example.com')

Nested and SQL `alias`'s

The aliasing can also be used for common functions and define fields on another table to abstract away some of the complexity in your relational schema and provide a cleaner api interface.

e.g.

constdare=newDare({models: {users: {schema: {emailAddress: {// Explicitly define the alias// Reference the email define on another table, we can also wrap in SQL functions.alias: 'LOWER(usersEmails.email)',},},},// Any cross table join needs fields to mapusersEmails: {schema: {user_id: ['users.id'],},},},});

note: Defining an alias which includes SQL or points to another table will let it be used in a query filter and join conditions. However an error will throw if attempting to set a value to such an alias.

Field attribute: `defaultValue`

Defining the defaultValue introduces default conditions or values when querying or inserting records respectively.

defaultValue can be any value supported by the context it is used. And it might be useful to use fieldDefinition extensions based

e.g we can define what defaultValue is used in various scenarios:

// GET (defaultValue = active), adds conditionfilter[prop]=defaultValue;// POST (defaultValue = active)body[prop]=defaultValue;// Del (defaultValue = active)filter[prop]=defaultValue;

defaultValue

In the example we're going to set the defaultValue for the users.status field to "active".

constdare=newDare({models: {users: {schema: {status: {defaultValue: 'active',},},},},});// And now requests will by default include that valueawaitdare.post('users',{name: 'Andrew'});// INSERT INTO users (name, status) VALUES ("Andrew", "active")awaitdare.get('users',['id'],{limit: 100});// SELECT id FROM users WHERE status = 'active' LIMIT 100// ... adds same condition for Del and Patch methods

Field attribute: `readable`/`writeable`

A flag to control access to a field

constdare=newDare({models: {users: {schema: {// Explicit objectid: {readable: true,writeable: false,// non-writeable},// Shorthand for non-readable + non-writeablepassword: false,},},},});

With the above writeable/readable field definitions an error is thrown whenever attempting to access the field e.g.

awaitdare.get('users',['password'],{id: 123});// throws {code: INVALID_REFERENCE}

Or when trying to modify a field through post or patch methods, e.g.

awaitdare.patch('users',{id: 321},{id: 1337});// throws {code: INVALID_REFERENCE}

Field attribute `[method]: Object`

A key with name like (post, patch, get and del) with a value containing Field Definitions. Can override requests with methods of the same key name.

For example:

constdare=newDare({models: {users: {schema: {/** * Should you want a field to be writeable only on `post`, but not writeable on any other operation you could write... */name: {writeable: false,post: {writeable: true,},},/** * defaultValue: apply only on `get` requests */status: {get: {defaultValue: 'active',},},},},},});

`default` Field definition

When a request is made for a field which doesn't exist in the model schema, Dare will fallback to a default field key - if that has been defined, otherwise it will continue with no explicit properties.

The default field can be used to prevent access to fields which should not be accessed, or to prevent unnessary SQL requests for fields which dont exist in the DB Table.

constdareInst=dare.use({models: {// Example member modelmember: {schema: {// Assign a default field keydefault: {readable: false,},name: {type: 'string',},},},},});awaitdareInst.get('member',['name','password']);// Throw `Field 'password' is not readable`

`model.shortcut_map`

shortcut_map, can be used to define shortcut's to nested tables

constdare=dare.use({infer_intermediate_joins: false,// A 'shortcut' can be used to replace infer_intermediate_joinsmodels: {users: {shortcut_map: {myTeams: 'userTeams.team',},},},});

The definition above will help simplify references to the teams model.

constresp=awaitdare.get({table: 'users',fields: [{// Direct link to userTeams, automatically uses pivot table userTeamsmyTeams: ['id','name'],},],join: {myTeams: {'%name': '%team%',},},});// SELECT CONCAT('[', GROUP_CONCAT(IF(c._rowid IS NOT NULL, JSON_ARRAY(c.id,c.name), NULL)), ']') AS 'myTeams[id,name]'// FROM users a// LEFT JOIN userTeams b ON (b.user_id = a.id)// LEFT JOIN teams c ON (c.id = b.team_id)// WHERE c.name LIKE '%team%'// GROUP BY a._rowid// LIMIT 1

`model.get`

Here's an example of setting a model to be invoked whenever we access users model. In the below example we're restricting what records the user has access to.

functionget(options){// In this example we're filtering access to the `users` model by the properties of the `state` data.options.filter.id=options.state.userId;}// For completeness we'll assume the new Dare instance approach for adding the options...constdare=newDare({models: {users: {
get,},},});// Here we're using `table:users`, so the model's `get` Function would be invokedawaitdare.get({table: 'users',fields: ['name'],limit: 100,state: {userId: 123,},});// SELECT name FROM users WHERE id = 123 LIMIT 100;

Data validation

Dare has limited validation built in (see above). Set your own validationInput to check input values being applied in patch and post operation.

validateInput(fieldAttributes, field, value)

The validateInput validation is called after any method model handlers. It contains the following parameters...

property	Type	Description
fieldAttributes	Object	Field Attributes
field	string	Field Name
value	*	Input value

e.g.

dare.use({models: {// Member Modelmember: {schema: {username: {required: true,type: 'string',maxlength: 5,test(value){if(!/\W/.test(value)){thrownewError(`😞: username should only contain alphabetical characters`);}},},age: {// Another field},},},},validateInput(fieldAttributes,field,value){if(!fieldAttribute){thrownewError(`😞: ${field} field is unknown`);}if(fieldAttributes.required&&value===undefined){thrownewError(`😞: ${field} is missing`);}if(fieldAttributes.type&&typeofvalue!=='string'){thrownewError(`😞: ${field} should be a string`);}if(fieldAttributes.maxlength&&value.length>fieldAttributes.maxlength){thrownewError(`😞: ${field} should be less than ${fieldAttributes.maxlength} characters`);}fieldAttributes.test?.(value);},});// Then see what errors you'd get...dare.post('member',{username: 'Fine',hello: "What's this?"});// 😞: hello field is unknowndare.post('member',{age: 5});// 😞: username is missingdare.post('member',{username: 123});// 😞: username should be a stringdare.post('member',{username: 'Thisistoolong'});// 😞: username should be less than 5 charactersdare.post('member',{username: 'No strange characters !@£$%^YU'});// 😞: username should only contain alphabetical characters

default attributes of model schema

The default field definition can be defined per model. This is useful to say when to be strict with unknown fields.

e.g.

dare.use({models: {// Member Modelmember: {schema: {default: {// Be strict with the member modelwriteable: false,},// ... other field definitions below},},},validateInput(fieldAttributes,field,value){if(!fieldAttribute){// Do nothing, We have no field definitions for this modelconsole.log(`Someone should write field definitions for ${field} 👉`);}if(fieldAttributes.writeable===false){thrownewError(`😞: ${field} field is un-writeable`);}// ... other validation rules below},});// So on the member table the default field would be replaced with an unknown field and would be caughtdare.post('member',{hello: "What's this?"});// 😞: hello is un-writeable// Whilst the same unknown field would be allowed through where the default field is not declareddare.post('emails',{hello: "What's this?"});// Someone should write field definitions for hello 👉`

`options.getFieldKey`

The options.getFieldKey is used to obtain the correct schema field definition and SQL field name. This can be useful when allowing queries to be written in camelCase, but where the fields and the schema use snake_case.

e.g.

// Create a new dare instancedare=dare.use({getFieldKey(field,schema){// Normalif(fieldinschema){returnfield;}// Convert camelCase to snake_caseconstsnakeCase=field.replaceAll(/[A-Z]+/g,(m,index)=>`${index>0 ? '_' : ''}${m.toLowerCase()}`);if(snakeCaseinschema){returnsnakeCase;}returnfield;},});// CamelCase parameters here...awaitdare.patch('users',{lastName},{firstName});// Get's mapped to snake_case parameters here// UPDATE users a// SET// a.`first_name` = 'B'// WHERE// a.last_name = 'Name'// LIMIT 1

`options.rowHandler`

The options.rowHandler can be used to additionally preformat the response array, whilst it's already iterating on the response, or to redirect the records to another service (When this is used with Streaming it can drastically reduce the memory used by Dare).

E.g. here the records are redirected back to the client

// create a new dare instance to avoid polluting the others.constresp=awaitdare.get({table: 'users',fields: ['name'/*... and more*/],limit: 1_000_000,// a big number// Define a rowHandler for the request...rowHandler(row){// rudimentary write out as CSV.res.write(Object.keys(item).join(',')+'\n');// do not return anything, save dare building up resultset internally},});console.log(resp===undefined);// empty response... as it was all redirected

Streaming

Typically databases will buffer the resultset into memory and send over one large payload. Whereas streaming sends over the results to the SQL Client as they are available. This has the benefit of being much more performant and memory efficient on large datasets.

Dare, has some functions to take advantage of Streaming

this.addRow(record): process an individual record
options.rowHandler: See above

When combined we can efficiently redirect the results immediatly without building up an internal memory.

// Define an execute handler and call `this.addRow(record)`dare.execute=asyncfunction(query){// Define the current Dare InstanceconstdareInstance=this;// Return a promise out of a stream...returnnewPromise((accept,reject)=>{// Create resultStreamconstresultStream=newPassThrough({objectMode: true});// Pipe results into resultStream...dbconn.connection.query(query).stream().pipe(resultStream);// Catch errors...resultStream.on('error',reject);// Event handlers...resultStream.on('data',row=>{// Process the row record with Dare, i.e. for formatting etc...dareInstance.addRow(row);});// on:end; resolve with empty PromiseresultStream.on('end',()=>accept());});};// Construct the Dare Request// define a `rowHandler` to pass through the newly formatted records as and when they come in.awaitdare.get({table: 'users',fields: ['name'/*... and more*/],limit: 1_000_000,// a big number// Define a rowHandler for the request...rowHandler(row){// rudimentary write out as CSV.res.write(Object.keys(item).join(',')+'\n');// do not return anything - saves dare from building up internal resultset},});// returns: undefined

Exists

Determining whether a record exists can be achieved by simply making a query using the notfound to return a falsy value, and an arbitary fields reference, or for a shorthand, omit the fields entirely - see this shorthand approach below - which as an example will instead return a truthy value {recordExists: true} for a hit, or a null for a miss.

Note

Dont rely on the object properties of a hit, only that it has returned a truthy value.

constexists=awaitdare.get({
table,
filter,notfound: null,});// Hit: {recordExists: true}// Miss: null (whatever provided by notfound)

Additional Options

Fulltext Search

Dare support MySQL's FullText search syntax. The * symbol preceeding the key in a condition denote compare using a fulltext search syntax.

A prerequirement for FullText Search is that the corresponding FullText Index has been created for the fields being accessed.

i.e.

awaitdare.get({table: 'users',fields: ['name'],filter: {'*name': 'Andrew',},});// SELECT name FROM users WHERE MATCH(name) AGAINST ('Andrew' IN BOOLEAN MODE)

The approach also supports multiple field definitions in the key, i.e.

 filter: {'*name,email': 'Andrew',}// SELECT name FROM users WHERE MATCH(name, email) AGAINST ('Andrew' IN BOOLEAN MODE)

Note

It might be handy to create a new field in the Model Schema which aliases all the Indexed fields, i.e...

schema: {
textsearch: 'name,email';}

Now the filter would just call the alias name, and there's only one place to change it.

 filter: {'*textsearch': 'Andrew',}// SELECT name FROM users WHERE MATCH(name, email) AGAINST ('Andrew' IN BOOLEAN MODE)

Performance with LIMIT'ed nested queries

Nested subqueries generated via Dare do not take advantage of restricted datasets through SQL LIMIT - atleast this was the case with MySQL's InnoDB tables.

To address this in MySQL 8, and other databases which support Common Table Expressions (CTE), Dare will by default apply filtering and limiting via a CTE with an INNER JOIN to the rowid to the base table.

By default, the rules defined in applyCTELimitFiltering enables this features for all databases (with the exception of MySQL 5.*), and for requests for less than 10k records.

The rules around when to apply the CTE can be adjusted, e.g.

constdare=newDare(options);dare.applyCTELimitFiltering=options=>{returnoptions.limit<10_000;};

To enable/disable CTE, have the function return truthy/falsy value.

Multiple joins/filters on the same table

In order to both: show all relationship on the join table AND filter the main results by the joined table. One can either create separate table aliases (as described above) using one for the field name, and one for the filter. Or alternatively append an arbitary label, a $ sign followed by an string. E.g.

E.g. Include all the tags associated with users AND only show users whom include the tag "Andrew"

awaitdare.get({table: 'users',fields: ['name',{tags: ['name']}],filter: {tags$a: {name: 'Andrew',},},});

This will get all users who contain atleast the tags 'Andrew', as well as returning all the other tags.

After Handlers

An dareInstance.after handler is executed after the initial request has completed but before Dare has resolved the call. This makes it useful for logging as well as manipulating the response. If the handler returns undefined or Promise<undefined> then the original response is returned unaltered. And anything other than undefined will become the new response.

E.g. here is an example using the after handlers in the users.patch model to record a transaction.

options.models.users={asyncpatch(options,dareInstance){/** * Check that the data to be modified * By using the options to construct a SELECT request first */// Clonse the optionsconstopts={
...options,fields: ['id','name'],};// Execute a dare.get with the cloned optionsconst{id: ref_id,name: previous_name}=awaitdare.get(opts);// Set the after handlerdareInstance.after=()=>{dare.post('changelog',{message: 'User updated',type: 'users',
ref_id,
previous_name,});// Returns undefined so no change};},};

Handling dates and date ranges

The library supports a number of user friendly ways of passing in dates and date ranges by constructing the formal timestamp implied in the data passed to Dare.

E.g. here is a list of supported syntaxes and the resulting timestamp.

2018-01-01..2018-01-02,
2018-01-01..02,
2018-1-1..2
=== 2018-01-01T00:00:00..2018-01-02T23:59:59
etc...

Changing the default MAX_LIMIT

By default the maximum value for a limit option is set by dare.MAX_LIMIT, you can override this in an instance of Dare.

importDarefrom'dare';// Initiate itconstdare=newDare();awaitdare.MAX_LIMIT=1000000;

Disabling intermediate model joins `infer_intermediate_models`

By default infer_intermediate_models = true. This allows two models which share a common relationship with another model to be joined in a query directly. However sometimes this can be unpredictable if there are potentially more than one shared references between the models. In which case you would need to use explicit full paths, you then might like to disable infer_intermediate_models so that you catch anything which doesn't tow the line.

// Disable intermediate models// On new instanceconstdare=newDare({infer_intermediate_models: false});// On extended instanceconstdareInst=dare.use({infer_intermediate_models: false);// On individual queries...awaitdare.get({// ... other optionsinfer_intermediate_models: false});

Infering conditional operators based on value `conditional_operators_in_value`

By default conditional_operators_in_value = '!%'. Which is a selection of special characters within the value to be compared.

%: A string containing % within the value to be compared will indicate a wild character and the SQL LIKE conditional operator will be used.
!: A string starting with ! will negate the value using a SQL LIKE comparison operator.
..: A string containing .. will use a range BETWEEN, < or > comparison operator where a string value contains .. or the value is an array with two values (dependending if the first or second value is empty it will use < or > respecfively). This denotes a range and is enabled using the ~ operator (because . within prop name has another meaning)

// Enabling support for one or more of the above special characters...// On new instanceconstdare=newDare({conditional_operators_in_value: '%!~'});// On extended instanceconstdareInst=dare.use({conditional_operators_in_value: '%!~');// On individual queries...awaitdare.get({table: 'mytable',fields: ['id'],filter: {name: '%compare%',created: '2022-01-01..'}conditional_operators_in_value: '%!~'});// ... SELECT id FROM mytable WHERE name LIKE '%compare%' AND created > '2022-01-01'// The same query with the option disabledawaitdare.get({table: 'mytable',fields: ['id'],filter: {name: '%compare%',created: '2022-01-01..'}conditional_operators_in_value: ''});// Fallbacks to the '=' conditional operator// ... SELECT id FROM mytable WHERE name = '%compare%' AND created = '2022-01-01..'

Overriding table schema per operation

You can override the schema per operation using the models option:

E.g.

// On an instance, or create new instance with newDareInstance = dare.use(options)constdare=newDare({models: {my_table: {schema: {a_write_protected_field: {type: 'datetime',writeable: false,}}},}});// On an individual requestawaitdare.patch({table: 'my_table',body: {a_write_protected_field: 'new value,},models: {my_table: {schema: {write_protected_field: {writeable: true,},}},},});

DB Engine compatibility

This version of Dare is designed to work with:

MySQL (5.6, 5.7 and 8)
Postgres (16.3)
MariaDB (11)

Set the property engine on the Dare instance e.g.

constdare=newDare{{engine: 'postgres:16.3',
...
}};// Or, have multiple instances...letdareWithPostgres=dare.use({engine: 'postgres:16.3'});

Caveats

Only one column name may appear in a Field Expression

Dare can't have more than one model field/column names in the same field expression.

e.g. Fields array....

[{// Both fist_name and last_name in the same field expression 💥`displayName`: 'CONCAT(first_name, last_name)'// ❌}]

To work around this we'd simply use post-formatting. Either write one yourself or make a Generated Field (handler) in the Dare schema and request that by name in the field expression.

Name		Name	Last commit message	Last commit date
.github		.github
.husky		.husky
src		src
test		test
.editorconfig		.editorconfig
.gitignore		.gitignore
.prettierignore		.prettierignore
CHANGELOG.md		CHANGELOG.md
LICENSE		LICENSE
README.md		README.md
eslint.config.js		eslint.config.js
package-lock.json		package-lock.json
package.json		package.json
renovate.json		renovate.json
tsconfig.json		tsconfig.json

Latest commit

History

Folders and files

Repository files navigation

Database and REST (dare)

Install

Setup

Example

Connect

Methods

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

dare.get(options Object)

Fields Array fields

Aliased fields and Formatting (objects)

Nesting Fields

Filter filter

Filter Syntax

Negate entire joins (i.e. NOT EXISTS)

Group by groupby

Order By orderby

Join

Pagination limit and start

No limit set and notfound

dare.getCount(table[, filter][, options])

dare.getCount(options Object)

dare.post(table, body[, options])

dare.post(options Object)

dare.post(options Object) with multiple values

Post options (additional)

query

dare.patch(table, filter, body[, options])

Patch options (additional)

dare.del(table, filter[, options])

Del options (additional)

options Object

Model

Model Table table

Model Schema schema

Field Attributes

Field attribute: reference

Field attribute: type

Field attribute: handler

Field attribute: alias

Nested and SQL alias's

Field attribute: defaultValue

Field attribute: readable/writeable

Field attribute [method]: Object

default Field definition

model.shortcut_map

model.get

Data validation

validateInput(fieldAttributes, field, value)

default attributes of model schema

options.getFieldKey

options.rowHandler

Streaming

Exists

Additional Options

Fulltext Search

Performance with LIMIT'ed nested queries

Multiple joins/filters on the same table

After Handlers

Handling dates and date ranges

Changing the default MAX_LIMIT

Disabling intermediate model joins infer_intermediate_models

Infering conditional operators based on value conditional_operators_in_value

Overriding table schema per operation

DB Engine compatibility

Caveats

Only one column name may appear in a Field Expression

About

Topics

Resources

Stars

Watchers

Forks

Releases

Packages

Used by

Contributors

Fields Array `fields`

Filter `filter`

Group by `groupby`

Order By `orderby`

Pagination `limit` and `start`

No `limit` set and `notfound`

Post `options` (additional)

Patch `options` (additional)

Del `options` (additional)

`options` Object

Model Table `table`

Model Schema `schema`

Field attribute: `reference`

Field attribute: `type`

Field attribute: `handler`

Field attribute: `alias`

Nested and SQL `alias`'s

Field attribute: `defaultValue`

Field attribute: `readable`/`writeable`

Field attribute `[method]: Object`

`default` Field definition

`model.shortcut_map`

`model.get`

`options.getFieldKey`

`options.rowHandler`

Disabling intermediate model joins `infer_intermediate_models`

Infering conditional operators based on value `conditional_operators_in_value`