Using the Sencha Touch 2.1 SQL Proxy

27 Replies

Note: This post has been updated. Please see https://druckit.wordpress.com/2013/04/02/revisiting-the-sencha-touch-2-2-sql-proxy/

Sencha introduced a WebSQL proxy in Sencha Touch 2.1 that enables you to easily create client-side relational databases using  Touch 2 methods that you are already familiar with. There are at least two advantages to using the SQL proxy over the well-proven localStorage proxy.

  1. The localStorage proxy can only utilize up to 5MB of data on the user’s machine. Web SQL will prompt the user to use more space on an as-needed basis. Theoretically, you could store gigs of data on the user’s machine, if they allowed you to.
  2. The localStorage proxy stores data as serialized JSON, which works great as a persistence mechanism, but isn’t very flexible. The SQL proxy stores data in relational tables that you can use native JavaScript methods to access as well as perform more complex operations such as multi-table joins.

Here’s a brief  example to help get you started:


Ext.require(['Ext.data.proxy.SQL']);

Ext.define("User", {
  extend: "Ext.data.Model",
  config: {
    fields: [ "firstName","lastName"]
  }
});

Ext.create("Ext.data.Store", {
  model: "User",
  storeId: 'Users',
  proxy: {
    type: "sql"
  }
});

Ext.getStore('Users').add([{
  firstName: 'Steve',
  lastName: 'Drucker'
}]);

Ext.getStore('Users').sync();

Image

Presently Sencha Touch 2.1 has some bugs when reading WebSQL data back into a store. Hopefully this will get fixed in the next point release.

27 thoughts on “Using the Sencha Touch 2.1 SQL Proxy”

    1. Steve Drucker

      Modern browsers have a database built into them (“WebSQL”) which is patterned after SQLLite. If you wanted to transfer the data into an enterprise database, you’d have to read the data into the store, then call the store’s model.setProxy() method to change the proxy over to AJAX, JSONP, Ext.Direct, etc. Then loop through the store calling model.save() to transmit each model instance to your application server where you would need to write some synchronization logic.

      Reply ↓
  4. Steve Drucker

    UPDATE: I received a notice yesterday from Sencha’s JIRA that the bugs that I reported regarding reading data back into Sencha Touch from localstorage have been fixed. So we should definitely be seeing those changes in the next point release of Touch!

    Reply ↓
  5. Jose

    Thanks for the tutorial – very useful. But is this useable? When I try it with a MVC structure – the SQL proxy fails with type error. You are also claiming the read back doesn’t work. So is this just a gimmick check off item on Sencha’s part and not really useable. I am going back to the local storage proxy – it appears to work.

    Reply ↓
    1. sdrucker Post author

      Well, I did say that there were issues with it. It’s not really usable in its current form, however, the bugs that I filed with Sencha have been logged as fixed – so we should see something usable come out in the next point release.

      Reply ↓
  6. Vinícius Andrade (@ViniAndrade_)

    Hi. I’m load the store and apapparently works, in Chrome console i see the data stored.But when i put the code Ext.getStore(‘Users’) and clear(), no data clean. It only work when I add the data and clean in the same execution (this example). Is there a way to load an existing database?

    Reply ↓
  7. Greg M. Silverman

    Indeed there is next to no documentation on this. However, I can’t get this to work. The data aren’t getting loaded to the SQLite database. Plus, I get “Uncaught TypeError: Object # has no method ‘getStore’…” Annoying, for such a straightforward operation.

    Thanks!

    Reply ↓
  8. JRS

    Thanks for the tutorial – will you be doing a MVC type tutorial? I’ve wasted quite a bit of time trying to make SQL work before finding your site. There is an extension written by Grgur that works – I was thinking about using it – don’t understand why Sencha just didn’t use that.

    Reply ↓
  9. Jose

    Steve, Sencha has a alpha version of ST2.2 – Have you had a chance to look at it and is it any better?
    Is there any issues with using just localstorage as opposed to SQL in sencha touch?

    Reply ↓
    1. sdrucker Post author

      Yes, the issues that I refer to in this blog post have been fixed in ST2.2

      No issues using localstorage, however, storing to local db could be more efficient if you wanted to write custom, cross-store joins. Also, you can ask the user to provision more than 5MB for your db — theoretically giving you unlimited storage potential.

      Reply ↓
  10. Samuel

    Hi! Does this works when packaging the app into a native app? Does data disappear when we have to update the native app to a new version? ? Thank you.

    Reply ↓
  11. Greg M. Silverman

    Hey Steve,
    I’ve been using the localstorage proxy for a fill-in until the sql proxy issues have been fixed. Do you think it’s ready tor prime time yet. In particular, reading/writing data from/to the store: Does it work? Associations: Are all foreign key constraints created properly and able to be utilized for linking associated data across models properly without any kludges? I hope the answer to all questions is a big thumbs up!! Thanks a lot!

    Reply ↓
  13. sdrucker Post author

    I’ll be working on the updates to Sencha’s official Touch 2.2 training course next week and will post my findings regarding the updated Sql Proxy. Based on my read of the release notes, it appears as though a ton of work has been done on it and several bugs have been resolved.

    Reply ↓
    1. Greg M. Silverman

      Cool! I look forward to hearing all about your excursions into ST 2.2.0 (I REALLY hope ST 2.2.0 is a very positive step forward, especially given all the time I have committed to converting my project from jQM/backboneJS to it – often wondering in the back of my mind whether I had made the right decision to go that route…)

      Reply ↓

Leave a comment