Creating a Data Source

To create a Data Source for storing our notes in a local SQL database, we need to create the Data Source itself and a few Contract classes. The Contract classes will define the structure, helper methods, and constants for our SQLite database and its tables.

1. Creating the Contracts

In the note_app/modules/infrastructure/lib/data_sources/sqlbrite/contracts/ directory, create the following contract files:

1.1. Create a contract for our note database

Create a contract class named notes_database_contract.dart to define the name and version of our database:

/// {@template NotesDatabaseContract}
/// A contract class for the notes SQL database.
/// {@endtemplate}
class NotesDatabaseContract {
  /// {@macro NotesDatabaseContract}
  NotesDatabaseContract._();

  /// The name of the database.
  static const databaseName = 'notes.db';

  /// The current version of the database.
  static const databaseVersion = 1;
}

1.2. Create a contract for our note table

Create a contract class named note_entry_contract.dart for our notes table to define its structure and available query commands:

/// {@template NoteEntryContract}
/// A contract class for the note entries table of the notes database.
/// {@endtemplate}
class NoteEntryContract {
  /// {@macro NoteEntryContract}
  NoteEntryContract._();

  /// The name of the database table.
  static const String tableName = 'noteEntries';

  /// The name of the ID column.
  static const String idField = 'id';

  /// The name of the title column.
  static const String titleField = 'title';

  /// The name of the content column.
  static const String contentField = 'content';

  /// The name of the last updated time column.
  static const String updateTimestampField = 'updateTimestamp';

  /// A SQL query for creating the note entries table.
  static const String createTableQuery = '''
    CREATE TABLE $tableName (
      $idField TEXT PRIMARY KEY,
      $titleField TEXT NOT NULL,
      $contentField TEXT NOT NULL,
      $updateTimestampField INTEGER NOT NULL
    )
  ''';

  /// A SQL query for inserting one or more new note entries into the database.
  static String insertQuery([int count = 1]) {
    final buffer = StringBuffer('''
      INSERT INTO $tableName (
        $idField,
        $titleField,
        $contentField,
        $updateTimestampField
      ) VALUES (?, ?, ?, ?) 
    ''');

    for (var i = 1; i < count; i++) {
      buffer.write('''
        , (?, ?, ?, ?)
      ''');
    }

    return buffer.toString();
  }

  /// A where clause for selecting a note entry by its ID.
  static String whereId = '$idField = ?';

  /// A where clause for ordering the note entries by their update timestamp in
  /// descending order.
  static String orderByUpdateTimestampDesc = '$updateTimestampField DESC';
}

2. Create the Data Source

Finally, create the Data Source to allow for CRUD (create, read, update, delete) operations on our local database.

Under the note_app/modules/infrastructure/lib/data_sources/sqlbrite directory, create a file called sqlbrite_data_source.dart and copy the following code:

import 'dart:async';
import 'dart:math';

import 'package:flutter_lorem/flutter_lorem.dart';
import 'package:infrastructure/data_sources/sqlbrite/contracts/note_entry_contract.dart';
import 'package:infrastructure/data_sources/sqlbrite/contracts/notes_database_contract.dart';
import 'package:infrastructure/data_sources/sqlbrite/note_entry_model.dart';
import 'package:sqlbrite/sqlbrite.dart';

/// {@template SqlbriteDataSource}
/// A data source that uses [BriteDatabase] to interact with the local SQLite
/// database.
/// {@endtemplate}
class SqlbriteDataSource {
  /// A reference to the [BriteDatabase] instance used to interact with the
  /// underlying database
  ///
  /// [initialize] must be called before accessing this property.
  late final BriteDatabase _database;

  /// Initializes the database.
  ///
  /// Must be called at the start of the app.
  Future<void> initialize() async {
    // Open the database
    final database = await openDatabase(
      NotesDatabaseContract.databaseName,
      version: NotesDatabaseContract.databaseVersion,
      onCreate: (db, version) async {
        // Create the note entries table when the database has been created
        await db.execute(NoteEntryContract.createTableQuery);
        await _generateData(db);
      },
    );

    _database = BriteDatabase(database, logger: null);
  }

  /// Saves the new note entry to the database.
  Future<void> createNoteEntry({
    required String title,
    required String content,
  }) {
    final noteEntry = NoteEntryModel(
      id: _generateId(),
      title: title,
      content: content,
      updateTimestamp: DateTime.now().millisecondsSinceEpoch,
    );

    return _database.insert(NoteEntryContract.tableName, noteEntry.toJson());
  }

  /// Produces a random ID for the note entry.
  String _generateId() {
    final rnd = Random.secure();
    return List<int>.generate(20, (index) => rnd.nextInt(10)).join();
  }

  /// Updates the note entry with the given [id].
  Future<void> updateNoteEntry({
    required String id,
    required String? title,
    required String? content,
  }) {
    return _database.update(
      NoteEntryContract.tableName,
      {
        if (title != null) NoteEntryContract.titleField: title,
        if (content != null) NoteEntryContract.contentField: content,
        NoteEntryContract.updateTimestampField:
            DateTime.now().millisecondsSinceEpoch,
      },
      where: NoteEntryContract.whereId,
      whereArgs: [id],
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// Deletes the note entry with the given [id].
  Future<void> deleteNoteEntry({required String id}) {
    return _database.delete(
      NoteEntryContract.tableName,
      where: NoteEntryContract.whereId,
      whereArgs: [id],
    );
  }

  /// Fetches a list of note entries from the database sorted by the update
  /// timestamp in descending order.
  ///
  /// An [offset] can be provided to skip a number of entries ideal for
  /// implementing pagination.
  Future<List<NoteEntryModel>> fetchNoteEntries({
    required int limit,
    int? offset,
  }) async {
    final result = await _database.query(
      NoteEntryContract.tableName,
      orderBy: NoteEntryContract.orderByUpdateTimestampDesc,
      limit: limit,
      offset: offset,
    );

    return result.map<NoteEntryModel>(NoteEntryModel.fromJson).toList();
  }

  /// Streams a list of note entries sorted by the update timestamp in
  /// descending order with the number of entries specified by the given
  /// [limit].
  ///
  /// The stream will emit a new list of note entries whenever a change is made
  /// to the database.
  Stream<List<NoteEntryModel>> watchRecentNoteEntries({required int limit}) {
    return _database
        .createQuery(
          NoteEntryContract.tableName,
          orderBy: NoteEntryContract.orderByUpdateTimestampDesc,
          limit: limit,
          offset: 0,
        )
        .mapToList((row) => NoteEntryModel.fromJson(row));
  }

  /// A method for generating initial test data for the database.
  Future<int> _generateData(Database db) async {
    final rnd = Random();
    final currentTime = DateTime.now().millisecondsSinceEpoch;

    final mockNoteEntries = <NoteEntryModel>[
      for (var i = 0; i < 100; i++)
        NoteEntryModel(
          id: _generateId(),
          title: lorem(
            paragraphs: 1,
            words: rnd.nextInt(2) + 1,
          ).replaceAll('.', ''),
          content: lorem(
            paragraphs: 1,
            words: rnd.nextInt(10) + 1,
          ),
          updateTimestamp: currentTime - (i * 3600),
        ),
    ];

    return db.rawInsert(
      NoteEntryContract.insertQuery(mockNoteEntries.length),
      mockNoteEntries
          .expand((e) => [
                e.id,
                e.title,
                e.content,
                e.updateTimestamp,
              ])
          .toList(),
    );
  }
}