PhormCore — CRUD Operations¶

PhormCore<T> is the main service class. It wraps your Table<T> configuration and DB manager, and provides all CRUD operations with automatic timestamp handling, soft delete support, and type safety.

Setup¶

// 1. Table configuration (usually generated by phorm_generator)
final usersTable = Table<User>(
  name: 'users',
  schema: '...CREATE TABLE SQL...',
  fromJson: User.fromJson,
  primaryKey: 'id',
  paranoid: true,        // soft deletes
  timestamps: true,      // auto created_at/updated_at
  relationships: [
    HasMany(model: Order, foreignKey: 'user_id'),
  ],
  columns: ['id', 'first_name', 'email', ...],
);

// 2. DB manager
final db = DB(
  databaseName: 'app.db',
  version: 1,
  tables: [usersTable, ordersTable],
);

// 3. Service (Recommended)
final userService = db.service<User>();

// Alternative manual creation:
// final userService = PhormCore<User>(dbManager: db, table: usersTable);

// Pluralized Service (Recommended)
final rowId = await Users.insert(user);

// Using traditional PhormCore instance
final rowId = await userService.insert(user);

Note

insert automatically injects created_at and updated_at if table.timestamps = true. If created_at is already set in the model's toJson(), it is preserved; updated_at is always overwritten.

// Pluralized Service
await Users.update(updatedUser);

// Traditional
await userService.update(updatedUser);

Note

update always updates updated_at but removes created_at from the update payload to prevent accidental overwrite.

// Pluralized Service
await Users.upsert(user);

// Traditional
await userService.upsert(user);

Caution

upsert uses SQLite's INSERT OR REPLACE which deletes and re-inserts the row if there's a conflict. This resets the rowid and may affect foreign key constraints. For partial updates, prefer update.

// Soft delete
await Users.delete('user_id_here');

// Hard delete
await Users.delete('user_id_here', force: true);

// Restore (paranoid mode only)
await Users.restore('user_id_here');

Warning

restore throws StateError if table.paranoid is false. Always check the table configuration before calling restore.

// 1. Basic read
final user = await Users.readOne('user_id_here');

// 2. Include soft-deleted records
final user = await Users.readOne('user_id_here', withDeleted: true);

// 3. Eager loading
final user = await Users.readOne(
  'user_id_here',
  include: [Includable.model<Order>()],
);

Read All (Paginated)¶

PHORM provides two explicit methods depending on whether you need a total count:

// Returns List<T> — just the page of data
final users = await Users.query
  .where(Users.city.eq('Sofia'))
  .orderBy(Users.firstName)
  .limit(20)
  .get();

for (final user in users) { ... }


// Returns ResultWithCount<T> — data + total matching rows
final result = await Users.readAllWithCount(
  limit: 20,
  where: Users.city.eq('Sofia'),
);

print('Showing ${result.data.length} of ${result.count}');

Parameters (shared by both methods)¶

Parameter	Type	Default	Description
`limit`	`int`	`20`	Max rows to return
`offset`	`int`	`0`	Number of rows to skip
`where`	`WhereBuilder?`	`null`	Filter conditions
`sort`	`SortBuilder?`	`null`	ORDER BY clause
`withDeleted`	`bool`	`false`	Include soft-deleted records
`onlyDeleted`	`bool`	`false`	Return only soft-deleted records
`include`	`List<Includable>?`	`null`	Eager-load relationships
`attributes`	`Attributes?`	`null`	Column selection
`columns`	`List<String>?`	`null`	Raw column list (alternative to attributes)

Return Types¶

Method	Return type	Properties
`readAll(...)`	`Result<T>`	`.data` only
`readAllWithCount(...)`	`ResultWithCount<T>`	`.data` + `.count`

Important

readAllWithCount uses SQLite's COUNT(*) OVER() window function — count is computed in the same query without a second round-trip. However, it adds overhead to every row. Only use it when you need the count (e.g., pagination UI).

Aggregate Functions¶

PHORM provides type-safe aggregate functions. These functions automatically respect soft-delete filtering (paranoid mode) and any WhereBuilder conditions.

// 1. Count
final total = await Users.count();
final active = await Users.count(where: Users.isActive.eq(true));

// 2. Sum
final revenue = await Orders.sum(Orders.total);

// 3. Average
final avgRating = await Reviews.avg(Reviews.rating);

// 4. Min/Max
final minPrice = await Products.min(Products.price);
final maxPrice = await Products.max(Products.price);

Note

sum, avg, min, and max return a double? (null if no records match). count returns an int.

