I propose to add a Python SQL ORM library, SQLAlchemy, to our LORIS-MRI Python technology stack. The old code can continue using handwritten SQL, but new PRs such as #1117 could benefit from it.
Why a library ?
See some discussion on established libraries in #1141.
Why an ORM ?
I think using a typed ORM (which SQLAlchemy 2.0+ is) is better than using handwritten SQL for three reasons:
Readability: For simple queries, handwritten SQL is not problematic. However, for large queries, that may change according to some parameters, using handwritten SQL requires to build the query string and its argument list, which can be quite complex. An ORM mitigates that complexity by providing operations on the query abstraction instead of on the query string and argument list separately, which I believe is a more direct and readable way.
Robustness: As an advocate of static typing, I believe using a typed ORM helps to write more robust program, as any ill-typed interaction with the database produces a static error.
Maintainability: LORIS database is messy. I believe one of the reasons why it is hard to change it is that no static error is produced if a change in the database breaks our handwritten SQL code. A typed ORM solves that problem by producing static errors if a change in the database breaks any code (as long as the ORM database information is updated, which is very easy to do).
Is SQLAlchemy established ?
Both me and @driusan agree that SQLAlchemy is established and the main Python SQL ORM. I can bring more arguments on this point if needed.
Potential conflict with PyBIDS
It seems that PyBIDS depends on a 1.x version of SQLAlchemy, which may not be compatible with SQLAlchemy 2.0+. The problem is obviously with PyBIDS, as it is the library that is not up-to-date, but we still need to work around that if we are to use SQLAlchemy. Here are the potential solutions I see:
Ask PyBIDS Github if they can update their SQLAlchemy requirement, and maybe do the PR ourself. Lots of unknowns here, may be either easy (like a day) or hard.
Update the install script to create a local copy of SQLAlchemy, likely the simplest solution.
Accept the conflict and pray that everything still works, not advisable in my opnion.
Use SQLAchemy 1.x, not advisable in my opnion since we lose the benefits of static typing.
Example
I did some tests a month ago by using this library in #1117, I was very satisfied with the result, which is available this branch. You can especially look at the directory python/lib/db and the file python/lib/dicom/dicom_database.py.
Proposal
I propose to add a Python SQL ORM library, SQLAlchemy, to our LORIS-MRI Python technology stack. The old code can continue using handwritten SQL, but new PRs such as #1117 could benefit from it.
Why a library ?
See some discussion on established libraries in #1141.
Why an ORM ?
I think using a typed ORM (which SQLAlchemy 2.0+ is) is better than using handwritten SQL for three reasons:
Readability: For simple queries, handwritten SQL is not problematic. However, for large queries, that may change according to some parameters, using handwritten SQL requires to build the query string and its argument list, which can be quite complex. An ORM mitigates that complexity by providing operations on the query abstraction instead of on the query string and argument list separately, which I believe is a more direct and readable way.
Robustness: As an advocate of static typing, I believe using a typed ORM helps to write more robust program, as any ill-typed interaction with the database produces a static error.
Maintainability: LORIS database is messy. I believe one of the reasons why it is hard to change it is that no static error is produced if a change in the database breaks our handwritten SQL code. A typed ORM solves that problem by producing static errors if a change in the database breaks any code (as long as the ORM database information is updated, which is very easy to do).
Is SQLAlchemy established ?
Both me and @driusan agree that SQLAlchemy is established and the main Python SQL ORM. I can bring more arguments on this point if needed.
Potential conflict with PyBIDS
It seems that PyBIDS depends on a 1.x version of SQLAlchemy, which may not be compatible with SQLAlchemy 2.0+. The problem is obviously with PyBIDS, as it is the library that is not up-to-date, but we still need to work around that if we are to use SQLAlchemy. Here are the potential solutions I see:
Example
I did some tests a month ago by using this library in #1117, I was very satisfied with the result, which is available this branch. You can especially look at the directory python/lib/db and the file python/lib/dicom/dicom_database.py.