Database

Introduction
Defining a model
Relating models across features
Saving your changes
Creating a row together with another
How migrations are tracked
Running migrations
Migrating when the product starts
1. Migrating in a separate step
Pointing at the database
Advanced
Reference
1. MigrationManager
2. The splent_migrations table
See also

Introduction

Every feature defines its models on db, the one Flask-SQLAlchemy instance the whole app shares. Each feature also ships its own Alembic migrations, so it creates and changes only its own tables, at its own pace.

from splent_framework.db import db


class Note(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)

splent db:migrate notes
splent db:upgrade

db:migrate writes the migration script into the feature, and db:upgrade applies it.

Defining a model

from splent_framework.db import db


class OrderLine(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    item_id = db.Column(db.Integer, db.ForeignKey("item.id"), nullable=False)
    quantity = db.Column(db.Integer, nullable=False, default=1)

Without a __tablename__, the table takes the class name in snake case. OrderLine lives in order_line, and a foreign key to it is "order_line.id".

Relating models across features

from splent_framework.db import db
from splent_io.splent_feature_notes.models import Notes


class ReportEntry(db.Model):
    __tablename__ = "report_entries"

    id = db.Column(db.Integer, primary_key=True)
    note_id = db.Column(db.Integer, db.ForeignKey("notes.id"), nullable=False)
    note = db.relationship(Notes, backref="report_entries")

All features share one db, so a relationship to another feature’s model works as usual. The features load in the order the UVL model gives, so the feature you depend on has its models ready first.

Saving your changes

db.session.add(note)
db.session.commit()

Nothing commits for you. When the request ends, whatever you did not commit is thrown away. Call commit() and rollback() yourself, in a service or a repository. BaseRepository already commits in create, update and delete.

Creating a row together with another

from sqlalchemy import event


@event.listens_for(Order, "init")
def _totals_of_new_order(target, args, kwargs):
    if "totals" not in kwargs and "totals_id" not in kwargs:
        kwargs["totals"] = OrderTotals(items=0, amount=0)

When one row always comes with another, build both when the object is built. The init event runs before any flush, so the relationship saves both in the same flush. Adding objects from an after_insert listener runs inside the flush, and SQLAlchemy does not promise to save them.

init fires only for objects built in Python. Rows loaded from the database, or inserted by a migration, never get the default.

How migrations are tracked

A feature's migrations only touch its own version table, so features migrate independently. The central table backs the db:* commands, and the manifest holds the lifecycle state.

A feature keeps its migrations in its own migrations/ folder, committed to its repository.

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

Each feature records its revision in its own version table, alembic_<feature_name>, so two features never get in each other’s way. The splent_migrations table keeps one row per feature with its last revision, and the db:* commands read it. A feature without migrations/ never appears there.

The lifecycle state lives in splent.manifest.json, and db:upgrade and db:rollback keep it in step. feature:status, feature:remove and feature:detach read it from there.

Running migrations

Command	What it does	State
`splent db:migrate`	Writes a script from your model changes and applies nothing. An empty script is dropped. `--empty` keeps a blank one for a data migration	Unchanged
`splent db:upgrade`	Applies every pending migration	`installed` to `migrated`
`splent db:rollback <feature>`	Rolls back the last migrations of a feature, one by default	`migrated` to `installed` at base. A partial rollback stays `migrated`
`splent db:status`	Shows each feature’s revision against its latest script	Unchanged

Migrating when the product starts

The product’s container migrates on its own when it starts. Three scripts do it, in order.

Script	What it does
`02_2_db_create_splent_migrations.sh`	Creates `splent_migrations` if it is missing
`03_initialize_migrations.sh`	Runs `splent db:upgrade` once, under a lock of the database server, so a second replica waits and then finds nothing left to apply. It also notes whether the database was empty
`04_handle_migrations.sh`	Seeds an empty database with `splent db:seed -y` when `RUN_DB_SEED=true`, never in production. Then it runs `splent storage:migrate`

A database that had tables is never seeded again. RUN_DB_SEED is true in the development .env and false in production.

Migrating in a separate step

SPLENT_MIGRATE_ON_START=check

Set it in the container’s environment when a deploy agent migrates on its own before it starts the app. The container then migrates nothing and refuses to start unless every feature’s schema is at head (db:status --check), so it never serves on a schema older than its code. See automatic deployment.

Value	The start
`upgrade` (default, also when unset)	Applies what is pending
`check`	Applies nothing and stops unless the schema is at head
`off`	Applies nothing and checks nothing

Neither check nor off seeds. Any other value stops the start.

Pointing at the database

The framework builds SQLALCHEMY_DATABASE_URI from the MARIADB_* variables, so your product inherits it.

Variable	Description
`MARIADB_USER`, `MARIADB_PASSWORD`	The credentials
`MARIADB_HOSTNAME`	The host, `db` inside Docker
`MARIADB_PORT`	`3306` when unset or blank. Anything but a port number stops the app
`MARIADB_DATABASE`	The main database
`MARIADB_TEST_DATABASE`	The database `TestingConfig` uses

To use another URI, set it in your product’s config.py, as Configuration shows.

Advanced

Changing a constraint the database named

def _foreign_key(table, column):
    for key in sa.inspect(op.get_bind()).get_foreign_keys(table):
        if key["constrained_columns"] == [column]:
            return key["name"]


def upgrade():
    op.drop_constraint(_foreign_key("invoice_line", "invoice_id"), "invoice_line", type_="foreignkey")
    op.alter_column("invoice_line", "invoice_id", existing_type=sa.Integer(), nullable=False)
    op.create_foreign_key(
        "fk_invoice_line_invoice_id_invoice", "invoice_line", "invoice",
        ["invoice_id"], ["id"], ondelete="CASCADE",
    )

A first revision often lets MariaDB name a foreign key, such as invoice_line_ibfk_1. To change that key, read its name from the database, drop it, and create the new one with a name of your own that the model declares too.

MariaDB refuses to drop an index a foreign key stands on. In the downgrade, drop the key before the index and create the old key last. Test both directions on the tables the earlier revisions left.

Taking over another feature’s tables

When a new feature replaces an old one, it can keep the old tables and their rows as they are. Its version table starts empty, so its first revision must not create the tables again.

Keep the revision id of the old revision that created the tables, and its down_revision.
Create only what is missing. Check with sa.inspect(bind) before each table, column, index or constraint. An old database then passes with no DDL, and a new one gets the schema.
Drop only empty tables in the downgrade. It cannot tell its own tables from the old feature’s, so it keeps a table with rows and warns.
Declare the columns it adds to a third feature’s table under [tool.splent.refinement.extends], so only its migrations touch them.

Your product moves in one change. Remove the old feature (feature:remove asks for --force, since its migrations are applied), declare the new one and run db:upgrade.

Never db:rollback the old feature while it is declared. Its downgrade drops the tables and their rows.

The old row in splent_migrations stays as an orphan. db:status shows it and db:status --check ignores it. db:rollback on the undeclared feature offers to remove the row and runs no downgrade.

A model of a feature the app does not load

A model is mapped as soon as its module is imported, even when the app does not load its feature. A test can do that by accident. If such a model cannot be configured, for example because a relationship needs a column that another unloaded feature adds, the framework takes it out of the mappers and warns.

WARNING  Collection of splent_io.splent_feature_collections cannot be mapped in this app, which does not load collections (...), so it is left out of the mappers: querying it fails, every other model keeps working. Load collections in the app (feature:test --with collections, or [tool.splent.test] features in the feature's pyproject.toml), or do not import its models.

The query that met the failure still raises, and querying that class keeps failing. Every other model works. A model of an unloaded feature that configures fine stays mapped.

Reference

`MigrationManager`

MigrationManager(app) binds db to the app, wires Flask-Migrate and creates splent_migrations when it is missing. It runs during startup. The CLI uses its static methods.

Method	Returns or does
`get_feature_migration_dir(feature_name)`	The feature’s `migrations/` path, or `None`
`get_all_feature_migration_dirs()`	`{feature: path}` for every declared feature with `migrations/`, in UVL order
`get_current_feature_revision(feature_name, engine)`	The revision in the feature’s version table
`update_feature_status(app, feature_name, revision)`	Writes the feature’s row in `splent_migrations`
`get_all_status(app)`	Every row as `(feature, last_migration)`, by feature
`delete_feature_status(app, feature_name)`	Removes the feature’s row

The `splent_migrations` table

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;