Skip to main content
Modulo

Database Abstraction

Modulo’s database layer is built on SQLAlchemy 2.0 with multi-backend support (PostgreSQL, SQLite, MariaDB, MySQL). See Architecture for the high-level system design.

Key components

  • Declarative model system: Base(DeclarativeBase), TimestampMixin, OrgScoped
  • Multi-backend repository layer: Row-Level Security (RLS) policies and generic Object-Relational Mapping (ORM) filter injection on PostgreSQL, explicit tenant filtering on SQLite, MariaDB, and MySQL
  • Alembic migrations: incremental migration scripts for schema versioning
  • Create, read, update, delete (CRUD) service layer: function-based entity-specific modules
  • Cursor-based pagination: keyset pagination with URL-safe Base64 cursors
  • Testcontainers integration: real PostgreSQL 16 in test suites

Choosing a backend

MODULO_DB selects the backend and defaults to postgres. DATABASE_URL carries the async connection string, for example postgresql+asyncpg://user:password@host:5432/modulo. See Configuration for the full variable list.

Choosing a backend
Backend Tenant isolation Notes
PostgreSQL Row-Level Security (RLS) plus ORM filter injection Default. Also provides advisory locks and flood protection, and the test suite runs against PostgreSQL 16 in Testcontainers.
SQLite Explicit ORM filtering Choosing it disables RLS, advisory locks, and flood protection.
MariaDB / MySQL Explicit ORM filtering Connects through the aiomysql driver.

Multi-tenancy

Only PostgreSQL isolates tenants in the database itself. On SQLite, MariaDB, and MySQL, the ORM layer applies the tenant filter instead, which is why PostgreSQL is the backend for multi-tenant deployments.

Next: Lifecycle Maps