Development guide

This document is meant for developers of the Eyened database and ORM. It explains how to develop changes and how to create and test database migrations.

ORM Conventions

We use Annotated Declarative Syntax to specify columns. This has the advantages that the resulting ORM objects have type annotations and everything is very readable.

Column Definitions

• Every column should have a Mapped type declaration, e.g. MyColumn: Mapped[int]
• Use Optional to create a nullable column, e.g. MyColumn: Mapped[Optional[int]]
• Use mapped_column() on the right hand side if necessary. mapped_column() derives the datatype and nullability from the Mapped annotation. mapped_column() is not necessary if there are no additional arguments to pass such as index, unique, or default
• Specify VARCHAR lengths on the right, e.g. MyColumn: Mapped[str] = mapped_column(String(64)) will create a VARCHAR(64)
• JSON should be coded as Mapped[Dict[str,Any]]
• ENUM columns should be defined as Python types. The left hand values should match the desired SQL ENUM. The right hand side is not relevant. To create ENUM('OCT', 'CF'):

  class FeatureModalityEnum(enum.Enum):
      OCT = 1
      CF = 2

  Then set up the column as: MyColumn: Mapped[FeatureModalityEnum]

Relationships

To add new relationships, take a look at how they are currently set up in the ORM. For more information refer to the Relationship docs.

Naming Conventions

• Classmethods starting with by_ are selectors. For example, by_id in the ImageInstance class will query the database and return an image object with the given ID.
• Classmethods starting with from_ are alternative constructors. For example, from_imagesets in the Task class will construct a Task object given lists of image IDs and other arguments needed to define a Task.

Migrations

We use Alembic for managing database migrations. This section explains our processes:

• Setting up Alembic
• Developing migrations
• Testing migrations

Setting up Alembic

Development Repository

Make sure eyened_orm is installed (see Getting Started). Alembic is installed with eyened_orm. Make sure the alembic command is available in the terminal.

To set up a testing environment, check the README here: https://github.com/Eyened/eyened-platform/tree/main/orm/migrations

Note

Alembic reads the ORM objects with Base.metadata in orm/migrations/alembic/env.py. This is used only to generate automatic migrations (see below). The code in env.py will look for an eyened_orm Python package in the current environment. If you installed the ORM as above, you should be set up. Any changes to the ORM code will be directly accessible to Alembic.

Committing to Production

When committing migrations to production:

1. Ensure all tests pass
2. Create a pull request with the migration changes
3. Get code review approval
4. Merge to main branch
5. Deploy the changes following the deployment procedure

Developing Migrations

1. Prepare a copy of the production database (see https://github.com/Eyened/eyened-platform/tree/main/orm/migrations)

2. Change the ORM code to reflect the desired change in database structure. See ORM Conventions above. Then navigate to the migrations directory:

   cd orm/migrations

3. Auto-generate Alembic migration:

   alembic revision --autogenerate -m "my migration description"

   This will create a new migration file in alembic/versions. Every migration *.py script contains two functions: upgrade() and downgrade(). upgrade() is the migration and will apply any changes necessary to make the database match the current state of the ORM. It should contain commands that apply your newly made changes to the database. If you auto-generate the migration without making any changes to the ORM (i.e., when DB and ORM are in sync), the function should be blank (pass). The downgrade() function contains instructions to undo the migration.

4. Check if alembic revision --autogenerate -m "test" leaves a blank migration file. If not, fix discrepancies and generate a new migration.

5. Manually edit the migration functions upgrade() and downgrade()

   Caution

   Check carefully. Especially note that Alembic cannot detect changes in table and column names and will instead generate, e.g., a drop_column command followed by an add_column command. These should be changed into alter_column or rename_table. See here for limitations of Alembic auto-generation.

6. Once you are satisfied with the migration file, run the migration on the test database:

   alembic upgrade head

7. If the migration fails, recreate the test database and then repeat steps 4-6 until the migration is successful.

8. The test database and test ORM are now in sync. Store the full SQL migration by running:

   ./generate_alembic_sql.sh

   This script is located in the orm/migrations directory.

9. Push the changes on a branch with a convenient name that reflects the changes made. Submit a pull request.

Testing Migrations

Note

This documentation overlaps with the README: https://github.com/Eyened/eyened-platform/tree/main/orm/migrations

Environment Setup

The ORM includes a utility to easily create a copy of the production database into a running MySQL server for testing. For this to work, the ORM needs access to the database credentials for both production and test databases. Add source.env (production) and test.env (test) files as explained in ORM Configuration.

Set up Alembic to work with your testing environment:

cd orm/migrations
python set_connection_string.py test.env

This sets up the connection string in alembic.ini based on the variables in the specified .env file. Replace test.env with the path to your test environment file.

Create Test Database

Once this is done, you have two options to populate the database:

1. Full Database Copy (Recommended, but might take a while):

   eorm database-mirror-full -c transfer-config.yml

   This creates a complete copy of the production database. The configuration file should specify source and target database settings.

2. Limited Data Copy (For a faster copy, e.g., during development work when you need to test a migration several times):

   eorm database-mirror-test -c transfer-config.yml

   This creates a small test database with only the data specified in transfer-config.yml. The configuration file should specify source and target database settings and which objects to copy.

Both options will:

1. Dump the production database to a temporary file
2. Drop and re-create the test database
3. Load the dump file into the new test database

Test the Migration

1. Check out the relevant eyened_orm commit/branch that you wish to test in your development repository (or test virtualenv set up for this purpose).

2. Apply the migration on the test database (from the migrations directory):

   alembic upgrade head

   Caution

   Make sure Alembic is pointing to the correct database (check alembic.ini).

   This should always succeed. If there are issues with running the migration, it could be a bug or that other migrations have been applied in between. In this case, changing the down_revision might be necessary. Be careful!

What to Test

Test any code the migration might affect, including at least:

• Data integrity after migration
• EyeNED viewer
• Importing images