selectable_sqlite_async.dart

import 'package:sqlite3/sqlite3.dart';
import 'package:sqlite_async/sqlite_async.dart';

typedef RowParser<T> = T Function(Row row);

extension SqliteDatabaseUtils on SqliteDatabase {
  Selectable<T> select<T>(
    String sql, {
    List<Object?> parameters = const [],
    Set<String> tables = const {},
    required RowParser<T> parser,
  }) {
    return Selectable(
      database: this,
      sql: sql,
      parameters: parameters,
      tables: tables,
      fromJson: parser,
    );
  }
}

class Selectable<T> {
  final SqliteDatabase database;
  final String sql;
  final List<Object?> parameters;
  final Set<String> tables;
  final T Function(Row row) fromJson;

  const Selectable({
    required this.database,
    required this.sql,
    required this.parameters,
    required this.tables,
    required this.fromJson,
  });

  Future<T> getSingle() async {
    final result = await database.get(sql, parameters);
    return fromJson(result);
  }

  Future<T?> getSingleOrNull() async {
    final result = await database.getOptional(sql, parameters);
    if (result == null) return null;
    return fromJson(result);
  }

  Future<List<T>> get() async {
    final results = await database.getAll(sql, parameters);
    return results.map((r) => fromJson(r)).toList();
  }

  Stream<T> watchSingle() async* {
    final results = database.watch(
      sql,
      parameters: parameters,
      triggerOnTables: tables,
    );
    await for (final result in results) {
      final row = result.single;
      yield fromJson(row);
    }
  }

  Stream<T?> watchSingleOrNull() async* {
    final results = database.watch(
      sql,
      parameters: parameters,
      triggerOnTables: tables,
    );
    await for (final result in results) {
      final row = result.singleOrNull;
      if (row == null) {
        yield null;
      } else {
        yield fromJson(row);
      }
    }
  }

  Stream<List<T>> watch() async* {
    final results = database.watch(
      sql,
      parameters: parameters,
      triggerOnTables: tables,
    );
    await for (final result in results) {
      final items = <T>[];
      while (result.iterator.moveNext()) {
        final row = result.iterator.current;
        items.add(fromJson(row));
      }
      yield items;
    }
  }
}