Local Database Stores

By on June 17, 2014 10:57 am

Modern browsers have powerful new database capabilities that enable applications to store data locally, and perform advanced indexed queries without a network connection. Applications can be built with offline support without any disruption to data interaction, including searching. However, these database capabilities have traditionally been difficult to use across browsers since Safari only supports WebSQL (although version 8 is slated to include IndexedDB support), and Firefox and Internet Explorer only support the W3C’s IndexedDB API (Chrome supports both). And these two interfaces couldn’t be more different, making it very problematic to write applications that work offline on all the major browsers.

But, it is now much easier to access these database capabilities. By taking advantage of the consistency of the Dojo object store implementation, in version Dojo toolkit version 1.10, dojox/store now includes object store implementations for IndexedDB and WebSQL, along with a wrapper that will automatically delegate to the appropriate store implementation based on browser support. With a single common interface, you can retrieve, update, add, and delete objects, and even perform sophisticated queries in exactly the same way with the different underlying storage implementations.

Using the LocalDB store

localstorage

The dojox/store/LocalDB module is the main, recommended entry store for accessing the local database. This store will automatically load the database implementation supported by the current browser, and the new store instance can be used like any other asynchronous store (you can also directly load the individual store implementations, if you want to target a specific technology). In order to use this store, we create a database configuration object that defines the version, name, and the schema of the properties of the objects that will go in each store. This is necessary so that the appropriate indices and table columns can be setup by each store. We need to define all of the stores that will be included in the database in the same configuration object, along with a version and any database name, so that the stores can properly version and update the database. Here is an example of a database configuration, where we define the database version (the name is optional) and each of the stores. We define the properties for each store’s objects. Our property configurations are simply empty objects, using the default configuration for each property.

	var dbConfig = {
		version: 1, // this is required
		stores: {
			product: {
				// just declare the properties, will configure later
				name: {},
				price: {},
				inStock: {},
				id: {}
			},
			order: {
				quantity: {}
			}
		}
	};

