Forming Dynamic SQL with the Repository

While developing DeepLynx, you may often find yourself in a situation where you need to retrieve certain information from the database beyond the default SELECT * FROM table. There are various ways to go about this, depending on your needs. This article will serve as a guide for which methods to use in which cases, as well as details on how to use the various functions in the repository layer.

Using a Mapper Function vs. Using the Repo Layer

The first question to ask yourself is if you even need to use the dynamic repository layer to build your query, or if it is more appropriate to use a mapper function. Mapper functions should be used if you don't need any filters on your query, or if you need the same filter every time you call the query. If there is a filter that is only applied sometimes, it is better to use a repository listing function. Below are a few examples of using mapper functions.

Mapper Function Example

Mapper functions could be used for a simple retrieval when targeting a specific node:

SELECT * FROM type_mappings WHERE id = $1 (where $1 is mapping ID)

You also might use a mapper function with a few filters on it to retrieve only specific fields that you need:

SELECT key, user_id, note FROM keypairs WHERE user_id = $1 AND note = $2 to fetch all key-secret pairs for a user with a specific note (only returning relevant fields)

Or you may use a mapper to apply filters from multiple tables, such as when searching for an edge based on its relationship:

SELECT e.* FROM current_edges e
JOIN metatype_relationship_pairs mp
    ON mp.id = e.relationship_pair_id
LEFT JOIN metatype_relationships mr
    ON mp.relationship_id = mr.id
LEFT JOIN metatypes origin
    ON origin.id = mp.origin_id
LEFT JOIN metatypes destination
    ON destination.id = mp.destination_id
WHERE origin.name = $1
AND relationship.name = $2
AND destination.name = $3

Using the Repository to Build Queries

If you find yourself needing something more complex or dynamic than what the mapper layer can provide, it may be time to use the repository layer for data retrieval. The repository layer builds off of the most basic mapper functions but allows for more flexibility in querying. Any interactions with the frontend query layer, graphQL, or optional filters (such as listing datasources with the parameter timeseries=true) are accomplished by using the repository layer.

Quick Reference

Below is a table describing common query actions, a SQL example, and the repository equivalent. Not all the examples will have every argument option listed, but more detail on arguments as well as examples and use cases will be given in corresponding wiki sections below the table.

	Desired Output	SQL Example	Repo Method
	Repository-Specific Functions
	Select all fields from table	SELECT t.* FROM table t	list() with no args
	Count all rows in table	SELECT COUNT(*) FROM table	count()
	Filter on a local domain property (column known to this repository)	WHERE column = 'value'	where().column(operator, value)
	Basic Querying
	Filter on any property	WHERE column = 'value'	query(column, operator, value)
	Filter on a sub-property of a JSON column	WHERE column->>'key' = 'value'	queryJsonb(key, column, operator, value)
	Use multiple filters together (AND conjunction)	WHERE A AND B	query().and().query()
	Use one filter or another (OR conjunction)	WHERE A OR B	query().or().query()
	Select-list Manipulation
	Override default SELECT * with custom fields	SELECT t.x, t.y FROM table t	select(newFields, table?)
	Add fields to current select-list	SELECT t.*, t.x, t.y FROM table t	addFields(newFields, table?)
	Eliminate duplicate records	SELECT DISTINCT t.*	list({distinct: true})
	Eliminate duplicates for a specific field	SELECT DISTINCT ON (t.id) t.*	list({distinct_on: {table: 't', column: 'id'} })
	Table manipulation
	Override default table	FROM different_table dt	from(table, alias?)
	Join tables	FROM origin o JOIN destination d ON o.join_col = d.join_col	join(destination, {conditions: {origin_col, destination_col}, destination_alias?}, origin?)
	Advanced Concepts
	Nested AND logic	WHERE A AND (B OR C)	query().and(new repo object with ANDed clauses)
	Nested OR logic	WHERE A OR (B AND C)	query().or(new repo object with ORed clauses)
	Add a subquery to the from or where clause	WHERE id IN (SELECT id FROM table WHERE condition = 'value') SELECT id, sub.name FROM (SELECT name FROM table WHERE condition = 'value') sub	query('id', 'in', subquery(new repo object specifying subquery) from(subquery(new repo object specifying subquery), 'sub')
	Group by certain columns when aggregating data	GROUP BY id	groupBy(fields, table?)
	Listing Options
	Sort data by field(s)	ORDER BY id DESC	list({sortBy: 'id', sortDesc: true})
	Limit results to the first n results	LIMIT 25	list({limit: 25})
	Skip first n results (typically used with LIMIT for pagination)	OFFSET 10	list({offset: 10})

Repository-specific Functions

Each domain-specific repository is derived from the base Repository object. The constructor for these domain-specific repositories sets up a base query for listing data, starting at the bare minimum SELECT * FROM table. This query can then be run using the .list() function, or built upon by chaining together query objects. A .count() method is also available in most repositories. In order to execute a straightforward list function for a given domain (let's say nodes), the following code can be used:

// initialize the repository
let repo = new NodeRepository();

// list the results- executes SELECT * FROM current_nodes
const results = await repo.list();

// ...or count them- executes SELECT COUNT(*) FROM current_nodes
const count = await repo.count();

Note that the repo object is declared using let instead of const. This will come in handy later when chaining together repo query objects.

Domain repositories have their own query functions that can be used for ease of access. Query functions take the structure of columnName(operator, value). There are several different operator types available:

Operator	Description	Example
'eq' or '=='	equals	name = 'bob'
'neq' or '!='	not equals	name != 'bob'
'like'	wildcard pattern matching (uses _ and % as wildcards)	name ILIKE 'b_b' - returns 'bob', 'bib', etc name ILIKE 'b%b' - returns 'bob', 'barb', 'bathtub', etc
'<'	less than	id < 1000
'>'	greater than	id > 10
'%'	trigram matching (click here for more info)	name % 'bob'
'in'	check if object is in list	name in ('billy','bob','joe')
'between'	check if object is between two values	id between 10 AND 1000
'is null'	checks for null. does not take a value.	name IS NULL
'is not null'	checks for not null. does not take a value.	name IS NOT NULL

Below is an example of using a few query functions to narrow down list results from the node repository:

let repo = new NodeRepository();

const metatypeNames = ['Asset', 'Action', 'Requirement']

// the where function initializes a where clause
repo = repo.where()
    .containerID('eq', 123)
    .and()
    .metatypeName('in', metatypeNames)
    .list()
// SQL of above statement:
// SELECT * FROM current_nodes
// WHERE container_id = 123
// AND metatype_name IN ('Asset', 'Action', 'Requirement')

Listing Options

Manipulating the SELECT-list

Choosing Tables to Query From

Basic Querying

Advanced Querying