Typeorm: LINQ-style query builder

[srolel]

First I'd like to say this is an Impressive framework!

Back on topic, I assume you have considered a more functional (rather than string-based) querying mechanism (like LINQ to SQL). What are the limitations and/or difficulty of implementing something like that?

[pleerock]

Thank you.

I assume you have considered a more functional

yes I did, ideally I would like it to make more functional and more type safe

What are the limitations and/or difficulty of implementing something like that?

design. Needs good proposal and good design how this new type of query builder should be implemented with the mind of the language limitations. If you have some ideas, make a proposal and lets discuss it

[lorenzofox3]

Hi
For one of my project I developed few month ago ship-hold which is based around simple query builders (see query builder repo) which you can use without my framework (they generate string).

I did not have much traction and so not much feedbacks either but I personally find at least that the api of the builders is quite nice to use: close enough to sql to have fine control but bringing as well simplicity through its abstraction/ composition.
It is made only for Postgres dialect but easily portable I think. The "type" dimension is not there tough. I am not sure what you expect there.

cheers !

[pleerock]

@lorenzofox3 hey, nice lib! Current query builder in typeorm work pretty fine and there is no real issues with it I think. What @Mosho1 suggests is to add more functional approach which may be really good if it will be designed well. I also wish to make query builder typesafe (not string based), maybe some of the latest typescript features can simply approaching of it, but still the problem in the design and its proposal. Need good proposal how we should design a new query builder API which should be more functional and more typesafe

[srolel]

So I'm trying to come up with something, somewhat stuck at the moment. Say you have this class which represents a model for a table:

class Photo {
    id: number;
    name: string;
    description: string;
    fileName: string;
    views: number;
    isPublished: boolean;
}

I would have liked to introduce the following type:

type QueryableModel<T> {
    [P in keyof T]: QueryableProp<T[P]>;
}

Where QueryableProp<T> represents a column and has all the utility methods needed to compose queries. For example for const x: QueryableProp<string>;, x.toLowerCase() would produce something that corresponds to LCASE(x) in the final query. The problem is that I don't have a way to distinguish between the generic types in QueryableModel<T>, and so I can't provide type safety this way (QueryableProp<number> would also have x.toLowerCase()).

This can be solved by implementing the types at the model level (x: StringColumn instead of x: string in the model class) but that doesn't sound like a very attractive solution, having to use a type for each type of column (not to mention having to work with those types instead of native TS types outside the query builder) or having an extra class altogether for the query builder. Another option would be property decorators that change the decorated property's type but I'm not aware of any plans to do that in TS.

[pleerock]

Yeah there are lot issues you can face. I also was thinking about all this including playing with in keyof operator. Even more problems youll have if you think about joins. Nothing elegant come into my head.

[Chris2011]

What about using this: https://github.com/kutyel/linq.ts as a dependency and to work with?

[Chris2011]

All together so TypeORM and Linq.ts feels like the Entity Framework (EF) and LINQ for C# and would be awesome.

[pleerock]

this package has pure docs and I don't see something interesting from those docs. select and where is already available in query builder. Plus as I can see this package is not actively maintained.

TypeORM maybe have somethink like linq but community should provide a design proposal how it should look like.

[IRCraziestTaxi]

I just created a repository called typeorm-linq-repository from some code I wrote in a project of mine to meet exactly this need - more type-safe, LINQ-style queries. I think it is a great start and welcome more testing and feedback on it. I have used it on a basic level in my own code, nothing too complex, although I believe the design allows a fair amount of complexity, and as a fallback, I do expose a way to create a native TypeORM QueryBuilder.

[pleerock]

if someone want to implement linq in typeorm start with good and complete proposal

[Chris2011]

The thing is that LINQ TS uses callbacks and types, where, when I see it right your QueryBuilder uses strings, which is a bit more error.prone. See here the example:

import { List } from 'linqts';

let query = people.Join(pets,
    person => person,
    pet => pet.Owner,
    (person, pet) =>
        ({ OwnerName: person.Name, Pet: pet.Name }));

