Pretty cool to read. We built a (currently proprietary) CMS with our own scripting language, and instead of going the ORM way, we merged basic SQL into the language itself. We did that mostly to eliminate sending raw strings to databases (and all the injection risks and complexity that comes with it) but it does allow a few extra optimisations because the compiler can look at both the query and the language using it.
So if I do our equivalent of the ORM API Misuse case:
IF(RecordExists(SELECT * FROM schema.variants WHERE track_inventory = 0))
{
...
}
(RecordExists is a function that only checks whether the query returned something, and has been marked that way) the compiler will already reduce this to:
IF(RecordExists(SELECT FROM schema.variants WHERE track_inventory = 0 LIMIT 1))
{
...
}
And likewise a function that selects all columns from a database and returns only one field, has the select reduced to only selecting that one column.
The drawback, of course, is that any SQL features of the underlying database not exposed by the scripting language, are unreachable unless you fallback to sending raw query strings again.
Of course it depends on the exact circumstances, but your query would probably be faster as `SELECT COUNT(*)` than the `LIMIT 1` you optimize to now. In fact, that’s one of the specific optimizations mentioned in the article.
So if I do our equivalent of the ORM API Misuse case:
(RecordExists is a function that only checks whether the query returned something, and has been marked that way) the compiler will already reduce this to: And likewise a function that selects all columns from a database and returns only one field, has the select reduced to only selecting that one column.The drawback, of course, is that any SQL features of the underlying database not exposed by the scripting language, are unreachable unless you fallback to sending raw query strings again.