npm package: @protoutil/celql
Description: A set of utilities for validating and converting CEL expressions to SQL WHERE clauses to be used in queries. The goal of this library is to assist in authoring filters which can be parsed, type-checked, stanitized, and translated to a database query both 
Latest version: 0.2.5

# @protoutil/celql

A set of utilities for validating and converting CEL expressions to SQL WHERE clauses to be used in queries. The goal of this library is to assist in authoring filters which can be parsed, type-checked, stanitized, and translated to a database query both in the browser and on the web. It is not meant to be a full-featured SQL replacement. It is also not meant to provide an integration with any JavaScript framework (i.e. storing filter state in URL query parameters or providing UI components). It is a goal to eventually provide such utilities. But, they will be implemented in separate, framework-specific libraries.

## Install

Use your configured package manager to install the `@protoutil/celql` package. i.e. install from npm using `npm install @protoutil/celql`.

## Usage

First, you will need to let the CEL environment know about your columns. This can be done by defining a CEL `Env` instance. You can define each column with its CEL-equivalent type individually:

```typescript
import { variable } from '@protoutil/cel';
import { DefaultEnv } from '@protoutil/celql';

const env = new DefaultEnv(
  variable('name', StringType),
  ...
);
```

Or, if you have a protobuf representation of your database table, you can define them all in one go with `declareContextProto`:

```typescript

import { declareContextProto } from '@protoutil/cel';
import { DefaultEnv } from '@protoutil/celql';
import { MySchema } from './gen/myschema_pb.js';

const env = new DefaultEnv(
  declareContextProto(MySchema),
  ...
);
```

Now, you can convert your CEL expressions to SQL:

```typescript
import { translatePostgres } from '@protoutil/celql';

const whereClause = translatePostgres('my_column == "foo"', env);
// Will output { sql: 'my_column = $1', vars: ['foo'] }
```

SQL output is separated into a query clause and an array of variables. This is done so user input can be sanitized. This can be done either by manual sanitization or using a parameterized query. Ideally, both. The exception is that `Timestamp` values will be printed in 'yyyy-mm-dd hh:mi:ss.us' format with millisecond resolution. String parsing of `Timestamp` values will throw an error with invalid inputs which should disallow any unsanitized malicious user input.

If you want to validate your expression before sending it to the server to be converted, you can do that with the `compile` function:

```typescript
import { compile } from '@protoutil/celql';

try {
  // This will fail because it uses an invalid column and does not evaluate to a boolean expression
  compile('invalid_column + 1', env);
} catch (e) {
  // Handle your error
}
```

In addition to the defaults (`DefaultEnv`, `DefaultDialect`, `translateDefault`), this library also exports Postgres-specific functionality (`PostgresEnv`, `PostgresDialect`, `translatePostgres`). It is an eventual goal to provide environments, dialects, and translation functions for many different flavors of database.

### Notes

- `Timestamp` values will be formatted in 'yyyy-mm-dd hh:mi:ss.us' format with millisecond resolution. Successful querying will depend on how your flavor of SQL handles those inputs. You may need to specify a fractional section resolution for `Timestamp` columns (i.e. `TIMESTAMP(3)`). You can override this behavior with a custom dialect.

### Expressions

The `DefaultEnv` supports most default CEL expressions. But, the purpose of this library is to translate expressions to SQL clauses. As a result, some functionality is either not implemented or may have different signatures. There are also built-in SQL-specific functions.

#### Not Implemented

TODO: Document functionality that is not implemented

#### Modified Functions

##### String Functions

The string `contains`, `endsWith`, and `startsWith` member functions can optionally take a boolean `ignoreCase` parameter to control case sensitivity. Passing `true` will make their searches case insensitive. It is important to note that this parameter may cause the `LOWER` function to be called. So, creating a lower-case index of string columns may significantly improve performance for these queries.