Now that we have a database configuration, we can instantiate individual stores by providing the database config and the name of the store:

	require(['dojox/store/LocalDB'], function(LocalDB){
		var productStore = new LocalDB({
			dbConfig: dbConfig,
			storeName: 'product'
		});

Now, we can interact with the store using the standard dojo/store API:

	productStore.get(id).then(object){
		// get an object by id

		// we could make changes and store the object:
		productStore.put(updatedObject);
	});
	productStore.add(newObject); // add a new object

Querying

A major focus of this implementation is to provide not only basic data storage facilities, but uncompromising, high-performance database querying, so applications can continue to provide fast, full access to data, even when a large amount needs to be stored locally. Let’s start with the basics. To query a database, we use the store’s query() method. The first parameter can be a simple object that defines the filtering constraints on the data to be retrieved. For example, to retrieve all the products that are in stock, we can write:

var results = store.query({
		inStock: true
	});

And then we can iterate over the results using standard iterative methods:

results.forEach(function(object){
	// called for each object
});

Remember that queries are asynchronous, so forEach() will return before all the results are returned. The forEach() method will return a promise that can be used to wait for the completion of the query.

The query() method also accepts an optional second parameter that may be used to specify sort order and/or request a range of the data. For example, if we wanted to sort in-stock products by price and return the first ten products, we can write:

var results = store.query({
		inStock: true
	}, {
		sort: [{attribute: 'price'}],
		start: 0,
		count: 10
	});

When performing a query with a range, we may also want to check how many objects match the query (not just the 10 items within this range). This can be accessed through the results’ total property, which is a promise that resolves to the total count.

Filtering by property equality is useful for some queries, but what if we need to find all the in stock products with a certain price or lower? Typically, with the Memory store we might have defined a custom filtering function to accomplish more sophisticated querying that involves more than simple equality checks (or used more specific querying functionality of a server with the Rest store), but a filtering function can’t be efficiently used in conjunction with database indices. So the LocalDB stores define specific object filter structures to specify ranges or limits in a query. We can create our query by defining a comparison in our filter object. A comparison object is an object with a from or to property that specifies the lower or upper bound (respectively) of the returned objects. We could find products for $100 or less by writing:

var results = store.query({
		inStock: true,
		price: {
			to: 100
		}
	});

We can also use the excludeFrom and excludeTo flags to exclude the limits, so we could get just the items less than $100 by setting excludeTo to true (again, remember these flags are specific to the LocalDB stores, they don’t apply to other stores).

We can also filter properties using a trailing wildcard. This means that we could search for all the products that have a name that starts with a ‘sh’ (this is particularly useful for autocomplete widgets). We can do this by using a regular expression in our query:

var results = store.query({
		price: /sh.*/
	});

By including multiple properties on the query object, we specify multiple constraints that must ALL be satisfied to be included in the results. However, we may want do a union of selections, where one constraint can be satisfied OR another constraint can be satisfied. This can be done by using an array of objects, where the results will include objects that match the results of any of the objects’ constraints. For example, if we want to find any product with a name of “shoe’ or any product that was $100, we can write:

var results = store.query([{
		price: 100
	}, {
		name: 'shoe'
	}]);

Indexing

Proper indexing is critical for maintaining good performance as databases scale. Local store implementations index all defined properties by default, ensuring that most queries can be executed reasonably quickly. When a query is executed, the SQL store will translate it to a SQL query which uses the indices, and the IndexedDB store will use one of the indices to find the objects. However, we may want to provide further optimization of the index usage. We can specify a preference on the indexed usage of each property to help guide the IndexedDB store towards which index to give highest priority. For example, we may want to give higher priority to the ‘name’ index than the ‘price’ index, if we expect more unique values from the former property values than the latter property values. We can configure these preferences by setting the preference values in the database configuration schema.

We may also want to disable indexing on some properties. For example, if we don’t anticipate filtering on a particular property, we probably don’t want to index it. It is also generally advisable to avoid indexing boolean values (IndexedDB doesn’t actually support querying by boolean values). We can turn off indexing by setting the indexed property to false. However, it is important that the schema include all properties that could be filtered on, even if they aren’t indexed, to ensure that the proper SQL table columns are created on WebSQL databases. We can now update our database configuration with our index preferences. Note that any time we change the database configuration, we also need to update the database version number:

	var dbConfig = {
		version: 2, // we increment this for every update
		stores: {
			product: {
				name: {
					preference: 10
				},
				price: {
					preference: 5
				},
				inStock: {
					indexed: false
				}

Indexing Arrays

It is possible to store arrays in property values, and search on these arrays. We can configure a schema to index each item in an array by setting multiEntry: true. Unfortunately, despite the IndexedDB specification including multiEntry, Internet Explorer does not support this feature (as of IE 11). On other browsers, however, you can use multiEntry, and then set the filter’s contains property to search for objects with an array containing those items:

var results = store.query({
		tags: {
			contains: ['fun']
		}
	});

In future versions, we hope to shim this capability for Internet Explorer.

Additional Store Wrappers

In addition to the local database stores, we have added two more store wrappers as part of the 1.10 release that can provide helpful extra functionality, particularly in combination with the local database stores.

Transaction Store Wrapper

The dojox/store/transaction store wrapper provides support for storing multiple operations in a transaction log, and eventually sending these to a server in a single commit. The transaction store extends the dojo/store/Cache, and takes a single options arguments where we can define the three stores to compose its functionality:

  • Master store – This store is the canonical source of data. Any uncached data is retrieved from the master store, and committed operations are ultimately sent here. This should be assigned to the masterStore property of the single options argument to the transaction store function. This is typically a JsonRest store or something similar, that can retrieve and deliver data to the server.
  • Caching store – Any new, updated, or removed data is stored in the caching store, so that data can be accessed before it is committed to the master store. This should be assigned to the cachingStore property of the options argument. This is typically a LocalDB or a Memory store that will be storing the data locally.
  • Transaction log store – This store records a log of the operations that are part of the current transaction. This will default to a Memory store but may be any client-side store. For example, local database stores might be used to persist operations while offline so they can be sent to the server later when connectivity is restored. This can be set in the transactionLogStore property of the options argument to the transaction store function. If you are using LocalDB stores, you would most likely want to use the LocalDB stores for both the caching store and the log store.

As an example, you can create a transaction store and start and commit a transaction like:

require([
	'dojox/store/transaction',
	'dojox/store/LocalDB',
	'dojo/store/JsonRest'],
function(transaction, LocalDB, JsonRest){
	var masterProducts = new JsonRest({
		target: '/path/products/on/server'
	});
	var localProducts = new LocalDB({
		dbConfig: dbConfig,
		storeName: 'products'
	});
	var transactionLogStore = new LocalDB({
		dbConfig: dbConfig,
		storeName: 'transaction-log'
	});
	var transactionalProductStore = transaction({
		masterStore: masterProducts,
		cachingStore: localProducts,
		transactionLogStore: transactionLogStore
	});
	var transaction = transactionalProductStore.transaction();
	transactionalProductStore.put(someUpdatedProduct);
	... other operations ...
	transaction.commit();

When you call commit(), the list of operations within the transaction are sent to the master store. By default the master store will execute the operations one-by-one, which may result in a series of requests to the server. If you wish to send data to the server in a single bulk operation, you may want to override the commit() method and define a mechanism to send a set of operations in a single request.

Priority Store Wrapper

The dojox/store/priority wrapper allows you to prioritize different store operations. Each store can have a default priority (set in the priority property), and individual operations can set a priority in the options argument (the second argument on each method). If multiple operations are concurrently executing, higher priority operations are executed next. This can be useful when background synchronization operations should be lower priority than user interface requests that require immediate response. The priority also defines the maximum number of operations that can execute concurrently. We can define a prioritized store by passing any asynchronous store to the wrapper:

var prioritizedStore = priority(originalStore);

Then, for example, we could execute a put operation:

prioritizedStore.put(someObject, {priority: 2});

By setting a priority of 2, we are indicating that this operation should go in the queue and be executed once there are 2 or less other operations executing. Note, that the priority store wrapper only works with asynchronous stores (synchronously stores execute all operations sequentially, so prioritizing wouldn’t be meaningful).

Conclusion

Together these store wrappers and implementations pave the way for building client-side applications with large local storage needs, while maintaining the same fast and sophisticated querying capabilities that we have learned to expect from the server. These storage capabilities are available through the convenient Dojo object store interface and can even be used with transaction logging and prioritization wrappers.

Learning more

We cover object stores and custom object store creation in depth in our Dojo workshops offered throughout the US, Canada, and Europe, or at your location. We also provide expert JavaScript and Dojo support and development services, to help you get the most from JavaScript, Dojo, and managing data efficiently within your application. If you’d like more information about having SitePen assist your organization with managing data efficiently with JavaScript, please contact us to discuss how we can help.

Comments

  • Harlan

    Hi Kris,
    I enjoyed this article as well as other articles you’ve written. I’m having problems figuring out how to get autoIncrement to work. Any suggestions?

    Thanks,

    Harlan…

  • Pingback: dstore 1.1 released | Blog | SitePen()

  • mrosalesdiaz

    Hi,
    You article was really useful for me.
    I’d like you could provide with some help in a problem I’m having.

    When enable autoincrement in “id” field then searching in another column using regular expression stop working and shows a message error:

    “Failed to execute ‘only’ on ‘IDBKeyRange’: The parameter is not a valid key.”

    I will be grateful for any hint about this

  • commentr

    var productStore = new LocalDB({
    dbConfig: dbConfig,
    storeName: ‘product’
    });

    Is this a synchronous call or is there a promise callback to make sure that the indexedDB is created?

  • The creation of the LocalDB instance is synchronous as it returns a new object (e.g. https://github.com/SitePen/dstore/blob/master/db/IndexedDB.js#L68 ). The reference internally to the database instance is async (e.g. this.db) and returns a promise ( https://github.com/SitePen/dstore/blob/master/db/IndexedDB.js#L120-L124 ), and methods such as query will wait until that promise is resolved (e.g. https://github.com/SitePen/dstore/blob/master/db/IndexedDB.js#L120-L124 ).