all posts

Using browser SQLite’s LIMIT/OFFSET to page results with AJAX

Published to Blog on 22 Feb 2011

Another post mostly for me.

I spent some time tonight trying to solve a problem in a mobile/HTML 5 app I am building. I have two lists that pull data from a client-side database that could possibly contain many records. Rather than pull all the results on page load and dynamically build a huge list I decided to look into doing some sort of paged results. I wanted my results to look something like this:


Luckily SQLite’s SELECT statement supports LIMIT and OFFSET statements that are ideal for supporting AJAX-style paging. Following is psuedocode for the implementation:

function getAccounts(pageSize, pageIndex) {
  // setup the db transaction
  var sql = 'SELECT * FROM accounts ORDER BY name LIMIT ' \+ pageIndex + ',' \+ pageSize + ';';

  /// execute the sql statement

  if (result == null || result.rows.length == 0) {
    // hide the "More" button
  } else {
    for (var i = 0; i < result.rows.length; i++) {
      // dynamically add the item to the list

Dan Hounshell
Web geek, nerd, amateur maker. Likes: apis, node, motorcycles, sports, chickens, watches, food, Nashville, Savannah, Cincinnati and family.
Dan Hounshell on Twitter