@schuemie, your point about WebAPI’s new security layer is pretty hard to argue with. I have some reasons and justifications for doing my API development independently of WebAPI (I can implement new API features much more quickly and my client’s ultimate target platform, GBQ, is unlikely to be supported by WebAPI/SqlRender anytime soon), and I think, as a community, we need to discuss the possibility of providing alternatives to WebAPI (I’d love to see further discussion of @akshayubhat’s serialization ideas), but that might belong in a new thread. I will throw in, though, regarding the connection problem, that the API framework I’m using, loopback, solves it better than WebAPI does – and I think the primary justification for SqlRender is in the SQL dialect problem.
@JasonPoovey brought up the idea of a command line tool earlier in this thread, and that could be great for allowing SqlRender to be used beyond its current worlds of R packages and WebAPI.
You didn’t address my earlier question about SqlRender’s override features and whether they could allow a platform-specific query to exist without an OhdsiSql version. As @Christian_Reich (and you, to some degree) suggested, SqlRender’s life as a translation engine may be limited, but a focus on its override features (which should maybe be implemented in a separate utility?) could serve us in perpetuity. What we need, I think, are good conventions for storing SQL of various flavors, loading the appropriate flavor when it exists, assembling it to the appropriate flavor when its construction depends on variable parameters, and auto-translating it where possible. SqlRender’s override features haven’t been used so far, but I think if they were conceived more broadly, they could extend the use of its translation capacities indefinitely while also giving our community a way of solving some of its database diversity issues beyond R, WebAPI, and a small set of RDBMSs.