PHORM: a Dart/Flutter ORM that loads your whole object graph in one SQL query

#dart #flutter #sqlite #orm

PHORM: the Flutter ORM that loads your whole object graph in one query

If you have ever built a Flutter app with a local database, you know the three taxes you pay:

Stringly-typed everything. row['user_name'] as String? scattered across the codebase, errors discovered at runtime.
Boilerplate. Hand-written DAOs, toJson/fromJson, mappers, query helpers — for every single model.
The N+1 problem. Load 50 posts, then fire 50 more queries to load each author. Your "fast local DB" suddenly feels slow.

PHORM (Predictable Harmonious ORM) is a lightweight, type-safe, driver-agnostic ORM for Dart and Flutter built specifically to remove all three. Let me show you the parts that actually change how you write data code.

1. You write a plain class. PHORM writes the rest.

You annotate a normal Dart class:

@Schema(tableName: 'users')
class User extends Model with _$PhormUserMixin {
  @ID(autoIncrement: true)
  final int id;

  @Column(unique: true)
  final String email;

  @Column()
  final String name;

  @Column()
  final int age;

  User({required this.id, required this.email, required this.name, required this.age});

  factory User.fromJson(Map<String, dynamic> json) => _$PhormUserFromJson(json);
}

Run build_runner, and PHORM generates:

the CREATE TABLE / index / foreign-key SQL,
toJson / fromJson / copyWith / toString,
type-safe column descriptors (Users.age, Users.email, …),
and a full Active-Record-style service class (Users).

No DAO. No mapper. No Map<String, dynamic> in your business logic.

💡 There's even a VS Code extension that converts a plain Dart class into a fully annotated PHORM model in one click.

2. A data API that reads like a sentence

Because the service is generated per-model, your queries are fully typed and discoverable through autocomplete:

// Create
await Users.insert(user);

// Read by primary key
final user = await Users.readOne(1);

// Fluent, type-safe queries
final adults = await Users
    .where(Users.age.gt(18))
    .where(Users.name.like('%John%'))
    .orderBy(Users.name)
    .get();

// Pagination with a total count, in one call
final page = await Users.readAllWithCount(limit: 20, offset: 0);

Users.age.gt(18) is not a string — it's a typed PhormColumn<int>. Pass the wrong type and it won't compile. Every value is parameterized, so you get SQL-injection protection for free.

3. The killer feature: one query for the entire object graph

This is where PHORM earns its name. Say a User has many Posts, and each Post belongs to a Category. The naive ORM approach fires a cascade of queries (the N+1 problem). PHORM instead compiles the whole tree into a single SQL statement using the database's native JSON functions (SQLite's json_group_array, PostgreSQL's jsonb_agg):

final users = await Users
    .where(Users.age.gt(18))
    .include([
      Includable.model<Post>(
        include: [Includable.model<Category>()],
      ),
    ])
    .get();

// users.first.posts.first.category.name  —  already loaded, fully typed.

One round trip. No N+1. No manual joins. No stitching results together in Dart. On a list screen that shows nested relationships, this is the difference between janky and instant.

You can still filter by a related table's column — PHORM adds the LEFT JOIN for you automatically.

4. Driver-agnostic by design

PHORM separates query building and relationship mapping from database-specific SQL grammar via a pluggable Dialect system. The same models and the same generated API are meant to run across multiple SQL backends:

SQLite today (phorm_sqlite), including a Flutter Web (WASM + IndexedDB) backend with zero code changes.
PostgreSQL and MySQL dialects are scaffolded for the future.

Your application code doesn't care which one it talks to.

5. The everyday essentials, built in

Migrations — versioned, idempotent, with a fluent migrate() builder (addColumn, renameColumn, createIndex, custom, …).
Soft deletes — opt-in paranoid mode with delete / restore.
Timestamps — automatic createdAt / updatedAt.
Batch & transactions — atomic insertBatch / updateBatch and real transactions with a shared executor.
Validators — @Column(validators: [...]) generates both SQL CHECK constraints and Dart-side validate().

And when you don't want the large generated service for a given model, the new @Schema(generateFullService: false) keeps the output lean.

6. Reactive by default — perfect for Flutter

This is the part Flutter developers tend to love. Every model service exposes reactive streams that re-emit whenever the underlying data changes, so your widgets can be driven straight from the database — no manual refresh, no event bus, no "pull to reload after insert".

// Watch a single row by id (with relationships if you want)
Stream<User?> user$ = Users.watchOne(1);

// Watch a filtered, sorted, limited list
Stream<List<User>> activeAdults$ = Users.watchAll(
  where: WhereBuilder().gt(Users.age, 18),
  sort: SortBuilder().asc(Users.name),
  limit: 50,
  include: [Includable.model<Post>()], // nested data, still one query
);

Drop either stream into a StreamBuilder and the UI just stays in sync:

StreamBuilder<List<User>>(
  stream: Users.watchAll(
    where: WhereBuilder().eq(Users.status, 'active'),
    sort: SortBuilder().desc(Users.createdAt),
  ),
  builder: (context, snapshot) {
    final users = snapshot.data ?? const [];
    return ListView.builder(
      itemCount: users.length,
      itemBuilder: (_, i) => ListTile(
        title: Text(users[i].name),
        subtitle: Text(users[i].email),
      ),
    );
  },
);

Now call Users.insert(...), Users.update(...) or Users.delete(...) from anywhere in the app — the list rebuilds automatically. Because the same JSON-aggregation engine powers the streams, watching a list with nested relationships still costs one query per emission. It pairs naturally with StreamBuilder, StreamProvider, BlocProvider, or any state-management approach you already use.

Getting started

dependencies:
  phorm_sqlite: ^1.2.0 # SQLite driver — re-exports the phorm core

dev_dependencies:
  phorm_generator: ^1.2.0
  build_runner: ^2.4.0

dart run build_runner build --delete-conflicting-outputs

That's it — annotate your models, generate, and start querying.

Why I think it's worth a look

Most local-DB solutions force a trade-off: either raw and fast but stringly-typed, or "nice" but slow once relationships show up. PHORM's bet is that you shouldn't have to choose — type safety, an ergonomic Active-Record API, and single-query performance can coexist, with codegen doing the heavy lifting.

If you build Flutter apps with non-trivial local data, give it a try: