Proposal: Strong-typed queries

[ffMathy]

This is going to sound a bit crazy, but hear me out. There are currently a number of issues with this proposal, but it exists solely for the sake of demonstration. We can probably get around the limitations.

I have been experimenting with making the query function strong-typed, so that queries are validated at compile-time.

Features

• Validates that type names are correct in the query.
• Validates that the projection contains valid property names.
• Validates the criteria section (where), including.
  • Checking for valid property names.
  • Checking for valid operators for particular types.
    • string only allows:
      • = and is.
      • != and is_not.
      • in and not_in.
      • like and not_like.
    • number only allows:
      • = and is.
      • != and is_not.
      • in and not_in.
      • >, after and greater_than.
      • <, before and less_than.
      • >= and <=.
    • boolean only allows:
      • = and is.
      • != and is_not.
      • in and not_in.
    • object only allows:
      • has.
      • any.
  • Checking for valid where values.
    • If the property name does not exist, it gives a compile error.
    • If for instance the property name being queried is a string, it must be wrapped in quotes.
    • If the property name being queried is a number, it must be numeric.

Current limitations

If you like where this is headed, I am willing to invest in trying to resolve these limitations. So please don't see them as direct show-stoppers. Let's have a discussion about it.

• I'd like this to work with infer, so that instead of writing strongTypedQuery<Foo>('select fooProperty1 from Foo'), it can just be inferred from strongTypedQuery('select fooProperty1 from Foo'), since Foo is already a valid type name. This would be awesome, and would automatically add types for all queries, especially in combination with the type generator project.
• Right now, has and any is not being validated. Can easily add this.
• Right now, the Whitespace string literal only accepts a single whitespace. So an arbitrary amount of whitespaces between for instance the property names is not allowed. This could be a problem with multiline etc. And adding more might impact the type checking performance.
• and and or is not supported in the where clause. This is easy to add, but only with a specific maximum amount of potential properties being selected (due to TypeScript not supporting recursive string literal types). There may be ways to work around this.
• The projection part can only contain one property. This is easy to fix, but only with a specific maximum amount of potential properties being selected (due to TypeScript not supporting recursive string literal types). There may be ways to work around this.
• Right now, date types are not supported, but again very easy to add and even validate.

The code

Here's the code. Notice the strongTypedQuery function. That's where the magic happens. For every call to that, I've commented particular cases that the strong-typings catch.

type Foo = {
    fooProperty1: string,
    fooProperty2: number,
    fooProperty3: boolean
}

type Bar = {
    barProperty1: string,
    barProperty2: number,
    barProperty3: boolean
}

interface EntityTypeMap {
    Foo: Foo,
    Bar: Bar
}


type EntityTypes = EntityTypeMap[keyof EntityTypeMap];

type EntityTypeProperty<TEntityType extends EntityTypes, TPropertyType> = { 
    [K in keyof TEntityType]: TEntityType[K] extends symbol ? 
        never : 
        TEntityType[K] extends TPropertyType ? 
            K :
            never
}[keyof TEntityType];

type EntityType<TEntityType extends EntityTypes> = keyof EntityTypeMap;


type Whitespace = ` `;

type OptionalWhitespace = Whitespace | "";


type Projections<TEntityType extends EntityTypes, TPropertyType> = 
    `${EntityTypeProperty<TEntityType, TPropertyType>}`;
    
type ProjectionPrefix<TEntityType extends EntityTypes> = 
    `select${Whitespace}${Projections<TEntityType>}${Whitespace}from${Whitespace}` |
    "";


type CriteriaOperator<T> = 
    T extends string ?
        (
            `${'='|'is'}` |
            `${'!='|'is_not'}` |
            `${'in'}` |
            `${'not_in'}` |
            `${'like'}` |
            `${'not_like'}`
        ) :
        T extends number ?
            (
                `${'='|'is'}` |
                `${'!='|'is_not'}` |
                `${'in'}` |
                `${'not_in'}` |
                `${'>'|'after'|'greater_than'}` |
                `${'<'|'before'|'less_than'}` |
                `${'>='}` |
                `${'<='}`
            ) :
            T extends boolean ?
                (
                    `${'='|'is'}` |
                    `${'!='|'is_not'}` |
                    `${'in'}` |
                    `${'not_in'}`
                ) :
                T extends Object ?
                    (
                        `${'has'}` |
                        `${'any'}`
                    ) :
                    never;

