UnSQL

UnSQL is an open-source, lightweight JavaScript library that provides schemaless, class based, clean and modern interface to interact with structured Database (MySQL), through dynamic query generation. UnSQL is compatible with JavaScript based runtimes like Node.js and Next.js.

Table of Contents

1. Overview
2. Breaking Changes
3. What's New?
4. Features
5. Setup Guide
   • Installation
   • Prerequisite
6. Basics
   • How UnSQL Works?
   • Model Class (Example)
   • What is config inside model class?
7. What are the built-in methods in UnSQL?
   • Find method
   • Save method
   • Delete method
   • Export method
   • Reset method
8. What is Session Manager in UnSQL?
9. Examples
   • How to find (read/retrieve) record(s) using UnSQL?
   • How to save (insert/update/upsert) data using UnSQL?
   • How to delete (remove) record(s) using UnSQL?
   • How to use Session Manager?
10. FAQs

• How to import UnSQL in model class
• How does UnSQL differentiates between column name and string value?
• What are Reserved Constants in UnSQL?
• Does UnSQL support MySQL Json Datatype

Overview

UnSQL simplifies working with structured (MySQL) databases by dynamically generating queries under the hood while offering developers a flexible and intuitive interface. This library eliminates boilerplate code, enhances security, and improves productivity in database management.

Breaking Changes

Beyond version v2.0, backward compatibility has been dropped, from the default import, in favour of security, features and overall interface. For projects still running on version v1.x it is recommend to switch all the import / require of 'unsql' in your existing model classes to legacy flag 'unsql/legacy' as shown below:

// v1.x import
const UnSQL = require('unsql/legacy')
// or
import UnSQL from 'unsql/legacy'

// v2.x import
const { UnSQL } = require('unsql')
// or
import { UnSQL } from 'unsql'

Please note: Documentation for version v1.x is available on GitHub

What's New?

With the release of v2.0 UnSQL has been completely re-written with a even better engine and newer interface along with addition of new features like:

• Built-in AES Encryption/Decryption modes: Protect sensitive data natively without any third part package
• Dynamic Query Generation: Prevent SQL injection using placeholders
• Multiple Debug modes: Debug the Dynamically generated query or run Execution Benchmark
• Built-in Error Handling: No more try-catch, result holds it all
• Revamped Interface: Now uses object based params and properties for interaction
• Evolved IntelliSense Support: JSDoc-compatible type definitions provide better IDE support (Code suggestions) and type checking

Key Features

• Promise based interface with streamlined async/await support
• Schemaless eliminates boilerplate code and hectic to manage migrations
• Class-based Models encapsulates configurations into clean interface
• MySQL Support for single connection and connection pool
• Dynamic query generation perform CRUDs without writing SQL
• JSON as Response including execution success/failure acknowledgement and result or error
• Transaction based executions, handles rollbacks on failure
• Graceful Error Handling no try-catch required, returns structured error message
• JSDoc-compatible for type checking and code suggestion
• Built-in Debug Modes (eg.: 'query', 'error', 'benchmarks' etc)
• Built-in AES Encryption/Decryption methods with native support for multiple modes (eg.: ECB, CBC)

Setup Guide

Installation

UnSQL can be installed into your package via. any of the package managers viz. npm or yarn as shown below:

1. Using npm

npm install unsql

Or

npm i unsql

Or

2. Using yarn

yarn add unsql

Prerequisite

UnSQL requires MySQL connection or connection pool to connect to the MySQL database. mysql2 is the most commonly used package for this purpose. Make sure to add multipleStatements: true into your mysql2 createPool or createConnection method as shown below:

import mysql from 'mysql2/promise'

const pool = mysql.createPool({
    ...,
    multipleStatements: true // required
})

Basics

How UnSQL works?

UnSQL uses class-based approach, therefore first step is to create model class. Each table in your database is represented by a model class that extends from the UnSQL base class and holds config property specific to this model. These model classes are used to invoke various built-in methods to perform CRUDs.

Model Class (Example)

Below is the example for a model class using both CommonJS and ES6 module. Here, class named User, extending from the UnSQL base class, is defined inside the user.class.js file. For explanation, MySQL connection pool is used:

user.class.js (CommonJS)

// @ts-check
const { UnSQL } = require('unsql')

// get connection pool from your mysql provider service
const pool = require('path/to/your/mysql/service')

/**
 * @class User
 * @extends UnSQL
 */
class User extends UnSQL {

    /**
     * UnSQL config
     * @type {UnSQL.config}
     */
    static config = {
        table: 'test_user', // (mandatory) replace this with your table name
        pool, // replace 'pool' with 'connection' if you wish to use single connection instead of connection pool
        safeMode: true,
        devMode: false
    }

}
module.exports = { User }

user.class.js (ES6 Module)

// @ts-check
import { UnSQL } from 'unsql'

// get connection pool from your mysql provider service
import pool from 'path/to/your/mysql/service'

/**
 * @class User
 * @extends UnSQL
 */
export class User extends UnSQL {

    /**
     * UnSQL config
     * @type {UnSQL.config}
     */
    static config = {
        table: 'test_user', // (mandatory) replace this with your table name
        pool, // replace 'pool' with 'connection' if you wish to use single connection instead of connection pool
        safeMode: true,
        devMode: false
    }

}

What is config inside UnSQL model class?

config is a static object that is used to define global level configurations that are specific for all references of this model class. Below are the list of properties config accepts:

