GUIDE 07 · SCHEMA CHANGES6 min read

Migrations and database setup

Choose one database backend, detect model changes, and safely apply migration revisions.

IN THIS GUIDE

Work through each step in order. The examples use the generated LenAPI project structure and are designed to be adapted to your app.

Choose one database and set its URL

DATABASE_TYPE selects one active backend and DATABASE_URL connects to it for model operations and migrations. Leave the default SQLite setup for local development; choose MySQL, PostgreSQL, or MongoDB by changing both settings. Do not put a second database URL in a separate async setting: ACTIVATE_ASYNC enables async access to the same configured database. Keep real production credentials private and URL-encode reserved characters in passwords.

Python

# settings.py — choose ONE pair
# SQLite (default; relative path)
DATABASE_TYPE = "sqlite"
DATABASE_URL = "sqlite:///lenapi.db"

# MySQL (install: python -m pip install 'lenapi[mysql]')
# DATABASE_TYPE = "mysql"
# DATABASE_URL = "mysql://user:password@localhost:3306/app_db"

# PostgreSQL (install: python -m pip install 'lenapi[postgres]')
# DATABASE_TYPE = "postgresql"
# DATABASE_URL = "postgresql://user:password@localhost:5432/app_db"

# MongoDB (install: python -m pip install 'lenapi[mongodb]')
# DATABASE_TYPE = "mongodb"
# DATABASE_URL = "mongodb://user:password@localhost:27017/app_db"

ACTIVATE_ASYNC = False

TIP

When async access is enabled, SQLite uses aiosqlite; MySQL and PostgreSQL also need the async driver extras: lenapi[async-mysql] or lenapi[async-postgres]. MongoDB uses its separate sync/async document API. Relational driver URLs may omit an explicit driver; LenAPI selects its supported driver for the chosen backend.

Declare models in the app

Put app models in app/models.py and import them from main.py so migration discovery loads them before Alembic compares metadata.

Python

# app/models.py
from lenapi import model

class Student(model.Model):
    person_name = model.StringField(max_length=120, unique=True)
    course = model.StringField(max_length=120)

Know when to use migration or migrate

migration compares imported model metadata with the database, creates a new Alembic revision when there is a schema change, then applies pending revisions. If there is no model change, it skips the upgrade. migrate never generates a revision: it applies only migration files already present locally. Use migration during development after changing models; commit the generated revision, then use migrate in deployment/release steps to apply the reviewed revision.

Python

# Development: detect model changes, generate a revision, and apply it
python lenora.py migration

# Deployment: apply checked-in pending revisions only
python lenora.py migrate

TIP

Import app model modules before running either command so Alembic compares the complete application metadata. Optional revision label: python lenora.py migration --message 'add student phone'.

Positive fields and interrupted MySQL migrations

PositiveIntegerField and PositiveBigIntegerField generate one named database CHECK constraint each. LenAPI prevents Alembic's metadata-copy step from duplicating those constraints, which MySQL rejects as a duplicate constraint name. If a revision generated by an older framework version contains repeated identical sa.CheckConstraint(...) entries, fix or regenerate that unapplied revision before applying it. MySQL DDL is not transactional: a failed migration can leave earlier project tables behind without recording a revision. Inspect showmigrations and the tables in the current project namespace before retrying. For disposable data, delete_migrations can reset this project's objects and local revisions after you confirm; preserve needed data by reconciling the partial schema instead.

Python

# Inspect local revision files and applied database state
python lenora.py showmigrations

# Only when this project's data can be discarded; prompts for confirmation
python lenora.py delete_migrations
python lenora.py migration

TIP

Do not assume a failed MySQL migration rolled back earlier CREATE TABLE statements. delete_migrations is destructive within the current project's namespace; other project namespaces remain untouched.

Inspect migration history

showmigrations lists local revision files, marks which revisions are applied, and reports missing revision files referenced by the database or by another revision. Use it before repair and when a migration target is not up to date. With MongoDB it explains that Alembic revisions do not apply.

Python

python lenora.py showmigrations

Repair a missing revision reference

repair-migrations is for broken or missing Alembic history, not for applying model changes. It compares the live SQL schema with current model metadata and only stamps migration history when they match; it preserves tables and data. If schemas differ, it refuses. Restore the missing revision file or reconcile the live schema first. MongoDB does not support this command.

Python

python lenora.py showmigrations
python lenora.py repair-migrations
python lenora.py showmigrations

TIP

For an empty database with no revisions, use migration to create the initial revision instead. Do not use repair as a shortcut to ignore unapplied schema changes.

Reset migration state only when intended

clear_migrations removes local revision files and this project's Alembic version state only when this project has no application tables. delete_migrations is a confirmed destructive reset: it drops tables and views in this project's SQL namespace, or collections in this project's MongoDB namespace, and deletes local revision files. Other project namespaces are untouched. It removes this project's application data too; back up first and use it only when you intend to start from a clean project database.

Python

# Limited to an empty SQL database
python lenora.py clear_migrations

# Full destructive reset; prompts for y/n confirmation
python lenora.py delete_migrations

# Then create and apply a fresh initial revision
python lenora.py migration

TIP

MongoDB has no Alembic history: showmigrations reports that fact, migration/migrate initialize missing collections, repair-migrations and clear_migrations are SQL-only, and delete_migrations drops this project's namespaced collections after confirmation.

Install a backend driver

SQLite ships with the default package. MySQL and PostgreSQL use SQLAlchemy relational models and Alembic migrations. MongoDB uses the separate MongoDocument API; migration initializes collections but does not generate revisions for document field changes.

Python

python -m pip install 'lenapi[mysql]'
python -m pip install 'lenapi[postgres]'
python -m pip install 'lenapi[mongodb]'

# Optional async drivers
python -m pip install 'lenapi[async-mysql]'
python -m pip install 'lenapi[async-postgres]'