Hello.
The current release of questdb-connect to work with SQLAlchemy is lacking in a few areas. Specifically, the support for QuestDB's dialect wrt to SQLAlchemy's query framework needs further improvement.
For example
- Keywords like
LATEST ON, PARTITION BY is not recognized by the dialect. There is no way to use SQLAlchemy's querying framework to inject these keywords into the final SQL statement text that is sent to QuestDB.
- The way
LIMIT behaves in QuestDB is different from other SQL databases. LIMIT in QuestDB combines both LIMIT and OFFSET into a single keyword, which means OFFSET is not required. This breaks the integration with SQLALchemy since I can no longer do something like query.limit(100) if I want to specify an OFFSET. I would have to do query.limit(100,50) for example, which isn't valid in SQLAlchemy since it expects the parameter to be an integer.
- JSON functions are not accessible via the SQLAlchemy APIs.
Using something like with_hint() in SQLAlchemy to manually inject the commands into the SQL raw command generated by SQLAlchemy also runs into problems. For example, when using LATEST ON and PARTITION BY, the LIMIT keyword is inserted by SQLAlchemy BEFORE LATEST ON and PARTITION BY which causes an unexpected token error to be thrown.
Therefore, the current support for SQLAlchemy is really only useable when you generate your own SQL commands as strings manually such as in the examples provided here https://questdb.io/docs/third-party-tools/sqlalchemy/
This defeats the purpose of having SQLAlchemy support in the first place as a developer can use directly use the REST API of QuestDB to query data.
Hello.
The current release of questdb-connect to work with SQLAlchemy is lacking in a few areas. Specifically, the support for QuestDB's dialect wrt to SQLAlchemy's query framework needs further improvement.
For example
LATEST ON,PARTITION BYis not recognized by the dialect. There is no way to use SQLAlchemy's querying framework to inject these keywords into the final SQL statement text that is sent to QuestDB.LIMITbehaves in QuestDB is different from other SQL databases.LIMITin QuestDB combines bothLIMITandOFFSETinto a single keyword, which meansOFFSETis not required. This breaks the integration with SQLALchemy since I can no longer do something likequery.limit(100)if I want to specify anOFFSET. I would have to doquery.limit(100,50)for example, which isn't valid in SQLAlchemy since it expects the parameter to be an integer.Using something like
with_hint()in SQLAlchemy to manually inject the commands into the SQL raw command generated by SQLAlchemy also runs into problems. For example, when usingLATEST ONandPARTITION BY, theLIMITkeyword is inserted by SQLAlchemy BEFORELATEST ONandPARTITION BYwhich causes anunexpected tokenerror to be thrown.Therefore, the current support for SQLAlchemy is really only useable when you generate your own SQL commands as strings manually such as in the examples provided here https://questdb.io/docs/third-party-tools/sqlalchemy/
This defeats the purpose of having SQLAlchemy support in the first place as a developer can use directly use the REST API of QuestDB to query data.