• table: (mandatory) accepts name of the table in the database,
• pool: (optional) accepts mysql connection pool object,
• connection: (optional) accepts mysql connection object,
• safeMode: accepts boolean value, helps avoiding accidental delete all due to missing where and (or) having property in delete method,
• devMode: accepts boolean value, Enables/Disables features like Export to/Import from another model, reset database table mapped to model

Please note: Either of the two: pool or connection property is required. pool takes priority over connection in case value for both are provided

What are the built-in methods in UnSQL?

UnSQL provides five (05) static methods to perform the CRUD operations via. model class as mentioned below:

| Method | Description | | -------- | --------------------------------------------------------------------------------------------------------------------------------- | | find | fetch record(s) from the database table | | save | insert / update / upsert record(s) in the database table | | delete | remove record(s) from the database table | | export | export record(s) to a dynamically generated '.json' file or migrate data into another table mapped to a valid UnSQL model class | | reset | remove all records from database table (resets 'auto increment' IDs to zero (0)) |

Find method

find is a static, asynchronous method. It dynamically generates select query, that reads / retrieves record(s) from the database table. It accepts object as its parameter with various properties (optional). find method always returns a JSON object (Here referred as result) with execution success/failure acknowledgement via. success property (being true on success and false on failure) and result (Array of record(s)) or error (detailed error object) depending upon query was successful or not. find method combines findAll and findOne into one single method, as findOne method (in every other library/ORM) is just a wrapper around findAll and grabs the first matching record. find method along with all its parameters with their default values is shown below:

const result = await User.find({
    alias: undefined,
    select: ['*'],
    join: [],
    where: {},
    junction: 'and',
    groupBy: [],
    having: {},
    orderBy: {},
    limit: undefined,
    offset: undefined,
    encryption: {},
    debug: false,
    session: undefined
})

/**
 * Above code is equivalent to:
 * // 1. Passing empty object as parameter
 * const result = await User.find({ })
 * And
 * // 2. Not passing any parameter at all
 * const result = await User.find()
*/

Each of the aforementioned properties / parameters are explained below:

alias

alias is a very important parameter throughout UnSQL, it accepts string as value that defines local reference name of the table. It is context sensitive, meaning, it always refers to the immediate parent table (Here it refers to the parent model class). It automatically gets associated (unless explicitly another alias is prefixed) to all the column names in the context. This parameter plays an important role as it helps identify the columns being referred to in any property (e.g, select or where or having or join etc) when using sub-query type wrappers or join.

const result = await User.find({ alias: 't1' })

select

select accepts an array of values like column name(s), string value, boolean, number, wrapper methods (See wrapper methods). This property restricts the columns that needs to be fetched from the database table. By default, it is set to select all the columns. Below is a sample of select property:

const result = await User.find({
    select: [
        'userId',
        { 
        str: {
            value:'firstName',
            textCase: 'upper',
            as: 'fname'
            }
        }, 
        'lastName', 
        '#this is string value and will be printed as it is'
    ]
})

Explanation: In the above sample block, 'userId', 'lastName' and 'lastName' are the column names in the database table, and at the end starting with # is a static string value

join

join parameter accepts an array of join object(s). Each join object represents an association of a child table with the immediate parent table, on the bases of a common column, reference of which is provided inside using property. Join object along with its default values is explained below:

const result = await User.find({
    join: [
        {
            select: ['*'],
            table: 'some_table',
            type: null,
            alias: null,
            join: [],
            where: {},
            junction: 'and',
            groupBy: [],
            having: {},
            using:[],
            as: null
        }
    ]
})

Properties defined inside each join object are context sensitive and will work inside that scope only. Below are the explanation of each of these join properties:

select (optional) similar as explained above in find method (See select), this property is used to restrict the columns/values that will be fetched from the associated child table.

table (required) accepts name of the table that is being associated as a child

type (optional) defines the type of the association these two tables will have. Can have any one of the values 'left' | 'right' | 'inner'

• 'left' considers all records from the parent table and only the matching record(s) from the child table
• 'right' considers all records from the child table and only the matching record(s) from the parent table
• 'inner' considers only the overlapping records from the two table and ignores all the other records

Please note: If type is not provided, it results in a 'natural' join which results in only the matching column(s) record(s) of two tables

alias (optional) similar as explained inside find method, this property provides local reference name to the associated (child) table. This property is context sensitive hence alias property defined inside this join object will override the default alias being prefixed to the column name(s) and any column name from parent table needs to be specifically prefixed with parent alias value (if column name(s) are ambiguous). Until as property is set inside join object, alias is also used to refer the values from the associated (child) table outside the join object. (Also see alias for more details on alias)

join (optional) accepts array of join object(s). Used for nested join association(s) (Same as join)

where (optional) accepts object value, allows to filter records in the associated child table using various conditions (Also see where for more details on where)

junction (optional) defines the clause that will be used to connect different conditions inside the where | having property inside this join object. Similar to junction in find method (See junction for details),

groupBy (optional) used to group records in child table (see groupBy for details)

having (optional) allows to perform comparison on the group of records from associated (child) table (See having for details)

using (required) accepts array of column name(s) or object(s) in the format of { parentColumn: childColumn } here, parentColumn is the column name from the parent table and childColumn is the column name from the associated (child) table.

as (optional) provides a local reference name to this join object and helps refer column(s) outside this join object context, such as in select, where, having properties of the parent table