type CriteriaValue<T> = 
    T extends number ?
        `${number}` :
        T extends boolean ?
            `${boolean}` :
            T extends string ?
                `'${string}'` :
                never;

type CriteriaAndValue<TEntityType, TPropertyType> = 
    `${EntityTypeProperty<TEntityType, TPropertyType>}${Whitespace}${CriteriaOperator<TPropertyType>}${Whitespace}${CriteriaValue<TPropertyType>}`

type Criteria<TEntityType extends EntityTypes> = {
    [K in keyof TEntityType]: 
        `${CriteriaAndValue<TEntityType, TEntityType[K]>}`
}[keyof TEntityType];

type CriteriaSuffix<TEntityType extends EntityTypes> = 
    `${Whitespace}where${Whitespace}${Criteria<TEntityType>}` |
    ``;


type Query<TEntityType extends EntityTypes> = 
    `${ProjectionPrefix<TEntityType>}${EntityType<TEntityType>}${CriteriaSuffix<TEntityType>}`


function strongTypedQuery<TEntityType extends EntityTypes>(query: Query<TEntityType>) {
    return null!;
}


//valid, because "Foo" is a known type.
strongTypedQuery<Foo>("Foo");

//gives compile error, because "Blah" is not a valid type.
strongTypedQuery<Blah>("Blah");


//valid, because fooProperty1 exists on Foo
strongTypedQuery<Foo>("select fooProperty1 from Foo");

//gives compile error, because fooProperty1 does not exist on bar.
strongTypedQuery<Bar>("select fooProperty1 from Bar");


//valid, because fooProperty1 exists on Foo, and fooProperty1 is of type string.
strongTypedQuery<Foo>("Foo where fooProperty1 = 'abc'");

//gives compile error, because fooProperty1 is of type string, not number, so the value has to be wrapped in quotes.
strongTypedQuery<Foo>("Foo where fooProperty1 = 2388");

//gives compile error, because fooProperty1 is of type string, not boolean.
strongTypedQuery<Foo>("Foo where fooProperty1 = true");


//valid, because fooProperty2 is a number.
strongTypedQuery<Foo>("Foo where fooProperty2 = 24");

//gives compile error, because fooProperty2 is a number, not a string, so the value can't be wrapped in quotes.
strongTypedQuery<Foo>("Foo where fooProperty2 = 'abc'");

//gives compile error, because fooProperty2 is a number, not a boolean.
strongTypedQuery<Foo>("Foo where fooProperty2 = true");


//valid, because fooProperty3 is of type boolean.
strongTypedQuery<Foo>("Foo where fooProperty3 = false");

//gives compile error, because fooProperty3 is a boolean, so quotes are not allowed.
strongTypedQuery<Foo>("Foo where fooProperty3 = 'abc'");

//gives compile error, because fooProperty3 is a boolean, so numbers are not allowed.
strongTypedQuery<Foo>("Foo where fooProperty3 = 28");


//valid, because fooProperty1 exists on Foo, and fooProperty1 is of type string.
strongTypedQuery<Foo>("Foo where fooProperty1 = 'abc'");

//gives compile error, because fooProperty1 is a string, so the "<=" operator is not allowed.
strongTypedQuery<Foo>("Foo where fooProperty1 <= 'abc'");

//gives compile error, because fooProperty1 is a string, so the "has" operator is not allowed.
strongTypedQuery<Foo>("Foo where fooProperty1 <= 'abc'");


//long query syntax can also be used.
strongTypedQuery<Foo>("select fooProperty1 from Foo where fooProperty1 = 'abc'");

Playground link

Play around with it here. Right now it gives an error in line 90, but it doesn't seem to be a valid error. I think it actually might be a bug in TypeScript.

[gismya]

This is super cool and absolutely crazy. I love it. I've spent the hour reading and processing it.

Could you elaborate more on what the goal for this is and what use cases you see?

I think some of the errors of the playground link might come from mixups between the keyof EntityTypeMap (That you call EntityTypes) and the EntityTypeMap[keyof EntityTypeMap] (That you call EntityTypes). In the type generator, we call them EntityType and EntityData to make them easier to distinguish from each other.

[gismya]

I also had some concerns and did some quick experiments in the same vein to flesh out my thinking.
First of all I don't think TypeScript is designed for this kind of string parsing and has a high risk of leading to some of the issues you highlight in the limitations section. Specifically, I think that there is a high risk for performance problems and the brittleness might be a big issue.

