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'.

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 Alembic version state only when no application tables exist. delete_migrations is a confirmed destructive reset: it drops all tables and views in the configured SQL database, or all collections in MongoDB, and deletes local revision files. It removes application data too; back up first and use it only when you intend to start from a completely clean 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 all 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]'