Please note:

1. When the name of the columns that connect the two tables is different or when using multiple join objects (even with same connecting column names), it is required to set the value of using property in the format of { parentColumn: childColumn }.
2. While using multiple join objects, it is recommended to set appropriate (and unique) values to the alias property on both the parent as well as child tables.
3. It is mandatory to set as property while using select and (or) any other filtering properties viz. where and having property in case.

where

where parameter accepts object (simple or nested) as value, it is used to filter record(s) in the database based on the condition(s). Each object is in a key: value pair format, where key and value can be a either be a string or boolean or number or a wrapper method, on the other hand value can also accept array of values (each can be of any type: string, number, boolean or wrapper method) (see wrapper methods). Sample where property is show below:

const result = await User.find({
    where: {
        or: [
            { userId: { between: { gt: 1, lt: 30 } } },
            { userRole: ['#manager', '#admin'] }
        ],
        userStatus: 1
    }
})

Explanation: In the above sample, 'userId', 'userRole' and 'userStatus' are column names, 'manager', 'admin' are normal string values (starting with #) (See this for details on column name vs string value)

junction

junction can have any one of the two string values 'and' | 'or'. This property is used to connect the conditions passed inside the where | having properties. Default value is 'and'

const result = await User.find({ where:{...},  junction: 'and' })

Please note: junction property only works with where and having parameters, and setting junction parameter alone will have no effect.

groupBy

groupBy property accepts array of column name(s). These column name(s) can either be from the parent table, any of the associated (child) table(s) or both. When referencing any column name from the associated (child) table(s), if the alias (or as) property is set inside the join object context, then that column name is required to be prefixed with its respective alias (or as) property value and a '.' symbol connecting them.

// Example 1: when grouping records using a column (here 'role') from the parent table
const result1 = await User.find({ groupBy: ['userRole'] })

// Example 2: When grouping records using a column (here 'city') from the associated (child) table
const result2 = await User.find({
    alias:'t1',
    join: [
        {
            alias: 't2',
            table: 'order_history',
            using: ['userId']
        }
    ]
    groupBy: ['t2.city']
    })

// Example 3: When grouping records using a column (here 'city') from the associated (child) table in a complex association
const result3 = await User.find({
    alias:'t1',
    join: [
        {
            select: ['orderId', 'userId', 'city']
            table: 'order_history',
            alias: 't2',
            using: ['userId'],
            where: {
                totalValue: { gt: 5000 }
            },
            as: 'j1'
        }
    ]
    groupBy: ['j1.city']
    })

Explanation:

1. In the first example, all the user records are being grouped on the basis of their 'userRole' column.
2. In the second example, 'order_history' table (child) is associated with the 'user' (parent) table and the records are being grouped based on the 'city' name from the 'order_history' (child) table, hence the column name is being prefixed with the alias from the child table (here 't2' and connected using '.' symbol)
3. In the third example, similar to example 2, records are being grouped based on the 'city' name from the child table, however, in this case, complex association is used and a local reference name (here 'j1') is set using the as parameter, hence to refer any column from this association, this local reference needs to be prefixed to the column name using a '.' symbol

Please note:

1. In example 1, if the column belongs to the parent table, alias as prefix is note required as UnSQL will do that automatically based on the context relation.
2. In both the examples 2 and 3, if the column names being referenced are not ambiguous in both the tables, there is no need to prefix the column names with alias or as prefixes.

having

having property is similar to where property, as it also helps filtering the record(s) from the database table however, it is significantly different when it comes to the fact how it works. having property is capable of performing regular comparisons just like where property however, the major difference between that two properties is that having property can also perform comparisons using aggregate methods such as sum, avg, min, max etc. on the grouped records (using groupBy property), which is not possible with the where property. Below is an example of filtering with having property and groupBy property using sum aggregate (wrapper) method

const result = await User.find({
    groupBy: 'salary',
    having: { 
        sum: { 
            value: 'salary',
            compare: { gt: 5000 }
         }
        }
    })

Please note: groupBy property plays an important role when filtering records using aggregate method(s) to compare within having property.

orderBy

orderBy property is used to re-arrange the records being fetched in a specific order(s) based on the specified column name(s), it accepts object in key: value pair format, where in each pair the key represents the name of the column in the database table and the value is one of the two values i.e. 'asc' (ascending order) or 'desc' (descending order)

// Example 1:
const result1 = await User.find({
    orderBy: { firstName: 'desc' }
})

// Example 2:
const result2 = await User.find({
    orderBy: { firstName: 'asc', joiningDate: 'desc' }
})

Explanation:

1. In the first example, records are being re-arranged in the descending order based on the values of 'firstName' column from the database table
2. In the second example, records are being re-arranged based on the two provided criteria: first- ascending order of their 'firstName' column and, second- descending order of their 'joiningDate' column

limit

limit as the name suggests, this property limits the no. of records that will be fetched from the database table, default is null hence no limit is applied and all records are fetched.

const found = await User.find({ limit: 10 })

Explanation: Above example will limit the no. of records to '10'. limit along with offset property is used for pagination of records

offset

offset property accepts number value that will 'offset' the starting index of the records being fetched from the database table, default is null hence no offset is applied and records from the beginning are fetched

const found = await User.find({ offset: 10 })

Please note: Above example will offset the starting index of records to be fetched to '10'. If this index is set greater than the number of records in the database, it will return null or empty array.

encryption

encryption is one of the most important properties, it is used to define Encryption/Decryption related configurations such as mode, secret, iv and sha. These local configuration(s) will override global encryption property (see global config). These configurations are restricted to execution context and can be redefined for each execution as desired. It can hold any one of the four configurations (or all):

const result = await User.find({ 
    encryption: {
        mode: 'aes-256-cbc',
        secret: 'your_secret_string_goes_here'
        iv: 'Initialization Vector (required with CBC mode) goes here',
        sha: 512
    }   
})

Please note:

1. All the configurations inside encryption property are optional and can be used to either set or override any (or all) of global configuration(s) for local execution.
2. iv works only with 'cbc' mode and hence will be ignore (if set) in 'ecb' mode
3. When setting encryption mode, it is required to set multipleStatements: true inside your createConnection or createPool configuration.

debug

debug property controls the debug mode for each execution, and can be set to either 'query' | 'error' | true | false | 'benchmark' | 'benchmark-query' | 'benchmark-error'. debug property plays an important role in understanding the SQL query that is being generated and hence understanding the operation that will be performed in this execution. Debug mode can be controlled specifically for execution, avoiding unnecessary cluttered terminal. By default, debug mode is in disable mode hence if no value is set for this property, no debugging will be performed.

session

session (provided by SessionManager) enables UnSQL to chain multiple query executions and re-use one transaction across these queries and rollback (in case of error) or commit all changes at once using this session/transaction

import { SessionManager } from 'unsql'
import { pool } from '.path/to/your/db/service/'

router.post('/', async (req, res) => {

    const { userInfo, addressInfo } = req.body

    // Create session from Session Manager
    const session = new SessionManager()

    // invoke transaction by calling transaction lifecycle method provided by session
    await session.init(pool) // either of MySQL 'connection' or connection 'pool' is required as parameter
    
    // your code goes here...
    const userResponse = await User.save({ data: userInfo, session }) // pass session inside query to chain this query

    addressInfo.userId = userResponse.insertId // patch auto generated 'userId' to addressInfo

    const addressResponse = await Address.save({ data: addressInfo, session }) // pass session inside query to chain this query

    //  handle if error is encountered
    if(!userInfo.success || !addressInfo.success) {
        // rollback all prior changes if error is encountered
        await session.rollback()
        return
    }

    // finally commit all changes if no errors encountered
    await session.commit()
})

Please note:

1. Passing false as parameter for rollback and commit methods will allow you to perform their respective actions (at multiple locations) without closing the transaction and destroying the session
2. rollback and commit can be called at any position and it will either rollback/commit all proceeding changes till that position

| Mode | Description | | ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 'query' | prints Dynamically Generated SQL Query: 1. un-prepared statement, 2. values array (to be inserted) and 3. prepared statement after substituting all the values | | 'error' | prints only the structured error object (when error is encountered), includes error message, error code, full stacktrace etc | | 'benchmark' | prints the time taken to execute the method | | 'benchmark-query' | enables the combination of 'benchmark' and 'query' modes | | 'benchmark-error' | enables the combination of 'benchmark' and 'error' modes | | true | enables all debug modes i.e. 'query' and 'error' and 'benchmark' | | false | disables all query mode |

Please note:

1. Few 'warnings' like 'version configuration mismatch' or 'invalid value' or 'missing required field' errors will still be logged in the console even if the debug mode is off to facilitate faster resolving of the issue.
2. Irrespective of the debug mode is enabled or disabled, if the query fails, the error message/object will be available in the 'error' parameter of the 'result' object of the method along with the 'success' acknowledgement keyword being set to false.

Save method

save is a static, asynchronous method. It dynamically generates valid SQL query, that insert | update | upsert data (single or in bulk) into the database table When only data property is set, this method inserts record(s), when data along with where or having (with groupBy) is set, it updates record(s), when data and upsert are set, it upsert record. save method takes in an object as its parameter with various properties as mentioned below:

const result = await User.save({
    alias: undefined,
    data,
    where: {},
    junction: 'and',
    groupBy: [],
    having: {},
    upsert: {},
    encrypt: {},
    encryption: {},
    debug: false,
    session: undefined
})


/* result = {
  "success": true,
  "fieldCount": 0,
  "affectedRows": 1, // actual number of entries made
  "insertId": 1, // dynamically generated id for the first entry made in this query
  "info": "",
  "serverStatus": 3,
  "warningStatus": 1,
  "changedRows": 0
}
*/

Below are the explanations for each of these properties:

alias (optional) same as in find method (See alias for details)

const result = await User.save({ data, alias: 'u' })

data (mandatory) is the actual data that will be inserted | updated | upsert into the database table. data can either be a single object (supports insert, update and upsert) or an array of objects (supports only insert of bulk data).

// 1. insert single record
const result = await User.save({ 
    data: { 
        firstName: 'John', 
        userEmail: '[email protected]'
    }
 })

// 2. insert bulk records
const result = await User.save({ 
    data: [
        { 
            firstName: 'John', 
            userEmail: '[email protected]'
        },
        {
            firstName: 'Jane',
            userEmail: '[email protected]'
        }
        ...
    ]
 })

where (optional) when condition(s) are provided, converts save method from insert mode into update mode. Used to identify (filter) the record(s) to be updated in the database table, based on the set condition(s) (See where for more details)

// perform update
const result = await User.save({ 
    data: {...},
    where: {
        userId: 1
    }
 })

junction (optional) accepts one of the two string values 'and' | 'or' (See junction for details)

const result = await User.save({ 
    data: {...},
    where: {...},
    junction: 'and'
 })

groupBy (optional) used with having property to group records, same as explained above (See groupBy for details)

const result = await User.save({ 
    data: {...},
    groupBy: 'userRole',
    having: {...}
 })

having (optional) similar to where property, having also helps in updating the record(s) in the database (See having for more details)

const result = await User.save({ 
    data: {...},
    groupBy: 'department',
    having: {
        {
            sum: {
                value: 'salary',
                compare: {
                    gt: 50000
                }
            }
        }
    }
 })

upsert (optional) accepts single object value, when provided, switches save method into upsert mode. Value of this property is only used in a special case when 'ON DUPLICATE KEY' error is encountered, in this case, the conflicting record is updated using the values provided in this property, else this property is ignored

const result = await User.save({ 
    data: {...},
    upsert: {
        name: 'john',
        ...
    }
 })

encrypt (optional) holds information regarding the columns that needs to be encrypted and stored in the database. It accepts object in key: value format where each key represents the column name and value again is an object with three as key(s) secret, iv and sha

const result = await User.save({
    data: {...},
    encrypt: {
        userEmail: {
            secret: 'someSecret',
            sha: 512
        }
    }
})

Explanation: Above sample will encrypt 'userEmail' column inside data property using the encryption configurations secret and sha provided as value object.

encryption (optional) holds local level configurations such as mode, secret, iv and sha that can be used for encrypting columns from the data property, that are specified inside the encrypt property (See encryption for more details)

const result = await User.save({
    data: {...},
    encrypt: {
        userEmail: {}
    },
    encryption: {
        mode: 'aes-256-cbc',
        secret: 'someSecret',
        iv: 'someInitializationVector',
        sha: 512
    }
})

Explanation: In the above sample, encryption configurations are provided inside the encryption property and the value for the column name is an empty object inside encrypt property. Hence, the configurations from encryption property will be used

Please note: If values in both properties viz. encrypt and encryption are set, encrypt always takes priority and will override configurations provided in encryption property or global encryption configurations set inside config property of model class

debug (optional) enables various 'debug' modes (See debug for more details)

const result = await User.save({
    data: {...},
    debug: 'query'
})

Delete method

delete is a static, asynchronous method that is used to dynamically generate valid SQL query that removes record(s) from the database table. delete method takes in an object as its parameter with various properties as mentioned below:

const result = await User.delete({
    alias: undefined,
    where: {},
    junction: 'and',
    groupBy: [],
    having: {},
    encryption: {},
    debug: false,
    session: undefined
})

Below are the explanations for each of these properties:

alias (optional) same as in find method (See alias for details)

where (optional) is used to identify (filter) record(s) that needs to be removed/deleted from the database table (See where for more details)

junction (optional) accepts one of the two string values 'and' | 'or' (See junction for details)

groupBy (optional) used to group records in the database table (See groupBy for details)

having (optional) similar to where property having also helps in filtering the record(d) in the database that needs to be removed/deleted (See having for more details)

encryption (optional) same as explained above (See encryption for more details)

debug (optional) enables various debug modes (See debug for more details)

session (optional) enables UnSQL to re-use session transaction provided by SessionManager across multiple executions

Export method

export is a static, asynchronous method. As the name suggests, it is used to export record(s) from the database table into a dynamically generated json file (with same name as table property inside config property), by default inside 'exports_unsql' directory. Record(s) can be filtered using where property and even columns can also be restricted using the select property. This method only works when devMode inside config property is set to true. This method is helpful in taking backups of the database table. export method takes in an object as its parameter with various properties as mentioned below:

await User.export({
    target: User.config.table,
    directory: 'exports_unsql',
    select: ['*'],
    join: [],
    where: {},
    groupBy: [],
    having: {},
    orderBy: [],
    limit: undefined,
    offset: undefined,
    mode: 'append',
    debug: false
})

Each of these properties is explained below:

target (optional) defines the target, filename for the dynamically generated .json file or valid UnSQL model class, to export record(d) into. Defaults to the name of the table property of this model class

directory (optional) used to change the default name of the dynamically generated directory that contains all exported .json files

select (optional) limits the columns that will be considered while exporting records, can also be used to manipulate record(s) of selected columns while exporting (see select for details)

where (optional) filter record(d) based on condition(s) for exporting (see where for details)

groupBy (optional) groups record(s) by column name(s) to be exported (see group by)

having (optional) filter record(s) based on condition(s)/aggregate methods (See having for details)

orderBy (optional) re-order record(s) by column name(s) to be exported (see group by)

limit (optional) limits the record(s) to be extracted

limit (optional) sets the starting index of record(s) to be extracted

mode (optional) defines the behavior of export, 'append' will recursively add data if invoked multiple times, 'override' as the name suggests will override the dynamically generated file if invoked multiple times

debug (optional) enables various debug modes (see Debug for details)

Reset method

reset is a static, asynchronous method. As the name suggests, this method resets the database table to its initial state by removing all record(s) and also setting the auto increment Id to zero (0). This method only works when devMode is set to true and safeMode is set to false. export method takes in an object as its parameter with only one (optional) property debug (see debug for details) as mentioned below:

await User.reset({ debug: false })

Caution: This method results in a destructive change and hence should be used with caution as changes cannot be reverted back

What are wrapper methods in UnSQL?

UnSQL provides various built-in methods to interact with data and perform specific tasks, each of these wrapper methods belong a certain type. All of the wrapper methods have object like interface (key: value pair) to interact with them, where key can be any one of the specially reserved keywords that represents its respective wrapper method. Below is the list of wrapper methods along with their respective keywords available inside UnSQL:

| Keyword | Wrapper Type | Description | | :------: | :----------: | ----------------------------------------------------------------------------------------- | | str | string | performs string value related operations | | num | numeric | performs numeric value related operations | | date | date | performs date value related operations | | and | junction | performs junction override inside the where and having | | or | junction | performs junction override inside the where and having | | if | conditional | checks condition and returns respective value (used with select, where, having) | | case | conditional | checks condition and returns respective value (used with select, where, having) | | sum | aggregate | calculates 'total' from set of values | | avg | aggregate | calculates 'average' from set of values | | count | aggregate | performs 'count' operation on set of values | | min | aggregate | determines 'lowest' value among the provided values | | max | aggregate | determines 'highest' value among the provided values | | json | sub-query | creates a json object at the position it is invoked | | array | sub-query | creates a json array at the position it is invoked | | refer | sub-query | fetch a column from another table at the position it is invoked | | concat | merge | combines multiple values into one |

Please note:

1. junction type wrapper methods can only be used inside where and having property
2. aggregate type wrapper methods can only be used inside select and having property and not with where property

All the aforementioned wrappers are explained below along with their interface:

String wrapper

String wrapper (keyword str) is used to perform string/text data related operations, it can be used directly/nested inside select, where, having properties. Below is the interface for string wrapper method along with the default values for each of its properties:

const result = await User.find({
    select: [
        { 
            str: {
                value: 'string_value_goes_here',
                replace: {
                    target: null,
                    with: null
                }, 
                reverse: false,
                textCase: null, 
                padding: {
                    left: {
                        length: null,
                        pattern: null
                    },
                    right: {
                        length: null,
                        pattern: null
                    }
                },
                substr: {
                    start: 0,
                    length: null
                },
                trim: false,
                cast: null,
                decrypt: null,
                as: null,
                compare: {}
            }
        }
    ]
})

Each of these properties of string wrapper method are explained below:

value (mandatory) accepts column name or string value. All the operations are performed on this value only

replace (optional) accepts object with two properties target and with, both can accept either column name or string value

• target is used to identify the string value that needs to be replaced,
• with specifies the string value that will replace the target string

reverse (optional) accepts boolean value, if set to true, reverses the order of the characters in value property

textCase (optional) transforms the characters of value property to the specified case 'upper' | 'lower'

padding (optional) accepts object with two properties left and right. Each property further accepts an object with exactly two properties pattern (used to fill empty spaces) and length (defines minimum number of characters to be maintained in the value property)

substr (optional) accepts object with two properties start (defines starting index) and length (number of characters in substring)

trim (optional) removes/trims whitespace in value property based on the value 'left' (from the beginning) | 'right' (from the end) | true (both beginning and end)

cast (optional) converts/casts value property to the specified type / format using either of the values 'char' | 'nchar' | 'date' | 'dateTime' | 'signed' | 'unsigned' | 'decimal' | 'binary'

decrypt (optional) is an object with properties secret, iv (used with CBC mode) and sha used to decrypt value property. Overrides configuration(s) provided in local and global encryption (see encryption for details)

as (optional) renames/provides local reference name to the value property

compare (optional) used to compare the value returned by this wrapper method using comparators

Numeric wrapper

Numeric wrapper (keyword num) is used to perform mathematical operations on the numeric data, it can be used / nested inside select, where, having clause as a value. All the operations are executed sequentially, in order that follows BODMAS rule. Below is the interface for this wrapper method along with the default values for each of its properties:

const result = await User.find({
    select: [
        { 
            num: {
                value: 'column containing number' || number,
                decimal: null,
                mod: null,
                sub: 0,
                add: 0,
                multiplyBy: null,
                divideBy: null,
                power: null,
                cast: null,
                decrypt: null,
                as: null,
                compare: {}
            } 
        }
    ]
})

Each of these properties of numeric wrapper method are explained below:

value (mandatory) accepts column name or numeric value. All the operations are performed on this value only

decimal (optional) accepts 'floor' | 'ceil' | 'round' | number as value. It determines the behavior of decimal values or limits the no. of decimal values

mod (optional) accepts column name or numeric value. Performs 'modulus' operation of this value on value property

sub (optional) accepts column name or numeric value. Performs 'subtraction' of this value from value property

add (optional) accepts column name or numeric value. Performs 'addition' of this value to value property

multiplyBy (optional) accepts column name or numeric value. Performs 'multiplication' of value property by this value

divideBy (optional) accepts column name or numeric value. Performs 'division' of value property by this value

power (optional) accepts column name or numeric value. Applies this value as 'power' of value property

cast (optional) used to 'convert' or 'cast' string from value property to the specified type / format. It accepts either of the values 'char' | 'nchar' | 'date' | 'dateTime' | 'signed' | 'unsigned' | 'decimal' | 'binary'

decrypt (optional) is an object with properties secret, iv (used with CBC mode) and sha used to decrypt value property. Overrides configuration(s) provided in local and global encryption (see encryption for details)

Please note: mode of encryption can only be set inside the encryption configuration of either findObj or model class and not inside decrypt

as (optional) renames/provides local reference name to the value property

compare (optional) used to compare the value returned by this wrapper method using comparators

Date wrapper

Date wrapper (keyword date) is used to perform date related operations on value property, it can be used nested inside select, where, having clause as a value. Below is the interface for this wrapper method along with the default values for each of its properties:

const result = await User.find({
    select: [
        { 
            date: { 
                value: 'column containing date' || date,
                add: 0,
                sub: 0,
                fromPattern: null,
                cast: null,
                decrypt: null,
                format: null,
                as: null,
                compare: {}
            } 
        }
    ]
})

Each of these properties of date wrapper method are explained below:

value (mandatory) accepts column name or date value. All the operations are performed on this value only

add (optional) accepts number (representing 'days') or alpha numeric value (number along with date | time unit). Performs 'addition' of this value to value property

sub (optional) accepts number (representing 'days') or alpha numeric value (number along with date | time unit). Performs 'subtraction' of this value from value property

fromPattern (optional) accepts combination of date | time units arranged in a string pattern (see Date Time Patterns), used to identify date | time element(s) in value property, this pattern is then used to create 'date' | 'time' | 'datetime'

const result = await User.find({
    select: [
        'userId',
        {
            date: {
                value: '#march_10th@24',
                fromPattern: '%M_%D@y',
                as: 'dateCreated'              
            }
        }
    ]
})

// output: 2024-03-10

cast (optional) used to 'convert' or 'cast' string from value property to the specified type / format. It accepts either of the values 'char' | 'nchar' | 'date' | 'dateTime' | 'signed' | 'unsigned' | 'decimal' | 'binary'

decrypt (optional) is an object with properties secret, iv (used with CBC mode) and sha used to decrypt value property. Overrides configuration(s) provided in local and global encryption (see encryption for details)

Please note: mode of encryption can only be set inside the encryption configuration of either findObj or model class and not inside decrypt

format (optional) is used to format date in value property to match the desired combination of date time patterns (see Date Time Patterns)

const result = await User.find({
    select: [
        'userId',
        {
            date: {
                value: 'joiningDate',
                format: '%M %D, %Y',                
            }
        }
    ]
})

// output: 'Month name' 'Day of the month (with suffix: 1st, 2nd, 3rd...)', 'Full Year (4-digits)'

as (optional) renames/provides local reference name to the value property

compare (optional) used to compare the value returned by this wrapper method using comparators

Date Time Patterns

Date Time Patterns can be used with format and fromPattern properties of date wrapper but not with add and sub property. Below mentioned date time patterns (in any desired combination), along with white space ' ' or allowed special characters ('$', '@', '#', ',', '-', '_', '/') can be used to:

• Recognize parts of date within a regular string inside value property of date wrapper and can generate a valid date from it
• Reformat the date inside the value property of the date wrapper into any desired format

| Pattern | Description | | :-----: | ---------------------------------------------------------------------------- | | %a | Abbreviated weekday name (Sun to Sat) | | %b | Abbreviated month name (Jan to Dec) | | %c | Numeric month name (0 to 12) | | %D | Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...) | | %d | Day of the month as a numeric value (01 to 31) | | %e | Day of the month as a numeric value (0 to 31) | | %f | Microseconds (000000 to 999999) | | %H | Hour (00 to 23) | | %h | Hour (00 to 12) | | %I | Hour (00 to 12) | | %i | Minutes (00 to 59) | | %j | Day of the year (001 to 366) | | %k | Hour (0 to 23) | | %l | Hour (1 to 12) | | %M | Month name in full (January to December) | | %m | Month name as a numeric value (00 to 12) | | %p | AM or PM | | %r | Time in 12 hour AM or PM format (hh:mm:ss AM/PM) | | %S | Seconds (00 to 59) | | %s | Seconds (00 to 59) | | %T | Time in 24 hour format (hh:mm:ss) | | %U | Week where Sunday is the 1st day of the week (00 to 53) | | %u | Week where Monday is the 1st day of the week (00 to 53) | | %V | Week where Sunday is the 1st day of the week (01 to 53). Used with %X | | %v | Week where Monday is the 1st day of the week (01 to 53). Used with %x | | %W | Weekday name in full (Sunday to Saturday) | | %w | Day of the week where Sunday=0 and Saturday=6 | | %X | Year for the week (Sunday being 1st day of the week). Used with %V | | %x | Year for the week (Monday being 1st day of the week). Used with %v | | %Y | Year (4-digit) | | %y | Year (2-digit) |

