Drift Local Database For Flutter (Part 2- One-to-One Relationship)

Introduction

Continuing the series on integrating the Drift database into Flutter projects, Part 2 focuses on implementing a One-to-One relationship. If you haven’t read Part 1, it’s advisable to do so, for a better understanding of the example used in this part.

What is One to One Relationship

This kind of relationship exists when a record from one entity is connected(linked) with EXACTLY ONE record in another entity.

Implementation Of The Example

In this scenario, a One-to-One relationship exists between the User and Playlist entities. Let’s explore how to achieve this using Drift.

Step 1: Adding User ID to Playlist Table
The first step is to add the ID either of the user to the playlist or the playlist to the user. The user ID will be stored in the playlist table in our example. So, now your Playlist table should look like the example below.

When using the .drift file:

CREATE TABLE Playlist (
    id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    number_of_songs INT NOT NULL,
    userID INT NOT NULL REFERENCES User(id)
) AS PlaylistTable;

When using classes:

@UseRowClass(PlaylistEntity)
class Playlist extends Table {
  IntColumn get id => integer()();
  TextColumn get name => text()();
  IntColumn get numberOfSongs => integer()();
  IntColumn get userId => integer()();

  ///Specifying which from the field above is the primary key
  @override
  Set<Column> get primaryKey => {id};
}

After making the changes re-execute dart run build_runner build to apply the new changes to the auto-generated file.

Step 2: Creating Entity Classes
The second step is to create two new classes, UserEntity and PlaylistEntity. The reason for doing this is to simplify data access later on.

This part will be the same for both ways. (.drift file and classes)

class PlaylistEntity {
  int? id;
  String? name;
  int? numberOfSongs;
  int? userId;

  PlaylistEntity(
      {this.id, this.name, this.numberOfSongs, this.userId});

}

import 'package:drift_local_database_example/data/local_database/entities/playlist_entity.dart';

class UserEntity {
  int? id;
  String? username;
  String? musicStyle;
  String? favoriteSongName;
  PlaylistEntity? playlistEntity;

  UserEntity(
      {this.id,
      this.username,
      this.musicStyle,
      this.favoriteSongName,
      this.playListEntity});
}

If you check carefully the UserEntity class you can see there is an extra field called playlistEntity. This will help you have easy access to playlist data without the need to query every time.

Question: Why do you need to create those extra classes and you don’t just add extra fields into UserTable?
The reason is that you cannot store custom objects in the local database using drift.

Step 3: Methods for Saving Data
For the data-saving process, three methods are needed for both .drift file and classes:
1. toCompanion: Used to convert entities into actual tables.
2. saveSingleEntity: Used to save a single entity into the database.
3. saveListOfEntity: Used when you have a list with your entity data.

This part will be the same for both ways. (.drift file and classes)

Playlist Entity:

  PlaylistCompanion toCompanion() {
    return PlaylistCompanion(
      id: Value(id ?? -1),
      name: Value(name ?? ''),
      numberOfSongs: Value(numberOfSongs ?? 0),
      userID: Value(userId ?? -1),
    );
  }

  static Future<void> saveSinglePlaylistEntity(
      PlaylistEntity playlistEntity) async {
    AppDatabase db = AppDatabase.instance();
    await db
        .into(db.playlist)
        .insertOnConflictUpdate(playlistEntity.toCompanion());
  }

  static Future<void> saveListOfPlaylist(
      List<PlaylistEntity> playlistEntityList) async {
    await Future.forEach(playlistEntityList, (playlistEntity) async {
      await saveSinglePlaylistEntity(playlistEntity);
    });
  }

