Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at NCDevCon 2011 (Raleigh, NC) with:

My Safari Browser SQLite Database Hello World Example

By Ben Nadel on

After watching Ray Camden give his "Beginning SQLite Database Development for AIR" presentation yesterday, I was feeling very inspired. I've seen SQLite before, but I've never done anything with it. I don't have anything installed for AIR development (I'll be switching to ColdFusion Builder soon enough); so, I figured I would experiment with SQLite in the context of a browser. Currently, WebKit - the browser engine that powers both Desktop and Mobile Safari browsers - has support for SQLite databases that can be utilized within a standard HTML web page. This is actually perfect for what I want to be looking into right now since I'd love to convert my Dig Deep Fitness iPhone app into a rich-client, SQLite web-based application chock full of HTML5 goodness!

 
 
 
 
 
 
 
 
 
 

Seeing as this is the first time I am using SQLite, please view this more as an experiment and less so as a "Teaching" opportunity. In fact, some of the things that I read about SQLite seem to conflict slightly with code that I saw Ray use in his presentation. Part of that might be the difference between Safari and AIR - part of that might just be my lack of depth in understanding. For example, the SQLite documentation that I read only talked about "ordered parameters" where as Ray's code made use of both ordered and "named parameters." Also, Ray was able to open database connections in both a Synchronous and Asynchronous fashion, whereas the documentation that I read seemed to only allow for Asynchronous connectivity. But again, this is the first time I ever looked into this SQLite stuff, so chances are likely that I just couldn't find the right documentation.

