HTML5 SQLite for PhoneGap/Cordova

If you know Microsoft Entity Framework For .NET(EF), you will find this library is very similar to it, which is actually my goal. Like EF, the below cool features has been achieved in this library:

1. Code First Mode. The database and tables are defined/generated by javascript.
2. Data Migrations. When the database needs to upgrade, you only need to provide the version sql scripts and the upgrading will automatically run.
3. Repository. It means any add/delete/update will only take effect after the DbContext.prototype.saveChanges is called.
4. Linq-like query. e.g. db.users.where(...).where(...).orderBy(...).thenBy(...).toArray(callback).
5. Auto-increment field "id" is updated automatically on inserted. When a new item is inserted, the auto-increment field "id" will be updated automatically. Just like what EF does.

This library was originally from our Nova PhoneGap Framework. If you want to outsource your APPs development, or you are looking for PhoneGap/Cordova developers/programmers, you can contact our phonegap team.

1. Get Started

Please use google chrome to test, as I know FireFox doesn’t support HTML5 Sqlite for now.

Live demo: click here

Source code of the live demo: click here

=======================

Get started steps:

1.1 download all of the source code.

1.2 copy “core/nova.data.js” to your website, and reference it in your web pages.

1.3 define database

Reference in this section: DemoDbContext.js

Please refer to to see how to define a database.

In this library, the database and schema are generated by code, which is called “Code First” mode. When you call init method of your database instance, the database will be created (if not exists) or updated (if version changes).

When your database schema or data need to be updated on initialization, you need to increase the “version” parameter. If you don’t want to re-create the database (data lost), you will need to override the getMigrations method that allows you to execute sql scripts for each version change.

1.4 define tables

Reference in this section: User.js

Every table is actually a JavaScript class that inherits (extends) from nova.data.Entity. See the reference in this section.

A smart point is that the data type of a field if guessed from the default value. For instance, “createdTime” is a date data type in the above code. Currently, supported data types are date, integer, string, boolean.

To add the table to the database, back to the DemoDbContext.js file, see this line:

this.users = new nova.data.Repository(this, User, "users");

When the database is initialized, all tables will be created.

Important note: don’t define “id” to your table as it’s already defined in nova.data.Entity by default.

1.5 define global single database instance

This is not mandatory, but normally we define a global variable to keep the database instance, so that there is only one database instance during the lifetime of your APP.

Here demo.db.getInstance() returns the initialized database instance, that will be widely used in the APP.

1.6 access to the database

Normally we wrap all database access in a layer of “services”. In this document, we have a UserService.js.

1.6.1 query

Query the first or default:

get: function(id, callback) {
    demo.db.getInstance().users.firstOrDefault(callback, "id=" + id);
}

Query all:

getAll: function(callback) {
    demo.db.getInstance().users.toArray(callback);
},

Query by where expressions:

get: function(id, callback) {
    demo.db.getInstance().users.where("id=" + user.id).firstOrDefault(callback);
}

You can use many where expressions:

 demo.db.getInstance().users.where("birthYear>=1980").where("birthYear<1990").toArray(callback);

1.6.2 insert

The parameter “user” is an instance of “User” class that inherits from nova.data.Entity.

add: function(user, callback) {
    var db = demo.db.getInstance();
    user.lastUpdatedTime = null;
    db.users.add(user);
    db.saveChanges(callback);
}

Important feature: when the “user” is inserted into database, the user.id will be updated.

1.6.3 update

Please pay attention to the 5th line of the below code, which marks the “dbUser” to be updated on saveChanges.

update: function(user, callback) {
    var db = demo.db.getInstance();
    db.users.where("id=" + user.id).firstOrDefault(function(dbUser) {
        dbUser.updateFrom(user);
        db.users.update(dbUser);
        db.saveChanges(function() {
            user.lastUpdatedTime = dbUser.lastUpdatedTime;
            callback && callback();
        });
    });
}

1.6.4 delete

The below code deletes a user record. The “user” must have id correctly set.

 deleteUser: function(user, callback) {
    var db = demo.db.getInstance();
    db.users.remove(user);
    db.saveChanges(callback);
}

Or you can delete by where expression. (no need to call saveChanges then)

deleteUser: function(id, callback) {
    var db = demo.db.getInstance();
    db.users.removeByWhere("id=" + id, callback);
}

Or you can remove all, clear the table:

db.users.removeAll(callback);

1.7 explore more

1. try the live demo and read its source code.
2. [complete documentation] is coming...

=======================

You can donate to us if you like this library.