Database

SPLENT uses SQLAlchemy 2.0 for ORM and Alembic (via Flask-Migrate) for schema migrations. Each feature owns its own migration history. A central splent_migrations table tracks the current revision per feature.

---

Table of contents

1. The db singleton
   1. Defining models
   2. Session management
2. MigrationManager
   1. What it does
   2. API
3. Per-feature migration system
   1. Structure
   2. Isolated version tables
   3. The splent_migrations table
4. Migration commands
5. Startup script integration
   1. 02_2_db_create_splent_migrations.sh
   2. 04_handle_migrations.sh
6. Configuration
7. See also

---

The db singleton

File: splent_framework.db

from splent_framework.db import db

db is a single flask_sqlalchemy.SQLAlchemy instance shared across the entire application and all its features. It is initialized once in MigrationManager during create_app().

Defining models

Every feature defines its models against the shared db:

# src/splent_io/splent_feature_auth/models.py
from splent_framework.db import db

class User(db.Model):
    __tablename__ = "users"

    id       = db.Column(db.Integer, primary_key=True)
    email    = db.Column(db.String(256), unique=True, nullable=False)
    password = db.Column(db.String(256), nullable=False)

Because all features share the same db instance, cross-feature relationships work normally:

# splent_feature_profile / models.py
from splent_framework.db import db
from splent_io.splent_feature_auth.models import User

class UserProfile(db.Model):
    __tablename__ = "user_profiles"

    id      = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    user    = db.relationship(User, backref="profile")

Import order matters. If splent_feature_profile references User, then splent_feature_auth must be loaded (and its models imported) before splent_feature_profile. This is guaranteed by the topological load order resolved via UVL constraints.

Session management

db.session is a scoped session, automatically committed or rolled back per request. Use db.session.commit() and db.session.rollback() explicitly only in service or repository layer code.

---

MigrationManager

File: managers/migration_manager.py

Initialized once during create_app():

MigrationManager(app)

What it does

1. Initializes the SQLAlchemy db singleton with the Flask app (db.init_app(app)).
2. Wires Flask-Migrate to db so alembic commands work within the Flask context.
3. Creates the splent_migrations central tracking table if it does not exist.

API

Most interaction with MigrationManager happens through the CLI (db:migrate, db:upgrade, db:rollback), not directly. The useful programmatic API:

Method	Description
get_feature_migration_dir(feature_name)	Static. Return the path to a feature’s migrations/ directory.
get_all_feature_migration_dirs()	Static. Return paths for all features that have a migrations/ directory.
get_current_feature_revision(feature_name, engine)	Query the current Alembic revision for a feature from its version table.
update_feature_status(app, feature_name, revision)	Upsert a row in splent_migrations for a given feature.
get_all_status(app)	Return all rows from splent_migrations as a dict.

---

Per-feature migration system

Structure

Every feature ships its own migrations directory:

src/<org>/<feature_name>/
└── migrations/
    ├── alembic.ini
    ├── env.py
    ├── script.py.mako
    └── versions/
        ├── 001_initial.py
        └── 002_add_column.py

Migration scripts are generated by splent db:migrate and committed to the feature repository.

Isolated version tables

Each feature tracks its Alembic revision in its own version table, named:

alembic_<feature_name>

For example, splent_feature_auth uses alembic_splent_feature_auth. This ensures migrations never interfere with each other, even if two features are migrated simultaneously.

The splent_migrations table

The central tracking table holds the last applied revision per feature:

CREATE TABLE splent_migrations (
    feature        VARCHAR(255) NOT NULL,
    last_migration VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (feature)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This table is the source of truth for feature:status and the migration state guard in feature:remove / feature:detach.

Example rows after a full migration:

feature	last_migration
splent_feature_auth	abc123def456
splent_feature_redis	—
splent_feature_mail	789xyz123abc

Features without a migrations/ directory (e.g., splent_feature_redis) never appear here.

---

Migration commands

CLI command	What it runs	State transition
splent db:migrate	alembic revision --autogenerate + alembic upgrade head	installed → migrated
splent db:upgrade	alembic upgrade head (no script generation)	installed → migrated
splent db:rollback <feature_name>	alembic downgrade -N	migrated → installed
splent db:status	Read splent_migrations	—

See db:migrate, db:upgrade, db:rollback for full usage.

---

Startup script integration

During Docker startup, migrations are handled by two scripts:

02_2_db_create_splent_migrations.sh

Creates the splent_migrations table before any migrations run. It is idempotent (CREATE TABLE IF NOT EXISTS), so it is safe to run on every startup.

04_handle_migrations.sh

Checks each feature’s migrations/versions/ directory. If migration scripts exist but have not been applied yet (detected by comparing Alembic state with splent_migrations), it calls splent db:upgrade. Otherwise, it calls splent db:migrate to generate and apply new scripts.

After this script completes, all features with a migrations/ directory have state migrated in splent.manifest.json.

---

Configuration

The database connection is configured in the product’s config.py:

class DevelopmentConfig(BaseConfig):
    def __init__(self):
        super().__init__()
        self.SQLALCHEMY_DATABASE_URI = (
            f"mysql+pymysql://{os.getenv('MARIADB_USER')}:"
            f"{os.getenv('MARIADB_PASSWORD')}@"
            f"{os.getenv('MARIADB_HOSTNAME')}:3306/"
            f"{os.getenv('MARIADB_DATABASE')}"
        )

Relevant environment variables:

Variable	Description
MARIADB_USER	Database username
MARIADB_PASSWORD	Database password
MARIADB_HOSTNAME	Database host (e.g., db inside Docker)
MARIADB_DATABASE	Main database name
MARIADB_DATABASE_TEST	Test database name (used in TestingConfig)

---

See also

• db:migrate — generate and apply migrations
• db:upgrade — apply existing migrations
• db:rollback — roll back migrations before removing a feature
• Architecture layers — BaseRepository uses db.session
• Feature lifecycle — how migrated state is tracked