Add .timer ON option

[spauldhaliwal]

It would be nice if there was a means of enabling sqlite's timer option so that we can troubleshoot or benchmark our queries and indexes. This could be used in conjunction with the logStatements flag.

[simolus3]

The timer function is not a feature of the sqlite3 library, it's implemented in the CLI shell only.

You can do something similar in Dart by wrapping a QueryExecutor:

import 'package:drift/drift.dart';
import 'package:meta/meta.dart';

class TimedQueryExecutor extends _TimedQueryExecutor {
  @override
  final QueryExecutor inner;

  TimedQueryExecutor(this.inner);
}

abstract class _TimedQueryExecutor extends QueryExecutor {
  @visibleForOverriding
  QueryExecutor get inner;

  Future<T> _runAndLog<T>(Future<T> Function() inner, String description) {
    print('Starting with $description');
    final timer = Stopwatch()..start();

    return Future.sync(inner).whenComplete(() {
      timer.stop();
      print('took ${timer.elapsed}');
    });
  }

  @override
  TransactionExecutor beginTransaction() {
    return _TimedTransactions(inner.beginTransaction());
  }

  @override
  SqlDialect get dialect => inner.dialect;

  @override
  Future<bool> ensureOpen(QueryExecutorUser user) {
    return inner.ensureOpen(user);
  }

  @override
  Future<void> runBatched(BatchedStatements statements) {
    return _runAndLog(
        () => inner.runBatched(statements), 'Batched $statements');
  }

  @override
  Future<void> runCustom(String statement, [List<Object?>? args]) {
    return _runAndLog(
        () => inner.runCustom(statement, args), 'runCustom($args)');
  }

  @override
  Future<int> runDelete(String statement, List<Object?> args) {
    return _runAndLog(
        () => inner.runDelete(statement, args), 'runDelete($args)');
  }

  @override
  Future<int> runInsert(String statement, List<Object?> args) {
    return _runAndLog(
        () => inner.runInsert(statement, args), 'runInsert($args)');
  }

  @override
  Future<List<Map<String, Object?>>> runSelect(
      String statement, List<Object?> args) {
    return _runAndLog(
        () => inner.runSelect(statement, args), 'runSelect($args)');
  }

  @override
  Future<int> runUpdate(String statement, List<Object?> args) {
    return _runAndLog(
        () => inner.runUpdate(statement, args), 'runUpdate($args)');
  }
}

class _TimedTransactions extends _TimedQueryExecutor
    implements TransactionExecutor {
  @override
  final TransactionExecutor inner;

  _TimedTransactions(this.inner);

  @override
  Future<void> rollback() {
    return _runAndLog(() => inner.rollback(), 'rollback');
  }

  @override
  Future<void> send() {
    return _runAndLog(() => inner.send(), 'commit');
  }

  @override
  bool get supportsNestedTransactions => inner.supportsNestedTransactions;
}

Then, you can wrap your NativeDatabase() in a TimedQueryExecutor to log the time for individual queries. Unfortunately there's a bit of boilerplate involved in this wrapper, maybe drift should have an API to make wrapping existing executors to customize their behavior easier. But I don't think the timer log feature should be built into drift, I think the use-cases might be too narrow.

[spauldhaliwal]

Ah okay, my mistake regarding the timer function. For my usecase the above works for me, though I agree it would be a nice to have in terms of the drift api. Thanks!