So you have fully type recognition while typing. This is more powerfull than strings. LINQ is the same as for C# and because of using TypeScript, we already knows the type etc.

[Chris2011]

Maybe it is not needed to use LINQ TS but to use a similiar technology for the querybuilder would be great.

[Chris2011]

And here the little demo: https://raw.githubusercontent.com/kutyel/linq/master/assets/linqts.gif

[pleerock]

yeah creating such library with simple array is a easy thing to do, actually where method works exactly same as native filter function. In ORM its a different story. Its impossible to do same type safe style things in query builder because of flexibility sql has.

[Chris2011]

But LINQ für C# does the same too, or not? The Entity Framework, will handle the SQL for you, not the LINQ stuff. AFAIK.

[srolel]

@Chris2011 Linq uses C#'s built in compiler to compile functions to SQL queries. It's pretty wicked functionality that JS (and many other languages) doesn't have.

[Chris2011]

Ok got it and I understand that point, but I think the QueryBuilder will create SQL too, so what is the problem while using this

// Linq.ts
Persons.select(p: Person => p).where(p: Person => p.PersonId === 2);

instead of this

// TypeORM QueryBuild - Maybe not correct syntax, but only an example.
Persons.where("person.personId=:id", { id: 2 })

[Chris2011]

The output inside the functions or the code will be the same, I guess.

[pleerock]

Do you understand that this expression:

p: Person => p.PersonId === 2;

returns you boolean, not string. You need string because you need this in sql query. You don't have p Person object until you load it from the database, its not a regular array that can be filtered this way.

[rhyek]

@Chris2011 Linq uses C#'s built in compiler to compile functions to SQL queries. It's pretty wicked functionality that JS (and many other languages) doesn't have.

This basically makes it impossible to do "linq to sql" in JS at runtime, no? Maybe through a Babel plugin? How could that work?

[pleerock]

@rhyek somehow true. I'll close this issue because there is no clear way of implementing linq in typescript. But please continue discussion on it and maybe we can figure out some solution

[Chris2011]

So maybe we can dicuss it on gitter or so.

[Aracturat]

