So far we have been using SQLAlchemy extensively for the DB interactions, which has lead to a list of problems holding us back in some places:
- sqlalchemy seems to be a major performance bottleneck at this point, as the SQL queries for the search are significantly faster when executed in the sqlite cli
- foreign key clauses don't appear to be supported, meaning that we have to manually issue delete queries
- PRAGMA isn't supported either and would give us a proper place to store the DB schema version (see pragma user_version) (though this is just a nice-to-have and the disadvantage of changing the version stuff again might outweigh the benefit here)
- sqlalchemy is also just massively overkill for what we are doing, which complicates certain parts of the code
I suggest we move from using SQLAlchemy to using a plain sql for most of the DB interface and a minimal query building library like sql_fusion for the query builder
For more detail see past discussion on the discord, but I suggest we keep the important stuff regarding (specifically if we decide on anything) on Github.
So far we have been using SQLAlchemy extensively for the DB interactions, which has lead to a list of problems holding us back in some places:
I suggest we move from using SQLAlchemy to using a plain sql for most of the DB interface and a minimal query building library like sql_fusion for the query builder
For more detail see past discussion on the discord, but I suggest we keep the important stuff regarding (specifically if we decide on anything) on Github.