CRUD operations

You’ll write a small repository for the Post model from Define a schema covering every basic operation.

Prerequisites

The blog schema generated and migrated.
A Database connected as in Set up a project.

Steps

1. Take a `DbContext`

Write data-access code against DbContext so callers can run it inside or outside a transaction:

import 'package:blog/models/blog.gisila.g.dart';
import 'package:gisila_orm/gisila.dart';

class PostRepository {
  const PostRepository(this.db);
  final DbContext db;
}

final repo = PostRepository(db.context());

2. Create

The snippets in steps 2 to 7 are methods of PostRepository.

Future<Post> create({required int authorId, required String title, String? body}) =>
    posts().insert({
      'author_id': authorId,
      'title': title,
      'body': body,
    }).one(db);

Keys are column names; authorId would work too (camelCase is converted).
Leave out id, status, tags and anything else with a database default.
one returns the inserted row, including id and defaults, via RETURNING *.

From a model you’ve built, drop the null id first:

final draft = Post(title: 'Hi', status: PostStatus.draft, tags: const [], authorId: 1);
final saved = await posts().insert(draft.toRow()..remove('id')).one(db);

3. Create many

Future<List<Post>> createMany(int authorId, List<String> titles) {
  final q = posts().insert({'author_id': authorId, 'title': titles.first});
  for (final t in titles.skip(1)) {
    q.values({'author_id': authorId, 'title': t});
  }
  return q.run(db);
}

Every row must list the same columns. Keep batches below Postgres’s 65,535 parameter limit (rows × columns).

4. Read

Future<Post?> byId(int id) => posts().where(PostTable.id.eq(id)).first(db);

Future<Post> byIdOrThrow(int id) => posts().where(PostTable.id.eq(id)).one(db);

Future<List<Post>> byAuthor(int authorId) => posts()
    .where(PostTable.authorId.eq(authorId))
    .orderBy(PostTable.id, desc: true)
    .all(db);

Future<bool> titleTaken(String title) => posts().where(PostTable.title.eq(title)).exists(db);

Future<int> countByAuthor(int authorId) =>
    posts().where(PostTable.authorId.eq(authorId)).count(db);

one throws QueryNoRowsException when nothing matches (and QueryMultipleRowsException for more than one); first returns null.

5. Update

Future<Post?> rename(int id, String title) async {
  final rows = await posts().where(PostTable.id.eq(id)).update({'title': title}).run(db);
  return rows.isEmpty ? null : rows.single;
}

Future<void> publish(int id) => posts()
    .where(PostTable.id.eq(id))
    .update({
      'status': PostStatus.published.sqlValue,
      'published_at': DateTime.now().toUtc(),
    })
    .returning(false)
    .run(db);

Only the keys you pass are changed.
Write enum columns as .sqlValue.
An update without a where throws InvalidQueryBuilderException; call .all() if you mean every row.
Updates have no one(); take .single of the returned list.

6. Upsert

Future<Author> upsertAuthor(String email, String name) => authors()
    .insert({'email': email, 'name': name})
    .onConflictDoUpdate(['email'])
    .one(db);

['email'] must match a unique constraint (is_unique: true in the schema).

7. Delete

Future<bool> delete(int id) async {
  final deleted = await posts().where(PostTable.id.eq(id)).delete().run(db);
  return deleted.isNotEmpty;
}

Comments go with their post because the schema says on_delete: CASCADE. A delete without where throws unless you add .all().

8. Handle constraint errors

try {
  await authors().insert({'name': 'Ada', 'email': 'ada@example.com'}).one(db);
} on PostgresUniqueViolationException {
  print('email already registered');
} on PostgresForeignKeyViolationException {
  print('referenced row missing');
}

Verify it works

final a = await authors().insert({'name': 'Ada', 'email': 'ada@example.com'}).one(db);
final p = await repo.create(authorId: a.id!, title: 'First');
assert((await repo.byId(p.id!))!.title == 'First');
await repo.rename(p.id!, 'Second');
assert((await repo.byId(p.id!))!.title == 'Second');
assert(await repo.delete(p.id!));
assert(await repo.byId(p.id!) == null);