ts-rsql-to-sql

What does it do?

Transforms the AST produced from ts-rsql into a SQL predicate that is suitable to append to a base query and execute.

Consider a service that lists players in a game based on the number of points they have in descending order and then alphabetically by name.

select u.firstName    as "firstName",
       u.lastName     as "lastName",
       u.email,
       u.active,
       u.dob,
       u.tier,
       u.id,
       u.pointbalance as points
from tsrsql.users u
order by u.pointbalance DESC, u.lastname, u.firstname, u.id

| points | lastName | firstName | email | active | dob | tier | id | |:-------|:---------|:----------|:--------------------|:-------|:-----------|:-------|:-------------------------------------| | 3 | Cupcake | Charlie | [email protected] | true | 1960-03-05 | GOLD | 0399c724-5829-5458-b7ac-ac6a298e0e4b | | 2 | Banana | Bob | [email protected] | true | 1960-02-04 | SILVER | 7139e81e-dc13-54d1-8c10-6fe6f7bfb34e | | 1 | Apple | Alice | [email protected] | false | 1960-01-03 | BRONZE | 7fd757a2-2173-5a60-8d25-615994740358 |

Context and configuration for the SQL transform

| SqlContext Field | Description | |-----------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------| | values: Value[] | new array per query, typically just [] | | selectors: Record<string, string or SelectorConfig> | static config that is either inlined or declared at file scope | | lax?: true | if present, selectors are not required to be defined, but are enforced if defined |

Values extracted from the filter and order by handling are appended to this array. The length after adding a value determines the offset for its query parameter ($1, $2, etc).

Note that if the base query already has query parameters then the values array should contain those parameters to ensure any newly generated parameters do not conflict.

Filtering

The query builder leverages the RSQL expression parser from ts-rsql and transforms the resulting AST to SQL.

| RSQL | SqlContext Selector Config | SQL Output | Values | |--------------|-----------------------------------------------------|---------------------|---------| | points>500 | none | points>$1 | [500] | | points>42 | { points: u.pointbalance } | u.pointbalance=$1 | [42] | | points>42 | { points: { type: integer, sql: u.pointbalance} } | u.pointbalance=$1 | [42] | | points>abc | { points: { type: integer, sql: u.pointbalance} } | validation error | |

• Output is a parameterized query
• The building of the parameterized query appends to the Values array.
• Selector configuration is either a string or object.
• If the type of the selector is known then the value is validated.

Sorting

The order by expression builder leverages the sort expression parser from ts-rsql and transforms the resulting AST to SQL.

| Sort Expressions | SqlContext Selector Config | SQL Output | |------------------------------------|--------------------------------|-----------------------------------------------------------| | -points, lastName, firstName, id | none | order by points DESC, lastName, firstName, id | | -points, lastName, firstName, id | { points: "u.pointbalance" } | order by u.pointbalance DESC, lastName, firstName, u.id |

The order by expression builder use the same configuration in the SQLContext for its selectors.

Filtering, Sorting, and Pagination

This library implements the Seek Method for its pagination.

The target SQL dialect is Postgresql since it supports the SQL92 "row values" syntax for a SELECT.

SqlContext for the first page of results

Sort Expression: -points, lastName, firstName, id

SQLContext

const context: SqlContext = {
    values: [],
    mainQuery: "select * from tsrsql.users u",
    selectors: {
        points: {
            sql: "u.pointBalance",
            type: "integer"
        },
        lastName: "u.lastName",
        firstName: "u.firstName",
        id: "u.id"
    }
};

SQL Output: order by u.pointbalance DESC, u.lastName, u.firstName, u.id

SqlContext for the page after the keyset row

Sort Expression: -points, lastName, firstName, id

SQLContext (same as above)

SQL Output:

(u.pointbalance,u.lastName,u.firstName,u.id)<($1,$2,$3,$4)
order by u.pointbalance DESC, u.lastName, u.firstName, u.id

• output includes "row-values" syntax to implement the seek.
• The values from the encoded keyset parameter are appended to the values array

Creating the keyset value

Using the same sort order example of -points,lastName,firstName,id:

const rows = db.manyOrNone<UserRecord>(sql, context.values);
if (rows.length>0) {
    // pass keyset back to client so they can fetch the next page
    const keyset = toKeySet(lastRowToKeySet(rows[rows.length-1], sorts, context));
}

Building and running a query

See live-db.it.ts for how complete queries are built and run.

import {parseSort} from "ts-rsql";

const context: SqlContext = {
    values: [],
    mainQuery: "select * from tsrsql.users u",
    selectors: {
        points: {
            sql: "u.pointBalance",
            type: "integer"
        },
        lastName: "u.lastName",
        firstName: "u.firstName",
        id: "u.id"
    }
};

const filter: string | null = null; // should come from query parameter, mapped by app
const sort: string | null = null; // should come from query parameter, mapped by app
const keyset: string | null = null; // should come from query parameter, mapped by app 

// parsing the sorts into an array here for 
// possible reuse below in building a keyset.
const parsedSorts: SortNode[] = sort && sort !== "" ? parseSort(sort) : [];

const sql = assembleFullQuery(
    {
        filter,
        sort: parsedSorts,
        keyset,
    },
    context
);
if (sql.isValid) {
    const rows = await db.manyOrNone(sql.sql, context.values);
    let keysetForNextRequest: string | null = null;
    if (rows.length > 0) {
        invariant(rows[rows.length - 1]);
        // note that the function that builds the keyset expects to 
        // operate on the row shape from the query.
        // Also note the re-use of the parsedSorts array here
        keysetForNextRequest = toKeySet(lastRowToKeySet(rows[rows.length - 1], parsedSorts, context));
    }
}

License

See LICENSE.