We read every piece of feedback, and take your input very seriously.
To see all available qualifiers, see our documentation.
There was an error while loading. Please reload this page.
-
I am building a chat app with Dexie. The messages are distributed p2p so there is no central DB.
Currently I store all messages in a "messages" table and then filter by channel_id to get the right messages.
I was considering to create tables at runtime for each new channel to optimize query performance. Is that a good idea? My understanding so far is that for such updates I would have to pause the DB briefly --> I would have to create another DB to store incoming messages which could then be added to the main DB once it gets operational again.
Also considering to move to SQLite instead but that would be quite an undertaking which I would like to prevent.
Beta Was this translation helpful? Give feedback.
Updating the schema runtime is possible but I would not recommend it, and like with SQL, there are techniques to optimize query performance anyway by choosing smart primary keys or indexes.
My recommendation would be to use a compound index where the first part represents the channel ID and the second part the message ID. That way you could do all kinds of queries the same optimized way as if each channel had its own table.
Example:
const db = new Dexie('messaging') as Dexie & { messages: Message }; db.version(1).stores({ messages: ` ++messageId, [channelId+messageId], [channelId+timeStamp]` });
To query the 10 recent messages from a certain channel:
await db.messages .where(
await db.messages .where('[channelId+timeStamp]') .between( [channelId, 0], [channelId, Infinity]) .reverse() .limit(10) .toArray();
In case messageId doesn't need to be autoIncremented, you could actually use [channelId+messageId] as the primary key (if so, just delete '++messageId, ' from the schema and use a random string or GUID as the messageId.
Whenever you'd need to index a new property, consider adding a compound index with the channelId as the leading part to allow for more optimized queries on the property.
IndexedDB will always return the results in the same order as the index being queried. In this case [channelId+timeStamp] is ordered by channelId first and timeStamp second. Since the where-clause limits it to a single channelId, the result will be ordered by timeStamp. The 'reverse' method makes it travel the index backwards. The limit(10) will make it stop after the 10 last entries and ensure that the query will always be instantly fast.
This is great! I will start implementing this. Thank you very much :-)