@Chris2011, @rhyek We can try to use Typescript compiler API for it. (https://github.com/Microsoft/TypeScript/wiki/Using-the-Compiler-API , http://blog.scottlogic.com/2017/05/02/typescript-compiler-api-revisited.html) With it we can visit all nodes in AST of function and create equal SQL query for it.

[rhyek]

This seems like the way to go!

[pleerock]

Good to have any concrete yet simple example for typeorm

[Aracturat]

@rhyek @pleerock @Chris2011 Some example of using (with some code that does it):

Translate function (e => (e.Param1 == "3" || e.Param2 >= 4 && e.Param3 <= 2);
to string: "(e.Param1 = '3' or e.Param2 >= 4) and e.Param3 <= 2"

import * as ts from 'typescript'

function getArrowFunction(func: Function): ts.ArrowFunction {
    let src = ts.createSourceFile("test.ts", func.toString(), ts.ScriptTarget.ES2016, false);
    var expr = <ts.ExpressionStatement> src.statements[0];
    return <ts.ArrowFunction> expr.expression;    
}

function toSql(expr: ts.Node) {
    switch(expr.kind) {
        case ts.SyntaxKind.PropertyAccessExpression:
            var paExpr = <ts.PropertyAccessExpression> expr;
            var idObject = <ts.Identifier> paExpr.expression;
            return idObject.text + "." + paExpr.name.text;
        case ts.SyntaxKind.ParenthesizedExpression:
            var parExpr = <ts.ParenthesizedExpression> expr;
            return "(" + toSql(parExpr.expression) + ")";
        case ts.SyntaxKind.BinaryExpression:
            var bExpr = <ts.BinaryExpression> expr;

            var op = "";
            switch (bExpr.operatorToken.kind) {
                case ts.SyntaxKind.EqualsEqualsToken:
                    op = "=";
                    break;
                case ts.SyntaxKind.GreaterThanToken:
                    op = ">";
                    break;
                case ts.SyntaxKind.GreaterThanEqualsToken:
                    op = ">=";
                    break;
                case ts.SyntaxKind.LessThanToken:
                    op = "<";
                    break;
                case ts.SyntaxKind.LessThanEqualsToken:
                    op = "<=";
                    break;
                case ts.SyntaxKind.AmpersandAmpersandToken:
                    op = "and";
                    break;
                case ts.SyntaxKind.BarBarToken:
                    op = "or";
                    break;
            }

            return toSql(bExpr.left) + " " + op + " " + toSql(bExpr.right);
        case ts.SyntaxKind.NumericLiteral:
            var nlExpr = <ts.NumericLiteral> expr;
            return nlExpr.text;
        case ts.SyntaxKind.StringLiteral:
            var slExpr = <ts.StringLiteral> expr;
            return "'" + slExpr.text + "'";        
        default:
            return "[undefined]";
    }
}

var arrFunc = getArrowFunction(e => (e.Param == "3" || e.Param2 >= 4) && e.Param3 <= 2);

console.log(toSql(arrFunc.body));

[Aracturat]

@pleerock @Chris2011 @rhyek
Created new gist with the support of functions.
https://gist.github.com/Aracturat/4077e70292ffa995b37da78a35a1410b
e => (e.Param.toUpperCase().toLowerCase() == "3".toLowerCase().substring(0, 10) || e.Param2 >= 4) && e.Param3 <= 2
converted in
(LOWER(UPPER(e.Param)) = SUBSTRING(LOWER('3'), 0, 10) or e.Param2 >= 4) and e.Param3 <= 2

[pleerock]

@Aracturat your code snippet lack of usage examples... This looks like an extension for a language, right?

[Aracturat]

@pleerock @Chris2011 @rhyek
I have added examples here https://gist.github.com/Aracturat/4077e70292ffa995b37da78a35a1410b

/*
code below returns
    SELECT e.ParamString
    FROM Table e
    WHERE (LOWER(UPPER(e.ParamString)) = SUBSTRING(LOWER('3'), 0, 10) or e.ParamInt >= 4) and e.ParamString2 <= '2'
    ORDER BY e.ParamInt
*/
var result = new Collection<Test>()
    .where(e => (e.ParamString.toUpperCase().toLowerCase() == "3".toLowerCase().substring(0, 10) || e.ParamInt >= 4) && e.ParamString2 <= "2")
    .orderBy(e => e.ParamInt)
    .select(e => e.ParamString)
    .getSql();

[Aracturat]

@pleerock This code just convert TS function to AST and then to SQL code.

[rhyek]

This is very impressive. Great work as a proof-of-concept. In the end, the transformations should be applied when using the existing query builder. Query builder methods such as where, join, etc, should be able to accept either a string (current functionality) or functions (linq-to-sql).

[Aracturat]

@pleerock @Chris2011 @rhyek
The new version of gist supports complex select =) https://gist.github.com/Aracturat/4077e70292ffa995b37da78a35a1410b

/*
code below returns that
SELECT
e.ParamInt as IntParam,
UPPER(e.ParamString) as StringParam
FROM Table e
WHERE (LOWER(UPPER(e.ParamString)) = SUBSTRING(LOWER('3'), 0, 10) or e.ParamInt >= 4) and e.ParamString2 <= '2'
ORDER BY e.ParamInt
*/
    var result = new Collection<Test>()
    .where(e => (e.ParamString.toUpperCase().toLowerCase() == "3".toLowerCase().substring(0, 10) || e.ParamInt >= 4) && e.ParamString2 <= "2")
    .orderBy(e => e.ParamInt)
    .select(e => <any> {
        IntParam: e.ParamInt,
        StringParam: e.ParamString.toUpperCase()
    })
    .getSql();

[Aracturat]

@rhyek
I don't know how to insert this code in your library, because I haven't used it yet =( I just wanted to have simular library as Linq2Sql in .Net for TS.
Maybe it is a good idea to mark as deprecated old methods with strings as type unsafe and continue work only with new variant.

[rhyek]

@Aracturat this is not my library, lol. You might be right about deprecating the old methods. I was thinking it might be good to try to adapt what your transformations generate to what the current query builder methods expect as parameter, but that could mean unnecessary extra work and perhaps be impossible in some cases.

[Chris2011]

@Aracturat wow great Work. This Looks very nice :)

[Aracturat]

@Chris2011 thanks =) But it is just sample, we need to add a lot of mappings TS functions to SQL functions.
(And I don't know how to map for example "contain" (where Col in (1, 2, 3))) =(

[Aracturat]

@rhyek Oops =) You answered me a lot here and I thought that it is your library=)

