Known Limitations & Pitfalls¶

This page documents the known limitations, gotchas, and design trade-offs in PHORM. Understanding these will help you avoid surprises in production.

Query Builder¶

`eq()` and `ne()` silently skip `null`¶

String? filter = null;
WhereBuilder().eq('status', filter); // No condition added!

This is intentional for convenient optional filter patterns. If you need to match NULL explicitly, use .isNull('column').

`inList()` with empty list → always false¶

WhereBuilder().inList('status', []); // Produces: 1 = 0

This is a safe default — an empty IN () is invalid SQL. But it means zero records are returned when you pass an empty filter list. Guard it:

// Use the extension method instead
WhereBuilder().inListIfNotEmpty('status', statusList);

// Or make an empty list a hard error (also on typed columns):
WhereBuilder().inList('status', statusList, strict: true);
Users.status.inList(statusList, strict: true);

`notInList()` with empty list → no condition¶

WhereBuilder().notInList('id', []); // No condition added — all records match

This is asymmetric behavior compared to inList. Intentional for safety (excluding nothing = no restriction). If a silently missing filter would be a bug, use strict: true — it throws an ArgumentError on an empty list (works on WhereBuilder and typed columns alike).

Dot-notation column names require registered relationships¶

// Silently produces broken SQL if 'comments' is not in relationships[]
WhereBuilder().eq('comments.status', 'approved');

No error is thrown at build time. The generated SQL will reference a table that was not joined, causing a SQLite "no such column" error at runtime.

Custom SQL Functions & `regexp()` Setup¶

The REGEXP operator (and other custom SQL functions) is not available in standard sqlite3 builds by default. Using .regexp() or custom functions without setup will throw a DatabaseException at runtime.

However, PHORM provides an elegant built-in way to register custom SQL functions and regular expressions via the SqlFunction utility.

How to configure:¶

Provide customFunctions when initializing your DB manager:

final db = DB(
  databaseName: 'app.db',
  version: 1,
  tables: [usersTable],
  customFunctions: [
    SqlFunction.regexp(), // Registers the standard REGEXP function
    SqlFunction.custom(
      name: 'DOUBLE',
      argumentCount: 1,
      function: (args) {
        if (args[0] == null) return null;
        return (args[0] as int) * 2;
      },
    ),
  ],
);

Once registered, these functions are fully available inside isolate database sessions, custom raw queries, and WhereBuilder clauses:

// 1. Using built-in regexp helper
final users = await userService.readAll(
  where: WhereBuilder().regexp('email', r'.*@gmail\.com'),
);

// 2. Using custom functions via safe raw queries
final olderUsers = await userService.readAll(
  where: WhereBuilder().raw('DOUBLE(age) > ?', [50]),
);

`SortBuilder` requires joined tables for dot notation¶

SortBuilder().asc('orders.created_at');

While SortBuilder supports dot notation, the query will fail at runtime if the orders table is not joined. Joins are automatically triggered by adding a condition on the related table in WhereBuilder.

CRUD Operations¶

`upsert` deletes and re-inserts rows¶

SQLite's INSERT OR REPLACE deletes the existing row and inserts a new one when there's a conflict. This means:

The internal rowid changes.
ON DELETE CASCADE foreign key constraints may trigger.
Any columns not present in toJson() are lost.

Use update for partial updates.

`insertBatch` vs `upsertBatch` on conflicts¶

insertBatch performs plain INSERTs — a duplicate primary key aborts the batch transaction with a constraint error. Use upsertBatch when you want existing rows silently replaced (ConflictAlgorithm.replace); note the replace caveats from the upsert section above.

Transactions require passing the `executor`¶

// WRONG — operations inside transaction() must use the txn object
await db.transaction((txn) async {
  await userService.insert(user); // ← This uses the global connection, NOT txn!
});

// CORRECT — pass the txn as executor
await db.transaction((txn) async {
  await userService.insert(user, executor: txn);
});

Timestamps are always UTC¶

Since phorm 1.4.0, automatic timestamps (created_at, updated_at, deleted_at) are written as DateTime.now().toUtc().toIso8601String() — sortable and consistent across devices and timezones. Convert to local time for display with .toLocal(). Rows written by versions before 1.4.0 keep their local-time values.

Relationships¶

Soft deletes in `include` require `paranoid: true` on the related table¶

Eager loading filters soft-deleted children automatically — but only when the related table is declared paranoid: the subquery then adds related.deleted_at IS NULL. If the related table merely has a deleted_at column without paranoid: true, deleted rows will appear in the loaded relationship.

`Includable.model<T>()` resolution¶

The generator sets up the model-to-table mapping automatically. Only a concern when creating Table manually.

`HasMany` performance on large datasets¶

JSON aggregation with json_group_array builds the entire related collection in-memory within SQLite. For large HasMany relationships (thousands of rows), consider:

Using Attributes.include(...) on the relationship to limit columns.
Paginating the related records with a separate query.
Loading related data separately instead of using include.

`GROUP BY` changes aggregation behavior¶

When cross-table filtering generates a LEFT JOIN, GROUP BY users.id is automatically added. This means COUNT(*) OVER() (used by readAllWithCount) counts distinct primary keys, not raw rows. This is the desired behavior, but be aware of it if you're using custom aggregation via .raw().

Schema & Generation¶

`Table.columns` must match actual SQL columns¶

