SQLite provider

The uses of a clientside database

Client side data storage is no longer the exception, but rather the rule, and not just in native mobile applications and thick clients, but also in HTML5 web applications. The traditional use for a clientside database is to store user preferences, settings, various local data that the web application needs to remember such as favorites lists and shopping carts.

This is still very much needed, but another use is becoming more and more prevalent as clients become smarter and more powerful. You might want to offload server data to the client, which will have the double benefit of making user experience snappier and more responsive, and reducing the server workload, possibly saving you on bandwith and hosting. Many cloud services are billed per request or by data served, so offloading data to the client and saving on requests will directly translate to saving in costs.

Eg. as an e-newspaper, you might want to cache the entire issue on the client, with all articles, images, navigation and metadata. That way, the user will be able to flick between articles with the ease of traditional printed paper, and your servers will also be shielded from repeated requests to the same resources.

What helps you further in this is that with JayData, the programming paradigm for the remote service in the cloud (eg. OData) and the local storage is identical, so there is no need to write the same logic twice.

The JayData added value

The SQLite / WebSQL provider is one of our most developed and used providers, allowing you to make use of the full power of the JavaScript Language Query (JSLQ).

As a highlight, it allows the use of string functions and numeric operators in the map statement, and these operations are converted to SQL and run on the database. This makes it especially fit for creating viewmodels for your application. Say, to display a short preview from long articles without retrieving the full text each time:

$news.context.Article.map(function(e) {

    return { title: e.MainTitle.toUpperCase(), preview: e.Text.substr(0,100) };


See our examples entry on how JayData code compares to using the native WebSQL API.

Provider peculiarities

Since the SQLite / WebSQL providers are code-first providers, meaning that they create the database schema based on the Javascript model definition, you need to specify in the context creation how to handle schema changes (if there is already a database of the specified name, but with different schema).

This is a context instantiation with the sqLite provider. As you can see, it takes two additional parameters, databaseName and dbCreation.

$org.context = new $org.types.OrgContext({ name: “sqLite”, databaseName: “Organization”, dbCreation: $data.storageProviders.DbCreationType.DropTableIfChanged });

While databaseName is straightforward, dbCreation needs a little explanation. These are the allowed values:


  • DropTableIfChanged: If a table exists with different columns (eg. from a previous version), JayData will drop and re-create it. This will result in data loss between version updates. (This behavior is different in case of using IndexedDB – it is dropped if there is any change in the key fields of the data model).
  • DropAllExistingTables: This option is for testing puposes primarily. The entire database will be deleted and recreated upon each run.

Note: the namespace of DbCreationType has been changed, now it can be found in $data.storageProviders namespace, not under $data.types.storageProviders.webSql or indexedDb. We changed the namespace to provide cleaner, provider-independent API.

WebSQL/SQLite Provider Pro

In case your app will manage thousands of records and you want to opimize the performance with indices or you want to use WebSQL with transactions, give a try to JayData WebSQL Provider Pro – more info

, ,