User Entity:

  UserCompanion toCompanion() {
    return UserCompanion(
      id: Value(id ?? -1),
      username: Value(username ?? ''),
      musicStyle: Value(musicStyle ?? ''),
      favoriteSongName: Value(favoriteSongName ?? ''),
    );
  }

  static Future<void> saveSingleUserEntity(UserEntity userEntity) async {
    AppDatabase db = AppDatabase.instance();
    await db.into(db.user).insertOnConflictUpdate(userEntity.toCompanion());
    if (userEntity.playlistEntity != null) {
      await PlaylistEntity.saveSinglePlaylistEntity(userEntity.playlistEntity!);
    }
  }

  static Future<void> saveListOfUserEntity(
      List<UserEntity> userEntityList) async {
    await Future.forEach(userEntityList, (userEntity) async {
      await saveSingleUserEntity(userEntity);
    });
  }

Step 4: Methods for retrieving Stored Data
The final step involves understanding the process of retrieving stored data.

For .drift file: Four methods are required, including:
1. convertTableToEntity: This method converts a table to an entity. When you retrieve data from local database the returning type is the table instead of entity which is the one used to show data on the screen.
2. queryAllPlaylists: Used to get a list with all stored playlists.
3. queryPlaylistById: Used to get only 1 playlist in case the given id matches an id from the stored playlists otherwise returns null.
4. queryPlaylistByUserId: Used to get the linked playlist when you retrieve user data as you will see in the examples.

For classes: Three methods are required, including:
1. queryAllPlaylists: Used to get a list with all stored playlist
2. queryPlaylistById: Used to get only 1 playlist in case the given id is matching an id from the stored playlists otherwise returns null.
3. queryPlaylistByUserId: Used to get the linked playlist when you retrieve user data as you will see in the examples.

This approach reduces the number of methods when using classes due to the use of the @UseRowClass annotation, which seamlessly handles entity recognition.

Using the .drift file:

  static Future<PlaylistEntity?> convertTableToEntity(PlaylistTable? playlistTable) async {
    if(playlistTable != null) {
      return PlaylistEntity(
        id: playlistTable.id,
        name: playlistTable.name,
        numberOfSongs: playlistTable.numberOfSongs,
        userId: playlistTable.userID
      );
    } else {
      return null;
    }
  }

  static Future<List<PlaylistEntity>> queryAllPlaylists() async {
    AppDatabase db = AppDatabase.instance();
    List<PlaylistEntity> playlistEntityList = [];
    List<PlaylistTable> playlistTableList = await db.select(db.playlist).get();
    await Future.forEach(playlistTableList, (playlistTable) async {
      PlaylistEntity? tempPlaylistEntity = await convertTableToEntity(playlistTable);
      if(tempPlaylistEntity != null) {
        playlistEntityList.add(tempPlaylistEntity);
      }
    });
    return playlistEntityList;
  }

  static Future<PlaylistEntity?> queryPlaylistById(int playlistId) async {
    AppDatabase db = AppDatabase.instance();
    PlaylistTable? playlistTable =
    await (db.select(db.playlist)..where((tbl) => tbl.id.equals(playlistId))).getSingleOrNull();
    return convertTableToEntity(playlistTable);
  }

  static Future<PlaylistEntity?> queryPlaylistByUserId(int userId) async {
    AppDatabase db = AppDatabase.instance();
    PlaylistTable? playlistTable =
    await (db.select(db.playlist)..where((tbl) => tbl.userID.equals(userId))).getSingleOrNull();
    return convertTableToEntity(playlistTable);
  }

Using the class:

  static Future<List<PlaylistEntity>> queryAllPlaylists() async {
    AppDatabase db = AppDatabase.instance();
    List<PlaylistEntity> playlistEntityList =
        await db.select(db.playlistTable).get();
    return playlistEntityList;
  }

  static Future<PlaylistEntity?> queryPlaylistById(int playlistId) async {
    AppDatabase db = AppDatabase.instance();
    PlaylistEntity? playlistEntity = await (db.select(db.playlist)
          ..where((tbl) => tbl.id.equals(playlistId)))
        .getSingleOrNull();
    return playlistEntity;
  }

  static Future<PlaylistEntity?> queryPlaylistByUserId(int userId) async {
    AppDatabase db = AppDatabase.instance();
    PlaylistEntity? playlistEntity = await (db.select(db.playlist)
          ..where((tbl) => tbl.userId.equals(userId)))
        .getSingleOrNull();
    return playlistEntity;
  }