[Chris2011]

@Aracturat in the first version of your Implementation using Callbacks, it is Not needed to Support everything. We can.provide it later time after time when wenn have a solution for specific cases. Maybe WE have alternatives for contains

[rhyek]

.where(e => [1, 2, 3].includes(e.ParamInt)) is how it should look like. It's been a while since I've used Entity Framework, but I think that's how it was.

You are right in that the amount of possible mappings will be huge, but even EF started somewhere and there are basic things that even now you can't do in it.

[rhyek]

.incudes is from Array.prototype.includes, btw.

[Aracturat]

@rhyek thanks! I haven't seen it in my editor before, because of wrong tsconfig =(

[Aracturat]

@rhyek I think EF is more complex product, because it does a lot of optimizations with queries. But my code just convert AST to SQL without any optimization and changes =(. But in future we can think about optimizations =)

[MaikelH]

@Aracturat that looks pretty nice :). Been looking into this the last couple of days and the the compiler option is probably the only way to go currently. The downside I noticed that it is not super fast unfortunately. For a single function it is probably ok, but multiple insertions can become problematic (maybe you can cache the compiled queries?).

The amount of mapping that needs to be done is a lot, but you can start off pretty simple. Then over time you can expand. The code that is used for that can also be used in other parts which is good and then we can probably make the whole interface typesafe.

@Aracturat EF has been in development for nearly 10 years, so it has a little headstart 😄.

[Aracturat]

@MaikelH I think than we need to rewrite my sample code using https://www.npmjs.com/package/flora-sql-parser .
So we can create SQL AST from LINQ AST and cache it (SQL AST). In future we can use it for query optimization.

[Aracturat]

@pleerock Would it be possible to include functionality with LINQ functions (as in examples before) to your library?

[Chris2011]

@Aracturat keep up the good work 👍

[MaikelH]

I have been going though the Typeorm source code today to get a better idea what needs to be done to enable this. On a global level this I think are the operations that are needed.

expression -> AST-generation -> AST-transformations -> SQL Generation -> SQL (string)

The AST-generation is handled by the Typescript compiler. The most important part here for us is the SQL
generation (essentially the example @Aracturat posted here). Looking at the QueryBuilder, most of the SQL generation is being done in the QueryBuilder itself and it descendants (e.g. where).

The code there joins the different where clauses and replaces the property names. Doing something similiar for the LINQ style queries should be pretty easy to do. While doing this some questions sprang to mind.

How to handle syntax differences between databases
In the current API the user is responsible for inputting strings that are valid SQL. In the LINQ style interface this responsibility is for Typeorm, since that generates the SQL completely. Especially functions can differ greatly between databases. This will lead to a lot more database specific code. We can put it all one place (SQL generation) or make separate modules for the different databases.

Do we deprecate the string syntax? If yes, do we do a hard break
If we make the two separate interfaces co-exist, we have to make sure that they compatible with each other. In my opinion they should co-exist for the near future, since you can use the current interface for complex queries which are not supported in the LINQ interface. The downside is that it increases the complexity and the amount of testing that needs to be done.

On what API's do we want/need LINQ style queries
Here I looked only at the QueryBuilder class, but these kind of functions can also be implemented for the Repository and probably others.

There are probably a lot of other things, but this what came to my mind right now.

I think getting LINQ style functions and the typesafe interface is hugely valuable to Typeorm. It is something that needs some decent engineering to get in. I will have some time in the coming weeks to spend some time on this and maybe get some PR's in.