Batch Operations¶

All batch operations run in a single SQLite transaction, making them significantly faster than individual calls.

// Pluralized Service
await Users.insertBatch([user1, user2]);
await Users.updateBatch([user1, user2]);
await Users.upsertBatch([user1, user2]);
await Users.deleteBatch(['id1', 'id2']);
await Users.restoreBatch(['id1', 'id2']);

Note

Batch operations call batch.commit(noResult: true) for best performance. The individual row IDs are not returned.

Nested Writes — `insertWith`¶

Inserts a model together with its related children in one transaction — the write-side counterpart of eager loading:

await Users.insertWith(user, {
  'posts': [post1, post2], // HasMany: user_id is stamped automatically
  'tags': [tagA],          // ManyToMany: inserts tagA + a pivot row
});

Keys of the map are related table names, exactly like in include.
HasMany/HasOne children get the parent's local key written into their foreign key column. For a fresh autoincrement parent the returned row id is used.
ManyToMany children are inserted and linked through the pivot table.
BelongsTo entries throw an ArgumentError — insert the parent row first.
If any insert fails, the whole transaction rolls back.

Transactions¶

Use when multiple operations must succeed or fail together.

await db.transaction((txn) async {
  // You can use PhormCore methods inside transaction by passing the executor
  await userService.insert(user, executor: txn);
  await orderService.insert(order, executor: txn);
});

Tip

Always pass the txn object to executor parameter of CRUD methods inside the transaction block. If you omit it, the operation will run on a separate connection outside the transaction.

Nested Transactions (Savepoints)¶

transaction() can be called inside another transaction. The outermost call issues BEGIN/COMMIT; inner calls become SQLite savepoints, so a failed inner transaction rolls back only its own writes:

await db.transaction((txn) async {
  await userService.insert(user, executor: txn);

  try {
    await db.transaction((inner) async {
      await auditService.insert(riskyEntry, executor: inner);
      // throws → only riskyEntry is rolled back
    });
  } catch (_) {
    // The outer transaction continues and can still commit.
  }

  await orderService.insert(order, executor: txn);
});

An outer rollback always reverts everything, including successfully released inner savepoints.

Query Observability (`onQuery`)¶

Pass an observer to DB to receive a QueryEvent for every database operation — successful, slow or failed — independently of logQueries:

final db = DB.autoVersion(
  databaseName: 'app.db',
  tables: [usersTable],
  onQuery: (event) {
    metrics.timing('db.query', event.duration);
    if (event.isSlow) tracer.report('slow query', event.sql);
    if (event.failed) crashlytics.recordError(event.error, event.stackTrace);
  },
);

QueryEvent carries the SQL (or a short action label like SOFT DELETE users), bound arguments, duration, an isSlow flag (per slowQueryThreshold) and the error with stack trace for failed operations.

Important

The callback runs synchronously on the query path — keep it fast and non-throwing. Offload heavy work (network, disk) asynchronously.

Attributes — Column Selection¶

Use Attributes to fetch only the columns you need, reducing memory and transfer overhead.

// Include only specific columns
final result = await userService.readAll(
  attributes: Attributes.include(['id', 'first_name', 'email']),
);

// Exclude specific columns (fetch everything else)
final result = await userService.readAll(
  attributes: Attributes.exclude(['raw_metadata', 'internal_notes']),
);

// Apply to relationship columns too
final result = await userService.readAll(
  include: [
    Includable.model<Order>(
      attributes: Attributes.include(['id', 'total']),
    ),
  ],
);

Caution

Attributes.include() applies the filter from table.columns — the static list generated at build time. If you include a column that doesn't exist in that list, it will silently be absent from the query. Make sure column names match exactly.

SortBuilder¶

Simple fluent builder for ORDER BY clauses.

SortBuilder()
  .asc('first_name')     // first_name ASC
  .desc('created_at');   // created_at DESC

// Builds to: "first_name ASC, created_at DESC"

Note

SortBuilder validates column names and supports dot-notation for related tables (e.g., orders.created_at). However, for the query to work, the related table must be joined (e.g., by having a condition on it in WhereBuilder).

PhormCore — CRUD Operations¶

Setup¶

Read All (Paginated)¶

Parameters (shared by both methods)¶

Return Types¶

Aggregate Functions¶

Batch Operations¶

Nested Writes — insertWith¶

Transactions¶

Nested Transactions (Savepoints)¶

Query Observability (onQuery)¶

Attributes — Column Selection¶

SortBuilder¶

Nested Writes — `insertWith`¶

Query Observability (`onQuery`)¶