qgisorm.py

query = From(iface.activeLayer()) \
             .where('"Pit Type" = \'MH\'') \
             .limit(10) \
             .select("Pit Type", "SURVEY", "CATCHMENT", geometry=True)

query, layer = SQL("SELECT SURVEY, geometry FROM Pits GROUP BY Pit LIMIT 20", name="MyQuery")

query, layer = SQL("SELECT Pit, SURVEY, ST_UNION(ST_Buffer(geometry, 20)) as geometry FROM Pits GROUP BY Pit",
                   name="Buffers")

query.py

from qgis.core import QgsFeatureRequest
from PyQt4.QtCore import QUrl

class Query(object):
    def __init__(self, layer):
        self.rq = QgsFeatureRequest()
        self.layer = layer

    def where(self, expression):
        self.rq.setFilterExpression(expression)
        return self

    def in_(self, *ids):
        self.rq.setFilterFids(ids)
        return self

    def extent(self, rect):
        self.rq.setFilterRect(rect)
        return self

    def no_geometry(self):
        self.rq.setFlags(QgsFeatureRequest.NoGeometry)
        return self

    def limit(self, top):
        self.rq.setLimit(top)
        return self

    def select(self, *fields, **kwargs):
        if fields:
            self.rq.setSubsetOfAttributes(fields, self.layer.pendingFields())
        if "geometry" in kwargs and not kwargs['geometry']:
            self.no_geometry()

        return self.layer.getFeatures(self.rq)

def SQL(sql, name="querylayer"):
    sql = "?query={0}&geometry=geometry".format(sql)
    query = QUrl(sql).toEncoded()
    layer = QgsVectorLayer(str(query), name, "virtual")
    if not layer.dataProvider().error().isEmpty():
        raise Exception(layer.dataProvider().error().message())
    return From(layer).select(), layer

def From(layer):
    return Query(layer)

Are you suggesting all three syntaxes be valid, or asking for opinions on which is best?

Gain readability and expressibility with an SQL like syntax.
Performances costs? (because ORM are now less use to fully take advantage of the underlying DB)