That said, for this experiment, I wanted to create a very simple, "Hello World," type situation. I have a single SQLite database with a single table, girls, that has an auto-incrementing ID column and a NAME column. I then have a simple web form that allows me to add girls to the list; and, I have a single link that allows me to clear all the girls from the list. Again, just trying to keep it simple for my first time.

  • <!DOCTYPE HTML>
  • <html>
  • <head>
  • <title>Safari SQLite Hello World Example</title>
  • <script type="text/javascript" src="jquery-1.4.2.min.js"></script>
  • <script type="text/javascript">
  •  
  • // The first thing we want to do is create the local
  • // database (if it doesn't exist) or open the connection
  • // if it does exist. Let's define some options for our
  • // test database.
  • var databaseOptions = {
  • fileName: "sqlite_helloWorld",
  • version: "1.0",
  • displayName: "SQLite Hello World",
  • maxSize: 1024
  • };
  •  
  • // Now that we have our database properties defined, let's
  • // creaete or open our database, getting a reference to the
  • // generated connection.
  • //
  • // NOTE: This might throw errors, but we're not going to
  • // worry about that for this Hello World example.
  • var database = openDatabase(
  • databaseOptions.fileName,
  • databaseOptions.version,
  • databaseOptions.displayName,
  • databaseOptions.maxSize
  • );
  •  
  •  
  • // -------------------------------------------------- //
  • // -------------------------------------------------- //
  •  
  •  
  • // Now that we have the databse connection, let's create our
  • // first table if it doesn't exist. According to Safari, all
  • // queries must be part of a transaction. To execute a
  • // transaction, we have to call the transaction() function
  • // and pass it a callback that is, itself, passed a reference
  • // to the transaction object.
  • database.transaction(
  • function( transaction ){
  •  
  • // Create our girls table if it doesn't exist.
  • transaction.executeSql(
  • "CREATE TABLE IF NOT EXISTS girls (" +
  • "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
  • "name TEXT NOT NULL" +
  • ");"
  • );
  •  
  • }
  • );
  •  
  •  
  • // -------------------------------------------------- //
  • // -------------------------------------------------- //
  •  
  •  
  • // Now that we have our database table created, let's
  • // create some "service" functions that we can use to
  • // touch the girls (no, not in that way - perv).
  •  
  • // NOTE: Since SQLite database interactions are performed
  • // asynchronously by default (it seems), we have to provide
  • // callbacks to execute when the results are available.
  •  
  •  
  • // I save a girl.
  • var saveGirl = function( name, callback ){
  • // Insert a new girl.
  • database.transaction(
  • function( transaction ){
  •  
  • // Insert a new girl with the given values.
  • transaction.executeSql(
  • (
  • "INSERT INTO girls (" +
  • "name " +
  • " ) VALUES ( " +
  • "? " +
  • ");"
  • ),
  • [
  • name
  • ],
  • function( transaction, results ){
  • // Execute the success callback,
  • // passing back the newly created ID.
  • callback( results.insertId );
  • }
  • );
  •  
  • }
  • );
  • };
  •  
  •  
  • // I get all the girls.
  • var getGirls = function( callback ){
  • // Get all the girls.
  • database.transaction(
  • function( transaction ){
  •  
  • // Get all the girls in the table.
  • transaction.executeSql(
  • (
  • "SELECT " +
  • "* " +
  • "FROM " +
  • "girls " +
  • "ORDER BY " +
  • "name ASC"
  • ),
  • [],
  • function( transaction, results ){
  • // Return the girls results set.
  • callback( results );
  • }
  • );
  •  
  • }
  • );
  • };
  •  
  •  
  • // I delete all the girls.
  • var deleteGirls = function( callback ){
  • // Get all the girls.
  • database.transaction(
  • function( transaction ){
  •  
  • // Delete all the girls.
  • transaction.executeSql(
  • (
  • "DELETE FROM " +
  • "girls "
  • ),
  • [],
  • function(){
  • // Execute the success callback.
  • callback();
  • }
  • );
  •  
  • }
  • );
  • };
  •  
  •  
  • // -------------------------------------------------- //
  • // -------------------------------------------------- //
  •  
  •  
  • // When the DOM is ready, init the scripts.
  • $(function(){
  • // Get the form.
  • var form = $( "form" );
  •  
  • // Get the girl list.
  • var list = $( "#girls" );
  •  
  • // Get the Clear Girls link.
  • var clearGirls = $( "#clearGirls" );
  •  
  •  
  • // I refresh the girls list.
  • var refreshGirls = function( results ){
  • // Clear out the list of girls.
  • list.empty();
  •  
  • // Check to see if we have any results.
  • if (!results){
  • return;
  • }
  •  
  • // Loop over the current list of girls and add them
  • // to the visual list.
  • $.each(
  • results.rows,
  • function( rowIndex ){
  • var row = results.rows.item( rowIndex );
  •  
  • // Append the list item.
  • list.append( "<li>" + row.name + "</li>" );
  • }
  • );
  • };
  •  
  •  
  • // Bind the form to save the girl.
  • form.submit(
  • function( event ){
  • // Prevent the default submit.
  • event.preventDefault();
  •  
  • // Save the girl.
  • saveGirl(
  • form.find( "input.name" ).val(),
  • function(){
  • // Reset the form and focus the input.
  • form.find( "input.name" )
  • .val( "" )
  • .focus()
  • ;
  •  
  • // Refresh the girl list.
  • getGirls( refreshGirls );
  • }
  • );
  • }
  • );
  •  
  •  
  • // Bind to the clear girls link.
  • clearGirls.click(
  • function( event ){
  • // Prevent default click.
  • event.preventDefault();
  •  
  • // Clear the girls
  • deleteGirls( refreshGirls );
  • }
  • );
  •  
  •  
  • // Refresh the girls list - this will pull the persisted
  • // girl data out of the SQLite database and put it into
  • // the UL element.
  • getGirls( refreshGirls );
  • });
  •  
  • </script>
  • </head>
  • <body>
  •  
  • <h1>
  • Safari SQLite Hello World Example
  • </h1>
  •  
  • <form>
  • Name:
  • <input type="text" name="name" class="name" />
  • <input type="submit" value="Add Girl" />
  • </form>
  •  
  • <h2>
  • Girls
  • </h2>
  •  
  • <ul id="girls">
  • <!-- To be populated dynamically from SQLite. -->
  • </ul>
  •  
  • <p>
  • <a id="clearGirls" href="#">Clear Girls</a>!
  • </p>
  •  
  • </body>
  • </html>

