Dexie.js - A Holistic Guide

Dexie.js is a minimalistic indexed DB wrapper that provides near-native performance and easy to use database.  It's hard to believe, but the Dexie DB package size is around 22KB and it works cross-browser and devices. It solves error handling via promises rather than events, so it will result in less coding and more maintainable code.

To get started, we will first create an order electron app. Clone the electron typescript template and install Dexie to it. 

git clone https://github.com/electron/electron-quick-start-typescript.git

npm install dexie

Going to create a dexiedbmanager.ts file where we can create the database, tables, and database access methods. By creating the constructor of Dexie, it will create the database if it does not exist already. 'autoOpen' DB settings will open the database on the first query. This database appears in the indexeddb part of the browser. 

xxxxxxxxxx

 this.orderdb = new Dexie('orderdb', { autoOpen: true });

Order app requires schema like products for creating the order to the customer with quantity and price. Tables required are products as inventory, order, and order detail are order related tables and customer to have a history of customers. Creating a table and having primary keys are so simple in dexie to have JSON format of the data available. Primary keys are marked with '++' which will be auto-generated numbers, all fields provided against the table are indexed keys, the ones which are marked as '&' will be unique keys. Dexie is basically a Key-Value database where data are stored or referenced by keys. So the fields mentioned in the below structure are the keys available for querying the data from the tables. 

x

// Create a tables

      this.orderdb.version(DexieDBManager.VERSION_1_O).stores({

        product: '++id, &name, description',

        customer: '&mobileno, name,  &mailid',

        order: '++id, date, totalqty, mobileno, name',

        orderdetail: '++id, orderid, productid',

      });

      console.log("database initialized");

When we run the code,  tables with keys will be created under Indexed DB which can be seen in the devtools. Apart from the fields mentioned, any fields can be stored but it will not be searchable. The primary key is also not mandatory as it maintains its own sequence number, the customer table is having database maintained sequence number, mobile number is like a unique secondary key.

 Loading the inventory of products would be the first step for an ordering app. Dexie allows bulk insert of records which is more performance effective as it inserts all the records one shot without invoking promise handler for each call. 

It is not like traditional RDMS, there are only table access methods and to get the table we use the database. 'getTable' method will use the DB instance variable and get the table by the method "table" passing the schema name used at the time of database creation. Dexie Table type is a generic type with two parameters - one for the table type and the other for the primary key type.

Product model interface with all the fields is represented as below.

xxxxxxxxxx

export interface Product {

    id?: number;

    name: string;

    description: string;

}

 In the product table, we have the Product type to denote the product and the key will be auto-generated id. table object has a clear method to clear the table. Table bulkAdd method will pass the products data JSON array with options as allKeys true so that promise response will give all ids array. On a success response, the keys are used for bulk get to load the combo box select element.

xxxxxxxxxx

loadProducts(): Promise<Product[]> {

      return new Promise((resolve, reject) => {

        const table: Dexie.Table<Product, number> = this.getTable("product");

        table.clear();

        table.bulkAdd(PRODUCTSDB, {allKeys: true}).then(keys => {

          return resolve(table.bulkGet(keys));

        }).catch(error => {

          console.log(`error while loading the products ${error}`);

          reject("failed to load the products");

        });

      });

    }

    getTable<T, IndexableType>(schema: string): Table<T, IndexableType> {

      return this.orderdb.table(schema);

    }

const PRODUCTSDB = [

  {'name': 'dell inspiron laptop', 'description': "its a refurbished laptop"},

  {'name': 'kingston usb drive', 'description': "8gb flash memory"},

  {'name': 'portable hdd', 'description': "portable hard disk"},

  {'name': 'google nest', 'description': "home automation system"},

  {'name': 'dslr camera', 'description': "18-55 lens canon camera"}

];

Now when a user enters the order 's customer details and with items and quantities. Order can be created using the put method which returns the id. This order id to be used to create order items using the "put" method and updated order with the total amount and price using "bulkPut" method. 

xxxxxxxxxx

    createorder(order: Order): Promise<number> {

      const table: Table<Order, number> = this.getTable("order");

      return new Promise((resolve, reject) => {

        table.add(order).then((id) => {

          return resolve(id);

        })

        .catch(err => {

          console.log(`error while creating the order ${err}`);

          reject("failed to create the order");

        });

      });

    }

    updateOrder(order: Order, orderitems: OrderDetail[]): Promise<boolean> {

      const orderTable: Table<Order, number> = this.getTable("order");

      const orderDetailTable: Table<OrderDetail, number> = this.getTable("orderdetail");

      return new Promise((resolve, reject) => {

        orderTable.put(order).then((id) => {

          orderDetailTable.bulkPut(orderitems).then(ids => {return resolve(true);}).catch(err => {

            console.log(`error while creating the order items ${err}`);

            reject("failed to create the order and order items");

          })

        })

      }); 

    }

To lookup customer name by mobile number, we can use the "where" method with the key pair of having mobile no. The response will be an array where the first record is taken to the customer's name. 

xxxxxxxxxx

    getCustomer(custmobile: string): Promise<Customer> {

      const table: Table<Customer, string> = this.getTable("customer");

      return new Promise((resolve, reject) => {

        table.where({mobileno: custmobile}).toArray().then((customers: Customer[]) => {

          return resolve(customers[0]);

        }).catch(err => {

          console.log(`error while fetching customer by mobileno ${err} for ${custmobile}`);

          reject("Failed to get the customer ")

        });

      });

    }

Models for order and order detail shown below:

x

export interface Order {

    id?: number;

    date: Date;

    totalqty?: number;

    totalprice?: number;

    mobileno: string;

    name: string;

}

export interface OrderDetail {

    id?: number;

    orderId: number;

    productId: number;

    qty: number;

    price: number;

}

Once the order inserted successfully, it has to be displayed in the UI screen for successful completion. As this tutorial is done for the understanding of dexie, i have shown the whole order and order detail as JSON string. 

xxxxxxxxxx

    const orderId: number = await dexiedbmanager.createorder(order);

    let totalAmount = 0, totalQuantities = 0;

    const orderItems: OrderDetail[] = []; 

    for (let i=0; i< selectedProducts.length; i++) {

        const prdElem = selectedProducts.item(i) as HTMLSelectElement;

        const qtyElem = quantities.item(i) as HTMLInputElement;

        const priceElem = prices.item(i) as HTMLInputElement;

        const productId = parseInt(prdElem.value);

        const qty = parseInt(qtyElem.value);

        const price = parseInt(priceElem.value);

        totalQuantities += qty;

        totalAmount +=  qty * price;

        const orderItem: OrderDetail = {orderId, productId, qty, price};

        orderItems.push(orderItem);

    }

    order.totalprice = totalAmount;

    order.totalqty = totalQuantities;

    dexiedbmanager.updateOrder(order, orderItems).then(() => {

        console.log("order saved successfully");

        const result = document.getElementById("result") as HTMLDivElement;

        result.innerText = `${JSON.stringify(order)} --> ${JSON.stringify(orderItems)}`;

    });

After clicking on the save order, it will display the order JSON.

The complete code has been pushed to my Github.