Many-to-many relationships

Since drift is a relational database library and not an ORM, it doesn't automatically fetch relationships between entities for you. Instead, it gives you the tool to manually write the joins needed to express more complex queries efficiently.

This example shows how to do that with a complex many-to-many relationship by implementing a database for an online shop. In particular, we'll focus on how to model shopping carts in SQL. Here, there is a many-to-many relationship between shopping carts and products: A product can be in many shopping carts at the same time, and carts can of course contain more than one product too.

In sqlite3, there are two good ways to model many-to-many relationships between tables:

1. The traditional way of using a third table storing every combination of products and carts.
2. A more modern way might be to store product IDs in a shopping cart as a JSON array.

The two approaches have different upsides and downsides. With the traditional relational way, it's easier to ensure data integrity (by, for instance, deleting product references out of shopping carts when a product is deleted). On the other hand, queries are easier to write with JSON structures. Especially when the order of products in the shopping cart is important as well, a JSON list is very helpful since rows in a table are unordered.

Picking the right approach is a design decision you'll have to make. This page describes both approaches and highlights some differences between them.

Common setup

In both approaches, we'll implement a repository for shopping cart entries that will adhere to the following interface:

typedef ShoppingCartWithItems = ({
  ShoppingCart cart,
  List<BuyableItem> items,
});

abstract class CartRepository {
  Future<ShoppingCartWithItems> createEmptyCart();
  Future<void> updateCart(ShoppingCartWithItems entry);

  Stream<ShoppingCartWithItems> watchCart(int id);
  Stream<ShoppingCartWithItems> watchAllCarts();
}

We also need a table for products that can be bought:

class BuyableItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get description => text()();
  IntColumn get price => integer()();
  // we could add more columns as we wish.
}

In a relational structure

Defining the model

We're going to define two tables for shopping carts: One for the cart itself, and another one to store the entries in the cart. The latter uses references to express the foreign key constraints of referencing existing shopping carts or product items.

class ShoppingCarts extends Table {
  IntColumn get id => integer().autoIncrement()();
  // we could also store some further information about the user creating
  // this cart etc.
}

@DataClassName('ShoppingCartEntry')
class ShoppingCartEntries extends Table {
  // id of the cart that should contain this item.
  IntColumn get shoppingCart => integer().references(ShoppingCarts, #id)();
  // id of the item in this cart
  IntColumn get item => integer().references(BuyableItems, #id)();
  // again, we could store additional information like when the item was
  // added, an amount, etc.
}

Inserts

We want to write a CartWithItems instance into the database. We assume that all the BuyableItems included already exist in the database (we could store them via into(buyableItems).insert(BuyableItemsCompanion(...))). Then, we can replace a full cart with

Future<void> updateCart(ShoppingCartWithItems entry) {
  return transaction(() async {
    final cart = entry.cart;

    // first, we write the shopping cart
    await update(shoppingCarts).replace(cart);

    // we replace the entries of the cart, so first delete the old ones
    await (delete(shoppingCartEntries)
          ..where((entry) => entry.shoppingCart.equals(cart.id)))
        .go();

    // And write the new ones
    for (final item in entry.items) {
      await into(shoppingCartEntries)
          .insert(ShoppingCartEntry(shoppingCart: cart.id, item: item.id));
    }
  });
}

We could also define a helpful method to create a new, empty shopping cart:

Future<ShoppingCartWithItems> createEmptyCart() async {
  final cart = await into(shoppingCarts)
      .insertReturning(const ShoppingCartsCompanion());
  // we set the items property to [] because we've just created the cart - it
  // will be empty
  return (cart: cart, items: <BuyableItem>[]);
}

Selecting a cart

As our CartWithItems class consists of multiple components that are separated in the database (information about the cart, and information about the added items), we'll have to merge two streams together. The rxdart library helps here by providing the combineLatest2 method, allowing us to write

Stream<ShoppingCartWithItems> watchCart(int id) {
  // load information about the cart
  final cartQuery = select(shoppingCarts)
    ..where((cart) => cart.id.equals(id));

  // and also load information about the entries in this cart
  final contentQuery = select(shoppingCartEntries).join(
    [
      innerJoin(
        buyableItems,
        buyableItems.id.equalsExp(shoppingCartEntries.item),
      ),
    ],
  )..where(shoppingCartEntries.shoppingCart.equals(id));

  final cartStream = cartQuery.watchSingle();

  final contentStream = contentQuery.watch().map((rows) {
    // we join the shoppingCartEntries with the buyableItems, but we
    // only care about the item here.
    return rows.map((row) => row.readTable(buyableItems)).toList();
  });

  // now, we can merge the two queries together in one stream
  return Rx.combineLatest2(cartStream, contentStream,
      (ShoppingCart cart, List<BuyableItem> items) {
    return (cart: cart, items: items);
  });
}

Selecting all carts

Instead of watching a single cart and all associated entries, we now watch all carts and load all entries for each cart. For this type of transformation, RxDart's switchMap comes in handy:

Stream<List<ShoppingCartWithItems>> watchAllCarts() {
  // start by watching all carts
  final cartStream = select(shoppingCarts).watch();

  return cartStream.switchMap((carts) {
    // this method is called whenever the list of carts changes. For each
    // cart, now we want to load all the items in it.
    // (we create a map from id to cart here just for performance reasons)
    final idToCart = {for (var cart in carts) cart.id: cart};
    final ids = idToCart.keys;

    // select all entries that are included in any cart that we found
    final entryQuery = select(shoppingCartEntries).join(
      [
        innerJoin(
          buyableItems,
          buyableItems.id.equalsExp(shoppingCartEntries.item),
        )
      ],
    )..where(shoppingCartEntries.shoppingCart.isIn(ids));

    return entryQuery.watch().map((rows) {
      // Store the list of entries for each cart, again using maps for faster
      // lookups.
      final idToItems = <int, List<BuyableItem>>{};

      // for each entry (row) that is included in a cart, put it in the map
      // of items.
      for (final row in rows) {
        final item = row.readTable(buyableItems);
        final id = row.readTable(shoppingCartEntries).shoppingCart;

        idToItems.putIfAbsent(id, () => []).add(item);
      }

      // finally, all that's left is to merge the map of carts with the map of
      // entries
      return [
        for (var id in ids) (cart: idToCart[id]!, items: idToItems[id] ?? []),
      ];
    });
  });
}

With JSON functions

This time, we can store items directly in the shopping cart table. Multiple entries are stored in a single row by encoding them into a JSON array, which happens with help of the json_serializable package:

@DataClassName('ShoppingCart')
class ShoppingCarts extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get entries => text().map(ShoppingCartEntries.converter)();

  // we could also store some further information about the user creating
  // this cart etc.
}

@JsonSerializable()
class ShoppingCartEntries {
  final List<int> items;

