Relational databases

Relational databases are a type of a database where information is described using tables and their relationships. Each item is stored as a row in a table, and the rows in the table can be referred to using keys. Relational databases are queried using the Structured Query Language (SQL).

Learning to work with relational databases is out of the scope for this course. As it is possible that the course is taken by participants who have not taken an introductory databases course, we only discuss the use of relational databases, but do not require completing exercises on the topic.

SQLite with sqlflite

If the database that is being used in the mobile application is only needed locally, a commonly used solution is SQLite. SQLite can be added to Flutter applications using the sqflite plugin. To use it, it needs to be added to the pubspec.yaml. The following example outlines adding the sqflite to the project, expecting the version 2.3.0 or newer.

# ...
  sqflite: ^2.3.0
# ...

We continue on the theme of storing tasks as done in the previous part. We use a database table for storing the tasks. The database table is created as follows. Note that INTEGER is used instead of BOOLEAN as SQLite does not support booleans -- integers are used to distinguish booleans.

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  priority INTEGER DEFAULT 0;
);

Note! The following examples only work for Android, iOS and macOS, but not e.g. in a browser.

When working with a database, it is meaningful to create a separate class that wraps the database functionality. In our case, we could create a class called TaskDatabase, which would be as follows.

import 'package:sqflite/sqflite.dart';

// ...


class TaskDatabase {
  static Database? _database;

  Future<Database> _initDatabase() async {
    return openDatabase('task_database.db', version: 1,
        onCreate: (Database db, int version) async {
      await db.execute('''
          CREATE TABLE tasks (
            id SERIAL PRIMARY KEY,
            name TEXT NOT NULL,
            priority INTEGER DEFAULT 0
          )
        ''');
    });
  }

  Future<Database> get database async {
    _database ??= await _initDatabase();
    return _database!;
  }

  Future<List<Task>> getTasks() async {
    final db = await database;
    final taskRows = await db.rawQuery('SELECT * FROM tasks');

    return List<Task>.from(taskRows.map((row) => Task(
        name: row['name']!.toString(),
        priority: int.parse(row['priority']!.toString()) == 1)));
  }

  Future<void> addTask(Task task) async {
    final db = await database;
    await db.rawQuery('''
      INSERT INTO tasks 
        (name, priority) 
        VALUES 
        (?, ?)
    ''', [task.name, task.priority ? 1 : 0]);
  }
}

The key parts in the implementation are the Database related functions openDatabase, which is used to create and return an instance of a Database. The methods execute, rawQuery, and rawInsert are used to perform queries on the database: the method execute is used when the query does not return a value, the method rawQuery is used for retrieving information, and the method rawInsert is used for adding information to the database.

In addition to the database related functionality, the class also utilizes a static reference to the database and a getter method for gaining access to the database. For additional information on the functionality, see the Dart language tour on getters and setters.

Having created the TaskDatabase above, we can take our earlier functionality that we worked on when working with shared preferences, and replace SharedPreferences with TaskDatabase. In the present case, as the creation of the TaskDatabase is not asynchronous, we can work only with the TaskNotifier and the taskProvider. These would be modified as follows.

// ...
class TaskNotifier extends StateNotifier<List<Task>> {
  final TaskDatabase taskDatabase = TaskDatabase();
  TaskNotifier() : super([]);

  _initialize() async {
    state = await taskDatabase.getTasks();
  }

  addTask(Task task) async {
    state = [task, ...state];
    await taskDatabase.addTask(task);
  }
}

final taskProvider = StateNotifierProvider<TaskNotifier, List<Task>>((ref) {
  final tn = TaskNotifier();
  tn._initialize();
  return tn;
});
// ...

In addition, starting the application would no longer require overriding shared preferences. That is, the main.dart would be as follows.

// ...
main() async {
  runApp(ProviderScope(
    child: TaskApp(),
  ));
}
// ...

Now, the application would use a local SQLite instead of shared preferences for storing the information.

PostgreSQL with postgres

While SQLite is heavily used in local installations, there exists a wide variety of relational databases. Let's briefly peek into using a remote PostgreSQL database. In the following example, we presume that the relational database is available online.

To use PostgreSQL in a Flutter project, the postgres plugin would be added to the pubspec.yaml file. Below, we would add the version 2.6.2 or newer of the postgres plugin.

# ...
  postgres: ^2.6.2
# ...

The documentation for the postgres plugin is available at https://pub.dev/documentation/postgres/latest/postgres/postgres-library.html. Similar to using sqflite, the key functionality is related to forming a database connection and to executing the database queries.

A connection is created using the class PostgresConnection, which also represents the connection. The constructor of PostgresConnection takes in the database connection details, and the method open is used for opening the connection. The method query executes an SQL query and returns an instance of PostgreSQLResult, which can be worked on as if it was a list of maps.

Below, we have modified the TaskDatabase class to use the postgres library. Below, we assume that the database schema still represents the priority as an integer, although this would not be necessary for PostgreSQL.

import 'package:postgres/postgres.dart';

// ...

class TaskDatabase {
  static PostgreSQLConnection? _database;

  Future<PostgreSQLConnection> _initDatabase() async {
    _database = await PostgreSQLConnection(/* connection details */).open();
  }

  Future<PostgreSQLConnection> get database async {
    _database ??= await _initDatabase();
    return _database!;
  }

  Future<List<Task>> getTasks() async {
    final db = await database;
    final taskRows = await db.query('SELECT * FROM tasks');

    return List<Task>.from(taskRows.map((row) => Task(
        name: row['name']!.toString(),
        priority: int.parse(row['priority']!.toString()) == 1)));
  }

  Future<void> addTask(Task task) async {
    final db = await database;
    await db.rawQuery('''
      INSERT INTO tasks 
        (name, priority) 
        VALUES 
        (@name, @priority)
    ''', substitutionValues: {
      "name": task.name,
      "priority": task.priority ? 1 : 0
    });
  }
}

// ...