IndexedDB is architectured a lot like an SQL database, in that it has tables, rows and transactions, even though they are named differently (Table = ObjectStore, row = Object).
Using Dexie, it is quite easy to do those kind of typical joins with foreign keys. IndexedDB doesn't check the constraints of the foreign keys, but you can do queries that are similar to SQL joins.
There is an addon to dexie, dexie-relationships, that can assist in doing the join queries.
import Dexie from 'dexie'
import relationships from 'dexie-relationships'
class OrdersDB extends Dexie {
customers: Dexie.Table<Customer, string>;
products: Dexie.Table<Producs, string>;
pricesPerCustomer: Dexie.Table<PricePerCustomer, string>;
orders: Dexie.Table<Order, string>;
constructor() {
super ("OrdersDB", {addons: [relationships]});
this.version(1).stores({
customers: 'id, name',
products: 'id, name',
pricesPerCustomer: `
id,
customerId -> customers.id,
productId -> products.id,
[customerId+productId]`, // Optimizes compound query (see below)
orders: `
id,
customerId -> customers.id,
productId -> products.id`
});
}
}
interface Customer {
id: string;
name: string;
orders?: Order[]; // db.customers.with({orders: 'orders'})
prices?: PricesPerCustomer[]; // with({prices: 'pricesPerCustomer'})
}
interface Product {
id: string;
name: string;
prices?: PricesPerCustomer[]; // with({prices: 'pricesPerCustomer'})
}
interface PricePerCustomer {
id: string;
price: number;
currency: string;
customerId: string;
customer?: Customer; // with({customer: 'customerId'})
productId: string;
product?: Product; // with({product: 'productId'})
}
interface Order {
id: string;
customerId: string;
customer?: Customer; // with({customer: 'customerId'})
productId: string;
product?: Product; // with({product: 'productId'})
quantity: number;
price?: number; // When returned from getOrders() below.
currency?: string; // --"--
}
const db = new OrdersDB();
/* Returns array of Customer with the "orders" and "prices" arrays attached.
*/
async function getCustomersBeginningWithA() {
return await db.customers.where('name').startsWithIgnoreCase('a')
.with({orders: 'orders', prices: 'pricesPerCustomer'});
}
/* Returns the price for a certain customer and product using
a compound query (Must use Dexie 2.0 for this). The query is
optimized if having a compound index ['customerId+productId']
declared in the database schema (as done above).
*/
async function getPrice (customerId: string, productId: string) {
return await db.pricesPerCustomer.get({
customerId: customerId,
productId: productId
});
}
async function getOrders (customerId: string) {
// Load orders for given customer with product property set.
const orders = await db.orders.where({customerId: customerId})
.with({product: 'productId'});
// Load prices for this each customer/product
const prices = await Promise.all(orders.map(order =>
getPrice(customerId, order.id)));
// Return orders with price and currency properties set:
return orders.map((order, idx) => {
const pricePerCustomer = prices[idx];
return {
...order,
price: pricePerCustomer.price,
currency: pricePerCustomer.currency
};
});
}
Note that I have declared each primary key as a string, so you will have to invent each key manually. Could have been using auto-generated numbers as well (using "++id, ..." instead of "id, ...") in the schema declaration. If so, declare the tables as Dexie.Table<Customer, number> instead of Dexie.Table<Customer, string>.