  ShoppingCartEntries({required this.items});

  factory ShoppingCartEntries.fromJson(Map<String, Object?> json) =>
      _$ShoppingCartEntriesFromJson(json);

  Map<String, Object?> toJson() {
    return _$ShoppingCartEntriesToJson(this);
  }

  static JsonTypeConverter<ShoppingCartEntries, String> converter =
      TypeConverter.json(
    fromJson: (json) =>
        ShoppingCartEntries.fromJson(json as Map<String, Object?>),
    toJson: (entries) => entries.toJson(),
  );
}

Creating shopping carts looks just like in the relational example:

Future<ShoppingCartWithItems> createEmptyCart() async {
  final cart = await into(shoppingCarts)
      .insertReturning(const ShoppingCartsCompanion());

  // we set the items property to [] because we've just created the cart - it
  // will be empty
  return (cart: cart, items: <BuyableItem>[]);
}

However, updating a shopping cart doesn't require a transaction anymore since it can all happen in a single table:

Future<void> updateCart(ShoppingCartWithItems entry) async {
  await update(shoppingCarts).replace(entry.cart.copyWith(
      entries: ShoppingCartEntries(items: [
    for (final item in entry.items) item.id,
  ])));
}

To select a single cart, we can use the json_each function from sqlite3 to "join" each item stored in the JSON array as if it were a separate row. That way, we can efficiently look up all items in a cart:

Stream<ShoppingCartWithItems> watchCart(int id) {
  final referencedItems = shoppingCarts.entries.jsonEach(this, r'#$.items');

  final cartWithEntries = select(shoppingCarts).join(
    [
      // Join every referenced item from the json array
      innerJoin(referencedItems, const Constant(true), useColumns: false),
      // And use that to join the items
      innerJoin(
        buyableItems,
        buyableItems.id.equalsExp(referencedItems.value.cast()),
      ),
    ],
  )..where(shoppingCarts.id.equals(id));

  return cartWithEntries.watch().map((rows) {
    late ShoppingCart cart;
    final entries = <BuyableItem>[];

    for (final row in rows) {
      cart = row.readTable(shoppingCarts);
      entries.add(row.readTable(buyableItems));
    }

    return (cart: cart, items: entries);
  });
}

Watching all carts isn't that much harder, we just remove the where clause and combine all rows into a map from carts to their items:

Stream<List<ShoppingCartWithItems>> watchAllCarts() {
  final referencedItems = shoppingCarts.entries.jsonEach(this, r'#$.items');

  final cartWithEntries = select(shoppingCarts).join(
    [
      // Join every referenced item from the json array
      innerJoin(referencedItems, const Constant(true), useColumns: false),
      // And use that to join the items
      innerJoin(
        buyableItems,
        buyableItems.id.equalsExp(referencedItems.value.cast()),
      ),
    ],
  );

  return cartWithEntries.watch().map((rows) {
    final entriesByCart = <ShoppingCart, List<BuyableItem>>{};

    for (final row in rows) {
      final cart = row.readTable(shoppingCarts);
      final item = row.readTable(buyableItems);

      entriesByCart.putIfAbsent(cart, () => []).add(item);
    }

    return [
      for (final entry in entriesByCart.entries)
        (cart: entry.key, items: entry.value)
    ];
  });
}