Repository files navigation

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

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
   • Raw Query 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 SQL Json Datatype
    • How Encryption/Decryption works in UnSQL?

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

Beyondversion 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 theimport /require of'unsql' in your existingmodel classes to legacy flag'unsql/legacy' as shown below:

// v1.x importconstUnSQL=require('unsql/legacy')// orimportUnSQLfrom'unsql/legacy'// v2.x importconst{ UnSQL}=require('unsql')// orimport{UnSQL}from'unsql'

Please note:Documentation for version v1.x is available onGitHub

With the release of:

• Minor Bug fixes code is more stable than before
• Performance enhancement query generation is now faster and more efficient

For fullrelease notes please visitthis

• 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
• Support connectionpool reuses connections
• Dynamic query generation perform CRUDs without writing SQL
• JSON as Response including execution success/failure acknowledgement andresult orerror
• 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 protect sensitive data natively without any third part package

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

1. Usingnpm

npm install unsql

Or

npm i unsql

Or

2. Usingyarn

yarn add unsql

1. MySQL:dialect: 'mysql' (default)

MySQLconnection or connectionpool (recommended) is required to connect to the MySQL database.mysql2 is the most commonly used package for this purpose. Make sure toaddmultipleStatements: true into yourmysql2createPool orcreateConnection method as shown below:

