Drift Local Database For Flutter (Part 4-Many-to-Many Relationship)

Introduction

The last part of the series on integrating the Drift database into Flutter projects focuses on the implementation of Many-to-Many relationships. If you haven’t read the previous parts, it’s advisable to do so, for a better understanding of the example used in this part.

What is Many to Many Relationship

This kind of relationship exists when multiple records from one table are connected(linked) with multiple records in another table.

Implementation Of The Example

In this scenario, a Many-to-Many relationship exists between the Song and the Playlist entities. So, let’s see how you can accomplish this using drift.

Step 1: Creating an Extra Table
To handle this relationship, the creation of a new table is required. So, create an extra table named PlaylistWithSongTable. This table will hold the IDs of the tables you are trying to connect.

When using the .drift file:

CREATE TABLE playlistWithSong(
    id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
    song_id NOT NULL REFERENCES Song(id),
    playlist_id NOT NULL REFERENCES Playlist(id),
    CONSTRAINT songIdWithPlaylistID UNIQUE (song_id, playlist_id)
) AS PlaylistWithSongTable;

When using the class:

@UseRowClass(PlaylistWithSongEntity)
class PlaylistWithSongTable extends Table {
  IntColumn get id => integer()();
  IntColumn get songId => integer()();
  IntColumn get playlistId => 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 the corresponding entity class for the newly created table. This entity will only hold the two IDs.

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

class PlaylistWithSongEntity {
  int? songId;
  int? playlistId;

  PlaylistWithSongEntity({this.songId, this.playlistId});
}

Then you have to update the PlaylistEntity to include a list of songs. By doing this the PlaylistEntity now should look like the example below.

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

class PlaylistEntity {
  int? id;
  String? name;
  int? numberOfSongs;
  int? userId;
  List<SongEntity>? songEntityList;

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

Step 3: Storing Data
The third step is to store the data in the local database. This requires changes to the PlaylistEntity and the newly created PlaylistWithSongEntity.

Starting with the new entity you have to create 3 methods.
1. toCompanion: Used to convert entities into actual tables.
2. saveSingleEntity: Used to save a single entity into the database.
3. cleanRelationshipBasedOnPlaylistId: Used to delete any record that matches the playlistId.

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

PlaylistWithSongCompanion toCompanion() {
    return PlaylistWithSongCompanion(
      songId: Value(songId ?? -1),
      playlistId: Value(playlistId ?? -1),
    );
  }

  static Future<void> saveSinglePlaylistWithSongEntity(PlaylistWithSongEntity playlistWithSongEntity) async {
    AppDatabase db = AppDatabase.instance();
    await db.into(db.playlistWithSong).insertOnConflictUpdate(playlistWithSongEntity.toCompanion());
  }

  static Future<void> cleanRelationshipBasedOnPlaylistId(int playlistId) async {
    AppDatabase db = AppDatabase.instance();
    await (db.delete(db.playlistWithSong)
          ..where((tbl) => tbl.playlistId.equals(playlistId)))
        .go();
  }

The next thing you need to do is to modify the PlaylistEntity to save the song data if exists, delete any existing record from the PlaylistWithSongEntity table which matches the playlistId and save the IDs of each playlist and each song into the new table to establish the Many to Many relationship.
For this, you need to edit the saving method and create one more method to handle the deletion and the saving functionality of the song and the PlaylistWithSong. The name of the new method is up to you but you can reference the code below.

When using the .drift file:

  static Future<void> saveSinglePlaylistEntity(
      PlaylistEntity playlistEntity) async {
    AppDatabase db = AppDatabase.instance();
    await db
        .into(db.playlist)
        .insertOnConflictUpdate(playlistEntity.toCompanion());
    await PlaylistWithSongEntity.cleanRelationshipBasedOnPlaylistId(playlistEntity.id ?? -1);
    if (playlistEntity.songEntityList != null) {
      await _saveSongAndRelationshipData(
          playlistEntity.songEntityList ?? [], playlistEntity.id ?? -1);
    }
  }

