Drift Local Database For Flutter (Part 3- One-to-Many Relationship)
Introduction
Part 3 of the series on using the Drift database in Flutter projects explores the implementation of the One-to-Many relationship. 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 One-to-Many Relationship
This kind of relationship exists when a record from one entity is connected(linked) with ONE OR MORE records in another entity.
Implementation Of The Example
In this scenario, One-to-Many relationship exists between the Artist and the Song entities. So, let’s see how you can accomplish this using drift.
Step 1: Adding Artist ID to Song Table
As an artist can have many songs, the idea here is to add the artist’s ID to the songs table. So, now your Song table should look like the example below.
When using the .drift file:
CREATE TABLE Song (
id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
duration INT NOT NULL,
artist_id INT NOT NULL
) AS SongTable;When using the class:
@UseRowClass(SongEntity)
class SongTable extends Table {
IntColumn get id => integer()();
TextColumn get name => text()();
IntColumn get duration => integer()();
IntColumn get artistId => 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: Create Entity Classes
The second step is to create the corresponding entity classes for each table similar to the UserEntity and PlaylistEntity in Part 1.
Starting with the ArtistEntity you will have an extra field than the ArtistTable. This field will be a list of songs which represent the songs owned by each artist.
This part will be the same for both ways. (.drift file and classes)
class ArtistEntity {
int? id;
String? name;
int? age;
String? musicStyle;
List<SongEntity>? songEntityList;
ArtistEntity(
{this.id, this.name, this.age, this.musicStyle, this.songEntityList});
}Following is the SongEntity which includes the same fields as the table.
This part will be the same for both ways. (.drift file and classes)
class SongEntity {
int? id;
String? name;
String? duration;
int? artistId;
SongEntity({this.id, this.name, this.duration, this.artistId});
}Step 3: Storing Data
The third step is to store both artists and songs in the database. The process involves the same methods used in the One-to-One relationship. So, you have the following methods:
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)
Artist Entity:
ArtistCompanion toCompanion() {
return ArtistCompanion(
id: Value(id ?? -1),
name: Value(name ?? ''),
musicStyle: Value(musicStyle ?? ''),
age: Value(age ?? 0));
}
static Future<void> saveSingleArtistEntity(ArtistEntity artistEntity) async {
AppDatabase db = AppDatabase.instance();
await db.into(db.artist).insertOnConflictUpdate(artistEntity.toCompanion());
if (artistEntity.songEntityList != null) {
await SongEntity.saveListOfSongsEntity(artistEntity.songEntityList ?? []);
}
}
static Future<void> saveListOfArtistEntity(
List<ArtistEntity> artistEntityList) async {
await Future.forEach(artistEntityList, (artistEntity) async {
await saveSingleArtistEntity(artistEntity);
});
}Song Entity:
SongCompanion toCompanion() {
return SongCompanion(
id: Value(id ?? -1),
name: Value(name ?? ''),
duration: Value(duration ?? 0),
artistId: Value(artistId ?? 0));
}
static Future<void> saveSingleSongEntity(SongEntity songEntity) async {
AppDatabase db = AppDatabase.instance();
await db
.into(db.song)
.insertOnConflictUpdate(songEntity.toCompanion());
}
static Future<void> saveListOfSongsEntity(
List<SongEntity> songEntityList) async {
await Future.forEach(songEntityList, (songEntity) async {
await saveSingleSongEntity(songEntity);
});
}Step 4: Retrieving Stored Data
The last step again is similar to what you did in the previous part. So, starting with the ArtistEntity.
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. queryAllArtists: Used to get a list with all stored artists.
3. queryArtistById: Used to get only 1 artist in case the given id matches an id from the stored artists otherwise returns null.
For classes: Two methods are required, including:
1. queryAllArtists: Used to get a list with all stored artists.
2. queryArtistById: Used to get only 1 artist in case the given id matches an id from the stored artists otherwise returns null.
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<ArtistEntity?> convertTableToEntity(
ArtistTable? artistTable) async {
if (artistTable != null) {
List<SongEntity>? songEntityList =
await SongEntity.queryListOfSongsByArtistId(artistTable.id ?? -1);
return ArtistEntity(
id: artistTable.id,
name: artistTable.name,
musicStyle: artistTable.musicStyle,
age: artistTable.age,
songEntityList: songEntityList);
} else {
return null;
}
}
static Future<List<ArtistEntity>> queryAllArtists() async {
AppDatabase db = AppDatabase.instance();
List<ArtistEntity> artistEntityList = [];
List<ArtistTable> artistTableList = await db.select(db.artist).get();
await Future.forEach(artistTableList, (artistTable) async {
ArtistEntity? tempArtistEntity = await convertTableToEntity(artistTable);
if (tempArtistEntity != null) {
artistEntityList.add(tempArtistEntity);
}
});
return artistEntityList;
}
static Future<ArtistEntity?> queryArtistById(int artistId) async {
AppDatabase db = AppDatabase.instance();
ArtistTable? artistTable = await (db.select(db.artist)
..where((tbl) => tbl.id.equals(artistId)))
.getSingleOrNull();
return convertTableToEntity(artistTable);
}Using the class:
static Future<List<ArtistEntity>> queryAllArtists() async {
AppDatabase db = AppDatabase.instance();
List<ArtistEntity> artistEntityList = await db.select(db.artist).get();
await Future.forEach(artistEntityList, (artistEntity) async {
artistEntity.songEntityList =
await queryListOfSongEntityByArtistId(artistEntity.id ?? -1);
});
return artistEntityList;
}
static Future<ArtistEntity?> queryArtistById(int artistId) async {
AppDatabase db = AppDatabase.instance();
ArtistEntity? artistEntity = await (db.select(db.artist)
..where((tbl) => tbl.id.equals(artistId)))
.getSingleOrNull();
if (artistEntity != null) {
artistEntity.songEntityList =
await queryListOfSongEntityByArtistId(artistEntity.id ?? -1);
}
return artistEntity;
}
///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<List<SongEntity>> queryListOfSongEntityByArtistId(
int artistId) async =>
await SongEntity.queryListOfSongsByArtistId(artistId);Then for the SongsEntity:
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. queryAllsongs: Used to get a list with all stored songs.
3. querySongById: Used to get only 1 song in case the given id matches an id from the stored songs otherwise returns null.
4. queryListOfSongsByArtistId: Used to get all songs which are connected to a specific artist. if the given ID does not match an artistId an empty list is returned.
For class: Three methods are required, including:
2. queryAllsongs: Used to get a list with all stored songs.
3. querySongById: Used to get only 1 song in case the given id matches an id from the stored songs otherwise returns null.
4. queryListOfSongsByArtistId: Used to get all songs which are connected to a specific artist. if the given ID does not match an artistId an empty list is returned.
Using the .drift file:
static Future<SongEntity?> convertTableToEntity(SongTable? songTable) async {
if(songTable != null) {
return SongEntity(
id: songTable.id,
name: songTable.name,
duration: songTable.duration,
artistId: songTable.artistId);
} else {
return null;
}
}
static Future<List<SongEntity>> queryAllSongs() async {
AppDatabase db = AppDatabase.instance();
List<SongEntity> songEntityList = [];
List<SongTable> songTableList = await db.select(db.song).get();
await Future.forEach(songTableList, (songTable) async {
SongEntity? tempSongEntity = await convertTableToEntity(songTable);
if(tempSongEntity != null) {
songEntityList.add(tempSongEntity);
}
});
return songEntityList;
}
static Future<SongEntity?> querySongById(int songId) async {
AppDatabase db = AppDatabase.instance();
SongTable? songTable =
await (db.select(db.song)..where((tbl) => tbl.id.equals(songId))).getSingleOrNull();
return convertTableToEntity(songTable);
}
static Future<List<SongEntity>> queryListOfSongsByArtistId(int artistId) async {
AppDatabase db = AppDatabase.instance();
List<SongEntity> songEntityList = [];
List<SongTable>? songTableList =
await (db.select(db.song)..where((tbl) => tbl.artistId.equals(artistId))).get();
await Future.forEach(songTableList, (songTable) async {
SongEntity? tempSongEntity = await convertTableToEntity(songTable);
if(tempSongEntity != null) {
songEntityList.add(tempSongEntity);
}
});
return songEntityList;
}Using the class:
static Future<List<SongEntity>> queryAllSongs() async {
AppDatabase db = AppDatabase.instance();
List<SongEntity> songEntityList = await db.select(db.song).get();
return songEntityList;
}
static Future<SongEntity?> querySongById(int songId) async {
AppDatabase db = AppDatabase.instance();
SongEntity? songEntity = await (db.select(db.song)
..where((tbl) => tbl.id.equals(songId)))
.getSingleOrNull();
return songEntity;
}
static Future<List<SongEntity>> queryListOfSongsByArtistId(
int artistId) async {
AppDatabase db = AppDatabase.instance();
List<SongEntity> songEntityList = await (db.select(db.song)
..where((tbl) => tbl.artistId.equals(artistId)))
.get();
return songEntityList;
}Conclusion
Part 3 concludes the Flutter Local Database Series, covering up to now the setup of Dirft as a local database in a Flutter project, the implementation of One-to-One relationship and One-to-Many relationship.
If you have found this article helpful feel free to share and check out the rest 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:
- 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/
