Skip to content

Forming Dynamic SQL with the Repository

Jump to bottom
Jaren Brownlee edited this page Feb 8, 2023 · 13 revisions

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 various SQL query clauses, their repo equivalent, any arguments the method may take, and examples of those arguments. These functions will be described in more detail below the table.

Desired Output SQL Clause Repo Method Arguments Example

Repository-specific Functions

Select all fields from table 
SELECT t.*
FROM table t
 list() with no arguments N/A 
repo.list()
Count all rows in table 
SELECT COUNT(*)
FROM nodes
 count() N/A 
repo.count()
Filter on a specific column using a wrapper function from the repository 
WHERE thisTable.name = 'bob'
 columnName(operator, value)

operator: comparison or matching operator for filter
value: value to match or compare to

 
repo.where().name('eq', 'bob')

Listing Options

Use grouping to aggregate data 
SELECT id, COUNT(*)
FROM nodes
GROUP BY id
 groupBy(fields, table?)

fields: string or array of strings specifying fields to group by.
table (optional): alias of table to select from (uses default table if none specfied)

 
repo.addFields('id')
    .groupBy('id')
    .count()

Limit (select first n) results

 
LIMIT 25
 list({limit})

limit: integer specifying number of records to limit to.
This is one of many query options. Note that there may be another argument to .list() before options, as this function is repo-specific.

 
repo.list({limit: 25})
Order results 
ORDER BY name DESC
 list({sortBy, sortDesc})

sortBy: column to sort by
sortDesc: boolean indicating whether to sort descending. Defaults to false, which sorts query in ascending order.
These are two of many query options. Note that there may be another argument to .list() before options, as this function is repo-specific.

 
repo.list({sortBy: 'name', sortDesc: true})

Offset (skip first n) results

 
OFFSET 10
 list({offset})

offset: integer specifying number of records to skip.
This is one of many query options. Note that there may be another argument to .list() before options, as this function is repo-specific.

 
repo.list({offset: 10})
Eliminate duplicate records 
SELECT DISTINCT t.*
 list({distinct})

distinct: boolean specifying whether to SELECT DISTINCT.
This is one of many query options. Note that there may be another argument to .list() before options, as this function is repo-specific.

 
repo.list({distinct: true})
Eliminate duplicates for one field 
SELECT DISTINCT ON (t.id) t.*
 list({distinct_on})

distinct_on: string specifying column to select distinct on.
This is one of many query options. Note that there may be another argument to .list() before options, as this function is repo-specific.

 
repo.list({distinct_on: 'id'})

Manipulating the SELECT-list

Select specific fields from table (replacing the default SELECT *) 
SELECT t.x, t.y, t.z
 select(fields, table?)

fields: string or array of strings specifying fields to be selected. This replaces the default select-list.
table (optional): alias of table to select from (uses default table if none specfied)

 
repo.select(['x', 'y', 'z'], 't')
Add fields to current select-list (in addition to the default SELECT *) 
SELECT t.*, t2.name
 addFields(fields, table?)

fields: string or array of strings specifying fields to be selected. This adds to the default select-list.
table (optional): alias of table to select from (uses default table if none specfied)

 
repo.addFields(['name'], 't2')

Choosing Tables to Query From

Override default table 
FROM new_table nt
 from(table, alias?)

table: name of new table to select from
alias (optional): alias of new table (if none specified, alias will be auto-generated)

 
repo.from('new_table', 'nt')
Join two tables 
FROM books b
INNER JOIN authors a
ON b.author_id = a.id
 join(destination, options, origin?)

destination: table you want to join to
options: conditions for the join clause, alias of destination table, and join type. See below for more details.
origin (optional): table you want to join from (uses default table if none specified)

 
repo.from('books', 'b')
    .join('authors', {
            conditions: {origin_col: 'author_id', destination_col: 'id'}, 
            destination_alias: 'a', 
            join_type: 'INNER'
        }
    )

Basic Querying

Filter on a column that does not have a repo wrapper function 
WHERE notThisTable.name = 'bob'
 query(column, operator, value, conditions?)

column: the column to query on
operator: comparison or matching operator for filter
value: value to match or compare to
conditions: data type (for casting value), table name and table alias (if none specified, uses default table). For details on query conditions, see below.

 
repo.where()
    .query('name', 'eq', 'bob', {tableAlias: 'notThisTable'})
Filter on property of JSON column 
WHERE properties->>'name' = 'bob'
 queryJsonb(key, column, operator, string, conditions?)

key: subproperty to filter on
column: name of JSON column which contains subproperty
operator: comparison or matching operator for filter
value: value to match or compare to
conditions: data type (for casting value), table name and table alias (if none specified, uses default table). For details on query conditions, see below.

 
repo.where()
    .queryJsonb('name', 'properties', 'eq', 'bob')
Combine two filters 
WHERE name = 'bob'
AND state = 'ID'
 query(...args).and().query(...args) N/A 
repo.where()
    .name('eq', 'bob')
    .and()
    .state('eq', 'ID')
Use one filter or another 
WHERE name = 'bob'
OR state = 'ID'
 query(...args).or().query(...args) N/A 
repo.where()
    .name('eq', 'bob')
    .or()
    .state('eq', 'ID')

Advanced Querying

Use one filter, then one or another 
WHERE containerID = 1
AND (name = 'bob' OR state = 'ID')
 and(repo)

repo: a new repository object containing the nested filters

 
repo.where()
    .containerID('eq', 1)
    .and(
        new MyRepository()
            .name('eq', 'bob')
            .or()
            .state('eq', 'ID')
    )
Use one filter or a combination of other filters 
WHERE containerID = 1 OR
(name = 'bob' AND state = 'ID')
 or(repo)

repo: a new repository object containing the nested filters

 
repo.where()
    .containerID('eq', 1)
    .or(
        new MyRepository()
            .name('eq', 'bob')
            .and()
            .state('eq', 'ID')
    )
Filter using a subquery 
WHERE import_id IN (
    SELECT sub.id
    FROM imports sub
    WHERE sub.data_source_id = 123
)
 subquery(repo)

repo: a new repository object containing the subquery

 
const sub = repo.subquery(
    new MyRepository()
        .select('id', 'sub')
        .from('imports', 'sub')
        .where()
        .query('data_source_id', 'eq', 123, {tableAlias: 'sub'})
)
repo = repo.where().import_id('in', sub)

Repository-specific Functions

Listing Options

Manipulating the SELECT-list

Choosing Tables to Query From

Basic Querying

Advanced Querying

Examples

DeepLynx Wiki

Sections marked with ! are in progress.

Building DeepLynx

DeepLynx Overview

Getting Started

Building From Source

Admin Web App

Deploying DeepLynx

Integrating with DeepLynx

Using DeepLynx

Ontology

Data Ingestion

Timeseries Data

Manual Path
Automated Path
File/Blob Storage

Data Querying

Event System

Data Targets

Developing DeepLynx

Developer Overview

Project Structure and Patterns

Data Access Layer

Development Process

Current Proposals

Clone this wiki locally