Relation in version.stores()

[zanzara]

I try to understand this example under
https://dexie.org/docs/Version/Version.stores()#detailed-sample
but it isn't possible to me. Imo there is smt missing regarding relation.

Could smb pls explain me how this is should work under Detailed Sample Explained ?
There is no db.relation.add() or smt else done. Isn't it necessary at all?
And why userId1 and userId2 ? Is this a relation only for 2 entries in the user table?

And furtheron: How would this work in conjunction with: https://github.com/dfahlander/dexie-relationships

Really not easy to understand how relations would work in dexie.
Tia.

[dfahlander]

You can build relations in dexie just like in other databases following the same pattern as being used in SQL databases. Dexie does not have contraints for foreign keys such as cascade deletion but it has transactions and indexes which means that you can use the exact same design patterns as you would do in an SQL database.

For one-to-many relations, you just use an indexed property on one side representing the primary key the other side.

For many-to-many relations, you can use an associative entity just like with other databases (also called graphs). With Dexie, there's also a simpler option possible in the cases when one side of the relation is limited (won't ever grow more than a couple of hundreds): to use an array property of IDs on one side and a simple foreign key property on the other side.

Some examples (I hope better than the page you refer to):

const db = new Dexie('relationsDb');
db.version(1).stores({
  artists: 'id, firstName, lastName',

  // many-to-one relation: Many songs, one artist:
  songs: `
    id,
    name,
    artistId`, // Index artistId and use it to associate song with artist

  // many-to-many: albums and songs where number of songs won't grow forever
  albums: `
    id,
    name,
    *songIds`, // multiEntry index songIds array (to find album given a song)

  listeners: 'id, ...other props',

  // A graph/associative-entity between listeners and songs they like
  // Use the combination of artistId and listenerId as the primary key
  // Also index the listenerId
  // These two make it possible to find related entities in both directions:
  // db.likes.where({artistId: x}).toArray();
  // db.likes.where({listenerId: y}).toArray();
  likes: `
    [songId+listenerId],
    listenerId`
});

dexie-relationsships

Dexie relationships is an addon to dexie that makes it possible to use relationship declaration within the schema and request related entities with a single query. However, it's not hard to do that with dexie only and dexie-relationships only supports one-to-many relationships. So I actually recommend implementing relations with plain dexie instead.

Querying plain dexie for related entities

So here are some queries for retrieving related entities with plain dexie (based on the schema defined above):

function listSongsByArtist(artistId) {
  return db.songs.where({artistId}).toArray();
}

async function listSongsByAlbum(albumId) {
  const album = await db.albums.get(albumId);
  if (!album) return [];
  const songs = await db.songs.bulkGet(album.songIds);
  return songs;
}

function listAlbumsBySongId(songId) {
  return db.albums.where({songIds: songId}).toArray();
}

function countSongLikes(songId) {
  return db.likes.where({songId}).count();
}

async function listLikedSongsByListener(listenerId) {
  const likes = await db.likes.where({listenerId}).toArray();
  const songs = await db.bulkGet(likes.map(like => like.songId));
  return songs;
}

Referencial integrity

Use 'rw' transactions whenever creating or deleting entities and check that the associated entity exists.

function addSong({id, name, artistId}) {
  return db.transaction('rw', db.songs, db.artists, async () => {
    if ((await db.artists.where({id: artistId}).count()) < 1) {
      throw new Error('Artist does not exist');
    }
    await db.songs.add({id, name, artistId});
  });
}

function deleteArtist(artistId) {
  return db.transaction('rw', db.artists, db.songs, async () => {
    if ((await db.songs.where({artistId}).count()) > 0) {
      throw new Error("There's at least one song with this artist - failed to delete artist.");
    }
    await db.artists.delete(artistId);
  };
}

import { remove } from 'dexie'; // operator to remove array items from properties

function deleteSong(songId) {
  // Example of a cascading delete
  return db.transaction('rw', db.songs, db.albums, db.likes, () => {
    // Cascade delete songIds from related albums:
    db.albums.where({songIds: songId}).modify({
      songIds: remove([songId]) // https://dexie.org/docs/PropModification/remove()
    });
    // Cascade delete related likes
    db.likes.where({songId}).delete();
    // Delete song itself
    db.songs.delete(songId);
  };
}

The removal of an ID from the songIds property is accomplished using the remove operator which is new in dexie@4. In older versions of dexie, you could replace it with: db.albums.where({songIds: songId}).modify(album => album.songIds = album.songIds.filter(id => id !== songId));.

[zanzara]

@dfahlander first of all: ty vm!!! for that explanation. Great!
Now things are more clear to me.
So I will omit dexie-relationships for now and try ur way of doing.

1-2 more questions to that:
You never put ++id into ur schema examples?! Isn't necessary here? Or schouldn't it be auto-incremented in that case?

I need to use encryptions and would use like that (sry this question for being OT a bit) :

   db = new Dexie('aDexieDB')
    applyEncryptionMiddleware(db,pwd,{
      tableWithSecrets: {
          type: cryptoOptions.ENCRYPT_LIST,
          fields: ['code','explanation'] // note: these cannot be indices
       }
    })
    db.version(2).stores({
     tableWithSecrets: '++id, name',
     anotherTable: '++id, *tableWithSecretsId',
    })
    db.open().catch(e => {
      window.alert(JSON.stringify(e.stack))
    }).then(...

There is written that encrypted fields cannot be indices (what is smh understandable)
But can I do a db.tableWithSecrets.where({iAmNotInTheSchemaOfIndices: aValue}) ?
Or only keys in the schema can be search by where() ?

(iirc indices in database tables are usually only for a faster search)

Again, appreciating any help. Thx.

[dfahlander]

Auto-incremented primary keys (++) can be handy, but it's optional.
Where-clauses need to use indexed properties only.
To filter non-indexed properties, use Collection.filter()

[zanzara]

Again ty vm.

Where-clauses need to use indexed properties only.

This i didn't know yet. Good to know. Thx.

To filter non-indexed properties, use Collection.filter()

Would that mean, that I can use Collection.filter() aswell for encrypted fields then?
(Will encrypted fields be prior auto-decrpyted for that prupose?)

[dfahlander]

Yes, filter can be used on encrytped fields.