Date Time Units

Below date time units are only usable with add and sub property of date wrapper method and not with format and fromPattern property

| Keyword | Unit | | :-----: | ------------------ | | f | MICROSECOND | | s | SECOND | | i | MINUTE | | h | HOUR | | d | DAY | | w | WEEK | | m | MONTH | | q | QUARTER | | y | YEAR | | smi | SECOND_MICROSECOND | | mmi | MINUTE_MICROSECOND | | ms | MINUTE_SECOND | | hmi | HOUR_MICROSECOND | | hs | HOUR_SECOND | | hm | HOUR_MINUTE | | dmi | DAY_MICROSECOND | | ds | DAY_SECOND | | dm | DAY_MINUTE | | dh | DAY_HOUR | | yM | YEAR_MONTH |

And / Or wrapper

and wrapper* (keyword and) | or wrapper (keyword or) both are similar in interface as both accepts array of objects. The only difference in the two is that and wrapper joins each immediate child condition using 'and' clause (junction) whereas, or wrapper joins each immediate child condition using 'or' clause (junction). Both can be used / nested inside where and having properties only and not (directly) in select property

const result = await User.find({
    where: { 
        and: [
            { userId: 55 },
            { department: 'sales' }
        ],
        or: [
            { userStatus: 0 },
            { userStatus: 2 },
        ]
     }
})

