UnSQL can be defined as a light weight schemaless ORM. It provides clean and yet extensive interface to interact with MySQL Databases without getting into the complexities. It provides dynamic query generation through easy to understand Javascript based m
UnSQL is a lightweight, open-source JavaScript library that facilitates class based, schemaless interactions with the structured databases viz. MySQL, PostgreSQL and SQLite through dynamic query generation. It is the only library that supports single codebase across all dialects. It is compatible with javascript runtime environments like NodeJS and NextJS.
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 of version 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 your import/require from 'unsql' to 'unsql/legacy', as shown below:
// v1.x import
const UnSQL = require('unsql/legacy')
// or
import UnSQL from 'unsql/legacy'
// v2.x import
const { UnSQL } = require('unsql')
// or
import { UnSQL } from 'unsql'
Documentation for v1.x can be found on GitHub
Version v2.1 brings support for Multiple Dialects along with Unified codebase, Bug Fixes, Improved Code Suggestions, brought back the rawQuery Method, enhanced Session Manager and better code optimization under the hood and much more
pool for better performanceresult or errorUnSQL can work with three different dialect of SQL ('mysql', 'postgresql' and 'sqlite'). Each of them require different prerequisite setup which are utilized by UnSQL as a source of connection pool as mentioned below:
MySQL (default) (dialect: 'mysql')
mysql2 is the most commonly used package to provided connection pool to interact with MySQL database.
import mysql2 from 'mysql2/promise'
export const pool = createPool({
host: 'localhost', // or link to remote database
database: 'test_db',
user: 'your_username',
password: 'your_password',
namedPlaceholders: true, // (optional) required if using rawQuery with named placeholders
multipleStatements: true // (optional) required if using multiple statements in rawQuery
})
PostgreSQL (dialect: 'postgresql')
pg is the package required to generate connection pool
import { Pool } from 'pg'
export const pool = new Pool({
host: 'localhost',
database: 'test_db',
user: 'your_username',
password: 'your_password'
})
SQLite (dialect: 'sqlite')
Both sqlite and sqlite3 packages are required to be installed in your project to interact with SQLite db.
import sqlite3 from 'sqlite3'
import { open } from 'sqlite'
export const pool = (async () => {
try {
return await open({
filename: './databases/test2.db',
driver: sqlite3.Database
})
} catch (error) {
console.error('Error initializing database:', error)
throw error // Rethrow the error to be handled by the caller
}
})()
Please note:
- Named placeholders and multiline statement settings are only required to be configured with MySQL
- Although SQLite provides connection reference (here
db), it is still used withpoolproperty ofconfig
UnSQL can be installed using any of the package managers viz. npm or yarn:
npmnpm i unsql
yarnyarn add unsql
Unsql uses class based approach hence, after prerequisites and installation, next step is to create model classes. Each model is mapped to a database table and extends from the UnSQL base class and has a static property named config that holds all the configurations related to the respective model class. Below if the sample model class using CommonJS and ES6 Module:
// @ts-check
const { UnSQL } = require('unsql')
// get connection pool from your db provider service
const pool = require('path/to/your/db/service')
/**
* @class
* @extends UnSQL
*/
class User extends UnSQL {
/**
* UnSQL config
* @type {UnSQL.config}
*/
static config = {
table: 'test_user', // (mandatory) replace this with your table name
pool, // provide 'db' instance here in 'sqlite' mode
safeMode: true,
devMode: false,
dialect: 'mysql' // (default) or 'postgresql' or 'sqlite'
}
}
module.exports = { User }
// @ts-check
import { UnSQL } from 'unsql'
// get connection pool from your db provider service
import { pool } from 'path/to/your/db/service'
/**
* @class
* @extends UnSQL
*/
export class User extends UnSQL {
/**
* UnSQL config
* @type {UnSQL.config}
*/
static config = {
table: 'test_user', // (mandatory) replace this with your table name
pool, // provide 'db' instance here in 'sqlite' mode
safeMode: true,
devMode: false,
dialect: 'mysql' // (default) or 'postgresql' or 'sqlite'
}
}
Config property is the heart and soul of any model class, it holds all configurations related to the model class and is used throughout query generation and execution. It can also hold global level configurations related to Encryption/Decryption for that table so that you don't have to re-define them for each query.
| Property | Description |
|---|---|
table | (required) name of the database table to be mapped with this model class |
pool | (required) connection / pool of connection provided by prerequisite package |
safeMode | (required) defaults to true prevents accidental delete all and reset query |
devMode | (required) defaults to false, unless true, prevents export/import of data |
dialect | (required) defines the dialect used for dynamic query generation |
encryption | (optional) defines various properties viz. secret, iv, sha and mode at global level to used by all executions |
dbEncryptionMode | (optional) defaults to unknown, defines the encryption mode set on the database |
Please note:
secretis the secret key that is used to encrypt the dataivandshaare only used whendialectis set to'mysql', aspostgresqlsets upivinternally andsqlitedoes not have any built-in Encryption/Decryption methods- When
dbEncryptionModeis same asmodeinsideencryptionproperty, inmysqldialect, an additional internal query that is used to set theblock_encryption_modeis skipped
Unsql provides various static, asynchronous built-in methods as mentioned below:
| Method | Description |
|---|---|
find | used to read / retrieve /fetch record(s) from database |
save | used to insert / update / upsert record(s) into database |
delete | used to remove / delete record(s) from database |
rawQuery | used to write custom SQL (manually), can be used for any of type of query execution |
reset | will remove all record(s) and reset auto increment column to initial state |
export | can dump record(s) from database to specified target (json file or model class) |
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 of findOne and findAll methods into one find method, as findOne (in other libraries) is just a wrapper around findAll to fetch first returning record irrespective of the response set. Interface of find method along with its default properties is explained below:
const response = await User.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 to find:
// Interface for each join object:
{
type: '', // (default '') 'left'/'right'/'inner'/'cross'/'fullOuter'
alias: undefined, // local reference name to the child table
table: null, // (required) table to associate
select: ['*'], // columns to be fetched
join: [], // nest another association inside this
where: {}, // filter record(s) based on condition(s)
junction: 'and', // connect condition(s) using
groupBy: [], // 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 order
limit: undefined, // limit no. of records
offset: undefined, // set the starting index for records
using: [], // (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:
const response = await Order.find({
select: ['orderId', 'createdOn',
{
json: { // creating json object using columns from associated table
value: {
itemId: 'itemId', // column from associated table
name: 'itemName', // column from associated table
quantity: 'quantity' // column from associated table
},
aggregate: true, // wrapping multiple objects inside array
as: 'items'
}
}
],
join: [{ table: 'order_items', using: ['orderId'] }] // ref. of join object
})
Please note:
using property can accept array of column names or an object like { parentColumn: childColumn } where parentColumn is the column from parent table and childColumn is the column from child table. When alias is passed, it is automatically patched to the respective column nameselect | where | having inside join, as is mandatoryalias and as is set, as will be used as prefix to refer column names from child tables outside join object contextBelow is the explanation for each of these join 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 tableright considers all records in child table and only matching records from parent table (not supported by sqlite)inner only matching rows based on using column(s)cross cartesian product of records in parent and child tablesfullOuter returns all records from both tables, regardless of matching condition (only supported by postgresql)where filters record(s) to be fetched from the database based on the conditions provided as simple (or nested) objects in key: value pairs, comparator methods, wrapper methods etc.
// Sample:
const response = await User.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 inside where and having properties. Defaults to 'and'
groupBy groups record(s) based on the column name(s) provided as an array
having similar to where, filter record(s) based on condition(s) the only difference is that it supports aggregate object(s) (in wrapper 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 to encryption inside config 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' | logs prepared, 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 of SessionManager object, used to override the transaction/commit/rollback features to be controlled externally
save is a static, asynchronous method, used to insert | update | upsert record(s) into the database. It can insert | update single or even multiple records (in bulk) in single execution. It also supports data Encryption during this process. When only data property is set, this method operates in insert mode, when along with data, any or both of where and having are also set, this method operates in update mode, and when along with data, upsert property is set, this method operates in upsert mode. Interface along with default values for this method is shown below:
const response = await User.save({
alias: undefined,
data,
where: {},
junction: 'and',
groupBy: [],
having: {},
upsert: {},
encrypt: {},
encryption: {},
debug: false,
session: undefined
})
/* When successful
1. MySQL returns
response = {
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 returns
response = {
"success": true,
"result": [{...}] // record (with primary key ID) that was recently added
}
3. Sqlite returns
response = {
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 explained heredata (required) this is the actual data that will be inserted | updated into the database. It can either be a single object (supports insert | update | upsert) or an array of objects (supports only insert)where same as explained here, used to filter record(s) to be updatedjunction same as explained heregroupBy same as explained herehaving same as explained here, used to filter record(s) to be updatedencrypt accepts key: value pair, where key can be column name and value is another object that holds configurations like secret, iv and sha 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 in encryption property (local or global) is used. This property helps encrypting different columns with different secretdebug same as explained hereencryption same as explained heresession same as explained herePlease Note: In Upsert mode, while mysql and postgresql will only update the columns provided in the upsert object, with dialect: 'sqlite' if any existing column value is ignored in the upsert object, then that value will either be set to null or predefined default value will be assigned to that column, due to the native upsert behavior (INSERT OR REPLACE) of SQLite
delete is a static, asynchronous method, used to remove record(s) from the database. where and having properties are used to filter record(s) that will be removed, if no conditions are provided in where and (or) having property, this method will remove all records in the database. safeMode property (when set to true) in the config property of the model class helps prevent accidental delete 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:
const response = await User.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 explained herewhere same as explained here, used to filter record(s) to be removedjunction same as explained herehaving same as explained here, used to filter record(s) to be removeddebug same as explained hereencryption same as explained heresession same as explained hererawQuery 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 including DDL, DML etc (In sqlite, set methodType: 'exec'). It also supports execution of multiple SQL statements in one query. When multiple SELECT statements are executed (not supported by sqlite), result contains nested array one for each SELECT statement.
In mysql, use multiQuery: true to enable execution of multiple SQL statements in single query
For sqlite, UnSQL supports various types of methods (as mentioned below) that can be set manually, each method has specific capabilities:
| Method Type | Description |
|---|---|
all | supports Session Manager and SELECT query returns record(s) as array |
run | supports Session Manager, INSERT and UPDATE query, returns insertId and changes |
exec | supports CREATE, DROP ALTER and similar query, returns nothing |
It supports normal as well as parameterized (with placeholders) queries:
mysql:
??, ?,:namedVariable,@userVariable,postgresql:
$1, $2, $3...sqlite:
?,:namedVariable or $namedVariable or @namedVariable,$1, $2, $3... or ?1, ?2, ?3...// Sample: (dialect: 'mysql')
const response = await User.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 a static, asynchronous method that works when devMode: true is set in config, as it is used to export record(s) from the database table either to a .json file or to another model class, depending upon the value set in the target 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)
const response = await User.export()
// Sample: Export to model (limited columns to be exported)
const response = await User.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 to another model class. It defaults to the table name property inside config of the respective model class
directory determines the name of the folder that will be created (dynamically) to store the dynamically created .json filealias same as explained hereselect restricts the column(s) to be exported, also used to mutate values while exporting them including Decryption etc. Same as explained herejoin used to associate another table to fetch record(s) together with this table while exporting. Same as explained here, used to filter record(s) to be removedwhere filter record(s) to be exported. Same as explained here, used to filter record(s) to be removedjunction same as explained heregroupBy same as explained herehaving filter record(s) to be exported. Same as explained here, used to filter record(s) to be removedorderBy same as explained herelimit limits the number of record(s) to be exported, Same as explained hereoffset defines the starting index for the record(s) to be exported. Same as explained heremode (works when exporting to a json file) when the export is executed and the file already contains data, this property determines whether to override or append the contents to the fileencrypt encrypts the columns mentioned as key in this object during export. Same as explained hereencryption same as explained heredebug same as explained herereset is a static, asynchronous method used to clear all record(s) in the model class and also reset the auto increment ID (if any) to their initial state. This only works when devMode: true and safeMode: false in config. This only expects one property debug in its parameter object. Interface is shown below:
const response = await User.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-in constants (supported by SQL) as mentioned below:
| Constant | Description |
|---|---|
currentDate | provides only current date in YYYY-MM-DD format |
currentTime | provides only current time in hh:mm:ss format |
now | provides both, current date and time in YYYY-MM-DD hh:mm:ss format, with configured timezone |
currentTimestamp | synonym for now |
localTimestamp | similar to now or timestamp but in reference to local timezone |
localTime | exactly same as localTimestamp |
utcTimestamp | provides currentTimestamp in UTC format |
pi | provides value of mathematical constant pi i.e. approx. 3.141593 |
isNull | provides SQL compatible IS NULL value |
isNotNull | provides SQL compatible IS NOT NULL value |
UnSQL supports various Date / 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 using format | fromPattern (not supported by sqlite) 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:
- 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,qfromPatternproperty / feature is not supported bysqlite- 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 using add / 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 like
2d 5m 1M 10yinadd|sub
UnSQL provides various built-in special objects to perform various specialized actions. Following is the list of special objects:
| Keyword | Type | Description |
|---|---|---|
str | string | perform string based operations |
num | numeric | perform mathematical operations |
date | date | perform date related operations |
and | junction | perform junction override inside where and having property |
or | junction | perform junction override inside where and having property |
if | conditional | checks condition and returns respective true or false value |
case | conditional | checks multiple conditions and return respective value |
sum | aggregate | calculates total from set of values |
avg | aggregate | calculates average from set of values |
count | aggregate | performs count operation on set of values |
min | aggregate | determines lowest value among the provided values |
max | aggregate | determines highest value among the provided values |
json | sub-query | performs json object/array related operations |
refer | sub-query | fetch a column from another table at the position it is invoked |
concat | merge | combines multiple values into one |
All objects are explained below:
str):Performs string/text based operation(s) on value property. Interface with default properties is shown below:
// Interface:
{
str: {
value: 'some value / column containing text',
replace: {
target: null, // chars to be replaced
replaceWith: null // replace target with this
},
reverse: false, // rearrange characters in reverse order
textCase: null, // transform text case to 'upper' or 'lower'
padding: { // maintains min. no. of chars. by filing text
left: { // fill missing text to left direction
length: null, // min. chars to maintain
pattern: null // text to fill
},
right: { // fill missing text to right direction
length: null, // min. chars to maintain
pattern: null // text to fill
}
},
substr: { // create sub-string
start: 0, // start index for sub-string
length: null // length of the sub-string
},
trim: false, // remove whitespace from 'left' / 'right' or both
cast: null, // type cast 'value' into 'binary', 'unsigned', 'char' etc.
decrypt: null, // decrypt 'value' using properties inside this
encoding: 'utf8mb4', // convert decrypted buffer array using this
as: null, // local reference name to this object 'value'
compare: {} // compare 'value' returned, similar to 'where' and 'having'
}
}
// Sample:
const response = await User.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:
value propertyreverse and padding are not supported by sqlitecast can be any of the values:mysql:
'char' | 'nchar' | 'date' | 'dateTime' | 'signed' | 'unsigned' | 'decimal' | 'binary'postgresql:
'integer' | 'text' | 'timestamp' | 'numeric'sqlite:
'integer' | 'text' | 'real' | 'blob'decrypt is an important property that holds an object with following properties:secret is the secret key when provided here, will override all other secret properties defined (if any) on execution level encryption property or global level encryption (inside config)iv (Initialization Vector) only used with mysql. Should be same for Encryption/Decryption. postgresql manages iv internally and does not require to be entered manually.sha determines the hash algorithm to be used (defaults to 512) only used by mysql. postgresql does not require this.sqlite does not support built-in AES Encryption/Decryption hence will throw error if values are setencoding (only used with mysql) determines the character set to be used while decrypting data. It can be any character set supported by mysql like: 'utf8mb4' (default) | 'latin1' | 'ascii' | 'utf8' | 'ucs2' | 'utf16' etccompare is similar to where and having, it compares value returned by this object to the condition specified in this object.replace property, due to limitation of implementation by SQL, target and replaceWith properties are always expected to be static string and never a column name, hence adding a prefix of # is not required for these propertiesnum):Performs Numerical/Mathematical operation(s) on value property. Follows the rules of BODMAS 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 number
sub: 0, // subtract this from 'value'
add: 0, // add this to 'value'
multiplyBy: null, // multiply 'value' by this number
divideBy: null, // divide 'value' by this number
power: null, // apply this as power of 'value'
cast: null, // type cast 'value' into 'binary', 'unsigned', 'char' etc.
decrypt: null, // decrypt 'value' using properties inside this
encoding: 'utf8mb4', // convert decrypted buffer array using this encoding
as: null, // local reference name to this object 'value'
compare: {} // compare 'value' returned, similar to 'where' and 'having'
}
}
// Sample:
const response = await Specs.find({
select: [
{
num: {
value: 'calories',
decimal: 2, // limit decimals to 2 places '.00'
multiplyBy: 100,
divideBy: 'quantity',
as: 'unitCalories'
}
}
]
})
Please note:
date):Performs Date/Time operation(s) on value 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, years
sub: null, // sub. days as no. or any combination of days, months, years
fromPattern: null, // create date from any string (date) pattern
cast: null, // type cast 'value' into 'binary', 'unsigned', 'char' etc.
decrypt: null, // decrypt 'value' using properties inside this
encoding: 'utf8mb4', // convert decrypted buffer array using this encoding
format: null, // format 'value' to desired form using pattern defined here
as: null, // local reference name to this object 'value'
compare: {} // compare 'value' returned, similar to 'where' and 'having'
}
}
// Sample:
const response = await User.find({
select: [
{
date: {
value: 'joiningDate',
add: '6M', // adds 6 months to 'joiningDate'
format: 'null',
as: 'probationEndDate'
}
}
]
})
Please note:
and) / Or (Keyword or) WrappersBoth and wrapper and or wrapper are similar in interface as both accepts array of comparator objects, only difference is and wrapper joins these comparator objects with and clause and or wrapper joins these comparator objects using or clause. They override junction property for their immediate children comparator objects and can be nested inside each other to create complex conditions. Since there is no interface, below is a sample for and / or wrapper:
// Interface:
{ and: [ {...}, {...}, ...] } // and wrapper
{ or: [ {...}, {...}, ...] } // or wrapper
// Sample:
const response = await User.find({
where: {
or: [
{ salary: { between: { gt: 5000, lt: 15000 } } }, // condition1
{ role: 'intern' } // condition2
],
userStatus: 1 // condition3
}
})
// creates: ((condition1 or condition2) and condition3)
Please note:
where and having propertyif):Creates a if-else check and returns appropriate value. Below is the interface and default properties:
// Interface:
{
if: {
check: {...}, // condition to be checked
trueValue:'', // returns this value if 'check' is true
falseValue: '', // returns this value if 'check' is false
as: null // local reference name to this object 'value'
}
}
// Sample:
const response = await User.find({
select: [
{
if: {
check: { experience: { lt: 1 } },
trueValue:'Fresher',
falseValue: 'Experienced',
as: 'level'
}
}
]
})
case):Similar to a switch case, check contains array of conditional objects, each object containing when (condition to be checked) and then (value to be returned if respective when is true). Also contains a default else value when no condition is true. Below is the interface with default values:
// Interface:
{
case: [
{ // conditional object
when: {...}, // condition to be checked
then: 'some value' // value if 'when' is true
},
...
],
else: 'default value', // if no condition in any of the 'when' is true
as: null
}
// Sample:
const response = await User.find({
select: [
{
case: [
{
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):Calculate total based on column name or condition. Can be chained to compare using comparator object. Part of aggregate 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 columns
ifNull: undefined, // provide default value if incase this method returns null
cast: null, // type cast value to 'signed', 'unsigned' etc
as: null, // local reference name to this object 'value'
compare: {} // comparator object
}
}
// Sample:
const response = await User.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 }
}
}
})
avg):Calculate average based on column name or condition. Can be chained to compare using comparator object. Part of aggregate 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 considered
ifNull: undefined, // provide default value if incase this method returns null
cast: null, // type cast value to 'signed', 'unsigned' etc
as: null, // local reference name to this object 'value'
compare: {} // comparator object
}
}
// Sample:
const response = await User.find({
select: [
{
avg: {
value: 'salary',
ifNull: 0,
cast: 'unsigned',
as: 'averageSalary',
}
}
],
groupBy: ['department'],
having: {
avg: {
value: 'salary',
compare: { gt: 15000 }
}
}
})
count):Calculate count based on column name or condition. Can be chained to compare using comparator object. Part of aggregate 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 considered
ifNull: undefined, // provide default value if incase this method returns null
cast: null, // type cast value to 'signed', 'unsigned' etc
as: null, // local reference name to this object 'value'
compare: {} // comparator object
}
}
// Sample:
const response = await User.find({
select: [
{
count: {
value: '*',
distinct: true,
ifNull: 0,
as: 'totalEmployees',
}
}
],
groupBy: ['department']
})
min):Calculate lowest value based on column name or condition. Can be chained to compare using comparator object. Part of aggregate 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 considered
ifNull: undefined, // provide default value if incase this method returns null
cast: null, // type cast value to 'signed', 'unsigned' etc
as: null, // local reference name to this object 'value'
compare: {} // comparator object
}
}
// sample
const response = await User.find({
select: [
{
min: {
value: 'salary',
ifNull: 0,
cast: 'unsigned',
as: 'lowestSalary'
}
}
]
})
max):Calculate highest value based on column name or condition. Can be chained to compare using comparator object. Part of aggregate 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 considered
ifNull: undefined, // provide default value if incase this method returns null
cast: null, // type cast value to 'signed', 'unsigned' etc
as: null, // local reference name to this object 'value'
compare: {} // comparator object
}
}
// Sample:
const response = await User.find({
select: [
{
max: {
value: 'salary',
distinct: true,
ifNull: 0,
cast: 'unsigned',
as: 'highestSalary'
}
}
]
})
json):Can be used to create 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 to find method). Part of aggregate 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 json
alias: null, // local reference name for the table
join: [], // associate another table as child
where: {} // filter record(s) in sub-query
groupBy: [], // group record(s) in sub-query
having: {}, // filter record(s) in sub-query (also using aggregate methods)
orderBy: {}, // re-order record(s) in sub-query
limit: undefined, // limit record(s) in sub-query
offset: undefined, // reset start index in sub-query
extract: null, // extract values from json object / array
contains: null, // check if this value is contained in json object / array
aggregate: false, // when true, distinct column values will be considered
decrypt: null, // type cast value to 'signed', 'unsigned' etc
cast: null, // type cast value to 'signed', 'unsigned' etc
as: null, // local reference name to this object 'value'
compare: {} // comparator object
}
}
// Sample:
const response = await User.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:
value is object, it will create json objectvalue is array, it will crate json arrayvalue can also accept column name as stringaggregate can be set to true to combine multiple json objects/arraysrefer)Performs sub-query to extract value from another table, it is similar to have reference of entire find method as a special object, with all the properties (with additional table property) same as find method. Below is the interface and default values:
// Interface:
{
table: null, // table in sub-query to refer value to be used to create json
alias: null, // local reference name for the table
select: [], // column to be extracted
join: [], // associate another table as child
where: {} // filter record(s) in sub-query
groupBy: [], // group record(s) in sub-query
having: {}, // filter record(s) in sub-query (also using aggregate methods)
orderBy: {}, // re-order record(s) in sub-query
limit: undefined, // limit record(s) in sub-query
offset: undefined, // reset start index in sub-query
decrypt: null, // type cast value to 'signed', 'unsigned' etc
cast: null, // type cast value to 'signed', 'unsigned' etc
as: null, // local reference name to this object 'value'
}
// Sample:
const response = await User.find({
alias: 'u',
select: [
...,
{
refer: {
select: ['departmentName'],
from: 'departments_table',
where: {
departmentId: 'u.departmentId'
}
}
}
],
where: {
userStatus: 1
}
})
concat)Used to combine (concat) multiple values using string pattern, it is similar to str but with multiple values.
// Interface:
{
concat: {
value: [], // list of values / special objects to be combined
pattern: '', // pattern to be used to connect values
textCase: null, // transform text case to 'upper' or 'lower'
padding: { // maintains min. no. of chars. by filing text
left: { // fill missing text to left direction
length: null, // min. chars to maintain
pattern: null // text to fill
},
right: { // fill missing text to right direction
length: null, // min. chars to maintain
pattern: null // text to fill
}
},
substr: { // create sub-string
start: 0, // start index for sub-string
length: null // length of the sub-string
},
trim: false, // remove whitespace from 'left' / 'right' or both
as: null, // local reference name to this object 'value'
compare: {} // comparator object
}
}
// Sample:
const response = await User.find({
select: [
{
concat: {
value: ['firstName', 'lastName'],
as: 'fullName'
}
}
]
})
UnSQL provides various objects to compare different values, as mentioned below:
| Comparator | Expression | Description |
|---|---|---|
eq | = | compares, key is equal to value |
notEq | != | compares, key is not equal to value |
gt | > | compares, key is greater than to value |
lt | < | compares, key is lower than to value |
gtEq | >= | compares, key is greater than to value |
ltEq | <= | compares, key is lower than to value |
between | BETWEEN ? AND ? | checks, key is in a range of values |
in | IN | checks, key has an exact match in a set of values in value |
notIn | NOT IN | checks, key does not have exact match in a set of values in value |
like | LIKE '%?%' | fuzzy search, value contains key at any position |
notLike | NOT LIKE '%?%' | fuzzy search, value does not contain key at any position |
startLike | LIKE '?%' | fuzzy search, value begins with key |
notStartLike | NOT LIKE '?%' | fuzzy search, value does not begins with key |
endLike | LIKE '%?' | fuzzy search, value ends with key |
notEndLike | NOT LIKE '%?' | fuzzy search, value does not ends with key |
Session Manager is a special class, used to create an instance of session object. It also provides various static asynchronous methods to manage the lifecycle of a persistent (reusable) instance of transaction across multiple query execution as mentioned below:
| Method | Description |
|---|---|
init | initializes session (transaction) |
rollback | undo all (un-committed) changes, reverting to the initial state |
commit | finalizes all changes, making them permanent (cannot be undone) |
close | ends the transaction and closes the session |
Please note:
- Constructor requires
connectionor connectionpoolas parameterrollbackandcommitaccept an optional boolean parameter, to closesession(whentrue) at this point- When trying to combine Session Manager with
rawQuery, 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) => {
const response = await User.find() // similar to await User.find({ })
// your code here
})
router.get('/users/:userId(\\d+)', async (req, res) => {
const { userId } = req.params
const response = await User.find({ where: { userId } })
// your code here
})
router.post('/users/login', async (req, res) => {
const { loginId, password } = req.body
const response = await User.find({
select: [...],
where: {
or: [
{ email: `#${loginId}` },
{ mobile: `#${loginId}` }
]
}
})
// your code here
})
Please note: UnSQL uses # as prefixed to recognize string as plain text instead of column name
router.get('/users', async (req, res) => {
const response = await User.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) => {
const response = await User.find({
select: ['userId', 'firstName',
{
json: {
value: 'address',
extract: 'permanent.city' // this will extract 'city' from 'address' json object
as: 'city'
}
}
]
})
// your code here
})
router.get('/users', async (req, res) => {
const response = await User.find({
alias: 'u',
select: ['userId', 'firstName',
{
json: { // creates custom json object
value: {
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) => {
const data = req.body // {...} single user or [{...}] multiple users
const response = await User.save({ data })
// your code here
})
router.put('/users/:userId(\\d+)', async (req, res) => {
const { userId } = req.params
const data = req.body
const response = await User.save({ data, where: { userId } })
// your code here
})
router.post('/users', async (req, res) => {
const data = req.body
const { userId, ...upsert } = data // extracted Id to create update object (upsert)
const response = await User.save({ data, upsert })
// your code here
})
router.delete('/users/:userId(\\d+)', async (req, res) => {
const { userId } = req.params
const response = await User.delete({ where: { userId } })
// your code here
})
router.delete('/users', async (req, res) => {
const response = await User.delete({
where: {
departments: ['#sales', '#marketing']
}
})
// your code here
})
Please note: UnSQL uses # as prefixed to recognize string as plain text instead of column name
router.delete('/users', async (req, res) => {
const response = await User.delete()
// your code here
})
Please note: saveMode: false is required in model config to delete all users
router.delete('/users', async (req, res) => {
const response = await User.reset()
// your code here
})
Please note: saveMode: false and devMode: true is required in model config to use reset
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.params
const data = req.body
// create 'session' instance using 'SessionManager'
const session = new SessionManager(pool) // 'pool' or 'connection' is required
// initiate 'transaction' using 'init' lifecycle method
const initResp = await session.init()
// handle if session init failed
if (!initResp.success) return res.status(400).json(initResp)
// fetch objects inside bucket, pass 'session' object to the query method
const bucketResp = await Bucket.find({ where: { userId }, session })
// create order using 'data' and pass 'session' object to the query method
const orderResp = await Order.save({ data, session })
// attach 'orderId' to each item
const items = bucketResp.result.map(item => item.orderId = orderResp.insertId )
// save order 'items' and pass 'session' object to the query method
const itemsResp = await OrderItems.save({ data: items, session })
// clear bucket after successfully creating order and pass 'session' object to the query method
const clearBucket = await Bucket.delete({ where: { userId }, session })
// handle if any (or all) query failed
if(!bucketResp.success || !orderResp.success || !itemsResp.success) {
// rollback changes
await session.rollback()
return res.status(400).json({ success: false, message: 'Error while placing order!', error: bucketResp?.error || orderResp?.error || itemsResp?.error })
}
// commit changes if no errors were encountered
await session.commit()
return res.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 is target and replaceWith properties inside replace 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 like secret | iv | sha are declared in all places, encryption at method level will override encryption at config, similarly decrypt / 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 of postgresql and sqlite, identifiers like column names and table names are case sensitive by default. In case of mysql identifiers like table name and column name are case in-sensitive.
FAQs
UnSQL can be defined as a light weight schemaless ORM. It provides clean and yet extensive interface to interact with MySQL Databases without getting into the complexities. It provides dynamic query generation through easy to understand Javascript based m
The npm package unsql receives a total of 23 weekly downloads. As such, unsql popularity was classified as not popular.
We found that unsql demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Research
Security News
Malicious PyPI checkers validate stolen emails against TikTok and Instagram APIs, enabling targeted account attacks and dark web credential sales.
Security News
The Node.js TSC opted not to endorse a feature bounty program, citing concerns about incentives, governance, and project neutrality.
Research
Security News
A look at the top trends in how threat actors are weaponizing open source packages to deliver malware and persist across the software supply chain.