  static Future<void> _saveSongAndRelationshipData(
      List<SongEntity> songEntityList, int playlistId) async {
    await Future.forEach(songEntityList, (songEntity) async {
      SongEntity.saveSingleSongEntity(songEntity);
      await PlaylistWithSongEntity.saveSinglePlaylistWithSongEntity(
          PlaylistWithSongEntity(
              songId: songEntity.id, playlistId: playlistId));
    });
  }

When using the class:

  static Future<void> saveSinglePlaylistEntity(
      PlaylistEntity playlistEntity) async {
    AppDatabase db = AppDatabase.instance();
    await db
        .into(db.playlist)
        .insertOnConflictUpdate(playlistEntity.toCompanion());
    await PlaylistWithSongEntity.cleanRelationshipBasedOnPlaylistId(playlistEntity.id ?? -1);
    if (playlistEntity.songEntityList != null) {
      await _saveSongAndRelationshipData(
          playlistEntity.songEntityList ?? [], playlistEntity.id ?? -1);
    }
  }

  static Future<void> _saveSongAndRelationshipData(
      List<SongEntity> songEntityList, int playlistId) async {
    await Future.forEach(songEntityList, (songEntity) async {
      await SongEntity.saveSingleSongEntity(songEntity);
      await PlaylistWithSongEntity.saveSinglePlaylistWithSongEntity(
          PlaylistWithSongEntity(
              songId: songEntity.id, playlistId: playlistId));
    });
  }

Step 4: Retrieving Stored Data
The last step again is similar to what you did in the previous parts. So, starting with the PlaylistWithSongEntity.

For .drift file: Two 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. queryListOfPlaylistWithSongByPlaylistId: Used to get a list with playlist and song IDs where playlistId match the given ID. Otherwise empty list will be returned.

For class: One method is required:
1. queryListOfPlaylistWithSongByPlaylistId: Used to get a list with playlist and song IDs where playlistId match the given ID. Otherwise empty list will be returned.

Using the .drift file:

  static Future<PlaylistWithSongEntity?> convertTableToEntity(
      PlaylistWithSongTable? playlistWithSongTable) async {
    if (playlistWithSongTable != null) {
      return PlaylistWithSongEntity(
          songId: playlistWithSongTable.songId,
          playlistId: playlistWithSongTable.playlistId);
    } else {
      return null;
    }
  }

  static Future<List<PlaylistWithSongEntity>?>
      queryListOfPlaylistWithSongByPlaylistId(int playlistId) async {
    AppDatabase db = AppDatabase.instance();
    List<PlaylistWithSongEntity> playlistWithSongEntityList = [];
    List<PlaylistWithSongTable>? playlistWithSongTableList =
        await (db.select(db.playlistWithSong)
              ..where((tbl) => tbl.playlistId.equals(playlistId)))
            .get();
    await Future.forEach(playlistWithSongTableList,
        (playlistWithSongTable) async {
      PlaylistWithSongEntity? tempPlaylistWithSongEntity =
          await convertTableToEntity(playlistWithSongTable);
      if (tempPlaylistWithSongEntity != null) {
        playlistWithSongEntityList.add(tempPlaylistWithSongEntity);
      }
    });
    return playlistWithSongEntityList;
  }

Using the class:

  static Future<List<PlaylistWithSongEntity>?>
      queryListOfPlaylistWithSongByPlaylistId(int playlistId) async {
    AppDatabase db = AppDatabase.instance();
    List<PlaylistWithSongEntity> playlistWithSongEntityList =
        await (db.select(db.playlistWithSong)
              ..where((tbl) => tbl.playlistId.equals(playlistId)))
            .get();
    return playlistWithSongEntityList;
  }

The next step is to modify the queries in the PlaylistEntity to retrieve also the data required for the newly added field (songEntityList). For this, you need to do 2 queries.
1. Query from the PlaylistWithSongsEntity using the playlistId to get all the song IDs which belong to this playlist.
2. Use the retrieved list of the song IDs to query from the SongEntity to get all songs based on songId.

Below you can find the complete example of the queries needed to accomplish this.

Using the .drift file:

  static Future<PlaylistEntity?> convertTableToEntity(
      PlaylistTable? playlistTable) async {
    if (playlistTable != null) {
      List<PlaylistWithSongEntity>? playlistWithSongEntityList =
          await _queryToGetPlaylistWithSongEntityList(playlistTable.id ?? -1);
      List<SongEntity> queriedSongEntityList =
          await _queryToGetSongEntityList(playlistWithSongEntityList);
      return PlaylistEntity(
          id: playlistTable.id,
          name: playlistTable.name,
          numberOfSongs: playlistTable.numberOfSongs,
          userId: playlistTable.userID,
          songEntityList: queriedSongEntityList);
    } else {
      return null;
    }
  }

  ///This method is used to query a list of PlaylistWithSongEntity 
  ///Which are matches the playlistId.
  ///Is not mandatory to create the extra method.
  static Future<List<PlaylistWithSongEntity>?>
      _queryToGetPlaylistWithSongEntityList(int playlistId) async {
    return await PlaylistWithSongEntity.queryListOfPlaylistWithSongByPlaylistId(
        playlistId);
  }

   ///This method is used to query a list of SongEntity 
  ///Which are matches the songId from the previous queried list of playlistWithSongs.
  ///Is not mandatory to create the extra method.
  static Future<List<SongEntity>> _queryToGetSongEntityList(
      List<PlaylistWithSongEntity>? playlistWithSongEntityList) async {
    List<SongEntity> queriedSongEntityList = [];
    if (playlistWithSongEntityList != null) {
      await Future.forEach(playlistWithSongEntityList,
          (playlistWithSongEntity) async {
        SongEntity? tempSongEntity =
            await SongEntity.querySongById(playlistWithSongEntity.songId ?? -1);
        if (tempSongEntity != null) {
          queriedSongEntityList.add(tempSongEntity);
        }
      });
    }
    return queriedSongEntityList;
  }

  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.playlist).get();
    await Future.forEach(playlistEntityList, (playlistEntity) async {
      List<PlaylistWithSongEntity> playlistWithSongEntityList =
          await _queryToGetPlaylistWithSongEntityList(
                  playlistEntity.id ?? -1) ??
              [];
      List<SongEntity> songEntityList =
          await _queryToGetSongEntityList(playlistWithSongEntityList);
      playlistEntity.songEntityList = songEntityList;
    });
    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();
    List<PlaylistWithSongEntity> playlistWithSongEntityList =
        await _queryToGetPlaylistWithSongEntityList(playlistEntity?.id ?? -1) ??
            [];
    List<SongEntity> songEntityList =
        await _queryToGetSongEntityList(playlistWithSongEntityList);
    playlistEntity?.songEntityList = songEntityList;
    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();
    List<PlaylistWithSongEntity> playlistWithSongEntityList =
        await _queryToGetPlaylistWithSongEntityList(playlistEntity?.id ?? -1) ??
            [];
    List<SongEntity> songEntityList =
        await _queryToGetSongEntityList(playlistWithSongEntityList);
    playlistEntity?.songEntityList = songEntityList;
    return playlistEntity;
  }

  static Future<List<PlaylistWithSongEntity>?>
      _queryToGetPlaylistWithSongEntityList(int playlistId) async {
    return await PlaylistWithSongEntity.queryListOfPlaylistWithSongByPlaylistId(
        playlistId);
  }

  static Future<List<SongEntity>> _queryToGetSongEntityList(
      List<PlaylistWithSongEntity>? playlistWithSongEntityList) async {
    List<SongEntity> queriedSongEntityList = [];
    if (playlistWithSongEntityList != null) {
      await Future.forEach(playlistWithSongEntityList,
          (playlistWithSongEntity) async {
        SongEntity? tempSongEntity =
            await SongEntity.querySongById(playlistWithSongEntity.songId ?? -1);
        if (tempSongEntity != null) {
          queriedSongEntityList.add(tempSongEntity);
        }
      });
    }
    return queriedSongEntityList;
  }

Conclusion

Part 4 concludes the Flutter Local Database Series, covering the setup of Dirft as a local database in a Flutter project, the implementation of the One-to-One relationship, the One-to-Many relationship and the Many-to-Many 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.

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/