Repository files navigation

UnSQL is a lightweight, open-source JavaScript library that facilitates class based, schemaless interactions with the structured databases viz.MySQL,PostgreSQL andSQLite through dynamic query generation. It is the only library that supports single codebase across all dialects. It is compatible withNodeJS based javascript runtime environments likeExpressJS,Fastify andNextJS, also works with serverless applications like AWS lambda (Through Lambda layers) and Vercel functions and can be used to create cross-platform apps using frameworks likeElectronJS.

1. Overview
   • 1.1 Breaking Changes
   • 1.2 What's new
   • 1.3 Key Features
2. Getting Started
   • 2.1 Prerequisites
   • 2.2 Installation
   • 2.3 Setup Guide
3. Built-in Query Methods
   • 3.1 Find Method
   • 3.2 Save Method
   • 3.3 Delete Method
   • 3.4 Raw Query Method
   • 3.5 Export Method
   • 3.6 Reset Method
4. Built-in Constants (Reserved Keywords) / Units / Wrapper Objects / Comparator Objects
   • 4.1 Constants (Reserved Keywords)
   • 4.2 Units
   • 4.3 Wrapper Objects
   • 4.4 Comparator Objects
5. Session Manager
6. Examples
7. FAQs

UnSQL simplifies working with structured databases by dynamically generating SQLs under the hood. It provides developer friendly interface while eliminating the complexities of SQL. UnSQL also utilizes placeholders and parameterized SQL statements to prevent SQL-injections.

With the release ofversion v2.0, UnSQL has been re-written from scratch to cater modern challenges, including enhanced security and including new features all while also keeping the interface clean and simple, improving the overall developer experience. If your project is still using version v1.x then it is recommended you switch yourimport/require from'unsql' to'unsql/legacy', as shown below:

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

Documentation for v1.x can be found on GitHub

Version v2.1 brought support forMultiple Dialects along withUnified codebase,Bug Fixes,Improved Code Suggestions, brought back therawQuery Method, enhancedSession Manager and better code optimization under the hood and much more

• Promise based interface with streamlined async/await support
• Schemaless eliminates boilerplate code and hectic to manage migrations
• Unified Codebase enables maintaining single codebase while switching between SQL dialects
• Class-based Models encapsulates configurations into clean interface
• Reuse connections supports connectionpool for better performance
• Dynamic query generation perform CRUDs without writing SQL
• Safer code prevents SQL-injections with placeholders and prepared statements
• JSON as Response including execution success/failure acknowledgement andresult andmeta (in case ofmysql andpostgresql) 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 suggestions
• 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 work with three differentdialect of SQL ('mysql','postgresql' and'sqlite'). Each of them require differentprerequisite setup which are utilized by UnSQL as a source of connectionpool as mentioned below:

• MySQL (default) (dialect: 'mysql')

  mysql2 is the most commonly used package to provided connectionpool to interact withMySQL database.