Explanation: In the above sample, 'userId', 'department' and 'userStatus' represents columns in user table. Here, 'conditions' to check 'userId' and 'department' (inside and array) will be using 'and' clause whereas, the two 'conditions' to check 'userStatus' (inside or array) will be connected using 'or' clause Please note:

1. and | or wrappers directly cannot be used inside select property however, they can be used in-directly withing json | array | refer wrappers
2. Since, junction is not provided hence conditions inside and and or clause will be using the default value 'and' to connect with each other
3. and and or clause can also be nested in any fashion as desired

If wrapper

If wrapper (keyword if) has a check property that accepts a conditional object to compare two values and returns either true or false. If the check property returns true, the value in trueValue property is returned by this wrapper, if check is false then the value in falseValue property is returned. as (optional) is used to provide a local reference name to the value returned by if wrapper method. Below is the interface for the if wrapper:

const result = await User.find({
    select: [
        { 
            if: {
                check: {},
                trueValue: null,
                falseValue: null,
                as: null
            }
        }
    ]
})

Case wrapper

Case wrapper (keyword case) is similar to if wrapper as it is also used to check the conditions provided inside check property and return respective value. However, a major difference here is that if wrapper is used to check 'single condition' whereas case wrapper is used when you have 'multiple condition' and corresponding value pairs. check property accepts an array of object(s), each object consists of exactly two key: value pairs, where key is when property that accepts object to check the condition and then property that holds the respective value (for each when property) to be returned when the condition is true. Below is the interface for the if wrapper:

