Support #4628


Consider/implement modeling of event database with SQLAlchemy declarative base.

Added by Jan Mach over 5 years ago. Updated over 3 years ago.

Research and analysis
Target version:
Start date:
Due date:
% Done:


Estimated time:
To be discussed:


For performance reasons we now use psycopg2 driver to manage and access. It would feel right to consolidate some of the database creation and management code by modelling the database schema with SQLAlchemy declarative base. It would be then possible to create and manage the database and database migrations in uniform way.

Related issues

Related to Mentat - Feature #4230: Make use of existing or implement own mechanism for handling SQL schema migrations.ClosedPavel Kácha07/27/2018

Actions #1

Updated by Jan Mach over 5 years ago

  • Related to Feature #4230: Make use of existing or implement own mechanism for handling SQL schema migrations. added
Actions #2

Updated by Radko Krkoš over 3 years ago

  • Tracker changed from Feature to Support
  • Category changed from Development - Core to Research and analysis
  • Assignee changed from Jan Mach to Radko Krkoš
Actions #3

Updated by Radko Krkoš over 3 years ago

Based on the videocall discussion, the motivation behind this issue is to get rid of Alembic. Alembic is a database schema migration tool created by the same author as SQLAlchemy [1], mainly to support the database migrations for SQLAlchemy-based projects. Of course, Alembic can be used without SQLAlchemy. As it happens, both mentat_main, which is SQLAlchemy managed, and mentat_events, which is managed using SQL DDL descriptions, use Alembic for migrations, the scripts are source:/lib/hawat/migrations/versions@devel and source:/migrations-events/versions@devel respectively. Alembic does have limited support for generating migrations scripts from SQLAlchemy schema changes, those must always be reviewed, fixed and tested manually. As for the mentat_events database, the migration scripts there surpass the abilities of automatic script generation greatly, so a schema definition using SQLAlchemy would not have helped much.
To sum it up, discarding Alembic does not seem realistic (without replacing it with something roughly identical) and Alembic does not depend on SQLAlchemy and can be used separately.

Based on discussion in issue #4230, which this issue stemmed from, the motivation for this issue was to unify "database creation, management and cleanup tasks". It would be fairly trivial to replace the direct SQL DDL description in source:/lib/mentat/services/, namely the database_create() and index_create() methods, using SQLAlchemy definitions and DDL calls ([2] is a simple tutorial containing almost all the required information). Then, for the cleanup and management tasks, a significant portion of EventStorage would have to be reimplemented. Of course the actual data access (and even some management tasks, such as the enum tables manipulation) is too complex to implement using pure SQLAlchemy, but that is outside of the scope of this issue anyways.


Actions #4

Updated by Radko Krkoš over 3 years ago

CTEs (common table expressions) are in fact possible with SQLAlchemy [1], so enum table manipulation queries can be translated. However the syntax is quite lengthy and subjectively non-obvious, as illustrated in the example code in the manual.
Also, it is possible to define GIN indices in the model. [2]


Actions #5

Updated by Radko Krkoš over 3 years ago

  • To be discussed set to Yes
Actions #6

Updated by Pavel Kácha over 3 years ago

  • Status changed from New to Rejected
  • To be discussed deleted (Yes)
Wrap-up from Radko's findings and video:
  • Migrations have been yet pretty much always so complex that they cannot be generated by Alembic from SQL Alchemy, but handcrafted.
  • Main and Events logic is very separate, we would not reach reasonable unification even with SQL Alchemy declaration.
  • User/Group/Network/Filter relations make sense to be modelled and enforced by means of SQL Alchemy, but we don't have any of those in Events logic (except just one relation to raw data table).
  • Table description is quite unreadable in SQL Alchemy, same for coping with cleanup and management tasks.

Ergo - quite a hunk of work not balanced by benefits.

Mek, you've raised it up originally, so if you disagree or we do neglect something, please speak up/reopen.

Actions #7

Updated by Pavel Kácha over 3 years ago

  • Target version changed from Backlog to 2.7

Also available in: Atom PDF