importmysql2from'mysql2/promise'exportconstpool=mysql2.createPool({host:'localhost',// or link to remote databasedatabase:'test_db',user:'your_username',password:'your_password',namedPlaceholders:true,// (optional) required if using rawQuery with named placeholdersmultipleStatements:true// (optional) required if using multiple statements in rawQuery})

• PostgreSQL (dialect: 'postgresql')

  pg is the package required to generate connectionpool

import{Pool}from'pg'exportconstpool=newPool({host:'localhost',database:'test_db',user:'your_username',password:'your_password'})

• SQLite (dialect: 'sqlite')

  Bothsqlite andsqlite3 packages are required to be installed in your project to interact with SQLite db.

importsqlite3from'sqlite3'import{open}from'sqlite'exportconstpool=(async()=>{try{returnawaitopen({filename:'./databases/test2.db',driver:sqlite3.Database})}catch(error){console.error('Error initializing database:',error)throwerror// Rethrow the error to be handled by the caller}})()

Please note:

1. Named placeholders and multiline statement settings are only required to be configured withMySQL
2. AlthoughSQLite provides connection reference (heredb), it is still used withpool property ofconfig

UnSQL can be installed using any of the package managers viz.npm oryarn orpnpm:

• Usingnpm

npm i unsql

• Usingyarn

yarn add unsql

• Usingpnpm

pnpm add unsql

Unsql uses class based approach hence, afterprerequisites andinstallation, next step is to createmodel classes. Each model is mapped to a database table andextends from theUnSQL base class and has astatic property namedconfig that holds all theconfigurations related to the respective model class. Below if the sample model class usingCommonJS andES6 Module:

• user.class.js (CommonJS)

//@ts-checkconst{ UnSQL}=require('unsql')// get connection pool from your db provider serviceconstpool=require('path/to/your/db/service')/** *@class *@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 *@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 property is theheart and soul of any model class, itholds all configurations related to the model class and is used throughoutquery generation and execution. It can also hold global level configurations related toEncryption/Decryption for that table so that you don't have to re-define them for each query.

Please note:

1. secret is thesecret key that is used to encrypt the data
2. iv andsha are only used whendialect is set to'mysql', aspostgresql sets upiv internally andsqlite does not have any built-in Encryption/Decryption methods
3. WhendbEncryptionMode is same asmode insideencryption property, inmysql dialect, an additionalinternal query that is used to set theblock_encryption_mode is skipped

Unsql provides variousstatic, asynchronous built-in methods as mentioned below:

Each of these methods are explained below:

find is a static, asynchronous method used to fetch record(s) from the database or add a dummy column(s) with static value(s) while execution. It can also perform several operations like re-order, filter, mutate or even Encryption/Decryption of record(s) while fetching. It can also combine multiple tables as child associations and retrieve record(s) from these tables combined. UnSQL has combined the features offindOne and findAll methods into onefind method, asfindOne (in other libraries) is just a wrapper aroundfindAll to fetch first returning record irrespective of the response set. Interface offind method along with its default properties is explained below:

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

Each of these properties is explained below:

• alias provides local reference name to the table. It is context sensitive hence when alias are defined in nested objects, each alias is by default attached to all columns inside that context, to use a different alias (from parent or child table), reference to that alias must be prefixed to that column name along with'.' symbol in between

• select is an array of values, each value can be column name, static string/number/boolean value, or any of the reserved keyword(s) or wrapper object(s). It is used to restrict the column(s) to be fetched from the database or create dummy column(s), or mutate any value (through wrapper object(s)) at execution

• join is an array of objects where each object represents association of a child table with this parent (model class) table. Below is the interface for join object, similar tofind:

  // Interface for each join object:{type:'',// (default '') 'left'/'right'/'inner'/'cross'/'fullOuter'alias:undefined,// local reference name to the child tabletable:null,// (required) table to associateselect:['*'],// columns to be fetchedjoin:[],// nest another association inside thiswhere:{},// filter record(s) based on condition(s)junction:'and',// connect condition(s) usinggroupBy:[],// group record(s) by column name(s)having:{},// filter record(s) based on condition(s) [including aggregate methods]orderBy:{},// re-arrange record based on column(s) in ascending or descending orderlimit:undefined,// limit no. of recordsoffset:undefined,// set the starting index for recordsusing:[],// (required) array of common column(s) or an object of { parentColumn: childColumn }as:null// required with 'select'/'where'/'having' properties takes priority over 'as' to refer columns from outside this object}// Sample:constresponse=awaitOrder.find({select:['orderId','createdOn',{json:{// creating json object using columns from associated tablevalue:{itemId:'itemId',// column from associated tablename:'itemName',// column from associated tablequantity:'quantity'// column from associated table},aggregate:true,// wrapping multiple objects inside arrayas:'items'}}],join:[{table:'order_items',using:['orderId']}]// ref. of join object})

  Please note:

  1. using property can accept array of column names or an object like{ parentColumn: childColumn } whereparentColumn is the column from parent table andchildColumn is the column from child table. Whenalias is passed, it is automatically patched to the respective column name
  2. When usingselect |where |having inside join,as is mandatory
  3. When bothalias andas is set,as will be used as prefix to refer column names from child tables outside join object context

  Below is the explanation for each of thesejoin types:

  • natural based on columns with the same name and datatype (automatically detected)
  • left considers all records in parent table and only matching records from child table
  • right considers all records in child table and only matching records from parent table (not supported bysqlite)
  • inner only matching rows based onusing column(s)
  • cross cartesian product of records in parent and child tables
  • fullOuter returns all records from both tables, regardless of matching condition (only supported bypostgresql)

• where filters record(s) to be fetched from the database based on the conditions provided as simple (or nested) objects inkey: value pairs, comparator methods, wrapper methods etc.

  // Sample:constresponse=awaitUser.find({where:{department:['#marketing','#sales'],joiningDate:{between:{gt:'2025-01-01',lt:'now'}},or:[{userStatus:1},{userStatus:2}]}})

• junction determines the connecting clause ('and' or'or') that will be used to connect conditions provided insidewhere andhaving properties. Defaults to'and'

• groupBy groups record(s) based on the column name(s) provided as an array

• having similar towhere, filter record(s) based on condition(s) the only difference is that it supportsaggregate object(s) (inwrapper objects)

• orderBy used to define the order in which record(s) are fetched

• limit limits the number of records to be fetched

• offset defines the starting index of the record(s) being fetched

• encryption defines configurations (similar toencryption insideconfig property) but limited to a specific execution (local level)

• debug enables various debug modes and prints to console: dynamically generated query (un-prepared and prepared statements), values to be injected, errors, benchmarks, based on the selected mode as explained below:

  Mode	Description
  'query'	logsprepared,un-prepared,values
  'error'	logs entire error object in the console
  'benchmark'	logs out the time taken to execute the query
  benchmark-query'	enables combination of'query' and'benchmark' modes
  benchmark-error'	enables combination of'error' and'benchmark' modes
  true	enables all three modes i.e.'query','error' and'benchmark'
  false	(default) disables all debug modes

• session reference ofSessionManager object, used tooverride the transaction/commit/rollback features to be controlledexternally

save is astatic, asynchronous method, used toinsert | update | upsert record(s) into the database. It caninsert | update single or even multiple records (in bulk) in single execution. It also supports data Encryption during this process. When onlydata property is set, this method operates ininsert mode, when along withdata, any or both ofwhere andhaving are also set, this method operates inupdate mode, and when along withdata,upsert property is set, this method operates inupsert mode. Interface along with default values for this method is shown 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'}*/

Each of these properties is explained below:

• alias same as explainedhere
• data (required) this is the actual data that will beinserted | updated into the database. It can either be asingle object (supportsinsert | update | upsert) or anarray of objects (supportsonly insert)
• where same as explainedhere, used to filter record(s) to be updated
• junction same as explainedhere
• groupBy same as explainedhere
• having same as explainedhere, used to filter record(s) to be updated
• encrypt acceptskey: value pair, wherekey can be column name andvalue is another object that holdsconfigurations likesecret,iv andsha that will be used to encrypt this column. When no properties are set i.e.value is set as{}, in such a case,configurations defined inencryption property (local or global) is used. This property helps encrypting different columns with differentsecret
• debug same as explainedhere
• encryption same as explainedhere
• session same as explainedhere

Please Note: InUpsert mode, whilemysql andpostgresql will only update the columns provided in theupsert object, withdialect: 'sqlite' if any existing column value is ignored in theupsert object, then that value will either be set tonull orpredefined default value will be assigned to that column, due to the native upsert behavior (INSERT OR REPLACE) ofSQLite

delete is astatic, asynchronous method, used to remove record(s) from the database.where andhaving properties are used tofilter record(s) that will be removed, if noconditions are provided inwhere and (or)having property, this method will remove all records in the database.safeMode property (when set totrue) in theconfig property of the model class helps prevent accidentaldelete all of the records. Interface for this method along with default values is shown below:

// Interface:{alias:undefined,where:{},junction:'and',groupBy:[],having:{},encryption:{},debug:false,session:undefined}// Sample:constresponse=awaitUser.delete({where:{joiningDate:{between:{gt:{date:{value:'now',sub:'1Y'}},lt:{date:{value:'now',sub:'6M'}}}},department:['sales','marketing'],userType:'intern'}})

Each of these properties is explained below:

• alias same as explainedhere
• where same as explainedhere, used to filter record(s) to be removed
• junction same as explainedhere
• having same as explainedhere, used to filter record(s) to be removed
• debug same as explainedhere
• encryption same as explainedhere
• session same as explainedhere

rawQuery method is the most powerful method among all, unlike other methods that are limited to the base mapping, this method is not tied to any particular table, but utilizes the connection pool to execute queries on that database itself. It is capable of executing any and all types of queries includingDDL, DML etc (Insqlite, setmethodType: 'exec'). It also supports execution of multiple SQL statements in one query. When multipleSELECT statements are executed (not supported bysqlite),result contains nested array one for eachSELECT statement.

Inmysql, usemultiQuery: true to enable execution of multiple SQL statements in single query

Forsqlite, UnSQL supports various types of methods (as mentioned below) that can be set manually, each method has specific capabilities:

It supports normal as well as parameterized (with placeholders) queries:

• Inmysql:
  • Positional placeholders:??,?,
  • Named placeholders::namedVariable,
  • user defined variables:@userVariable,
• Inpostgresql:
  • Positional placeholder:$1,$2,$3...
• Insqlite:
  • Positional placeholder:?,
  • Named placeholders::namedVariable or$namedVariable or@namedVariable,
  • Indexed placeholder:$1,$2,$3... or?1,?2,?3...

// Sample: (dialect: 'mysql')constresponse=awaitUser.rawQuery({// here user model is used just to utilize 'pool'sql:`CREATE TABLE IF NOT EXISTS users (            userId INT(11) PRIMARY KEY AUTO_INCREMENT,            firstName VARCHAR(45) DEFAULT NULL,            lastName VARCHAR(45) DEFAULT NULL,            email VARCHAR(255) UNIQUE DEFAULT NOT NULL,            password VARCHAR(255) DEFAULT NOT NULL,            createdOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP,            lastUpdatedOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,            status TINYINT(1) DEFAULT 1        );        CREATE TABLE IF NOT EXISTS order_history (            orderId INT(11) PRIMARY KEY AUTO_INCREMENT,            amount DECIMAL (10,2) DEFAULT 0.00,            coupon VARCHAR(45) DEFAULT NULL,            discount DECIMAL (10,2) DEFAULT 0.00,            createdOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP,            lastUpdatedOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,            status TINYINT(1) DEFAULT 0        );`,multiQuery:true// this enables multiple SQL statements in single query string, only for MySQL})

export is astatic, asynchronous method that works whendevMode: true is set inconfig, as it is used toexport record(s) from the database table eitherto a .json file orto another model class, depending upon the value set in thetarget property. Interface and default values of this method are shown below:

// Interface:{target:'table_name',directory:'exports_unsql',alias:undefined,select:['*'],join:[],where:{},groupBy:[],having:{},orderBy:{},limit:undefined,offset:undefined,mode:'append',encrypt:undefined,encryption:undefined,debug:false}// Sample: Export to file (this will export all columns to '.json' file)constresponse=awaitUser.export()// Sample: Export to model (limited columns to be exported)constresponse=awaitUser.export({select:['firstName','lastName','email','password','department','salary'],target:User2// another model (can be inside any database)})

Each of these properties are explained below:

• target plays an important role, as it determines if the records will be exported.to a json file or toanother model class. It defaults to thetable name property insideconfig of the respective model class
  • When set to a string value, record(s) will be exported to a.json file with that exact name,
  • When another model class reference is passed as value, record(s) are exported (inserted in) to that model class
• directory determines the name of the folder that will be created (dynamically) to store the dynamically created .json file
• alias same as explainedhere
• select restricts the column(s) to be exported, also used to mutate values while exporting them including Decryption etc. Same as explainedhere
• join used to associate another table to fetch record(s) together with this table while exporting. Same as explainedhere, used to filter record(s) to be removed
• where filter record(s) to be exported. Same as explainedhere, used to filter record(s) to be removed
• junction same as explainedhere
• groupBy same as explainedhere
• having filter record(s) to be exported. Same as explainedhere, used to filter record(s) to be removed
• orderBy same as explainedhere
• limit limits the number of record(s) to be exported, Same as explainedhere
• offset defines the starting index for the record(s) to be exported. Same as explainedhere
• mode (works when exporting to a json file) when the export is executed and the file already contains data, this property determines whether tooverride orappend the contents to the file
• encrypt encrypts the columns mentioned as key in this object during export. Same as explainedhere
• encryption same as explainedhere
• debug same as explainedhere

reset is astatic, asynchronous method used toclear all record(s) in the model class and alsoreset the auto increment ID (if any) to their initial state. This only works whendevMode: true andsafeMode: false inconfig. This only expects one propertydebug in its parameter object. Interface is shown below:

constresponse=awaitUser.reset({debug:false})

UnSQL has various Constants (Reserved Keywords), Units (Date/Time), Wrapper Objects and Comparator Objects. Each of them are explained below:

UnSQL supports various built-inconstants (supported by SQL) as mentioned below:

UnSQL supports variousDate / Time Patterns and Units for all sql dialects since the units and the format varies for each, unsql provides a unified symbols that are standard for all:

• When usingformat |fromPattern (not supported bysqlite) property:

  Keyword	Description
  d	Day of the month, single digit (e.g., 1, 2, ..., 31)
  dd	Day of the month, double digit (e.g., 01, 02, ..., 31)
  D	Day of the month with ordinal suffix (e.g., 1st, 2nd)
  dy	Abbreviated day of the week (e.g., Sun, Mon)
  Dy	Full day of the week (e.g., Sunday, Monday)
  dow	Day of week as number (0-6) Sunday = 0
  doy	Day of year (001-366)
  M	Month as a single digit (e.g., 1, 2, ..., 12)
  MM	Month as a double digit (e.g., 01, 02, ..., 12)
  Mon	Abbreviated month name (e.g., Jan, Feb)
  MON	Full month name (e.g., January, February)
  y	Year, two digits (e.g., 24, 25)
  Y	Year, four digits (e.g., 2024, 2025)
  H	Hour (0-23), single digit
  HH	Hour (00-23), double digit
  h	Hour (1-12), single digit
  hh	Hour (01-12), double digit
  m	Minute (0-59), single digit
  mm	Minute (00-59), double digit
  s	Second (0-59), single digit
  ss	Second (00-59), double digit
  ms	Microseconds (000000-999999)
  a	am or pm (lowercase)
  A	AM or PM (uppercase)
  w	Week number (00-53), Monday is the first day
  q	Quarter (1-4)
  TZ	Time zone name or abbreviation (e.g., UTC, EST)
  tz	Time zone offset from UTC (e.g., +0530, -0800)

Please note:

1. Due to limited / difference in implementation in all three dialects, some of the keywords mentioned below are not supported by respective sql dialect:
   • MySQL:tz,TZ,q
   • SQLite:tz,TZ,q
2. fromPattern property / feature is not supported bysqlite
3. Aforementioned units are only for formatting / creating date from string pattern and not to be confused with the date units used for addition / subtraction date / time units.

• When usingadd /sub property:

  MySQL	PostgreSQL	SQLite	Unit
  f	MICROSECOND	%f (fractional seconds)	MICROSECOND
  s	SECOND	%S (00-59)	SECOND
  m	MINUTE	%M (00-59)	MINUTE
  h	HOUR	%H (00-23)	HOUR
  d	DAY	%d (01-31)	DAY
  w	WEEK	Not supported	WEEK
  M	MONTH	%m (01-12)	MONTH
  q	QUARTER	Not supported	QUARTER
  y	YEAR	%Y (4-digit)	YEAR

Please note: You can use them in combination like2d 5m 1M 10y inadd |sub

UnSQL provides various built-inspecial objects to perform various specialized actions. Following is the list of special objects:

All objects are explained below:

• String wrapper (Keywordstr):

  Performs string/text based operation(s) onvalue property. Interface with default properties is shown below:

  // Interface:{str:{value:'some value / column containing text',replace:{target:null,// chars to be replacedreplaceWith:null// replace target with this},reverse:false,// rearrange characters in reverse ordertextCase:null,// transform text case to 'upper' or 'lower'padding:{// maintains min. no. of chars. by filing textleft:{// fill missing text to left directionlength:null,// min. chars to maintainpattern:null// text to fill},right:{// fill missing text to right directionlength:null,// min. chars to maintainpattern:null// text to fill}},substr:{// create sub-stringstart:0,// start index for sub-stringlength:null// length of the sub-string},trim:false,// remove whitespace from 'left' / 'right' or bothcast:null,// type cast 'value' into 'binary', 'unsigned', 'char' etc.decrypt:null,// decrypt 'value' using properties inside thisencoding:'utf8mb4',// convert decrypted buffer array using thisas:null,// local reference name to this object 'value'compare:{}// compare 'value' returned, similar to 'where' and 'having'}}// Sample:constresponse=awaitUser.find({select:[{str:{value:'firstName',textCase:'upper'}},{str:{value:'userBio',substr:{start:1,length:50},as:'shortBio'// (optional) rename to 'shortBio'}},{str:{value:'email',decrypt:{secret:'mySecret',iv:'customIV'}}}],encryption:{mode:'aes-256-cbc'}})

  Please note:

  1. All properties are optional and can be used inany combination
  2. All operations are performed onvalue property
  3. reverse andpadding are not supported bysqlite
  4. cast can be any of the values:
  • Formysql:
    • 'char' |'nchar' |'date' |'dateTime' |'signed' |'unsigned' |'decimal' |'binary'
  • Forpostgresql:
    • 'integer' |'text' |'timestamp' |'numeric'
  • Forsqlite:
    • 'integer' |'text' |'real' |'blob'
  5. decrypt is an important property that holds an object with following properties:
  • secret is thesecret key when provided here, willoverride all othersecret properties defined (if any) on execution levelencryption property or global levelencryption (insideconfig)
  • iv (Initialization Vector) only used withmysql. Should be same for Encryption/Decryption.postgresql managesiv internally and does not require to be entered manually.
  • sha determines thehash algorithm to be used (defaults to512) only used bymysql.postgresql does not require this.
  6. sqlite does not support built-in AES Encryption/Decryption hence will throw error if values are set
  7. encoding (only used withmysql) determines the character set to be used while decrypting data. It can be any character set supported bymysql like:'utf8mb4' (default) |'latin1' |'ascii' |'utf8' |'ucs2' |'utf16' etc
  8. compare is similar towhere andhaving, it compares value returned by this object to the condition specified in this object.
  9. Inreplace property, due to limitation of implementation by SQL,target andreplaceWith properties are always expected to be static string and never a column name, hence adding a prefix of# is not required for these properties

• Numeric Wrapper (Keywordnum):

  PerformsNumerical/Mathematical operation(s) onvalue property. Follows the rules ofBODMAS when performing multiple operations. Interface with default properties is shown below:

  // Interface:{num:{value:'some number/ column containing number',decimal:null,// limit no. of decimal or round-off: 'floor'/'ceil'/'round'mod:null,// calculate modulus of 'value' by this numbersub:0,// subtract this from 'value'add:0,// add this to 'value'multiplyBy:null,// multiply 'value' by this numberdivideBy:null,// divide 'value' by this numberpower:null,// apply this as power of 'value'cast:null,// type cast 'value' into 'binary', 'unsigned', 'char' etc.decrypt:null,// decrypt 'value' using properties inside thisencoding:'utf8mb4',// convert decrypted buffer array using this encodingas:null,// local reference name to this object 'value'compare:{}// compare 'value' returned, similar to 'where' and 'having'}}// Sample:constresponse=awaitSpecs.find({select:[{num:{value:'calories',decimal:2,// limit decimals to 2 places '.00'multiplyBy:100,divideBy:'quantity',as:'unitCalories'}}]})

  Please note:

  1. All properties are optional and can be used inany combination
  2. All operations are performed onvalue property
  3. Seecast,decrypt,encoding,compare for respective details

• Date Wrapper (Keyworddate):

  PerformsDate/Time operation(s) onvalue property. Interface along with default properties is shown below:

  // Interface:{date:{value:'column containing date'||date,add:null,// add days as no. or any combination of days, months, yearssub:null,// sub. days as no. or any combination of days, months, yearsfromPattern:null,// create date from any string (date) patterncast:null,// type cast 'value' into 'binary', 'unsigned', 'char' etc.decrypt:null,// decrypt 'value' using properties inside thisencoding:'utf8mb4',// convert decrypted buffer array using this encodingformat:null,// format 'value' to desired form using pattern defined hereas:null,// local reference name to this object 'value'compare:{}// compare 'value' returned, similar to 'where' and 'having'}}// Sample:constresponse=awaitUser.find({select:[{date:{value:'joiningDate',add:'6M',// adds 6 months to 'joiningDate'format:'null',as:'probationEndDate'}}]})

  Please note:

  1. All properties are optional and can be used inany combination
  2. All operations are performed onvalue property
  3. fromPattern is not supported bysqlite
  4. Regular string can be safely used insideformat by wrapping them inside[]
  5. Seecast,decrypt,encoding,compare for respective details

• And (Keywordand) / Or (Keywordor) Wrappers

  Bothand wrapper andor wrapper are similar in interface as both accepts array of comparator objects, only difference isand wrapper joins these comparator objects withand clause andor wrapper joins these comparator objects usingor clause. They overridejunction property for their immediate children comparator objects and can be nested inside each other to create complex conditions. Since there is nointerface, below is a sample forand / or wrapper:

  // Interface:{and:[{...},{...}, ...]}// and wrapper{or:[{...},{...}, ...]}// or wrapper// Sample:constresponse=awaitUser.find({where:{or:[{salary:{between:{gt:5000,lt:15000}}},// condition1{role:'intern'}// condition2],userStatus:1// condition3}})// creates: ((condition1 or condition2) and condition3)

  Please note:

  1. Both wrappers works only withwhere andhaving property

• If Wrapper (Keywordif):

  Creates aif-else check and returns appropriate value. Below is the interface and default properties:

  // Interface:{if:{check:{...},// condition to be checkedtrueValue:'',// returns this value if 'check' is truefalseValue:'',// returns this value if 'check' is falseas:null// local reference name to this object 'value'}}// Sample:constresponse=awaitUser.find({select:[{if:{check:{experience:{lt:1}},trueValue:'Fresher',falseValue:'Experienced',as:'level'}}]})

• Case Wrapper (Keywordcase):

  Similar to aswitch case,check contains array of conditional objects, each object containingwhen (condition to be checked) andthen (value to be returned if respectivewhen istrue). Also contains a defaultelse value when no condition istrue. Below is the interface with default values:

  // Interface:{case:{check:[{// conditional objectwhen:{...},// condition to be checkedthen:'some value'// value if 'when' is true},                ...],else:'default value',// if no condition in any of the 'when' is trueas:null}}// Sample:constresponse=awaitUser.find({select:[{case:{check:[{when:{experience:{lt:2}},then:'Fresher'},{when:{experience:{between:{gt:2,lt:4}}},then:'Junior'},{when:{experience:{between:{gt:4,lt:7}}},then:'Mid-level'}],else:'Senior',as:'expertise'}}]})

• Sum Wrapper (Keywordsum):

  Calculate total based on column name or condition. Can be chained to compare using comparator object. Part ofaggregate methods, is executed on group of record(s). Below is the interface and default values:

  // Interface:{sum:{value:'some column',// or conditional object {...}distinct:false,// when true, ignore duplicate columnsifNull:undefined,// provide default value if incase this method returns nullcast:null,// type cast value to 'signed', 'unsigned' etcas:null,// local reference name to this object 'value'compare:{}// comparator object}}// Sample:constresponse=awaitUser.find({select:[{sum:{value:'salary',ifNull:0,cast:'signed',// convert to 'singed' (number)as:'totalSalary'}}],groupBy:['department'],having:{sum:{value:'salary',ifNull:0,compare:{gt:5000}}}})

• Average Wrapper (Keywordavg):

  Calculate average based on column name or condition. Can be chained to compare using comparator object. Part ofaggregate methods, is executed on group of record(s). Below is the interface and default values:

  // Interface:{avg:{value:'some column',// or conditional object {...}distinct:false,// when true, distinct column values will be consideredifNull:undefined,// provide default value if incase this method returns nullcast:null,// type cast value to 'signed', 'unsigned' etcas:null,// local reference name to this object 'value'compare:{}// comparator object}}// Sample:constresponse=awaitUser.find({select:[{avg:{value:'salary',ifNull:0,cast:'unsigned',as:'averageSalary',}}],groupBy:['department'],having:{avg:{value:'salary',compare:{gt:15000}}}})

• Count Wrapper (Keywordcount):

  Calculate count based on column name or condition. Can be chained to compare using comparator object. Part ofaggregate methods, is executed on group of record(s). Below is the interface and default values:

  // Interface:{count:{value:'some column',// or conditional object {...}distinct:false,// when true, distinct column values will be consideredifNull:undefined,// provide default value if incase this method returns nullcast:null,// type cast value to 'signed', 'unsigned' etcas:null,// local reference name to this object 'value'compare:{}// comparator object}}// Sample:constresponse=awaitUser.find({select:[{count:{value:'*',distinct:true,ifNull:0,as:'totalEmployees',}}],groupBy:['department']})

• Min Wrapper (Keywordmin):

  Calculate lowest value based on column name or condition. Can be chained to compare using comparator object. Part ofaggregate methods, is executed on group of record(s). Below is the interface and default values:

  // Interface:{min:{value:'some column',// or conditional object {...}distinct:false,// when true, distinct column values will be consideredifNull:undefined,// provide default value if incase this method returns nullcast:null,// type cast value to 'signed', 'unsigned' etcas:null,// local reference name to this object 'value'compare:{}// comparator object}}// sampleconstresponse=awaitUser.find({select:[{min:{value:'salary',ifNull:0,cast:'unsigned',as:'lowestSalary'}}]})

• Max Wrapper (Keywordmax):

  Calculate highest value based on column name or condition. Can be chained to compare using comparator object. Part ofaggregate methods, is executed on group of record(s). Below is the interface and default values:

  // Interface:{max:{value:'some column',// or conditional object {...}distinct:false,// when true, distinct column values will be consideredifNull:undefined,// provide default value if incase this method returns nullcast:null,// type cast value to 'signed', 'unsigned' etcas:null,// local reference name to this object 'value'compare:{}// comparator object}}// Sample:constresponse=awaitUser.find({select:[{max:{value:'salary',distinct:true,ifNull:0,cast:'unsigned',as:'highestSalary'}}]})

• Json Wrapper (Keywordjson):

  Can be used tocreate json object/array during execution or by using values from a sub-query or combination of both,extract values from json object/array, check if json contains certain value or not. Supports full sub-query properties (similar tofind method). Part ofaggregate methods, is executed on group of record(s). Below is the interface and default values:

  // Interface:{json:{value:{...},// 'column name' or array [...]table:null,// table in sub-query to refer value to be used to create jsonalias:null,// local reference name for the tablejoin:[],// associate another table as childwhere:{},// filter record(s) in sub-querygroupBy:[],// group record(s) in sub-queryhaving:{},// filter record(s) in sub-query (also using aggregate methods)orderBy:{},// re-order record(s) in sub-querylimit:undefined,// limit record(s) in sub-queryoffset:undefined,// reset start index in sub-queryextract:null,// extract values from json object / arraycontains:null,// check if this value is contained in json object / arrayaggregate:false,// when true, distinct column values will be considereddecrypt:null,// type cast value to 'signed', 'unsigned' etccast:null,// type cast value to 'signed', 'unsigned' etcas:null,// local reference name to this object 'value'compare:{}// comparator object}}// Sample:constresponse=awaitUser.find({alias:'u',select:[{json:{value:{orderId:'orderId',purchaseDate:'createdOn',total:'amount',status:'status'},table:'order_history',where:{userId:'u.userId'},aggregate:true,as:'orders',}}]})

  Please note:

  1. Ifvalue is object, it will create json object
  2. Ifvalue is array, it will crate json array
  3. Ifvalue can also accept column name as string
  4. aggregate can be set totrue to combine multiple json objects/arrays

• Refer Wrapper (Keywordrefer)

  Performs sub-query to extract value from another table, it is similar to have reference of entirefind method as a special object, with all the properties (with additionaltable property) same asfind method. Below is the interface and default values:

  // Interface:{table:null,// table in sub-query to refer value to be used to create jsonalias:null,// local reference name for the tableselect:[],// column to be extractedjoin:[],// associate another table as childwhere:{},// filter record(s) in sub-querygroupBy:[],// group record(s) in sub-queryhaving:{},// filter record(s) in sub-query (also using aggregate methods)orderBy:{},// re-order record(s) in sub-querylimit:undefined,// limit record(s) in sub-queryoffset:undefined,// reset start index in sub-querydecrypt:null,// type cast value to 'signed', 'unsigned' etccast:null,// type cast value to 'signed', 'unsigned' etcas:null,// local reference name to this object 'value'}// Sample:constresponse=awaitUser.find({alias:'u',select:[        ...,{refer:{select:['departmentName'],from:'departments_table',where:{departmentId:'u.departmentId'}}}],where:{userStatus:1}})

• Concat Wrapper (Keywordconcat)

  Used to combine (concat) multiple values using stringpattern, it is similar tostr but with multiple values.

  // Interface:{concat:{value:[],// list of values / special objects to be combinedpattern:'',// pattern to be used to connect valuestextCase:null,// transform text case to 'upper' or 'lower'padding:{// maintains min. no. of chars. by filing textleft:{// fill missing text to left directionlength:null,// min. chars to maintainpattern:null// text to fill},right:{// fill missing text to right directionlength:null,// min. chars to maintainpattern:null// text to fill}},substr:{// create sub-stringstart:0,// start index for sub-stringlength:null// length of the sub-string},trim:false,// remove whitespace from 'left' / 'right' or bothas:null,// local reference name to this object 'value'compare:{}// comparator object}}// Sample:constresponse=awaitUser.find({select:[{concat:{value:['firstName','lastName'],as:'fullName'}}]})

UnSQL provides various objects tocompare different values, as mentioned below:

Session Manager is a special class, used to create an instance ofsession object. It also provides variousstatic asynchronous methods to manage the lifecycle of a persistent (reusable) instance oftransaction across multiple query execution as mentioned below:

Please note:

1. Constructor requiresconnection or connectionpool as parameter
2. rollback andcommit accept an optional boolean parameter, to closesession (whentrue) at this point
3. When trying to combine Session Manager withrawQuery, it will not work withmethodType: 'exec' is set indialect: 'sqlite' or when executing multiple SQL statements in single query

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

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

router.post('/users/login',async(req,res)=>{const{ loginId, password}=req.bodyconstresponse=awaitUser.find({select:[...],where:{or:[{email:`#${loginId}`},{mobile:`#${loginId}`}]}})// your code here})

Please note: UnSQL uses# as prefixed to recognize string asplain text instead ofcolumn name

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

router.get('/users',async(req,res)=>{constresponse=awaitUser.find({select:['userId','firstName',{json:{value:'address',extract:'permanent.city'// this will extract 'city' from 'address' json objectas:'city'}}]})// your code here})

router.get('/users',async(req,res)=>{constresponse=awaitUser.find({alias:'u',select:['userId','firstName',{json:{// creates custom json objectvalue:{orderId:'orderId',purchaseDate:'createdOn',total:'amount',discount:'discount'},table:'order_history',where:{userId:'u.userId'},limit:10,aggregate:true// wraps order object in array '[]'as:'orders'}}]})// your code here})

router.post('/users',async(req,res)=>{constdata=req.body// {...} single user or [{...}] multiple usersconstresponse=awaitUser.save({ data})// your code here})

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

router.post('/users',async(req,res)=>{constdata=req.bodyconst{ userId, ...upsert}=data// extracted Id to create update object (upsert)constresponse=awaitUser.save({ data, upsert})// your code here})

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

router.delete('/users',async(req,res)=>{constresponse=awaitUser.delete({where:{departments:['#sales','#marketing']}})// your code here})

Please note: UnSQL uses# as prefixed to recognize string asplain text instead ofcolumn name

router.delete('/users',async(req,res)=>{constresponse=awaitUser.delete()// your code here})

Please note:saveMode: false is required in modelconfig to delete all users

router.delete('/users',async(req,res)=>{constresponse=awaitUser.reset()// your code here})

Please note:saveMode: false anddevMode: true is required in modelconfig to usereset

import{SessionManager}from'unsql'import{pool}from'./path/to/your/db/service'// Other imports goes here...router.post('/orders',async(req,res)=>{const{ userId}=req.paramsconstdata=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 uses# as prefix to identify if string is plain text, or column name if string does not start with#. The only exception istarget andreplaceWith properties insidereplace due to the limited of implementation for these properties by SQL they only support plain text and not columns hence prefixing them with# is not required

When configurations likesecret |iv |sha are declared in all places,encryption at method level will overrideencryption atconfig, similarlydecrypt /encrypt inside special object will override all other.

Yes, UnSQL is the only library that supports unified codebase across multiple SQL dialects so you don't have to update your code while switching between SQL dialect to another.

Yes, in case ofpostgresql andsqlite, identifiers like column names and table names are case sensitive by default. In case ofmysql identifiers like table name and column name are case in-sensitive.

Yes, UnSQL can be used to create cross-platform applications via. NodeJS (Javascript) based frameworks likeElectronJS.

Yes, UnSQL can be used in serverless applications like AWS Lambda deployed on a NodeJS based environment. Just like any other package, UnSQL needs to be added in a Lambda layer and this lambda layer needs to added to the desired lambda function.

• Siddharth Tiwari