const result = await User.find({
    select: [
        { 
            case: {
                check: [{ when: {}, then: null }],
                else: null,
                as: null
            }
        }
    ]
})

Sum wrapper

Sum wrapper (keyword sum) is used to calculate 'sum' of a set (group) of records. This is and aggregate method hence it will be applied not to single but group of records. It can be used / nested only inside select and having parameters, and not with where clause as a value. Below is the interface for this wrapper method along with the default values for each of its properties:

{
    sum: {
        value:'',
        cast: null,
        compare: {},
        as: null
    }
}
const result = await User.find({
    select: [
        { sum: {
            value: 'salary',
            cast: 'signed',
            as: 'totalSalary'
            }
        }
    ],
    groupBy: ['department'],
    having: {
        sum: { 
            value: 'salary',
            compare: {
                gt: 5000
            }
         }
    }
})

Explanation: In the above sample, 'salary' and 'department' represents columns in user table. Here, inside select property, we are calculating sum of salaries, since we have used groupBy to group records using 'department', sum of salaries from each 'department' will be calculated are returned with the local reference name 'totalSalary', then we are filtering to fetch all records only when 'totalSalary' is greater than 5000 Please note:

1. cast is used for type casting of value property into desired type
2. compare property is available when sum is used inside having and not available when it is being used inside select clause
3. as property is available when this wrapper is used inside select and not available when it is being used inside having clause
4. value can either accept either a column name or number value or an object (simple or nested) as its value