```typescript
my_column.contains('foo', true); // Will output a case-insensitive query i.e. ILIKE for PostgreSQL
my_column.contains('foo', false); // Will output a case-sensitive query i.e. LIKE for PostgreSQL
```

##### Timestamp Functions

`timestamp` functions optionally take a second parameter which corresponds to the time zone.

```typescript
timestamp('2023-01-01T12:34:56Z', 'America/New_York'); // Will output { sql: `TIMESTAMP '2023-01-01 12:34:56.000' AT TIME ZONE $1`, vars: ['America/New_York'] }
```

#### New Functions

##### Date

**Signatures:**

- `date(date) -> date` (identity)
- `date(string) -> date` converts a string to a `Date`
- `date(timestamp) -> date` converts a `Timestamp` to a `Date`

```typescript
date(my_column) == date('2023-10-01'); // Will output { sql: 'DATE(my_column) = DATE($1)', vars: ['2023-10-01'] }
```

##### Time

**Signatures:**

- `time(time) -> time` (identity)
- `time(string) -> time` converts a string to a `Time`
- `time(timestamp) -> time` converts a `Timestamp` to a `Time`

```typescript
time(my_column) == time('12:34:56'); // Will output { sql: 'TIME(my_column) = TIME($1)', vars: ['12:34:56'] }
```

##### Timezones

**atTimeZone** \- Converts a `Timestamp` to the specified time zone.

```typescript
my_column.atTimeZone('America/New_York'); // Will output { sql: 'my_column AT TIME ZONE $1', vars: ['America/New_York'] }
```

##### String Functions

**lower** \- Converts a string to lower case.

```typescript
my_column.lower(); // Will output { sql: 'LOWER(my_column)', vars: [] }
```

**upper** \- Converts a string to upper case.

```typescript
my_column.upper(); // Will output { sql: 'UPPER(my_column)', vars: [] }
```

**trim** \- Trims whitespace from a string.

```typescript
my_column.trim(); // Will output { sql: 'TRIM(my_column)', vars: [] }
```

**like** \- Tests whether the operand matches a pattern. Uses the `LIKE` logical operator and can optionally take a boolean `ignoreCase` parameter to control case sensitivity. It is important to note that this parameter may not have an effect on all databases. For example, MySQL `LIKE` queries are case-insensitive by default.

```typescript
my_column.like('foobar'); // Will output { sql: 'my_column LIKE $1', vars: ['foobar'] }
my_column.like('foobar', true); // Will output { sql: 'my_column ILIKE $1', vars: ['foobar'] } for PostgreSQL
!my_column.like('foobar'); // Will output { sql: 'NOT my_column LIKE $1', vars: ['foobar'] }
```

### Custom Dialects

You are able to define your own `Dialect` class and add functions by extending the CEL environment:

```typescript
import { DefaultEnv, DefaultDialect, translate } from '@protoutil/celql';
import { BoolType, func, overload, StringType } from '@protoutil/cel';

const myFuncOverload = 'myFunc';

class MyDialect extends DefaultDialect {
  override functionToSqlOverrides(unparser: Unparser, functionName: string, args: Expr[]): boolean {
    switch (functionName) {
      case myFuncOverload:
        unparser.visit(args[0]);
        unparser.writeString(' MY_CUSTOM_OPERATOR ');
        unparser.visit(args[1]);
        return true;
      default:
        return super.functionToSqlOverrides(unparser, functionName, args);
    }
  }
}

const env = new DefaultEnv(
  ...,
  func(myFuncOverload, overload(myFuncOverload, [StringType, StringType], BoolType))
)

translate(`myFunc('a', 'b')`, env, new MyDialect());
// Will output: { sql: '$1 MY_CUSTOM_OPERATOR $2', vars: ['a', 'b'] }
```

## Contributing

### Building

Run `nx build celql` to build the library.

### Running unit tests

Run `nx test celql` to execute the unit tests via [Jest](https://jestjs.io).