As you can see, I have three service methods that wrap around database access - getGirls(), saveGirl(), and deleteGirls(). Since SQL execution appears to be asynchronous in Safari's SQLite, each of these methods accepts a callback function to be executed when the encapsulated SQL statement has finished and returned. Beyond that, I don't want to go into too much detail as I am sure that I will start to get explanations wrong.

SQLite seems like a very cool, very powerful database. And, the fact that you can access and leverage it directly from within the Safari browser (think iPhone) makes it all that much cooler. This is only my first time playing with SQLite, but I am already fantasizing about what I can do with my Dig Deep Fitness application in the near future - I'm picturing some awesome with local storage, cache manifests, and data synchronization... oh baby, oh baby!




Reader Comments

Ben,

Offline Web App development requires a lot of planning, which is probably why it hasn't caught on in a big way. It's good that you're helping to raise awareness that it exists.

Everyone is so selfish and stingy with their WiFi, as they're sometimes forced to be by governmental mandates to encrypt. And cell phone access to data is as constantly prone to interruption as their phone calls are. The ability for a web page to save off its data locally and wait for connectivity to sync up to a server is a future that's too slow coming.

AIR helps. Heck, the now-defunct Google Gears helped. But offline apps are still not everywhere (sigh).

What I would like to see are some UDFs from Adobe that give cfscript the same syntax as JavaScript and SQLite to save off data to the database. (Does AIR do that?) And of course the ability to say (cfscript src="savedata.js"). Wouldn't it be great to use the exact same functions on the browser as you use on the server to save data? And not have to code everything twice, once for browser, once for server?

Thanks again for this article.

Reply to this Comment

Thank you for the example. I've never really used that before but I consider it now. It might come in use as a cache for very database intensive web applications as well. I have to try to tweak some performance by caching search results over a browser-window-lifetime ;)

Reply to this Comment

@Steve,

Yeah, it definitely takes more planning. Just this morning, I was thinking about the concept of creating user accounts. Take something like my Dig Deep Fitness app - you need to create a username / password for it. If you get to the mobile site, I guess you need to create a login (email) / password. These credentials then need to be loaded into the live site (to make sure they are valid), and then, I suppose, cached in the local database as well for when the user logs into their mobil / SQLite version of the site. This, in and of itself, seems much more complicated than anything I've done before with logins :)

And then, I guess at some point, there needs to be an explicit sync to the live site? Or does that sync happen automatically when it can.

In any case, this seems like a really fun and challenging thing to start thinking about.

@Alex,

Yeah, this is totally new for me as well. Hopefully, I'll have some more tinkering with this soon.

Reply to this Comment

Ben,

In the absence of (cfscript src="something.js"), here's an approach that helps:

