Use with gisila_orm

You’ll back the notes service from Build a CRUD API with Postgres through gisila_orm: connect at startup, hand every request the database, run queries and transactions in services, turn constraint violations into 409/400 responses, and expose a readiness check.

Prerequisites

• A Postgres database and its URL in DATABASE_URL.
• gisila_orm models generated from a schema (see the ORM documentation). This guide assumes a Note model with NoteTable from lib/models/notes.gisila.g.dart.

Steps

1. Add the dependency

dependencies:
  gisila: ^0.4.1
  gisila_orm: any   # use the version or path your project pins

2. Connect and register the provider

package:gisila_orm/web.dart provides the glue:

• GisilaOrmDatabaseProvider(db) hands every request the shared Database. Its pool lends a connection per query, so nothing is opened per request. app.stop() closes the pool (pass closeOnDispose: false if something else owns it).
• PostgresDbErrorMapper() turns unique, foreign-key, not-null, check and bad-value errors into 409/400 responses, and a lost connection into 503.

import 'package:gisila/gisila.dart';
import 'package:gisila_orm/gisila.dart';
import 'package:gisila_orm/web.dart';

Future<void> main() async {
  final config = await DatabaseConfig.fromEnvironment(); // DATABASE_URL, or database.yaml
  final db = await Database.connect(config);
  final orm = GisilaOrmDatabaseProvider(db);

  final app = GisilaApp(
    config: AppConfig(
      database: orm,                                  // ctx.db<Database>()
      dbErrorMapper: const PostgresDbErrorMapper(),
      healthCheckPath: '/healthz',
      healthCheck: orm.isHealthy,                     // 503 when SELECT 1 fails
    ),
  );

  app.registerService<NoteService>(NoteService.new);
  app.registerController(attacher: NotesController().attachToApp);
  await app.start(port: 8080, stopOnSignals: true);
}

3. Query from a service

import 'package:gisila/gisila.dart';
import 'package:gisila_orm/gisila.dart';

import '../models/notes.gisila.g.dart';

class NoteService extends Service {
  DbContext get _db => db<Database>().context();

  Future<Page<Note>> list(PageParams page) async {
    final rows = await Query<Note>(NoteTable.metadata)
        .orderBy(NoteTable.id)
        .limit(page.limit)
        .offset(page.offset)
        .all(_db);
    final total = await Query<Note>(NoteTable.metadata).count(_db);
    return Page(rows, params: page, total: total);
  }

  Future<Note> get(int id) async =>
      await Query<Note>(NoteTable.metadata).where(NoteTable.id.eq(id)).first(_db) ??
      (throw NotFound('Note $id not found'));

  Future<Note> create(String title, String body) =>
      Query<Note>(NoteTable.metadata).insert({'title': title, 'body': body}).one(_db);
}

ctx.db<Database>() (or db<Database>() inside a service) returns the Database the provider handed out; context() gives the pool-backed DbContext that queries run against. Generated models have toJson(), so you can return them from controllers directly.

4. Use transactions for multi-step writes

The provider doesn’t open a transaction per request. Wrap the steps that must succeed together:

Future<Note> moveToFolder(int noteId, int folderId) {
  return db<Database>().transaction((tx) async {
    final updated = await Query<Note>(NoteTable.metadata)
        .where(NoteTable.id.eq(noteId))
        .update({'folder_id': folderId})
        .run(tx);                              // the updated rows (RETURNING *)
    if (updated.isEmpty) throw NotFound('Note $noteId not found');
    await Query<Folder>(FolderTable.metadata)
        .where(FolderTable.id.eq(folderId))
        .update({'updated_at': DateTime.now().toUtc()})
        .run(tx);
    return updated.single;
  });
}

Throwing inside the body rolls everything back; an HttpException thrown there still reaches the client as usual.

If you’d rather have one transaction per request (commit when the status is below 500, roll back otherwise), write your own DatabaseProvider whose open begins a transaction and whose close(ok:) commits or rolls back.

5. Let the error mapper handle constraints

With a unique index on notes.slug, inserting a duplicate answers:

{"error":{"status":409,"message":"\"slug\" is already taken.","code":"unique_violation","details":{"fields":["slug"]}}}

Messages name columns, never values, so they don’t leak other users’ data.

Verify it works

DATABASE_URL=postgres://postgres:postgres@localhost:5432/notes dart run bin/server.dart &
curl -s localhost:8080/healthz                 # {"status":"ok"}
curl -s -X POST localhost:8080/notes -H 'content-type: application/json' -d '{"title":"Hi"}'
curl -s localhost:8080/notes

Stop Postgres and /healthz answers 503 {"status":"unavailable"}, so your orchestrator or the gisila panel can take the instance out of rotation.

Next steps

• ORM documentation: schemas, migrations, relations, preloading.
• Database and cache reference
• Test controllers