importmysqlfrom'mysql2/promise'exportconstpool=mysql.createPool({    ...namedPlaceholders:true,// (optional) required if using rawQuery with named placeholdersmultipleStatements:true// (optional) required if using Encryption/Decryption features})

2. PostgreSQL:dialect: 'postgresql'

With version v2.1.0, support forpostgresql has been added.pg is the most commonly used package to create connectionpool forpostgresql databases. Yourdb.service.js file should look like:

import{Pool}from'pg'exportconstpool=newPool({...})

3. SQLite:dialect: 'sqlite'

With version v2.1.0, support forSQLite has been added.sqlite andsqlite3 are required packages for establishingconnection. Yourdb.service.js file should look like:

constsqlite3=require('sqlite3').verbose()constdb=newsqlite3.Database('./databases/test.db',err=>{if(err)console.error('error while opening database',err)})module.exports={ db}

Please note:

1. These libraries are required to establish aconnection with the respected database(s). Configuration insidedb.service.js for eachdialect is different (as aforementioned)
2. Common configurations likehost,user,password,database,port are not mentioned above but required by them
3. Although,sqlite does not supportconnection pool, the connectiondb established here is used insidepool property ofconfig
4. './databases/test.db' directory mentioned is just a sample name and no directory will be created automatically

UnSQL usesclass-based approach, therefore first step is to create model class. Each table in your database is represented by a model class thatextends from theUnSQL base class and holdsconfig property specific to this model. These model classes are used to invoke various built-in methods to performCRUDs.

Below is the example for a model class using both CommonJS and ES6 module. Here, class namedUser, extending from theUnSQL base class, is defined inside theuser.class.js file. For explanation, MySQL connectionpool is used:

user.class.js (CommonJS)

//@ts-checkconst{ UnSQL}=require('unsql')// get connection pool from your db provider serviceconstpool=require('path/to/your/db/service')/** *@class User *@extends UnSQL */classUserextendsUnSQL{/**     * UnSQL config     *@type {UnSQL.config}     */staticconfig={table:'test_user',// (mandatory) replace this with your table name        pool,// provide 'db' instance here in 'sqlite' modesafeMode:true,devMode:false,dialect:'mysql'// (default) or 'postgresql' or 'sqlite'}}module.exports={ User}

user.class.js (ES6 Module)

//@ts-checkimport{UnSQL}from'unsql'// get connection pool from your db provider serviceimport{pool}from'path/to/your/db/service'/** *@class User *@extends UnSQL */exportclassUserextendsUnSQL{/**     * UnSQL config     *@type {UnSQL.config}     */staticconfig={table:'test_user',// (mandatory) replace this with your table name        pool,// provide 'db' instance here in 'sqlite' modesafeMode:true,devMode:false,dialect:'mysql'// (default) or 'postgresql' or 'sqlite'}}

config is astatic object that is used to defineglobal level configurations that are specific for all references of this model class. Below are the list of propertiesconfig accepts:

• table: (mandatory) accepts name of the table in the database,
• pool: (optional) accepts sql connectionpool (orsqlitedb connection) object,
• connection: (optional) accepts mysqlconnection object,
• safeMode: acceptsboolean value, helps avoiding accidentaldelete all due to missingwhere and (or)having property indelete method,
• devMode: acceptsboolean value, Enables/Disables features likeExport to/Import from another model,reset database table mapped to model
• dialect: defines sql type viz.'mysql','postgresql' or'sqlite' (defaults to'mysql')

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

UnSQL provides six (06)static methods to perform theCRUD operations via. model class as mentioned below:

find is a static, asynchronous method, that dynamically generatesselect query, to read/retrieve record(s) from the mapped database table. It accepts object (optional) as its parameter with various properties (optional).find method always returns a JSON object with execution success/failure acknowledgement via.success property (beingtrue on success andfalse on failure) andresult (Array of record(s)) orerror (detailed error object) depending upon query was successful or not.find method combinesfindAll andfindOne into one single method, asfindOne method (in every other library/ORM) is just a wrapper aroundfindAll and grabs the first matching record.find method along with all its parameters with their default values is shown below:

constresponse=awaitUser.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 response = await User.find({ }) And // 2. Not passing any parameter at all const response = await User.find()*//*1. When successfulresponse = {    success: true,    result: [...] or [] (when no data found),}2. When error is encounteredresponse = {    success: false,    error: ErrorObject (containing error code, message, sql, trace)}*/

Each of the aforementioned properties / parameters are explained below:selector

alias is an important parameter throughoutUnSQL, it accepts string as value that defines local reference name of the table. It iscontext sensitive, meaning, it always refers to the immediate parent table (Here it refers to the parent model class). This parameter plays an important role as it helps identify the columns being referred to in any property (e.g,select orwhere orhaving orjoin etc) when using sub-query type wrappers orjoin.

constresponse=awaitUser.find({alias:'t1'})

select accepts an array of values like column name(s), string value, boolean, number, wrapper methods (Seewrapper 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 ofselect property:

constresponse=awaitUser.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 parameter accepts an array ofjoin object(s). Eachjoin object represents an association of a childtable with the immediate parent table, on the bases of acommon column, reference of which is provided insideusing property. Join object along with its default values is explained below:

constresponse=awaitUser.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 infind method (Seeselect), 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 insidefind method, this property provides local reference name to the associated (child) table. This property iscontext sensitive hencealias property defined inside this join object will override the defaultalias being prefixed to the column name(s) and any column name from parent table needs to be specifically prefixed with parentalias value (if column name(s) are ambiguous). Untilas property is set inside join object,alias is also used to refer the values from the associated (child) table outside the join object. (Also seealias for more details on alias)

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

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

junction (optional) defines the clause that will be used to connect different conditions inside thewhere |having property inside this join object. Similar tojunction infind method (Seejunction for details),

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

having (optional) allows to perform comparison on the group of records from associated (child) table (Seehaving 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 andchildColumn 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 inselect,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 ofusing property in the format of{ parentColumn: childColumn }.
2. While using multiple join objects, it is recommended to set appropriate (and unique) values to thealias property on both the parent as well as child tables.
3. It ismandatory to setas property while usingselect and (or) any other filtering properties viz.where andhaving property in case.

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 akey: value pair format, wherekey andvalue 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) (seewrapper methods). Sample where property is show below:

constresponse=awaitUser.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#) (Seethis for details on column name vs string value)

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

constresponse=awaitUser.find({where:{...},junction:'and'})

Please note:junction property only works withwhere andhaving parameters, and settingjunction parameter alone will have no effect.

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 thealias (oras) property is set inside thejoin object context, then that column name is required to beprefixed with its respectivealias (oras) property value and a'.' symbol connecting them.

// Example 1: when grouping records using a column (here 'role') from the parent tableconstresult1=awaitUser.find({groupBy:['userRole']})// Example 2: When grouping records using a column (here 'city') from the associated (child) tableconstresult2=awaitUser.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 associationconstresult3=awaitUser.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 beingprefixed with thealias 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 theas parameter, hence to refer any column from this association, this local reference needs to beprefixed to the column name using a'.' symbol

Please note:

1. In example 1, if the column belongs to the parent table, alias asprefix is note required asUnSQL 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 toprefix the column names withalias oras prefixes.

having property is similar towhere property, as it also helpsfiltering 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 likewhere property however, the major difference between that two properties is thathaving property can also perform comparisons usingaggregate methods such assum,avg,min,max etc. on thegrouped records (usinggroupBy property), which is not possible with thewhere property. Below is an example of filtering withhaving property andgroupBy property usingsum aggregate (wrapper) method

constresponse=awaitUser.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 withinhaving property.

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 inkey: value pair format, where in each pair thekey represents the name of the column in the database table and thevalue is one of the two values i.e.'asc' (ascending order) or'desc' (descending order)

// Example 1:constresult1=awaitUser.find({orderBy:{firstName:'desc'}})// Example 2:constresult2=awaitUser.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 as the name suggests, this property limits the no. of records that will be fetched from the database table, default isnull hence no limit is applied and all records are fetched.

constfound=awaitUser.find({limit:10})

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

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

constfound=awaitUser.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 is one of the most important properties, it is used to defineEncryption/Decryption related configurations such asmode,secret,iv andsha. Theselocal configuration(s) will overrideglobalencryption property (seeglobal 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):

constresponse=awaitUser.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 insideencryption 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 encryptionmode, it is required to setmultipleStatements: true inside yourcreateConnection orcreatePool configuration.

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 (provided bySessionManager) enablesUnSQL to chain multiple query executions andre-use one transaction across these queries androllback (in case of error) orcommit all changes at once using thissession/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 Managerconstsession=newSessionManager(pool)// invoke transaction by calling transaction lifecycle method provided by sessionawaitsession.init()// your code goes here...constuserResponse=awaitUser.save({data:userInfo, session})// pass session inside query to chain this queryaddressInfo.userId=userResponse.insertId// patch auto generated 'userId' to addressInfoconstaddressResponse=awaitAddress.save({data:addressInfo, session})// pass session inside query to chain this query//  handle if error is encounteredif(!userInfo.success||!addressInfo.success){// rollback all prior changes if error is encounteredawaitsession.rollback()return}// finally commit all changes if no errors encounteredawaitsession.commit()})

Please note:

1. SessionManager takes in another optional parameter viz.'mysql' (default),'postgresql' or'sqlite'
2. Passingfalse as parameter forrollback andcommit methods will allow you to perform their respective actions (at multiple locations)without closing thetransaction and destroying the session
3. rollback andcommit can be called at any position and it will eitherrollback/commit all proceeding changes till that position

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 isenabled ordisabled, 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 tofalse.

save is a static, asynchronous method. It dynamically generates valid SQL query, thatinsert |update |upsert data (single or in bulk) into the database table When onlydata property is set, this methodinserts record(s), whendata along withwhere orhaving (withgroupBy) is set, itupdates record(s), whendata andupsert are set, itupsert record.save method takes in an object as its parameter with various properties as mentioned below:

constresponse=awaitUser.save({alias:undefined,    data,where:{},junction:'and',groupBy:[],having:{},upsert:{},encrypt:{},encryption:{},debug:false,session:undefined})/* When successful1. MySQL returnsresponse = {    success: true,    result: {        "fieldCount": 0,        "affectedRows": 1, // number of records inserted/updated        "insertId": 1,  // dynamically generated primary key (only auto_increment id) of the first record inserted in this query, else zero '0'        "info": "",        "serverStatus": 2,        "warningStatus": 0,        "changedRows": 0    }}2. PostgreSQL returnsresponse = {    "success": true,    "result": [{...}] // record (with primary key ID) that was recently added}3. Sqlite returnsresponse = {    success: true,    insertId: 1, // in case of 'save', last 'inserted' ID    changes: 1 // in case of 'update'}*/

Below are the explanations for each of these properties:

alias (optional) same as infind method (Seealias for details)

constresponse=awaitUser.save({ data,alias:'u'})

data (mandatory) is the actual data that will beinserted |updated |upsert into the database table.data can either be a single object (supportsinsert,update andupsert) or an array of objects (supports onlyinsert of bulk data).

// 1. insert single recordconstresponse=awaitUser.save({data:{firstName:'John',userEmail:'john.doe@example.com'}})// 2. insert bulk recordsconstresponse=awaitUser.save({data:[{firstName:'John',userEmail:'john.doe@example.com'},{firstName:'Jane',userEmail:'jane.doe@example.com'}...]})

where (optional) when condition(s) are provided, convertssave method frominsert mode intoupdate mode. Used to identify (filter) the record(s) to beupdated in the database table, based on the set condition(s) (Seewhere for more details)

// perform updateconstresponse=awaitUser.save({data:{...},where:{userId:1}})

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

constresponse=awaitUser.save({data:{...},where:{...},junction:'and'})

groupBy (optional) used withhaving property to group records, same as explained above (SeegroupBy for details)

constresponse=awaitUser.save({data:{...},groupBy:'userRole',having:{...}})

having (optional) similar towhere property,having also helps inupdating the record(s) in the database (Seehaving for more details)

constresponse=awaitUser.save({data:{...},groupBy:'department',having:{{sum:{value:'salary',compare:{gt:50000}}}}})

upsert (optional) accepts single object value, when provided, switchessave method intoupsert 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 isupdated using the values provided in this property, else this property is ignored

constresponse=awaitUser.save({data:{...},upsert:{name:'john',        ...}})

encrypt (optional) holds information regarding thecolumns that needs to beencrypted and stored in the database. It accepts object inkey: value format where eachkey represents thecolumn name andvalue again is an object with three as key(s)secret,iv andsha

constresponse=awaitUser.save({data:{...},encrypt:{userEmail:{secret:'someSecret',sha:512}}})

Explanation: Above sample will encrypt'userEmail' column insidedata property using the encryption configurationssecret andsha provided as value object.

encryption (optional) holds local level configurations such asmode,secret,iv andsha that can be used for encrypting columns from thedata property, that are specified inside theencrypt property (Seeencryption for more details)

constresponse=awaitUser.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 theencryption property and the value for the column name is an empty object insideencrypt property. Hence, the configurations fromencryption property will be used

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

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

constresponse=awaitUser.save({data:{...},debug:'query'})

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:

constresponse=awaitUser.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 infind method (Seealias for details)

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

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

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

having (optional) similar towhere propertyhaving also helps infiltering the record(d) in the database that needs to beremoved/deleted (Seehaving for more details)

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

debug (optional) enables variousdebug modes (Seedebug for more details)

session (optional) enablesUnSQL tore-use session transaction provided bySessionManager across multiple executions

rawQuery method allows you toexecute raw SQL queries directly. This method is useful when you enjoy writing custom SQL queries or require any specific type of query that you are not able to figure out via. built-in methods. This method supports both type of placeholders:positional placeholders?? |? andnamed placeholders:variableName, along with dynamicallyprepared statement,debug modes, and also can be chained with other built-in (orrawQuery) methods usingsession (seeSessionManager). Below are the samples for both type of placeholders:

1. Positional Placeholders: Expectsvalues to be an array['tableName', 'columnName', 'value' [, ...]] that will be used toprepare statement by replacing positional placeholders. Here?? is used fortable/column names and? is used forvalue. The sequence of placeholders and value invalues array must be same

constresponse=awaitUser.rawQuery({query:'SELECT * FROM ?? WHERE ?? = ?',values:['users','userId',1],debug:true});

1. Named Placeholders: Expectsvalues to be an object{ variableName: value [, ...] } wherevariableName key must match the:variableName used in the query, this can be used to replace only thevalue(s) and does not support table/column name. In order to use named placeholders, setnamedPlaceholders: true insidecreatePool |createConnection method configuration

constresponse=awaitUser.rawQuery({query:'SELECT * FROM users WHERE userId = :userId',values:{userId:1},debug:true});

export is a static, asynchronous method. As the name suggests, it is used to export record(s) from the database table into a dynamically generatedjson file (with same name astable property insideconfig property), by default inside'exports_unsql' directory. Record(s) can be filtered usingwhere property and even columns can also be restricted using theselect property.This method only works whendevMode insideconfig property is set totrue. 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:

awaitUser.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 validUnSQL model class, to export record(d) into. Defaults to thename of thetable 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 (seeselect for details)

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

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

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

orderBy (optional) re-order record(s) by column name(s) to be exported (seegroup 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 (seeDebug for details)

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 theauto increment Id to zero (0).This method only works whendevMode is set to true andsafeMode is set tofalse.export method takes in an object as its parameter with only one (optional) propertydebug (seedebug for details) as mentioned below:

awaitUser.reset({debug:false})

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

UnSQL provides variousbuilt-in methods to interact with data and perform specific tasks, each of these wrapper methods belong a certaintype. All of the wrapper methods have object like interface (key: value pair) to interact with them, wherekey 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 insideUnSQL:

Please note:

1. junction type wrapper methods can only be used insidewhere andhaving property
2. aggregate type wrapper methods can only be used insideselect andhaving property and not withwhere property

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

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

constresponse=awaitUser.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 ofstring 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 propertiestarget andwith, 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 thetarget string

reverse (optional) accepts boolean value, if set totrue, reverses the order of the characters invalue property

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

padding (optional) accepts object with two propertiesleft andright. Each property further accepts an object with exactly two propertiespattern (used to fill empty spaces) andlength (defines minimum number of characters to be maintained in thevalue property)

substr (optional) accepts object with two propertiesstart (defines starting index) andlength (number of characters in substring)

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

cast (optional) converts/castsvalue property to the specifiedtype /format using either of the values'char' |'nchar' |'date' |'dateTime' |'signed' |'unsigned' |'decimal' |'binary' |'integer'

decrypt (optional) is an object with propertiessecret,iv (used with CBC mode whendialect: 'mysql') andsha used to decryptvalue property. Overrides configuration(s) provided in local and globalencryption (seeencryption for details)

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

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

Numeric wrapper (keywordnum) is used to perform mathematical operations on the numeric data, it can be used / nested insideselect,where,having clause as avalue. All the operations are executed sequentially, in order that followsBODMAS rule. Below is the interface for this wrapper method along with the default values for each of its properties:

constresponse=awaitUser.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 ofnumeric 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 onvalue property

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

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

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

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

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

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

decrypt (optional) is an object with propertiessecret,iv (used with CBC mode) andsha used to decryptvalue property. Overrides configuration(s) provided in local and globalencryption (seeencryption for details)

Please note:mode of encryption can only be set inside theencryption configuration of eitherfindObj ormodel class and not insidedecrypt

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

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

Date wrapper (keyworddate) is used to perform date related operations onvalue property, it can be used nested insideselect,where,having clause as avalue. Below is the interface for this wrapper method along with the default values for each of its properties:

constresponse=awaitUser.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 ofdate 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 withdate |time unit). Performs 'addition' of this value tovalue property

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

fromPattern (optional) accepts combination ofdate |time units arranged in a string pattern (seeDate Time Patterns), used to identifydate |time element(s) invalue property, this pattern is then used to create'date' |'time' |'datetime'

constresponse=awaitUser.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 fromvalue property to the specifiedtype /format. It accepts either of the values'char' |'nchar' |'date' |'dateTime' |'signed' |'unsigned' |'decimal' |'binary'

decrypt (optional) is an object with propertiessecret,iv (used with CBC mode) andsha used to decryptvalue property. Overrides configuration(s) provided in local and globalencryption (seeencryption for details)

Please note:mode of encryption can only be set inside theencryption configuration of eitherfindObj ormodel class and not insidedecrypt

format (optional) is used toformatdate invalue property to match the desired combination of date time patterns (seeDate Time Patterns)

constresponse=awaitUser.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 thevalue property

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

Date Time Patterns can be used withformat andfromPattern properties ofdate wrapper but not withadd andsub property. Below mentioneddate 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 insidevalue property ofdate wrapper and can generate a valid date from it
• Reformat the date inside thevalue property of thedate wrapper into any desired format

Belowdate time units are only usable withadd andsub property ofdate wrapper method and not withformat andfromPattern property

and wrapper* (keywordand) |or wrapper (keywordor) both are similar in interface as both accepts array of objects. The only difference in the two is thatand 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 insidewhere andhaving properties only and not (directly) inselect property

constresponse=awaitUser.find({where:{and:[{userId:55},{department:'sales'}],or:[{userStatus:0},{userStatus:2},]}})

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

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

If wrapper (keywordif) has acheck property that accepts a conditional object to compare two values and returns eithertrue orfalse. If thecheck property returnstrue, the value intrueValue property is returned by this wrapper, ifcheck isfalse then the value infalseValue property is returned.as(optional) is used to provide a local reference name to the value returned byif wrapper method. Below is the interface for theif wrapper:

constresponse=awaitUser.find({select:[{if:{check:{},trueValue:null,falseValue:null,as:null}}]})

Case wrapper (keywordcase) is similar toif wrapper as it is also used to check the conditions provided insidecheck property and return respective value. However, a major difference here is thatif wrapper is used to check'single condition' whereascase 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 twokey: value pairs, wherekey iswhen property that accepts object to check the condition andthen property that holds the respective value (for eachwhen property) to be returned when the condition istrue. Below is the interface for theif wrapper:

constresponse=awaitUser.find({select:[{case:{check:[{when:{},then:null}],else:null,as:null}}]})

Sum wrapper (keywordsum) is used to calculate 'sum' of a set (group) of records. This is an aggregate method hence it will be applied not to single but group of records. It can be used / nested only insideselect andhaving parameters, and not withwhere clause as avalue. 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}}constresponse=awaitUser.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 inuser table. Here, insideselect property, we are calculating sum of salaries, since we have usedgroupBy 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 5000Please note:

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

Average wrapper (keywordavg) is used to calculate 'average' of a set (group) of records. This is an aggregate method hence it will be applied not to single but group of records. It can be used / nested only insideselect andhaving parameters, and not withwhere clause as avalue. Below is the interface for this wrapper method along with the default values for each of its properties:

constresponse=awaitUser.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 inuser table. Here, insideselect property, we are calculatingaverage of salaries, since we have usedgroupBy 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 5000Please note:

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

Count wrapper (keywordcount) is used to calculate 'count' among a set (group) of records. This is an aggregate method hence it will be applied not to single but group of records. It can be used / nested only insideselect andhaving parameters, and not withwhere clause as avalue. Below is the interface for this wrapper method along with the default values for each of its properties:

constresponse=awaitUser.find({select:[{count:{value:{userStatus:1},as:'activeUsers'}}]})

Minimum wrapper (keywordmin) is used to calculate 'minimum' among a set (group) of records. This is an aggregate method hence it will be applied not to single but group of records. It can be used / nested only insideselect andhaving parameters, and not withwhere clause as avalue. Below is the interface for this wrapper method along with the default values for each of its properties:

constresponse=awaitUser.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 inuser table. Here, insideselect property, we are calculating minimum of salaries, since we have usedgroupBy to group records using'department', minimum salaries from each'department' will be calculated are returned with the local reference name'minSalary', then we are filtering to fetch all records only when 'minSalary' is greater than 5000Please note:

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

Maximum wrapper (keywordmax) is used to calculate 'maximum' among a set (group) of records. This is an aggregate method hence it will be applied not to single but group of records. It can be used / nested only insideselect andhaving parameters, and not withwhere clause as avalue. Below is the interface for this wrapper method along with the default values for each of its properties:

constresponse=awaitUser.find({select:[{max:{value:'salary'cast:'unsigned',as:'maxSalary'}}],groupBy:['department'],having:{max:{value:'salary',compare:{gt:5000}}}})

Explanation:In the above sample,'salary' and'department' represents columns inuser table. Here, insideselect property, we are calculating maximum of salaries, since we have usedgroupBy to group records using'department', maximum salaries from each'department' will be calculated are returned with the local reference name'maxSalary', then we are filtering to fetch all records only when 'maxSalary' is greater than 5000Please note:

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

Json wrapper (keywordjson) can be used toextract specific value from json Object (usingextract property) orcreate/attach json Object to record(s) by passing Object/Array invalue property, or even both. Below is the interface for this wrapper method along with the default values for each of its properties:

constresponse=awaitUser.find({alias:'u',select:[{json:{value:{},table:null,alias:null,join:[],where:{}groupBy:[],having:{},orderBy:{},limit:undefined,offset:undefined,as:null,// Defaults to 'json' (only inside select property) if no 'where' or 'having' property is setextract:null,compare:{}}}]})

Each of the properties is explained below:

value accepts an objectkey: value pair(s) as value.key being a string value,value can be either string value or a column or any of the UnSQL reserved constants (seereserved constants) or number or nested object

table (optional) reference to the child table from which the columns needs to be fetched

alias (optional) provides local reference to the child table, seealias

join (optional) used to associate another table, seejoin

where (optional) used to filter records, seewhere

groupBy (optional) groups record(s), seegroup by

having (optional) used to filter records, seehaving

orderBy (optional) re-orders record(s), seeorder by

limit (optional) limit record(s), seelimit

offset (optional) set starting index for record(s), seeoffset

as (optional) is used to rename the json object name, if not provided defaults to 'json'

extract (optional) available whencolumn name containing validjson object is passed invalue property, is used to extract a specified value from this json object. In order to extract any value inside nested json object, keys can be concatenated using. symbol

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

Please note:

1. Using alias is always a good practice but, if the column names inside the two referenced tables are not ambiguous then alias can be excluded
2. as property is available when this wrapper is used insidehaving and not available when it is being used insideselect clause
3. value can either accept either a column name or number value or an object (simple or nested) as its value

Array wrapper (keywordarray) can be used toextract specific value from json Array (usingextract property) orcreate/attach json Array to record(s) by passing Object/Array invalue property, or even both. This is similar tojson wrapper however, it can also be used to create an array with multiple json objects. Below is the interface for this wrapper method along with the default values for each of its properties:

constresponse=awaitUser.find({alias:'u',select:[{array:{value:[]||{},table:null,alias:null,join:[],where:{},groupBy:[],having:{},orderBy:{},limit:undefined,offset:undefined,as:null,// Defaults to 'array' (only inside select property) if no 'where' or 'having' property is setextract:null,compare:{}}}]})

Each of the properties is explained below:

value accepts an array of values or an object inkey: value pair format as value.key being a string ,value can be either string value or a column name or number or nested object

table (optional) reference to the child table from which the columns needs to be fetched

alias (optional) provides local reference to the child table, seealias

join (optional) used to associate another table, seejoin

where (optional) used to filter records, seewhere

groupBy (optional) groups record(s), seegroup by

having (optional) used to filter records, seehaving

orderBy (optional) re-orders record(s), seeorder by

limit (optional) limit record(s), seelimit

offset (optional) set starting index for record(s), seeoffset

as (optional) is used to rename the json object name, if not provided defaults to 'json'

extract (optional) available whencolumn name containing validjson object is passed invalue property, is used to extract a specified value from this json object. In order to extract any value inside nested json object, keys can be concatenated using. symbol

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

Please note:

1. Using alias is always a good practice but, if the column names inside the two referenced tables are not ambiguous then alias can be excluded

Refer wrapper (keywordrefer) is used to run'sub-query' to fetchsingle field from any specific record from anothertable. It can be used / nested only insideselect,where andhaving clause as avalue. This method is helpful to fetch 1 record in a one-to-one relation. Below is the interface for this wrapper method along with the default values for each of its properties:

constresponse=awaitUser.find({alias:'u',select:[{refer:{select:['*'],table:'table_name',alias:null,join:[],where:null,groupBy:[],having:[],orderBy:{},limit:undefined,offset:undefined,as:null}}]})

Each of the properties is explained below:

value accepts an array of values or an object inkey: value pair format as value.key being a string value,value can be either string value or a column name or number or nested object

table reference to the child table from which the columns needs to be fetched

alias (optional) provides local reference to the child table, seealias for details

join (optional) defines association of another table (as child), seejoin for details

where (optional) used to filter records, seewhere for details

groupBy (optional) used to group records, seegroupBy for details

having (optional) used to filter records with aggregate wrapper methods support, seehaving for details

orderBy (optional) used to re-order records, seeorderBy for details

limit (optional) used to limit no. of records, seelimit for details

offset (optional) used to change the starting index for the records to be fetched from, seeoffset for details

as (optional) is used to rename the json array name, if not provided defaults to 'array'

Please note: This wrapper method is very important as it similar to actualfind method insideUnSQL

Concat wrapper (Keywordconcat) is used to merge/combine multiple value(s)/columns together into one value. Accepts an array of value(s)/wrapper methods and merges them as one value usingpattern property as the separator between these values. Below is the interface for this wrapper method along with the default values for each of its properties:

constresponse=awaitUser.find({select:[{concat:{value:[],pattern:'',as:null,compare:null}}]})

Each of the properties is explained below:

value accepts an array of values, these values are similar toselect (seeselect) property offind method (seefind)

pattern used to connect values in thevalue property. Default is''

as (optional) is used to provide local reference name to the value returned by this wrapper method

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

comparator as the name suggests are used to compare two values. They have a layer of nested objectkey: { comparator: value } pair format like interface where thekey is compared with thevalue based on thecomparator used.key andvalue can be either string value or column name or number or boolean or any of the built-in wrapper methods.UnSQL provides various types ofcomparator as mentioned below:

SessionManager is a class that can be used to create an instance of asession object, which provides variousasynchronous methods (as an interface) to manage the lifecycle of a persistent (reusable) instance of atransaction across multiple query executions.SessionManager becomes extremely important in cases where multiple inter-linked queries are executed in a chained fashion, one after the other and a mechanism to control all transactions at once if any one of them fails is required. Each lifecycle method is explained below:

Please note: Constructor requiresconnection orpool (recommended)

router.get('/users',async(req,res)=>{constresponse=awaitUser.find()// above code is similar to// const response = await User.find({ })})

router.get('/users/:userId(\\d+)',async(req,res)=>{const{ userId}=req.paramsconstresponse=awaitUser.find({where:{ userId}})// above code is similar/shorthand for:// 1.// const response = await User.find({//     where: { userId: userId }// })// 2.// const response = await User.find({//     where: { userId: { eq: userId }}// })})

router.post('/users/login',async(req,res)=>{const{ loginId, password}=req.bodyconstresponse=awaitUser.find({select:['userId','userEmail','firstName','lastName','userPassword'],where:{or:[{userEmail:`#${loginId}`},{userMob:`#${loginId}`}]}})// rest of the authentication logic goes here...})

router.post('/users/login',async(req,res)=>{const{ loginId, password}=req.bodyconstresponse=awaitUser.find({select:['userId','userEmail','firstName','lastName','userPassword'],where:{or:[{str:{value:'userEmail',decrypt:{secret:'#your_secret',iv:'#your_initialization_vector'}compare:{eq:`#${loginId}`},},},{userMob:`#${loginId}`}]},encryption:{mode:'aes-256-cbc'}})// rest of the authentication logic goes here...})

Explanation: Here,'userId','userEmail','firstName','lastName','userPassword' are the column in the database table.str wrapper is used toDecrypt'userEmail' column using thesecret andiv properties.Encryption mode is set to'aes-256-cbc' insideencryption property. After Decrypting'userEmail', its value is then compared with theloginId received in therequest body. Sincesecretiv andloginId are regular strings and not column names hence, they are prefixed with#.

router.get('/users',async(req,res)=>{constresponse=awaitUser.find({select:['userId','firstName',{array:{value:{orderId:'orderId',placedOn:'createdOn',amount:'amount'},table:'orders_placed',where:{userId:'userId'},limit:5,orderBy:{createdOn:'desc'},as:'order_history'}}]})})

router.get('/users',async(req,res)=>{constresponse=awaitUser.find({select:['userId','firstName',{array:{value:['#Jabalpur','#Delhi','#Pune'],extract:0as:'city'}}]})})// Output: city: 'Jabalpur'

router.get('/users',async(req,res)=>{constresponse=awaitUser.find({select:['userId','firstName',{array:{value:[{...}],extract:'[*].city'as:'city'}}]})})

Explanation: In the above sample,city will be extracted from all objects in the array

Please note:* can be replace by a number to fetch city name from a record at that specific index number, else it will returnnull if value is not found or no object is found at that index

router.get('/users',async(req,res)=>{constresponse=awaitUser.find({select:['userId','firstName',{json:{value:{ ...,address:{city: ...,state: ...}},extract:'address.city'as:'city'}}]})})

router.post('/users',async(req,res)=>{constdata=req.bodyconstresponse=awaitUser.save({ data})})

router.put('/users/:userId(\\d+)',async(req,res)=>{const{ userId}=req.paramsconstdata=req.bodyconstresponse=awaitUser.save({ data,where:{ userId}})})

router.post('/users',async(req,res)=>{const{ userId}=req.paramsconstdata=req.body// extract conflicting key (here 'userId') out of payload (data) and create a new object (here upsert) that holds remaining fields that needs to be updated on conflictconst{ userId, ...upsert}=dataconstresponse=awaitUser.save({ data, upsert})})

router.delete('/users/:userId(\\d+)',async(req,res)=>{const{ userId}=req.paramsconstresponse=awaitUser.delete({where:{ userId}})})

router.delete('/users/:userId(\\d+)',async(req,res)=>{const{ userId}=req.paramsconstresponse=awaitUser.delete({where:{department:['#salesInterns','#marketingInterns'],{date:{value:'joiningDate',compare:{eq:{date:{value:'currentDate',sub:'6m'}}}}}}})})

Explanation: This will remove all record(s) in the'salesInterns' and'marketInterns''department' having'joiningDate' 6 months (represented by'6m') earlier to this date.

Let's assume we are creating an order for 'items' inside user 'bucket':

import{SessionManager}from'unsql'import{pool}from'./path/to/your/db/service'// Other imports/initializations and code goes here...router.post('/orders',async(req,res)=>{// fetch 'userId' from path paramsconst{ userId}=req.params// extract 'data' from body inside request objectconstdata=req.body// create 'session' instance using 'SessionManager'constsession=newSessionManager(pool)// 'pool' or 'connection' is required// initiate 'transaction' using 'init' lifecycle methodconstinitResp=awaitsession.init()// handle if session init failedif(!initResp.success){returnres.status(400).json(initResp)}// fetch objects inside bucket, pass 'session' object to the query methodconstbucketResp=awaitBucket.find({where:{ userId}, session})// create order using 'data' and pass 'session' object to the query methodconstorderResp=awaitOrder.save({ data, session})// attach 'orderId' to each itemconstitems=bucketResp.result.map(item=>item.orderId=orderResp.insertId)// save order 'items' and pass 'session' object to the query methodconstitemsResp=awaitOrderItems.save({data:items, session})// clear bucket after successfully creating order and pass 'session' object to the query methodconstclearBucket=awaitBucket.delete({where:{ userId}, session})// handle if any (or all) query failedif(!bucketResp.success||!orderResp.success||!itemsResp.success){// rollback changesawaitsession.rollback()returnres.status(400).json({success:false,message:'Error while placing order!',error:bucketResp?.error||orderResp?.error||itemsResp?.error})}// commit changes if no errors were encounteredawaitsession.commit()returnres.status(201).json({success:true,message:'Order placed successfully!',orderId:orderResp.insertId})})

UnSQL can be imported using any of the following:

1. CommonJS import

const{ UnSQL}=require('unsql')

2. ES6 Module import

import{UnSQL}from'unsql'

Any string value thatstarts with a# is considered as astring value and any other string thatdoes not start with# is considered as acolumn name. This# is ignored while utilizing the actual string value. If your string value is some sort of code or any value that also has a# at the beginning then also an additional# as prefix is required else the# in your value will be ignored (e.g.'#someCode' is required to be written as'##someCode')

constresponse=awaitUser.find({select:['userId','firstName','lastName','#test']where:{firstName:'#Siddharth'}})

Explanation: In the above example,'userId','firstName' and'lastName' are the column names hence does not start with# on the other hand'test' and'Siddharth' are the string values hence contains# as prefix to differentiate them with column names.

Apart from built-in methods,UnSQL also has various built-inreserved constants (supported by SQL database) as mentioned below:

Yes UnSQL providesjson andarray wrappers to interact withSQL json datatype (jsonb for'postgresql').save method supports insertion ofdata containingjson Object/Array into SQLjson datatype (TEXT insqlite) column, UnSQL internallystringify the json data data before saving it.

• Siddharth Tiwari