readOnlySelect function

Future<({List<Map<String, Object?>> rows, bool truncated})> readOnlySelect(DatabaseManager db, String sql, { List<Object?> bindings = const [], int limit = 100, })

Runs one SELECT/WITH/EXPLAIN statement that cannot write: inside a transaction that is always rolled back, read-only where the database enforces it (SQLite query_only, PostgreSQL READ ONLY; MySQL relies on the statement check and the rollback). At most limit rows.

Implementation

Future<({List<Map<String, Object?>> rows, bool truncated})> readOnlySelect(
  DatabaseManager db,
  String sql, {
  List<Object?> bindings = const [],
  int limit = 100,
}) async {
  final statement = sql.trim().replaceFirst(RegExp(r';\s*$'), '');
  if (statement.contains(';')) {
    throw ArgumentError('Pass a single statement.');
  }
  final verb = RegExp(
    r'^(select|with|explain)\b',
    caseSensitive: false,
  ).firstMatch(statement)?.group(1)?.toLowerCase();
  if (verb == null) {
    throw ArgumentError('Only SELECT, WITH and EXPLAIN statements run.');
  }
  // Ask for one row more than the limit to know whether rows were left out.
  final query = verb == 'explain'
      ? statement
      : 'SELECT * FROM ($statement) daravel_mcp_query LIMIT ${limit + 1}';
  final dialect = db.dialectOf();
  late List<Map<String, Object?>> rows;
  try {
    await db.transaction(() async {
      switch (dialect) {
        case SqlDialect.postgres:
          await db.statement('SET TRANSACTION READ ONLY');
        case SqlDialect.sqlite:
          await db.statement('PRAGMA query_only = ON');
        case SqlDialect.mysql:
          break;
      }
      try {
        rows = await db.select(query, bindings);
      } finally {
        if (dialect == SqlDialect.sqlite) {
          await db.statement('PRAGMA query_only = OFF');
        }
      }
      throw const _Rollback();
    });
  } on _Rollback {
    // Always rolled back.
  }
  return (
    rows: rows.length > limit ? rows.sublist(0, limit) : rows,
    truncated: rows.length > limit,
  );
}