Skip to main content

Database Portability

How to write Framework M applications that remain portable across supported relational databases.

0. What "Portable" Means Here​

Framework M supports multiple relational database backends. At the time of writing these are:

  • SQLite
  • PostgreSQL
  • MySQL
  • MariaDB
  • Oracle
  • Microsoft SQL Server

We call this the Big5+SQLite set. An app is portable when the same DocType definitions, repository calls, controllers, and business logic work on any of these backends without source changes.

Goal

You should be able to develop against SQLite or PostgreSQL locally and deploy to Oracle or MSSQL in production without rewriting application code.

1. What the Framework Abstracts​

The framework abstracts the following so that app code stays portable:

CapabilityPortable Abstraction
SchemaDocType definitions mapped to dialect-specific SQLAlchemy types
CRUDGenericRepository.get, save, delete
Querieslist_entities with FilterSpec and OrderSpec
Paginationlimit / offset with deterministic default ordering
Child tablesDocType fields marked as child tables
Link fieldsLink[OtherDoc] with automatic fetch-from resolution
ConcurrencyOptimistic locking via _version
Exists / CountRepository exists and count helpers
Unique constraintsDocType uniqueness rules mapped to indexes

The framework handles dialect-specific schema mapping. For example:

  • UUID primary keys become UNIQUEIDENTIFIER on MSSQL and CHAR(36) on MySQL.
  • Unbounded strings become VARCHAR(255) on MSSQL so they can participate in unique indexes.
  • JSON fields are mapped to JSON on PostgreSQL, OracleJSON, and equivalent types elsewhere.
  • Child-table reference columns receive appropriate lengths on MySQL/MariaDB.

2. What Developers Must Still Handle​

The framework cannot make raw database operations portable. Avoid the following if cross-database deployment is a goal:

Risky PatternWhy It Breaks Portability
Raw SQLtext("..."), native functions, and dialect-specific syntax vary across databases.
SQLAlchemy Core with dialect-specific functionsGeneric Core queries compile portably, but functions like func.jsonb_extract_path, func.to_tsvector, or ARRAY types do not exist on every database.
Stored procedures / triggersNot managed by Framework M; behavior differs by vendor.
Vendor-specific typesUsing ARRAY, JSONB operators, geospatial types, or full-text search directly.
Case sensitivityMySQL default collations may be case-insensitive; PostgreSQL is case-sensitive by default.
Empty string semanticsOracle treats empty strings as NULL.
Transaction isolationDefault isolation levels and lock behavior differ.
Date/time precisionFractional-second precision and NOW() equivalents vary.
Raw SQL Is a Portability Escape Hatch

If you write raw SQL, you are opting out of the framework's portability guarantees. That is sometimes necessary, but it should be an explicit decision.

3. Best Practices for Portable Apps​

3.1. Use DocTypes and Repository Methods​

Stay within the public repository API:

from framework_m_core.interfaces.repository import FilterOperator, FilterSpec

result = await repo.list_entities(
    session,
    filters=[FilterSpec(field="status", operator=FilterOperator.EQ, value="active")],
    order_by=[OrderSpec(field="modified", direction="desc")],
    limit=20,
)

This call compiles to the correct SQL for each backend.

3.2. Prefer Framework Field Types​

Use Framework M field types and annotations instead of SQLAlchemy-native constructs:

from framework_m import DocType, Field, Link

class Order(DocType):
    customer: Link[Customer]
    total: float
    status: str = Field(default="draft")

3.3. Avoid Database-Native Defaults​

Do not rely on database-side defaults, computed columns, or triggers for business logic. Implement defaults in DocType Field(default=...) or controllers.

3.4. Prefer Framework Methods Over SQLAlchemy Core​

SQLAlchemy Core is more portable than raw SQL because it compiles dialect-aware statements. However, it still exposes database-specific functions and types, and it bypasses Framework M features such as soft-delete filtering, RLS, link-field resolution, and optimistic concurrency. Use it only when the repository API cannot express what you need, and keep queries generic.

3.5. Treat creation / modified as Metadata​

These fields are maintained by the framework. Use them for display and coarse ordering, not as unique sequence identifiers.

3.6. Test on Your Target Database​

The framework runs a shared integration suite on all supported databases, but your application should still be tested on the database you plan to deploy to. Container-based local testing is supported via testcontainers.

4. How Confidence Is Verified​

The shared BaseDatabaseFlowTests class runs identical tests against SQLite, PostgreSQL, MySQL, MariaDB, Oracle, and MSSQL. It covers:

  • Table creation
  • Insert, update, delete
  • Child table CRUD, ordering, and cascade delete
  • Link field resolution
  • Filtering with GTE, LIKE, IN, IS NULL, and combined AND/OR
  • Ordering by single and multiple fields, including NULL values
  • Pagination, including offsets and deterministic tie-breaking
  • Optimistic concurrency
  • Exists / count
  • Unique constraint violations on insert and update
  • Soft delete exclusion and inclusion
  • DocType registration

CI runs these tests on every merge request and default-branch commit.

5. When to Stop Worrying About Portability​

You do not need portability if you control the production database and will never switch. In that case, using database-specific features is fine. Framework M will not stop you from writing raw SQL or custom adapters. Portability is a design goal, not a constraint.