SQLite3 Sync UI

builder.dart

import 'dart:async';

import 'package:flutter/material.dart';
import 'package:sqlite3/common.dart';

import '../src/database/types.dart';

class SqlBuilder extends StatefulWidget {
  const SqlBuilder({
    super.key,
    required this.db,
    required this.sql,
    required this.builder,
    this.watch = true,
    this.rowId,
    this.parameters = const <SqlParameter>[],
  });

  final Database db;
  final Sql sql;
  final bool watch;
  final int? rowId;
  final List<SqlParameter> parameters;
  final Widget Function(BuildContext context, ResultSet result) builder;

  @override
  State<SqlBuilder> createState() => _SqlBuilderState();
}

class _SqlBuilderState extends State<SqlBuilder> {
  StreamSubscription? updates;

  @override
  void initState() {
    super.initState();
    if (widget.watch) {
      final tables = widget.sql.getSourceTables(widget.db, widget.parameters);
      updates = widget.db.updates.listen((event) {
        bool rebuild = tables.contains(event.tableName);
        if (rebuild && widget.rowId != null && event.rowId != widget.rowId) {
          rebuild = false;
        }
        if (rebuild && mounted) setState(() {});
      });
    }
  }

  @override
  void dispose() {
    updates?.cancel();
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    final result = widget.db.select(widget.sql, widget.parameters);
    return widget.builder(context, result);
  }
}

database.dart

import 'package:sqlite3/common.dart';

import 'types.dart';

extension type AppDatabase(Database db) {
  AppDatabase.memory() : db = Database.openInMemory();

  void dispose() => db.dispose();

  void init() {
    createTables();
    addTestData();
  }

  void createTables() {
    db.execute(
      Sql(
        'CREATE TABLE example_rows('
        ' id INTEGER PRIMARY KEY AUTOINCREMENT,'
        ' value TEXT NOT NULL'
        ')',
      ),
    );
  }

  void addTestData() {
    final stmt = Sql('INSERT INTO example_rows (value) VALUES (?)').prepare(
      db,
      persistent: true,
    );

    for (var i = 0; i < 10000; i++) {
      stmt.executeWith(db, [
        SqlParameter('${i + 1}'),
      ]);
    }

    stmt.dispose();
  }

  int getTestCount() {
    final rows = Sql('SELECT COUNT(id) as count FROM example_rows;').select(
      db,
    );
    return rows.first['count'] as int;
  }

  ExampleRow? getExampleRow(int offset) {
    final rows = getExampleRows(limit: 1, offset: offset);
    return rows.firstOrNull;
  }

  List<ExampleRow> getExampleRows({int? limit, int? offset}) {
    String sql = 'SELECT * FROM example_rows';
    final params = <SqlParameter>[];
    if (limit != null) {
      sql += ' LIMIT ?';
      params.add(SqlParameter(limit));
    }
    if (offset != null) {
      sql += ' OFFSET ?';
      params.add(SqlParameter(offset));
    }
    final rows = Sql(sql).select(db, params);
    return rows.map(ExampleRow.new).toList();
  }
}

extension type ExampleRow(Row row) {
  int get id => row['id'] as int;
  String get value => row['value'] as String;
}

example.dart

import 'package:flutter/material.dart';

import '../src/database/database.dart';

class Example extends StatefulWidget {
  const Example({super.key});

  @override
  State<Example> createState() => _ExampleState();
}

class _ExampleState extends State<Example> {
  final db = AppDatabase.memory();

  @override
  void initState() {
    db.init();
    super.initState();
  }

  @override
  void dispose() {
    db.dispose();
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    final count = db.getTestCount();
    return Scaffold(
      appBar: AppBar(
        title: Text('Count: $count'),
      ),
      body: ListView.builder(
        itemExtent: kToolbarHeight,
        itemCount: count,
        itemBuilder: (context, index) {
          final row = db.getExampleRow(index);
          if (row == null) return const SizedBox.shrink();
          return ListTile(
            title: Text('Value: ${row.value}'),
          );
        },
      ),
    );
  }
}

types.dart

import 'dart:convert';

import 'package:sqlite3/common.dart';
import 'package:sqlite3/sqlite3.dart' show sqlite3;

extension type Database(CommonDatabase db) {
  Database.openInMemory() : db = sqlite3.openInMemory();

  Database.open(
    String file, {
    String? vfs,
    OpenMode mode = OpenMode.readWriteCreate,
    bool uri = false,
    bool? mutex,
  }) : db = sqlite3.open(file, vfs: vfs, mode: mode, uri: uri, mutex: mutex);

  void dispose() => db.dispose();

  ResultSet select(
    Sql sql, [
    List<SqlParameter> parameters = const [],
  ]) {
    return db.select(sql.sql, parameters.toSql());
  }

  void execute(
    Sql sql, [
    List<SqlParameter> parameters = const [],
  ]) {
    return db.execute(sql.sql, parameters.toSql());
  }

  PreparedStatement prepare(
    Sql sql, {
    bool persistent = false,
    bool vtab = true,
    bool checkNoTail = false,
  }) {
    return PreparedStatement(db.prepare(
      sql.sql,
      persistent: persistent,
      vtab: vtab,
      checkNoTail: checkNoTail,
    ));
  }
}

extension type Sql(String raw) {
  String get sql {
    final raw = this.raw.trim();
    if (!raw.endsWith(';')) return '$raw;';
    return raw;
  }

  ResultSet select(
    Database ctx, [
    List<SqlParameter> parameters = const [],
  ]) {
    return ctx.select(this, parameters);
  }

  void execute(
    Database ctx, [
    List<SqlParameter> parameters = const [],
  ]) {
    return ctx.execute(this, parameters);
  }

  PreparedStatement prepare(
    Database ctx, {
    bool persistent = false,
    bool vtab = true,
    bool checkNoTail = false,
  }) {
    return ctx.prepare(
      this,
      persistent: persistent,
      vtab: vtab,
      checkNoTail: checkNoTail,
    );
  }
}

extension type SqlParameter(Object? raw) {
  /// @source https://github.com/powersync-ja/sqlite_async.dart/blob/main/packages/sqlite_async/lib/src/sqlite_queries.dart
  Object? get value {
    if (raw == null ||
        raw is int ||
        raw is String ||
        raw is bool ||
        raw is num ||
        raw is List<int>) {
      return raw;
    } else {
      return jsonEncode(raw);
    }
  }
}

extension on List<SqlParameter> {
  List<Object?> toSql() => map((e) => e.value).toList();
}

extension type PreparedStatement(CommonPreparedStatement raw) {
  void dispose() => raw.dispose();

  void executeWith(
    Database ctx, [
    List<SqlParameter> parameters = const [],
  ]) {
    return raw.executeWith(StatementParameters(parameters.toSql()));
  }
}