Unconventional Queries

Appearance

Actions ​

Unconventional Queries supports these database actions: Select, Insert, Update, Delete, Select Many, Update Many.

Definition ​

Each action is customized by a query definition, which has the following properties:

PropTypeSupported ActionsDescription
tablestringAllThe database table on which to execute the query (Required by all)
whereSqlWhereSelect
Select Many
Update
Delete		Filters the query response by a particular set of criteria
dataObject | Object[]Insert
Update
Update Many		The data to insert or update in a table
expandRecord<string, Expansion>Select
Select Many		A list of relations to expand on the response object
orderSqlOrder[]Select ManyOrders the rows in the response object by a particular set of fields
pageSqlPaginateSelect ManyPaginates the rows in the response object
limitnumberSelect ManyReturns only a set number of rows
countbooleanSelect ManyIf true, returns the number of rows matching the query in the form {count: <# of rows>}
conflictSqlConflictInsertWhen used on an Insert query, either performs an upsert or ignores conflicts depending on which resolution is set

Types ​

Types used in the formation of a query definition object

For informational purposes. Feel free to skip to the next page for query examples.

Expansion ​

ts
interface Expansion {
  type: OneOrMany;
  fromTable: string;
  fromField: string;
  toTable: string;
  toField: string;
  throughTable?: string;
  throughFromField?: string;
  throughToField?: string;
  expand?: Record<string, Expansion>;
}
interface Expansion {
  type: OneOrMany;
  fromTable: string;
  fromField: string;
  toTable: string;
  toField: string;
  throughTable?: string;
  throughFromField?: string;
  throughToField?: string;
  expand?: Record<string, Expansion>;
}

SqlConflict ​

ts
interface SqlConflict {
  action: ConflictResolution;
  constraint?: string[];
}
interface SqlConflict {
  action: ConflictResolution;
  constraint?: string[];
}

SqlOrder ​

ts
interface SqlOrder {
  field: string;
  jsonPath?: string[];
  direction: SqlDirection;
}
interface SqlOrder {
  field: string;
  jsonPath?: string[];
  direction: SqlDirection;
}

SqlPaginate ​

ts
interface SqlPaginate {
  field: string;
  cursor: string | number;
}
interface SqlPaginate {
  field: string;
  cursor: string | number;
}

SqlWhere ​

ts
interface SqlWhere {
  field: string;
  jsonPath?: string[];
  operator: SqlWhereOperator;
  value: string | number | null;
  andOr?: AndOr;
}
interface SqlWhere {
  field: string;
  jsonPath?: string[];
  operator: SqlWhereOperator;
  value: string | number | null;
  andOr?: AndOr;
}

Enums ​

Enums used within definition properties above

AndOr ​

ts
enum AndOr {
  And = 'AND',
  Or = 'OR'
}
enum AndOr {
  And = 'AND',
  Or = 'OR'
}

ConflictResolution ​

ts
enum ConflictResolution {
  doNothing = 'DO NOTHING',
  doUpdate = 'DO UPDATE SET'
}
enum ConflictResolution {
  doNothing = 'DO NOTHING',
  doUpdate = 'DO UPDATE SET'
}

OneOrMany ​

ts
enum OneOrMany {
  One = 'one',
  Many = 'many'
}
enum OneOrMany {
  One = 'one',
  Many = 'many'
}

SqlDirection ​

ts
enum SqlDirection {
  Asc = 'ASC',
  Desc = 'DESC'
}
enum SqlDirection {
  Asc = 'ASC',
  Desc = 'DESC'
}

SqlWhereOperator ​

ts
enum SqlWhereOperator {
  Eq = '=',
  Neq = '<>',
  Gt = '>',
  Gte = '>=',
  Lt = '<',
  Lte = '<=',
  Like = 'LIKE',
  ILike = 'ILIKE',
  NotLike = 'NOT LIKE',
  In = 'IN',
  NotIn = 'NOT IN',
  IsNull = 'IS NULL',
  IsNotNull = 'IS NOT NULL',
  BitwiseAnd = '&'
}
enum SqlWhereOperator {
  Eq = '=',
  Neq = '<>',
  Gt = '>',
  Gte = '>=',
  Lt = '<',
  Lte = '<=',
  Like = 'LIKE',
  ILike = 'ILIKE',
  NotLike = 'NOT LIKE',
  In = 'IN',
  NotIn = 'NOT IN',
  IsNull = 'IS NULL',
  IsNotNull = 'IS NOT NULL',
  BitwiseAnd = '&'
}

SubAction ​

ts
enum SubAction {
  Count = 'count',
  Increment = 'increment'
}
enum SubAction {
  Count = 'count',
  Increment = 'increment'
}