SQL Queries

I've recently learned that some organizations value working with raw SQL. Keeping your queries in raw SQL makes sense and I am starting to design an additional, SQL-first ORM for AIRport.

The SQL skillset

SQL language is a juggernaut in its own right. Thus, defining and keeping your persistence logic in raw SQL makes sense since SQL is a more transferable skillset then the ability to understand and write queries in a given (procedural language friendly) ORM. AIRport is no different - far more people will be able to understand AIRport persistence logic if it were to be defined in raw SQL.

Early thoughts

Wouldn't it be great if you could take SQL, sprinkle it with some TypeScript wrappers and get object graphs out of it? I think so, and hence I'm designing an additional ORM for AIRport.

The idea is simple, join TypeScript and SQL together. Here is what I'm thinking of at this point - *.tiql files:

export class ExampleDao extends BaseTiqlExampleDao {

    async getExampleEntity(
        id: string
    ): Promise {
        SELECT 
            /*** {
                '*': Y,
                entityA: {}
            } */
            -- START GENERATED  
            ee.ARID,
            ee.RID,
            ee.AID,
            ee...
            ea.ARID as entity_a_arid,
            ea.RID as entity_a_rid,
            ea.AID as entity_a_aid,
            ea...
            -- END GENERATED
        FROM
            example_entity ee
            LEFT JOIN entity_a ea
            ---- TO ee
            -- START GENERATED  
                ON ee.ENTITY_A_ARID = ea.ARID
                AND ee.ENTITY_A_RID = ea.RID
                AND ee.ENTITY_A_AID = ea.AID
            -- END GENERATED
        WHERE
            ee.id = id
    }                        
}

The above ExampleDao.tiql file supports both TypeScript and SQL syntax with some demarcation where TypeScript must be and where SQL may be. The /*** and ---- comment blocks are strongly typed and allow the developer to specify the resulting Object graph and JOIN logic. TIQL processor will then create the code in the GENERATED blocks and add it to the SQL statement. It will also work in the reverse direction, and generate Object Graph definition and simplified JOIN syntax.

This is a first draft and does not consider anything related to how technically feasible such a notation is. The key requirement is that IDE integration works and gives both TypeScript and SQL auto-completion and syntax validation.

I need help to get this done

I don't have the skillset required to make this kind of syntax a reality. I can learn the necessary language processing skills but am far too busy with building out the rest of AIRport and the first Applications built on top of it. So, I am asking for help to get this done - is anybody interested? :)