Handling non-primitive types will also present issues. Mainly when using nested selects (Child.GrandChild), but there might be other issues as well.

The return values of the queries won't be more strongly typed than before. We will only validate that it's not an invalid query, but we still won't know if the API has the information to return or if the property will return empty.

A final problem is that the logic needed to get this to work will be too complicated which causes issues both with maintaining and also, for hypothetical future users, tracking back and parsing why they get the dreaded red squigglies will require really good code comments and would probably still take some effort.

All in all, I really like the idea and attempt but I have a hard time seeing that the practical applications (Unless I'm failing to imagine big practical uses) will be worth investing the time and effort that would be required to finalize this.

[ffMathy]

Thank you for chiming in.

I agree with your assessment. It makes a lot of sense, and makes me think the same.

The reason I'd be interested in this is due to us often changing our schema. We'd like to potentially catch errors in that regard that might happen at runtime.

But I think e2e tests are better suited for that at second glance.

Oh well, it was fun to experiment with! 😁

[gismya]

In individual projects, besides e2e tests, it could be a good idea to look into something like Zod or lizod for runtime validation of the results. It validates and strongly types the resulting data.

[ffMathy]

Thank you.

What about a builder pattern approach?

Something like:

session.query(q => q
    .from(f => f.Foo)
    .withProjections(
        p => p.fooProperty1,
        p => p.fooProperty2,
        p => p.fooProperty3
    )
    .withCriteria(c => c
        .and(
            a => a
                .property(p => p.fooProperty1)
                .is("hello world"),
            a => a
                .property(p => p.fooProperty2)
                .lessThan(42),
            a => a
                .property(p => p.fooProperty2)
                .notIn(56, 57)
        )
    )
    .build()
)

//would produce: "select fooProperty1, fooProperty2, fooProperty3 from Foo where fooProperty1 is 'hello world' and fooProperty2 < 42 and fooProperty2 not_in (56, 57)"

We could strong-type it, and even retain backwards compatibility by checking if the query is typeof "function".

Now, it could use a clever trick to get the names of properties of interfaces and types:
https://www.npmjs.com/package/@fluffy-spoon/name-of

This package (under the hood) uses EcmaScript (and Node's) Proxy object (see more here to infer names of types not available at runtime. Perfectly safe and stable.

Full code of the name-of package is here (look at how simple it is):

function getPropertyNameInternal<T = unknown>(expression: (instance: T) => any, options: {
    isDeep: boolean
}) {
    let propertyThatWasAccessed = "";
    var proxy: any = new Proxy({} as any, {
        get: function(_: any, prop: any) {
            if(options.isDeep) {
                if(propertyThatWasAccessed)
                    propertyThatWasAccessed += ".";
                
                propertyThatWasAccessed += prop;
            } else {
                propertyThatWasAccessed = prop;
            }
            return proxy;
        }
    });
    expression(proxy);

    return propertyThatWasAccessed;
}

export function getPropertyName<T = unknown>(expression: (instance: T) => any) {
    return getPropertyNameInternal(expression, {
        isDeep: false
    });
}

export function getDeepPropertyName<T = unknown>(expression: (instance: T) => any) {
    return getPropertyNameInternal(expression, {
        isDeep: true
    });
}

And here's usage example:

import { getPropertyName, getDeepPropertyName } from '@fluffy-spoon/name-of';

interface SomeType {
  foo: boolean;

  someNestedObject: {
      bar: string;
  }
}

console.log(getPropertyName<SomeType>(x => x.foo)); //prints "foo"
console.log(getPropertyName<SomeType>(x => x.someNestedObject)); //prints "someNestedObject"
console.log(getPropertyName<SomeType>(x => x.someNestedObject.bar)); //prints "bar"

console.log(getDeepPropertyName<SomeType>(x => x.foo)); //prints "foo"
console.log(getDeepPropertyName<SomeType>(x => x.someNestedObject)); //prints "someNestedObject"
console.log(getDeepPropertyName<SomeType>(x => x.someNestedObject.bar)); //prints "someNestedObject.bar"

[ffMathy]

It could also help alleviate #137 and therefore be an alternative to #139.

[ffMathy]

I tried making this in the playground. In my opinion, it works quite well.

Here is the link.

[jimmycallin]

as @gismya mentioned in the PR, this is cool but not within the scope of the api client

[ffMathy]

Yeah. Good call on cleaning it up.