@pleerock , is it an idea to make a new issue for an proposal where we can discuss the details and technical implementation?

[rhyek]

I think my initial instinct might be of some worth. It could be interesting to see if it's possible to integrate something like Knex which, if I'm not mistaken, already generates proper SQL for supported databases. Why not have the LINQ to SQL simply generate the parameters Knex requires?

user code

UserRepository.queryBuilder() // knex('users') under the hood
  .where(user => user.name === 'Freddy')
  .select(user => ({ id: user.id, name: user.name }));

after compile

UserRepository.queryBuilder()
  .where({ name: 'Freddy' })
  .select('id', 'name');

TypeORM + the power and community behind Knex might be worthwhile in itself.

[rhyek]

Maybe expose a small subset of the Knex API on TypeORM's repositories. After all, whereNot, whereIn, etc are not needed. The transformations would generate those, though.

before

UserRepository.queryBuilder()
  .where(user => user.name === 'Freddy' && !['Dale', 'John'].includes(user.name))

after

UserRepository.queryBuilder()
  .where({ name: 'Freddy' })
  .whereNotIn('name', ['Dale', 'John'])

```

[Aracturat]

@rhyek I think using Knex is a good idea. It will be faster to implement the mapping of LINQ to Knex API than to a lot of SQL functions.

[MichalLytek]

Looks like all ideas rely on parsing source code and then generating AST/sql. But do you want to do this on runtime? What about the situation when the code is transpilled to JS or even minified?
Or you want to do this a babel plugin or a weird hook on the typescript compiler? 😕

[rhyek]

@19majkel94 The idea is to hook into the Typescript compiler using the official Compiler API. This is not a hack.

I don't believe there is a way to do these transformations at runtime.

[MichalLytek]

AFAIK, TS compiler API allow you to manually load source file, traverse/navigate/manipulate and emit new code. You can do it manually like:

tsc docGenerator.ts --m commonjs 
node docGenerator.js test.ts

You can't just hook into the Typescript compiler that when I do tsc in root of my app (which is using typeorm), the compiler will understand that inside node_modules/typeorm there is a file that should be called like a babel plugin to transpile the linq-like repository code in my app code.

[MaikelH]

@19majkel94 It is not needed to hook into the compiler at compile time. We use the Typescript Compiler API at runtime and pass in the compiled functions. This compiled functions will be plain javascript, but since Typescript is a full superset of Javascript, the compiler can still generate a AST from it. So it does not matter it is compiled to Javascript. Minifying should also be fine in most cases, since minifying should not change the meaning of the code.

@rhyek Knex looks interesting. Definitely worth looking into, especially since that solves the big problem of mapping the functions and syntax differences between databases. We still need to go from AST to Knex though.

[pleerock]

It would be great if we start it as an extension to typeorm and see how good this idea will work.
I would really like to see some concrete and a bit more complete implementation of this.
But I don't really like you are using Knex naming in this repository 😒

[MichalLytek]

It could be interesting to see if it's possible to integrate something like Knex

I know that it

solves the big problem of mapping the functions and syntax differences between databases

But I don't really get the idea of mapping query builder to query builder, especially the new one. We already have pleerock's query builder which works with all the supported databases, so why don't use it like typeorm-linq-repository does? I would stick to them if syntax differences between databases are so common, or just do sql by hand as it can help creating optimal sql for our API.

[MaikelH]

But I don't really get the idea of mapping query builder to query builder, especially the new one. We already have pleerock's query builder which works with all the supported databases, so why don't use it like typeorm-linq-repository does? I would stick to them if syntax differences between databases are so common, or just do sql by hand as it can help creating optimal sql for our API.

I have been looking into knex the last couple of days and thinking about it, going with knex does not really make sense to me currently also. Converting to another query builder probably is going to be more work, then just doing the translation to SQL ourselves. Using the current query builder is probably the way to go.

typeorm-linq-repository looks interesting, could be interesting to have a look how some things are solved in there.

I will try to make a PR in the coming days, that does a very simple implementation and that we can use to look and discuss the technical side of things.

[MichalLytek]

It is not needed to hook into the compiler at compile time. We use the Typescript Compiler API at runtime and pass in the compiled functions. This compiled functions will be plain javascript, but since Typescript is a full superset of Javascript, the compiler can still generate a AST from it. So it does not matter it is compiled to Javascript. Minifying should also be fine in most cases, since minifying should not change the meaning of the code.

But it would be better to do it at compile time - faster app start & no problems with missing types or code transpilled to ES5. It could be done as CLI command typeorm compile and we would only have to add entry to package.json script "compile": "typeorm compile && tsc" or even typeorm compile could call tsc after the repositories query transformation. 😉 This would allow to print error about unsupported syntax/methods in where query that can't be mapped to sql query even before tsc compile and app bootstrap. So we just compile the functional query to AST or to sql prepared statement or maybe even stored procedures 😃

Webpack supports custom transformers but I wouldn't force people to use it on backend - typeorm compile is enough in my opinon.

@MaikelH
Can you show as a draft of your work?

[MaikelH]

@19majkel94 I will clean up some of the code I have currently and create a PR, unfortunately I was rather busy with some other projects the last couple of weeks.

It is interesting to see if we could do it during compile time. But I am a little afraid of the extra complexity that it adds. As far as I can see fow now we will need to do an complete parse of the source files and then transform them. Navigating the complete typescript syntax tree can be pretty complex.

In both cases we will need a way to convert the typescript parts to an SQL string. That is something we can develop as a standalone component. It maybe even possible to support both mechanisms later on.

[MichalLytek]

So your approach is that all of the runtime calls of the createLinqQueryBuilder would be catched and translated to sql string in runtime just by parsing the function body or the calling chain by itself, not by parsing the source file (JS in runtime)?

[MaikelH]

Yes that is my approach currently. I implemented the extra methods on the normal query builder, since that makes the code more simple. When called it will parse the function body and take the appropriate action. For a where this will be inserting it into the where expression map. The advantage of this is that it will only change the external interface and internally use the same mechanism as the normal api. This means that the normal API and the LINQ api can be used together.

[dgreene1]

Subscribing just to read all the cool stiffness you guys are talking about here.

Btw, do you guys think that it's even _possible_ to have the generated query determine the return type based off of the table models that it's retrieving and/or joining?

[Chris2011]

@MaikelH btw, how is the progress?

[MaikelH]

@Chris2011

I have been really busy with other projects the last couple of months unfortunately. So the progress has essentially stalled.

[Chris2011]

@MaikelH ok, thx for the info :)

[dgreene1]

@MaikelH if you don't mind showing your unfinished work: Can you push your working branch up, myself and a few other would probably like to help it along the way.

At a minimum, it would be fun to see how you were/are approaching the problem. :)

[ProTip]

Not a full proposal by any means, but here is something I've been kicking around. Proxy objects could be used to detect property access:

type Newable<T> = { new (...args: any[]): T; };


class Employee {
    name!: string
    company_id!: number
}

class Company {
    id!: number
}

class Builder {
    whereEqual<T1, T2>(t1: Newable<T1>, t2: Newable<T2>, f1: (t1: T1) => any, f2: (t2: T2) => any) {
        f1(new t1())
        f2(new t2())
        return this
    }
}

const builder = new Builder()

builder.whereEqual(
    Company,
    Employee,
    c => c.id,
    e => e.company_id)

Essentially, property access is hooked through a proxy and pushed into a stack or something. After f1() and f2() are called that stack can be checked to build the where clause.

I would like to offer my help with implementing whatever course of action is chosen...

[ProTip]

Was this issue closed on accident? I can't seem to figure out why it was closed.. Can we reopen?

I have an expanded example of a "type safe" query builder pattern that wouldn't require external projects:

type Newable<T> = { new (...args: any[]): T; };

enum Where {
    eq,
    ne,
    in,
}

type PropMap<T1> = { 
    [K in keyof T1]: K 
}

class Employee {
    name!: string
    company_id!: number
}

class Company {
    id!: number
}

function getProxyhandler(stack: any[]) {
    return {
        get: function(target: any, name: any) {
            stack.push(`${target.constructor.name}.${name}`)
        }
    }
}

class Builder {
    whereEqual<T1 extends object, T2 extends object>(t1: Newable<T1>, t2: Newable<T2>, f1: (t1: T1) => any, f2: (t2: T2) => any) {
        const stack: string[] = []
        f1(new Proxy(new t1(), getProxyhandler(stack)))
        f2(new Proxy(new t2(), getProxyhandler(stack)))
        console.log(`WHERE ${stack.pop()} = ${stack.pop()}`)
        return this
    }

    whereEqualKey<T1, T2>(t1: Newable<T1>, t2: Newable<T2>, k1: keyof T1, k2: keyof T2): Builder
    whereEqualKey<T1, K1 extends keyof T1>(t1: Newable<T1>, k1: K1, v1: T1[K1]): Builder
    whereEqualKey(...args: any[]) {
        return this
    }

    whereInKey<T1, K1 extends keyof T1>(t1: Newable<T1>, k1: K1, v1: Array<T1[K1]>): Builder {
        return this
    }

    where<T1, T2, K1 extends keyof T1, K2 extends keyof T2>(t1: Newable<T1>, t2: Newable<T2>, where: Where.eq, f1: (t1: PropMap<T1>) => K1, f2: (t2: PropMap<T2>) => K2): Builder
    where<T1, T2, K1 extends keyof T1, K2 extends keyof T2>(t1: Newable<T1>, where: Where.eq, f1: (t1: PropMap<T1>) => K1, v1: T1[K1]): Builder
    where<T1, T2, K1 extends keyof T1, K2 extends keyof T2>(t1: Newable<T1>, where: Where.in, f1: (t1: PropMap<T1>) => K1, v1: Array<T1[K1]>): Builder
    where(...args: any[]) {
        return this
    }
}


const builder = new Builder()

builder.whereEqual(
    Company,
    Employee,
    c => c.id,
    e => e.company_id,
)

builder.whereEqualKey(
    Company,
    Employee,
    "id",
    "company_id")

builder.whereEqualKey(
    Company,
    "id",
    1,
)

builder.whereInKey(
    Company,
    "id",
    [1, 2, 3]
)

builder.where(
    Company,
    Employee,
    Where.eq,
    c => c.id,
    e => e.company_id,
)

builder.where(
    Company,
    Where.eq,
    c => c.id,
    10
)

builder.where(
    Company,
    Where.in,
    c => c.id,
    [1],
)

On display are a few different ways of going about specifying the property being compared:

• By key string
• Via accessing a proxy property
• By returning a value from an object that has the keys of all the entity properties mapped to the name of that property

[Rockson]

Any progress on this? Is there some limitation from the language?

[dgreene1]

“Any progress on this?”

@rockson it seems like this extra package from @IRCraziestTaxi is covering most of the cases:
https://github.com/IRCraziestTaxi/typeorm-linq-repository

But I’m not sure what the actual TypeORM maintainers are planning on doing.

[spion]

Hi!

I just thought I'd mention node-sql here: https://node-sql-examples.github.io/

We wrote the types for node-sql, and the approach node-sql took allowed us to build queries without analyzing the AST like Linq does.

See https://github.com/TokyoFarmer/anydb-sql-2/blob/master/d.ts/anydb-sql.d.ts

[chriszrc]

I'm curious if a simpler approach has been suggested/tried/implemented. All we really need to make the typeorm querybuilder queries typesafe are the entity class property names as strings. I'm sure there could be a better/cleaner way to do this, but if you include a default constructor that includes all the properties in your entity, then you can do this (where "Mortality" is the entity):

//Will be an object like {"field_name":"field_name"}
mortalityKeys: {[key in keyof Mortality]:keyof Mortality};

constructor(){
     this.mortalityKeys = Object.getOwnPropertyNames(new Mortality()).reduce((a,c)=>{
       a[c]=c;
       return a;
     },{}) as {[key in keyof Mortality]:keyof Mortality};
}

findByCountryAndYear(){
    return this.mortalityRepository
      .createQueryBuilder("m")
      .select(this.mortalityKeys.sex_code)
      .addSelect(`SUM(m.${this.mortalityKeys.death_num})`, "death_total_num")
      .where(`m.${this.mortalityKeys.user_id} = :userId and m.${this.mortalityKeys.region_setting} = :regionSetting and m.${this.mortalityKeys.year4} = :year4 `, {
        userId: 'user0',
        regionSetting:"Poland",
        year4:2012,
      })
      .groupBy(`m.${this.mortalityKeys.sex_code}`)
      .getRawMany();
  }

That isn't particularly pretty, and there's nothing here to enforce using only values from your entities, but if you adhere to this layout, then you'd get errors at least if you tried to use a class property that no longer existed in your entity. Thoughts?

[Chris2011]

What about using this as a dependency? https://github.com/Siderite/LInQer I didn't tried it yet, but it looks promising and is what we wanted. Using it inside TypeORM will be one step forward. IMHO.

[lixaotec]

Was this discussion abandoned?

It would be very useful to have a entity typesafe queries..

[orobert91]

@pleerock It is pretty disappointing when you are coming from a C#/EntityFramework/LINQ world where query statements, even the most complexe ones, are fully statically typed with a well-engineered, highly-maintainable and database-independant LINQ syntax where the compiler will catch almost every single mistake you make before you even run them. And then plunging into the Javascript/Typescript world where every single ORM out there, even those written specifically for TypeScript, have string-based query builders. What is even more disappointing is that everytime someone tries to pitch the statically typed query builder idea to ORM maintainers, they get into a wall since the latter don't understand the benefits because of their unfamiliarity with fully statically-typed ORM. I really think this issue should be reopened.

[abinici]

@pleerock Building support for statically typed queries in TypeORM would have huge impact in the NodeJS community. Having statically typed queries would allow us to build and maintain even larger NodeJS-projects. A combination of TypeORM and NestJS would be a killer tech-stack in the NodeJS world.

Meanwhile I will use this excellent project by @IRCraziestTaxi:
https://github.com/IRCraziestTaxi/typeorm-linq-repository

Btw, the project typeorm-linq-repository is a good example of how this can be done. If there are no better ways, why not consider using the approach from this project?

Please consider reopening this issue.

[bugMaker-237]

@pleerock Building support for statically typed queries in TypeORM would have huge impact in the NodeJS community. Having statically typed queries would allow us to build and maintain even larger NodeJS-projects. A combination of TypeORM and NestJS would be a killer tech-stack in the NodeJS world.

Meanwhile I will use this excellent project by @IRCraziestTaxi:
https://github.com/IRCraziestTaxi/typeorm-linq-repository

Btw, the project typeorm-linq-repository is a good example of how this can be done. If there are no better ways, why not consider using the approach from this project?

Please consider reopening this issue.

If you're still looking for such a statistically typed query generator, I have been working on this: slim-ef for some months now. Writing queries just like with EF is now possible! Take a look, every feedback is appreciated.
An example of what can be done at this date.

  const context = new DeltaTravelDBContext();
  const data = {
    departureDate: new Date(2000, 1, 1)
  };
  const res = await context.trips
    .include(t => t.passengers)
    .where(
      (t, $) =>
        t.departureDate > $.departureDate ||
        (t.passengers.some(p => p.willTravel === false) &&
          t.departureDate < $.departureDate) ||
        (t.passengers.some(p => p.willTravel === true) &&
          t.passengers.some(p => p.IDNumber > 500000)),
      data
    )
    .orderBy(t => t.id)
    .select(t => ({
      id: t.id,
      passengers: t.passengers.map(p => ({
        name: p.lastname,
        isTravelling: p.willTravel,
        num: p.IDNumber,
        tripId: p.tripId
      })),
      departure: t.departureDate,
      arrival: t.estimatedArrivalDate
    }))
    .toList();

  console.table(res);

  context.dispose();