Test with a real database

You’ll write a test helper that gives each test its own empty Postgres schema with your migrations applied, and use it in package:test tests. You’ll also see how to assert on generated SQL without a database.

The ORM’s own test helpers live under its test/ directory and aren’t part of the published API, so you write your own; the version below follows the same approach.

Prerequisites

dart pub add --dev test and dart pub add postgres.

A disposable PostgreSQL, for example from Docker:

docker run -d --name blog-test -p 5455:5432 -e POSTGRES_PASSWORD=postgres postgres:16-alpine

Steps

1. Write the helper

test/support/test_db.dart:

import 'dart:io';
import 'dart:math';

import 'package:gisila_orm/gisila.dart';
import 'package:postgres/postgres.dart' show Connection, ConnectionSettings;

final _url = Platform.environment['TEST_DATABASE_URL'] ??
    'postgres://postgres:postgres@localhost:5455/postgres';

/// Runs [body] against a fresh schema with all migrations applied, then drops it.
Future<void> withTestDb(Future<void> Function(Database db) body) async {
  final base = DatabaseConnection.fromUrl('default', _url);
  final schema = 't_${Random().nextInt(1 << 32).toRadixString(36)}';

  Future<void> admin(String sql) async {
    final c = await Connection.open(
      Endpoint(
        host: base.host,
        port: base.port,
        database: base.database,
        username: base.username,
        password: base.password,
      ),
      settings: ConnectionSettings(sslMode: SslMode.disable),
    );
    try {
      await c.execute(sql);
    } finally {
      await c.close();
    }
  }

  await admin('CREATE SCHEMA "$schema"');
  final db = await Database.connect(
    DatabaseConfig(connections: [base]),
    // Every pooled connection, including replacements, uses the test schema.
    onOpen: (conn) async => conn.execute('SET search_path TO "$schema", public'),
  );
  try {
    final manager = MigrationManager(db);
    await manager.up(await manager.discoverIn('lib/models/migrations'));
    await body(db);
  } finally {
    await db.close();
    await admin('DROP SCHEMA IF EXISTS "$schema" CASCADE');
  }
}

A schema per test keeps tests independent and lets them run in parallel against one server.
onOpen reapplies search_path on every new physical connection, so it survives the pool replacing a connection.
Running the real migrations tests them too. If your schema uses an extension such as pgvector, create it in public once on the test server.

2. Write tests

test/post_repository_test.dart:

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

import 'support/test_db.dart';

void main() {
  test('preloads posts per author', () async {
    await withTestDb((db) async {
      final ctx = db.context();
      final ada = await authors().insert({'name': 'Ada', 'email': 'ada@example.com'}).one(ctx);
      await posts().insert({'title': 'A', 'author_id': ada.id}).values({'title': 'B', 'author_id': ada.id}).run(ctx);

      final list = await authors().preload([Author.posts]).all(ctx);
      expect(list.single.postsList.map((p) => p.title), unorderedEquals(['A', 'B']));
    });
  });

  test('email is unique', () async {
    await withTestDb((db) async {
      final ctx = db.context();
      await authors().insert({'name': 'Ada', 'email': 'ada@example.com'}).run(ctx);
      expect(
        () => authors().insert({'name': 'Ada 2', 'email': 'ada@example.com'}).run(ctx),
        throwsA(isA<PostgresUniqueViolationException>()),
      );
    });
  });

  test('rolls back on error', () async {
    await withTestDb((db) async {
      await expectLater(
        db.transaction((tx) async {
          await authors().insert({'name': 'Ada', 'email': 'ada@example.com'}).run(tx);
          throw StateError('boom');
        }),
        throwsStateError,
      );
      expect(await authors().count(db.context()), 0);
    });
  });
}

3. Skip when no database is available

So the suite still runs on machines without Postgres:

import 'dart:io';

Future<bool> testDbAvailable() async {
  try {
    final s = await Socket.connect('localhost', 5455, timeout: const Duration(milliseconds: 250));
    s.destroy();
    return true;
  } on Object {
    return false;
  }
}

void main() async {
  final available = await testDbAvailable();
  test('...', () async { /* ... */ }, skip: available ? false : 'no test database');
}

4. Unit-test SQL without a database

For logic that only builds queries, assert on compile():

test('search filter', () {
  final c = filtered(const PostFilter(authorId: 3)).compile();
  expect(c.sql, contains('"posts"."author_id" = \$1'));
  expect(c.params, [3]);
});

To check how many statements code issues, implement DbContext with a fake that records calls:

import 'package:postgres/postgres.dart' show Result, ResultRow, ResultSchema;

class RecordingDbContext implements DbContext {
  final statements = <String>[];

  @override
  Future<Result> execute(String sql, {List<Object?> parameters = const []}) async {
    statements.add(sql);
    return Result(rows: const [], affectedRows: 0, schema: ResultSchema(const []));
  }

  @override
  Stream<ResultRow> stream(String sql, {List<Object?> parameters = const []}) async* {
    statements.add(sql);
  }
}

It returns empty results, so it suits checking what runs, not what comes back. Behaviour that depends on data belongs in integration tests.

Verify it works

dart test

All tests pass, and \dn in psql shows no leftover t_... schemas.