Python library proposal: SQLAlchemy

[maximemulder]

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

There is currently a version conflict with PyBIDS 0.14.0 (the version we have installed), which requires SQLAlchemy < 1.4. This conflict can be solved by updating PyBIDS (works for the most recent version, 0.17.0, haven't tried with other versions).

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.

[maximemulder]

Closed by #1152