Comment on page

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}
/// 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}
/// 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 (
$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 (
) VALUES (?, ?, ?, ?)
for (var i = 1; i < count; i++) {
, (?, ?, ?, ?)
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(
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,
return _database.insert(NoteEntryContract.tableName, noteEntry.toJson());
/// Produces a random ID for the note entry.
String _generateId() {
final rnd =;
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(
if (title != null) NoteEntryContract.titleField: title,
if (content != null) NoteEntryContract.contentField: content,
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(
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(
orderBy: NoteEntryContract.orderByUpdateTimestampDesc,
limit: limit,
offset: offset,
/// 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
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 =;
final mockNoteEntries = <NoteEntryModel>[
for (var i = 0; i < 100; i++)
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(
.expand((e) => [,