(1) Define a string that represents an update to the database (XML, JSON, comma/tab/column-delimited text, whatever).
(2) Write your server-side update routine to accept that string as input. Code it to be used via cfinclude, expecting some Variables or Request scope data to be predefined.
(3) Write a Web Service that accepts username, password and that string as its cfarguments. Make the Web Service copy the data into the appropriate scope and cfinclude the update routine.
(4) On the browser, if the online flag is false, save the string to the SQLite database.
(5) When you come back online and you have cached updates in SQLite, call the Web Service.
(6) When you're online and don't have cached updates, post to the server (the form's action page). On the server, use the Form scope data to build the same string format and cfinclude the update routine.
(7) If the browser has JavaScript off, you're screwed on (4) and (5), but you can always do (6) when they're online.

This is called Service Oriented Architecture, where the cfincluded update routine is the shared service. I've created applications that are capable of being used that way, but they don't have any pressing need to enter data while the user is offline. I work at a government agency, and the disaster assistance team has a need to do stuff offline (walking through the rubble of Earthquakes or oil spills, with no 3G or WiFi). But I'm not maintaining the apps that team uses. I'm licking my chops to code an offline app, but currently have no need to. Hope the explanation above helps you, however.

Steve

Reply to this Comment

@Steve,

I think I see what you're saying. What I'd like to do, however, is always use the local database when possible, and only sync to the live site when necessary or explicitly asked to do so. Even when I do have 3G available (which, in a gym is more likely than WiFi - at least in gyms I've been to), the live-updates are not fast... and if you lose connectivity for a few minutes, you're dead in the water.

That's why, I figure I'd always go local until I need to sync something - just for speed.

That said, I think for account creation, I would always want the new accounts to be verified via the web service to make sure that the login is available and to issue a new user ID (internally). You have a great point, however, to pass the username and password along with all the web service calls. That is something that I had not even thought of; having to verify your login when interacting with the web service seems crucial if people might try to hack the app locally and upload corrupt data (or data not belonging to the correct user).

Now, I just need to come up with a nice, small example, if possible.

Reply to this Comment

@Ben,

As for working offline even when you CAN work online, many offline-capable browsers have a "Work Offline" menu item that allows you to override the DOM's online flag. Firefox and Opera for-sure do, but I don't see it in Safari 5. I'm writing from a Mac right now so I can't check MSIE. In any case, with the right browser, you can already do what you want.

As for passing login on the Web Service architecture, that's what truly makes it SOA. Other apps can call the same WS (if they have permission to do so, based on their login, of course). I like the idea of cfincluding if you're on the same server as the update routine. What an overhead saver. Easier debug too.

And as for a nice, small example, how about a Web page to record station, sets, reps and weight when you're in the gym?

Steve

Reply to this Comment

@Steve,

That is basically what Dig Deep Fitness does right now (exercise, sets, reps). But, it's slow. I want to speed it up and "modernize" it with some local database action.

If nothing else, it'll get me thinking outside the box.

Reply to this Comment

Ben, I tested it in Safari 5 and Chrome 5 it works fine but it doesn't seems to work on FireFox 3.6.8, any ideas why?

Also, how did you get that SQLite Administration tool down there on your browser is that some kind of browser extension?

Thanks,
Marcin.

Reply to this Comment

@Marcin,

I don't believe FireFox supports SQLite databases yet. As far as the admin, that's part of the core Safari developer console.

Reply to this Comment

@Ben, as for your previous post about your iPhone app and this is all just in words:

When user registers add user to live website/database and stored credentials at that point locally. Whenever user logs off remove local credentials so that user must login next time.

Let's say user is logged in, the local credentials are passed to live web just at the beginning of the application launch.

Usage of the login functionality is done through live web server and all that user data will be stored locally.

One option to synch is to send SQLite file to live web/server for backup and recovery at the close of the app.

Another option is to do it through live through triggers. When user inserts/updates or deletes a record from/to local table a trigger fires on such events to auto synch with live web server behind the scenes.

Great blog,
Thanks Ben.

Reply to this Comment

@Marcin,

So, are you saying that they have to log-in every time they use the locally cached app? Or, just after any time they have logged-out?

Perhaps I can let them use the local database without authorization; but, ask for credentials anytime they need to sync with the live database. Ultimately, if the database is local, I think we have to accept that security cannot be 100% solid since you can probably create your own Javascript that will update the local database (if you were so intent on hacking it).

The key, is to make sure that authorization is provided when the central, web-based database gets augmented?

I really need to think more about data syncing in general; looks like a dark art to me right now.

Reply to this Comment

@Ben, I was thinking that they have to login every time the user have logged-out, and also do authentication (send locally stored credentials to the server) every time the app is being started/opened and/or the user requests to sync the data. There is always a potential that the user's locally stored data might be hacked but that's just that one user's data rather than all the users that are stored on the server side. I would probably not worry about unless you're storing some financial or personal information.

Reply to this Comment

@Marcin,

I'm sure I'll play around with this stuff eventually. I just experimented with offline data sync for the first time the other day. Wow - that was waaaay more complicated than I hoped it would be. The more I dig, the more complicated local database storage becomes.

Reply to this Comment

Hi Ben,

It is a very good article. I am interested in this area and couple of questions, if you could help me.

- I tried this example on Chrome on Win and it worked. But if you delete cache, then the sqlite data seems to get wiped out. This seems pretty dangerous as one cannot hope that no cache deletion ever happens. Surprisingly, on Safari, cache deletion still retained the sqlite data.

- If I enter data via Chrome and access same url via Safari, it doesn't show the data. Is the sqlite db specific to each browser. Is it possible to connect to a central db and access it from there. So if I had opened in Chrome earlier and saved and now I open in Safari, I am still able to see the data.

- The syncing with a live Mysql db would be important I guess. Any ideas if this needs to be done auto in the background, how one could go about. The requirement would be something like, the moment the phone gets 3G signal or if it gets connected to wifi, then sync.

- Is there a limit on the sqlite db size and limit on the size of each transaction ?

Thanks in advance.

Regards

Nilesh

Reply to this Comment

@Nilesh,

The databases are definitely browser-specific. As such, things entered in Safari won't be available to Chrome. I am sure there also all kinds of domain-level restrictions as well (but I am not sure how these work with things like sub-domains).

I am surprised that clearing the cache deleted the database. I was told that the cache and the database are typically separated. But, again, these are all kind of "cutting edge" features and are all still evolving.

Each browser has a different limit I think, but I am not sure. I think some browsers will ask you every time you want to increment it while other will simply just stop you at one point.

Although, on that last point, I might be getting confused with the offline application cache, which is another kind of local storage.

Now data syncing - that's just a bear of a topic. I have only played around with it once and it was enough to scare me sufficiently:

http://www.bennadel.com/blog/2005-Experimenting-With-Offline-Data-Synchronization-Using-jQuery-And-ColdFusion.htm

As one developer put it, "syncing offline databases is a non-trivial task, like putting a man on the moon." It is not easy :D

I've heard that things like LiveCycle Data Services can make this kind of stuff easier; but I have no experience with that.

Reply to this Comment

Ben,

Great post, very usefull, but I have a question, may be you have the answer, how can I make a backup of a database stored in iphone, obviously, it is a SQLite database in the iphone's safari browser.

Thanks for your time

Reply to this Comment

sorry, noobs question, what's safari extension you use to debug SQLite data at the bottom of video there?

thanks

Reply to this Comment

@Mauricio,

I am not sure I understand your question? Are you asking how to make a remote backup (say, on your sever) of a SQLite database on the phone?

@Katopz,

I am just using the built-in Safari Developer tools. They can be hard to find - hidden in the menus somewhere is the "Developer" sub-menu. Look at the console.

@Reinhard,

Thanks my man - glad you found this useful :)

Reply to this Comment

Hi Ben,

Thanks for your answer.

... And Yes, that is the question, I want to make one of two possible things.

1. How can I make a backup on-line of the database (the entire database) in a remote server?
2. Is there a way to make the backup over itunes software? I mean, is it possible to interact with the desktop software (itunes) to save a database's backup?

Thanks Ben for your time.

Mauricio M.

Reply to this Comment

@Mauricio,

That's a tough question. Probably, the easiest thing to do would be do put a flag on the rows to indicate whether or not they have been backed up. Or, perhaps create some sort of table that stores the last ID of each table that needs to be backed up; then, you could, from time to time, flush any newer records (based on ID) to the server.

I've never really done anything like this.

Reply to this Comment

Ben,

I ran across this posting and am new to Safari development, I cut/pasted your code and it doesn't seem to interact with the database.

I don't have air, that I'm aware of.

What am I missing?

It creates the db but then doesn't write to it.

Thanks.

Reply to this Comment

@Amy: I can't see your code, but I implemented something very similar to this, and the error reporting is non-existent without callbacks. I ended up adding callback functions for both success and failure (with alerts ... ugh). I had a problem where the table I had created was missing a column, so the insert failed for that -- but I would not have known without some debugging.

In your "transaction.executeSQL" function, make sure you have 4 arguments: the sql, any parameters (or []), and 2 functions to report success/failure:

function(tx, rs) {
alert("insert succeeded");
},
function(tx, error) {
alert('sql error: ' + error.message);
}

(tx = transaction, rs = recordset, error = an error structure)

Reply to this Comment

@Tom,

Thanks Tom! I had a config problem and finally figured that one out. I appreciate you following up though.

After I got over my initial problem and had the thing talking to me I was able to resolve any further problems.

This was a nice "hello database" example that Ben posted, and the only thing I'd change about it is adding in an error routine.

Cheers!

Reply to this Comment

Hi, Thanks for the codes.
I am still confused about how to use "callback" for my application.
Here is what I am trying to do: I have 2 tables
Girls[id, name, idBoy]
Boys[id, name]
I want to show the list of girls and for each the name of the boy they "like". In PhP, the code might look something like that:
$qGirls="SELECT `Girl`.`id`, `Girl`.`name`, `Girl`.`idBoy` FROM `Girls`";
$rqGirls=mysql_query($qGirls);
while ($rowGirl = mysql_fetch_array($rqGirls, MYSQL_NUM)) {
$myBoyId = $rowGirl[2];
$qBoys="SELECT `Boys`.`name` FROM `Boys` WHERE `Boys`.`id` = $rowGirl[2]";
$rqBoys=mysql_query($qBoys);
$herBoy = mysql_fetch_array($rqBoys, MYSQL_NUM);

echo("$rowGirl[1] likes $herBoy <br>");
};

I wrote an equivalent code for SQLite database but herBoy comes out as undefined. Basically the code is something like that:
tx.executeSql(selectAllGirls, [], function(tx, result) {
'for loop to get girls-name';
tx.executeSql(selectherBoy, [idBoy], function(tx, result) {
}
}

I understand I need to use callback. But how?
Thank you.

Reply to this Comment

@JM,

Ok, forget abou' it!!! I just realized that I should be using INNER JOIN to do it. Thanks though.

Reply to this Comment

@JM,

your question is valid even though you solved your problem, the error callback (4th) is a function following the success callback (3rd parameter).

You MAY want to post your inner join solution as many people will find your post when searching for answers.

Reply to this Comment

Very interesting code, the best I've found so far on the internet, thank you :-))))
The girls-example works with a table containing 2 columns ('id' and 'name'). Only the girls-name is saved in the table (and an 'id' for each record). But how does the 'INSERT INTO' command looks like if, for examle, the 'name', the 'age' and 'city' are saved in the girls-table ?

I tried to make the code work to do this, all works, with the exception of the INSERT INTO command. Can you help ?

Thank you
Daniel

Reply to this Comment

I copied your code to an htm file and ran it in safari 5.1.7 on Windows 7, with the developer console on.

It seems each time I run this I create a new sqlite_helloWord database but no tables get created and no records saved.

I am unsure where, physically, the sql files are located.

I have looked in the Applecomputer folders under the user appdata folder.

Reply to this Comment

@Kymberley,

Hi

Please right click on the page in safari. It will show Inspect element, go to Resources tab. on the left panel there we can able to see a database. click on that .....

Reply to this Comment

@Jayakrishnan

Hi I had already been able to display this console, although the right click is easier. I clicked on the page icon then develop, which gives you a choice of windows.

I can see that:
1. there is a girls table created
2. it has no elements
3. when I enter a new name,
a. A new database element appears.
b. I t has a girls table
c. it is empty as well
d. when I click on the old element girls table I get an error message.

Given that this blog is 2 years old, I would expect Ben to have move on quite a lot and maybe have different ways of doing this sort of exercise.

However it is pretty basic and once it works, the principalecould be used in a multitude of other apps.

Reply to this Comment

Hello guys,

I want to create login page in html5, javascript,
can any one help me in this please

Reply to this Comment

Hello guys,

I'm new to Sqlite specially in html5, JS
I want to create login page in html5, javascript, Sqlite
can any one help me in this please,
i will be thankfull

Reply to this Comment

hai ben,
this program is really good
i could understand the concept but i dint know how to save it and how to open it as you have done in the video can u give that details pls

Reply to this Comment

Hi Ben,

Would this example save database permanently on the disk or will get erased if I restart my device?

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.