Attributes.include(['col1', 'col2']) applies against table.columns. If you pass a column name that's not in that list, it will simply not appear in the query (no error). The generator populates table.columns automatically. If you create Table manually, you must provide it correctly.

`timestamps: true` automatically injects Dart fields via mixin¶

The generator adds created_at, updated_at to the SQL schema and injects DateTime? createdAt / DateTime? updatedAt into the generated _$PhormModelMixin. You do not need to declare them manually — they are accessible directly on your model instance via the mixin.

If you need to customize them (e.g. rename the column or add annotations), declare them manually in your class body — the generator will detect your manual declaration and skip generating the duplicate.

`paranoid: true` requires `deleted_at` in schema¶

If you set paranoid: true but your CREATE TABLE SQL doesn't have a deleted_at TEXT column, soft delete operations (delete, readAll filter) will fail silently or throw a database error.

The generator adds this automatically. Only a concern when creating Table manually.

DB & Migrations¶

Downgrade destroys all data¶

Decreasing DB.version below the file version triggers onDowngrade, which deletes and recreates the entire database. This is irreversible.

Modifying a migration re-applies it¶

Migration idempotency is based on a hash of {table, version, description, priority}. If you change the description of an existing migration, the hash changes and it will be re-applied. If the migration is ALTER TABLE ADD COLUMN and the column already exists, this will throw a DatabaseException.

`autoVersion` minimum is `1`¶

Even if no migrations are defined, DB.autoVersion returns version 1 as the minimum. This is by design.

Testing¶

Use `:memory:` for test isolation¶

setUp(() {
  db = DB(databaseName: ':memory:', version: 1, tables: [usersTable]);
  userService = PhormCore<User>(dbManager: db, table: usersTable);
});

tearDown(() async {
  await db.close();
});

In-memory databases are destroyed when closed, ensuring test isolation.

`@visibleForTesting` on `buildJoinQuery`¶

`PhormCore.buildJoinQuery` is exposed with `@visibleForTesting` to allow unit testing of the SQL generation logic. It is not part of the public API and may change without notice.¶

SQL Dialect & Database Specifics¶

Pluggable Dialect Differences¶

Because PHORM compiles queries dynamically using the SqlDialect defined by the driver, minor syntax differences exist when executing raw SQL queries (db.rawQuery() or WhereBuilder().raw()) across different database drivers:

Placeholders: SQLite (phorm_sqlite) utilizes standard ? positional parameters. PostgreSQL (phorm_postgres) uses $1, $2 positional arguments.
Identifiers: Avoid hardcoded identifier escapes (backticks or double quotes) inside raw strings where possible. Let SqlDialect.escapeIdentifier handle it programmatically, or use the generated table/column attributes.

SQLite Specifics (`phorm_sqlite`)¶

SQLite is weakly typed¶

Unlike other relational databases (like PostgreSQL or MySQL) which fail fast on mismatched types, SQLite does not strictly enforce column types. You can technically insert a string into an integer column without database-level errors.

Recommendation: Always perform validation at the application layer using phorm_generator's built-in validators or custom logic in fromJson.

Booleans are stored as Integers¶

SQLite does not have a native BOOLEAN type. PHORM stores them as 1 (true) and 0 (false) on disk. The generator automatically handles the boolean conversion in toJson and fromJson, but if you are writing raw SQLite queries, you must filter using 1 and 0.

Note

Future drivers like phorm_postgres will map Booleans directly to PostgreSQL's native boolean type, handled transparently by its custom dialect.

Schema & Generator Limitations¶

Schema Generator: SQLite Fully Implemented, Postgres/MySQL Scaffolded¶

While PHORM's core runtime (Query Builder, Where Builder, Eager Loading) is fully database-agnostic and dynamically adapts to the active SqlDialect (handling identifier escaping and dynamic placeholders programmatically), the code generator (phorm_generator) currently produces complete DDL only for SQLite.

The entry point PhormSchemaGenerator reads @Schema(dialect: ...) and dispatches to a per-dialect generator (SqliteSchemaGenerator, PostgresSchemaGenerator, MysqlSchemaGenerator). The default dialect is SqlDialectKind.sqlite. Postgres and MySQL have type mapping in place but their remaining DDL specifics (auto-increment/identity, updated_at mechanism, identifier quoting) are still scaffolded with TODOs.

For the default SQLite dialect, the generator: - Maps Dart data types directly to SQLite types (e.g., DateTime is mapped to TEXT). - Generates automatic update triggers for the updated_at column using the SQLite-specific datetime('now') syntax:

CREATE TRIGGER update_users_timestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET updated_at = datetime('now') WHERE id = OLD.id;
END;

Implication for future drivers: If you plan to target PostgreSQL or MySQL in the future, the generated Table.schema string might not be fully compatible with their DDL syntax (as PostgreSQL uses BEFORE UPDATE triggers, custom functions, and the native TIMESTAMP type with NOW()).

To run PHORM against alternative databases, you will need to: 1. Define your own custom table schemas DDL and migrations instead of relying on the generator's Table.schema. 2. Use alternative/custom schema builders or define triggers manually at the database level.

Known Limitations & Pitfalls¶

Query Builder¶

eq() and ne() silently skip null¶

inList() with empty list → always false¶

notInList() with empty list → no condition¶

Dot-notation column names require registered relationships¶

Custom SQL Functions & regexp() Setup¶

How to configure:¶

SortBuilder requires joined tables for dot notation¶