On the other hand, UsersEntity:

For .drift file: Three methods are required, including:
1. convertTableToEntity: This method converts a table to an entity. When you retrieve data from local database the returning type is the table instead of entity which is the one used to show data on the screen.
2. queryAllUsers: Used to get a list with all stored users.
3. queryUserById: Used to get only 1 user in case the given id is matching an id from the stored users otherwise returns null.

For classes: Two methods are required, including:
1. queryAllUsers: Used to get a list with all stored users.
2. queryUserById: Used to get only 1 user in case the given id is matching an id from the stored users otherwise returns null.

User Entity using the .drift file

  static Future<UserEntity?> convertTableToEntity(UserTable? userTable) async {
    if (userTable != null) {
      PlaylistEntity? playlistEntity =
          await PlaylistEntity.queryPlaylistByUserId(userTable.id);
      return UserEntity(
          id: userTable.id,
          username: userTable.username,
          musicStyle: userTable.musicStyle,
          favoriteSongName: userTable.favoriteSongName,
          playlistEntity: playlistEntity);
    } else {
      return null;
    }
  }

  static Future<List<UserEntity>> queryAllUsers() async {
    AppDatabase db = AppDatabase.instance();
    List<UserEntity> userEntityList = [];
    List<UserTable> userTableList = await db.select(db.user).get();
    await Future.forEach(userTableList, (userTable) async {
      UserEntity? tempUserEntity = await convertTableToEntity(userTable);
      if (tempUserEntity != null) {
        userEntityList.add(tempUserEntity);
      }
    });
    return userEntityList;
  }

  static Future<UserEntity?> queryUserById(int userId) async {
    AppDatabase db = AppDatabase.instance();
    UserTable? userTable = await (db.select(db.user)
          ..where((tbl) => tbl.id.equals(userId)))
        .getSingleOrNull();
    return convertTableToEntity(userTable);
  }

User Entity using the class:

  static Future<List<UserEntity>> queryAllUsers() async {
    AppDatabase db = AppDatabase.instance();
    List<UserEntity> userEntityList = await db.select(db.user).get();
    await Future.forEach(userEntityList, (userEntity) async {
      userEntity.playlistEntity =
          await queryPlaylistEntityByUserId(userEntity.id ?? -1);
    });
    return userEntityList;
  }

  static Future<UserEntity?> queryUserById(int userId) async {
    AppDatabase db = AppDatabase.instance();
    UserEntity? userEntity = await (db.select(db.user)
          ..where((tbl) => tbl.id.equals(userId)))
        .getSingleOrNull();
    if (userEntity != null) {
      userEntity.playlistEntity =
          await queryPlaylistEntityByUserId(userEntity.id ?? -1);
    }
    return userEntity;
  }

///This method is used to query a list of SongEntity which are matches the 
/// artist ID. Is not mandatory. It could be included within the two query methods.
/// But is a best practice to create extra methods for common functionalities
static Future<PlaylistEntity?> queryPlaylistEntityByUserId(
          int userId) async =>
      await PlaylistEntity.queryPlaylistByUserId(userId);

Conclusion

Part 2 concludes the Flutter Local Database Series, covering up to now the setup of Dirft as a local database in a Flutter project and the implementation of One-to-One relationship.

If you have found this article helpful feel free to share and check out the rest parts of the series.

Suggestions and questions are welcome so, don’t hesitate to leave your comment. Of course, claps are highly appreciated as they fuel my motivation to produce more articles. Thank you for your time.

Learn about:
- One to Many
- Many to Many

GitHub Projects:
1. Example with .drift
2. Example with classes

References:
1. Drift Package on https://pub.dev/packages/drift
2. Drift Documentation https://drift.simonbinder.eu/docs/