Average wrapper

Average wrapper (keyword avg) is used to calculate 'average' of a set (group) of records. This is and aggregate method hence it will be applied not to single but group of records. It can be used / nested only inside select and having parameters, and not with where clause as a value. Below is the interface for this wrapper method along with the default values for each of its properties:

const result = await User.find({
    select: [
        { avg: {
            value: 'salary',
            cast: 'unsigned',
            as: 'averageSalary'
            }
        }
    ],
    groupBy: ['department'],
    having: {
        avg: { 
            value: 'salary',
            compare: {
                gt: 5000
            }
         }
    }
})

Explanation: In the above sample, 'salary' and 'department' represents columns in user table. Here, inside select property, we are calculating average of salaries, since we have used groupBy to group records using 'department', average of salaries from each 'department' will be calculated are returned with the local reference name 'averageSalary', then we are filtering to fetch all records only when 'averageSalary' is greater than 5000 Please note:

1. compare property is available when this wrapper is used inside having and not available when it is being used inside select clause
2. as property is available when this wrapper is used inside select and not available when it is being used inside having clause
3. value can either accept either a column name or number value or an object (simple or nested) as its value

Minimum wrapper

Minimum wrapper (keyword min) is used to calculate 'minimum' among a set (group) of records. This is and aggregate method hence it will be applied not to single but group of records. It can be used / nested only inside select and having parameters, and not with where clause as a value. Below is the interface for this wrapper method along with the default values for each of its properties:

const result = await User.find({
    select: [
        { min: {
            value: 'salary'
            cast: 'unsigned',
            as: 'minSalary'
            }
        }
    ],
    groupBy: ['department'],
    having: {
        min: { 
            value: 'salary',
            compare: {
                gt: 5000
            }
         }
    }
})

Explanation: In the above sample, 'salary' and 'department' represents columns in user table. Here, inside select property, we are calculating minimum of salaries, since we have used groupBy to group records using 'department', minimum salaries from each 'department' will be calculated are r