Introducing Web SQL Databases

by .

Please beware that as of 18th November the W3C is no longer actively working on the Web SQL Database specification.

The Web SQL database API isn’t actually part of the HTML5 specification, but it is part of the suite of specifications that allows us developers to build fully fledged web applications, so it’s about time we dig in and check it out.

What’s in the box?

If you haven’t guessed from the overly verbose specification title, Web SQL Databases is a spec that brings SQL to the client side. If you have a back-end developer’s background, then you’ll probably be familiar with SQL and happy as a pig in muck. If not, you might want to learn some SQL before you start hacking around, Google’s your friend here.

The specification is based around SQLite (3.1.19), but having come from MySQL myself, it’s all pretty much the same (sorry for the sweeping statement!).

For an example of Web SQL Databases working, have a look at the Twitter HTML5 chatter demo I put together.

order levitra
It uses SQL and the WHERE clause to narrow down the recent chat about HTML5 on Twitter (it will work in Safari, Chrome and Opera 10.50).

There are three core methods in the spec that I’m going to cover in this article:

  1. openDatabase
  2. transaction
  3. executeSql

Support is a little patchy at the moment. Only Webkit (Safari, SafariMobile and Chrome) and Opera 10.50 (ATOW alpha on Mac) support web databases. Fellow Doctor Bruce Lawson has told me that Firefox are holding off as they feel there’s a better implementation than SQLite (though I hope it’s similar, whatever they pick). Either way, I’d definitely recommend checking out the SQLite documentation for the functions that are available.

Because of this patchy support and the simple fact that Webkit had implemented the database spec some time ago, the spec on the W3C is now slightly ahead of the implementations in Safari, while Webkit is still catching up. On the other hand, since Opera has only just added support, it’s closer to the spec (I’ll mention the differences as we go along).

Nonetheless, it’s fun to play with, so let’s get playing!

Creating and Opening Databases

If you try to open a database that doesn’t exist, the API will create it on the fly for you. You also don’t have to worry about closing databases.

To create and open a database, use the following code:

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);

I’ve passed four arguments to the openDatabase method. These are:

  1. Database name
  2. Version number
  3. Text description
  4. Estimated size of database

The missing feature of openDatabase (I’m not sure when it was added) is the fifth argument:

  1. Creation callback

The creation callback will be called if the database is being created. Without this feature, however, the databases are still being created on the fly and correctly versioned.

The return value from openDatabase contains the transaction methods, so we’ll need to capture this to be able to perform SQL queries.

Estimated database size

From the tests I’ve run, only Safari prompts the user if you try to create a database over the size of the default database size, 5MB. The prompt is shown the image below, asking whether you want to grant the database permission to scale up to the next size of database — 5, 10, 50, 100 and 500MB. Opera, on the other hand, builds the database without complaining, which I expect might change later as it’s still in alpha.

Webkit database size prompt

Versions

I could be wrong, but everything I’ve tested so far says that versioning in SQL databases is borked. The problem is this:

If you upgrade your database to version 2.0 (e.g., there are some important schema changes since version 1.0), how do you know which visitors are on version 1.0 and which are on version 2.0?

The version number is a required argument to openDatabase, so you must know the version number before you try to open it. Otherwise, an exception is thrown.

Also, changeVersion, the method to change the database version, is not fully supported in Webkit. It works in Chrome and Opera, but not in Safari or Webkit. Regardless, if I can’t determine which version of database the user is on, then I can’t upgrade the user.

A possible workaround is to maintain a state database, something like the ‘mysql’ database in MySQL. This way, you would only have one version of this state database, and within this you would record the current version of any databases that control your application. It’s a hack, but it works.

Transactions

Now that we’ve opened our database, we can create transactions. Why bother with transactions instead of just running our SQL? Transactions give us the ability to rollback. This means that if a transaction — which could contain one or more SQL statements — fails (either the SQL or the code in the transaction), the updates to the database are never committed — i.e. it’s as if the transaction never happened.

There are also error and success callbacks on the transaction, so you can manage errors, but it’s important to understand that transactions have the ability to rollback changes.

The transaction is simply a function that contains some code:

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);
db.transaction(function (tx) {
  // here be the transaction
  // do SQL magic here using the tx object
});

I recently uploaded a demo to html5demos.com that demonstrates a transaction rollback in action: Web SQL database rollback demo

In the nightly builds of the browsers, we also have db.readTransaction, which allows only read statements to run on the database. I assume there are performance benefits to using a read-only readTransaction instead of a read/write transaction, most probably to do with table locking.

Now that we’ve got our transaction object (named tx in my example) we’re ready to run some SQL!

executeSql

This is the funnel of love for all your SQL goodness. executeSql is used for both read and write statements, includes SQL injection projection, and provides a callback method to process the results of any queries you may have written.

Once we have a transaction object, we can call executeSql:

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);
db.transaction(function (tx) {
  tx.executeSql('CREATE TABLE foo (id unique, text)');
});

This will now create a simple table called “foo” in our database called “mydb”. Note that if the database already exists the transaction will fail, so any successive SQL wouldn’t run. So we can either use another transaction, or we can only create the table if it doesn’t exist, which I’ll do now so I can insert a new row in the same transaction:

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);
db.transaction(function (tx) {
  tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)');
  tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "synergies")');
});

Now our table has a single row inside it. What if we want to capture the text from the user or some external source? We’d want to ensure it can’t compromise the security of our database (using something nasty like SQL injection). The second argument to executeSql maps field data to the query, like so:

tx.executeSql('INSERT INTO foo (id, text) VALUES (?, ?)', [id, userValue]);

id and userValue are external variables, and executeSql maps each item in the array argument to the “?”s.

Finally, if we want to select values from the table, we use a callback to capture the results:

tx.executeSql('SELECT * FROM foo', [], function (tx, results) {
  var len = results.rows.length, i;
  for (i = 0; i < len; i++) {
    alert(results.rows.item(i).text);
  }
});

(Notice that in this query, there are no fields being mapped, but in order to use the third argument, I need to pass in an empty array for the second argument.)

The callback receives the transaction object (again) and the results object. The results object contains a rows object, which is array-like but isn’t an array. It has a length, but to get to the individual rows, you need to use results.rows.item(i), where i is the index of the row. This will return an object representation of the row. For example, if your database has a name and an age field, the row will contain a name and an age property. The value of the age field could be accessed using results.rows.item(i).age.

That’s all you should need to get started with Web SQL Databases. I’m certain that mini JavaScript libraries are going to emerge to help support working with databases. If you want to find out more about SQL databases (shameless self promotion begins) I just finished the storage chapter for Introducing HTML5, which I’m writing with fellow Doc Bruce, so check that bad boy out too!

Demos

94 Responses on the article “Introducing Web SQL Databases”

  • […] posted here: Introducing Web SQL Databases | HTML5 Doctor Share and […]

  • BWRic says:

    “Firefox are holding off as they feel there’s a better implementation than SQLite”

    Any idea what they’re looking to implement? Aren’t they using SQLite for bookmarks, history etc in the browser now so I’m surprised they don’t like it here.

  • Shelley says:

    I wouldn’t expect this spec to go anywhere because of the external dependencies. It’s important that people reading this post be aware that there is a strong possibility of this not going anywhere.

    From the spec:

    “This specification has reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path. Until another implementor is interested in implementing this spec, the description of the SQL dialect has been left as simply a reference to Sqlite, which isn’t acceptable for a standard. Should you be an implementor interested in implementing an independent SQL backend, please contact the editor so that he can write a specification for the dialect, thus allowing this specification to move forward.”

    Think about what this is asking for. Now, how realistic do you believe this to be?

    I didn’t cover it in my book.

  • Remy Sharp says:

    @Shelly – I read the spec and had that exact same thought: I can’t imagine that there’s going to be a spec’ed version of SQL any time soon (personally I half expect that this doesn’t ever happen)!

    That doesn’t mean it shouldn’t be discussed or played with. There are platforms where the browser is known to developers (i.e. not a great example, but people writing iPhone web apps), so this is one technology that can be exploited.

  • Shelley says:

    Remy, true: if you’re working in an environment outside of the normal web, such as with iPhone development, then all you need worry about is the implementation supported by Safari. Same, with other smart phones and using Opera mini, or whatever.

    But I don’t think there will ever be a W3C “standard” to provide a level of stability that a lot of companies are going to way. I think this one will always be a maverick implementation, because it will always be dependent on SQLite.

    I suppose Google could create its own relational database backend for this — but you know, the company hasn’t been making the best choices lately, so I would hesitate to use a SQLite alternative it comes up with.

    People have to think about this, and ask themselves: what does it provide that localStorage won’t provide? And do they really want to embed SQL into their JavaScript applications?

    Maybe they do, and they can afford the limited implementation. That’s cool, as long as people’s expectations about where all of this is going are realistic. And folks know this is not a component of HTML5.

  • Shelley says:

    Sorry, “going to want”, not “going to way”.

  • Mark says:

    Slightly off topic – ‘relative_time’ doesn’t seem to be working correctly. All tweets are listed as ‘[posted less than 5 seconds ago]’.

  • edvakf says:

    @BWRic

    “Firefox are holding off as they feel there’s a better implementation than SQLite”

    This is not exactly true. Mozilla and Microsoft’s thoughs are that requiring a specific version of SQLite (as a library and as an SQL dialect) is too harmful to the web since they can’t assure SQLite’s future versions will be compatible with the current version.

    You can read more about it in the links below.

    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/0326.html
    http://groups.google.com/group/mozilla.community.web-standards/browse_thread/thread/da7000dcc486c0fb/c4aa832133ff907a#c4aa832133ff907a
    http://groups.google.com/group/mozilla.community.web-standards/browse_thread/thread/6f7af6cc0dd46070#
    http://lists.w3.org/Archives/Public/public-webapps/2009AprJun/1242.html
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/0426.html
    http://o-micron.blogspot.com/2009/09/now-published-alternative-to-sql-for.html
    http://lists.w3.org/Archives/Public/public-webapps/2009AprJun/0106.html
    http://lists.w3.org/Archives/Public/public-webapps/2009JulSep/1136.html
    http://www.w3.org/2009/11/02-webapps-minutes.html#item14
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/0674.html
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/1263.html

    Finally Hixie decided that he doesn’t want to push the spec further unless there is any vendor who wants to implement the Web SQL Database without use of the SQLite.
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/0942.html
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/1052.html
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/1078.html

  • Stuart Jones says:

    Out of interest – what is the advantage of this over, say, a well-implemented Ajax/Server system?

  • mors says:

    Hi.
    I’m an Opera developer, and participated in Opera’s Web DB implementation.

    About the article: simple and sweet, but you could have covered error handling better. Note that there is window.SQLError. You can also use resultSet.rows[index], per the specification.

    Just a few remarks about Opera’s implementation:
    – Opera provides a default quota of 5MB per domain (origin in html5). Only after trying to overflow that quota will Opera request anything from the user. Current betas of 10.50 don’t implement the request, but the final will.
    – the quota is shared among all databases for a single origin
    – Opera support a query execution timeout, so if you do a cartesian merge of a couple billion rows, it won’t lock the database in a never ending statement
    – you can see which databases Opera has created by looking at opera:webdatabases
    – if the database is empty, Opera will apply delete it when GCed, else you might explicitly use opera.deleteDatabase(name). Still at this time there is no explicit way to delete databases, so that method was the best way for us to simply our unit tests cleanup.

  • John Dyer says:

    I did some experiments on Web SQL and found that Chrome is hard coded to limit a database to 5MB, while Safari and Opera will ask the user if he or she wants to allow the database to increase.

    For my experiment, I attempted to load the entire Greek New Testament (about 137,000 words) with it’s morphology. This allows the user to do a lot of complex queries without stressing the server (I’ve only built a few due to the space limits). You can see it here: http://www.biblewebapp.com/websql/

  • […] http://html5doctor.com/introducing-web-sql-databases/The Web SQL database API isn’t actually part of the HTML5 specification, but it is part of the […]

  • Remy Sharp says:

    @Mark – I see the demo showing relative time ranging from seconds to hours – perhaps a glitch in the Matrix?

    @Stuart Jones – one simple answer (amongst many others): going offline. Since this is baked in to the browser, it will work without a connection.

    @Mors – ha, yep, there’s a lot more I could have talked about – I wanted to keep the article a decent length ;)

    @John – useful experiment, cheers. I know you’ve posted to the Chromium group, so hopefully they’ll fix that bad boy. Doesn’t make sense to hit the hard limit.

  • Kris Zyp says:

    The Web SQL Database API has essentially been abandoned by the W3C in favor of the Indexed DB API. It might be timely and interesting to cover the DB spec that W3C is working on now: http://www.w3.org/TR/IndexedDB/

  • Remy Sharp says:

    @Kris – are there any implementations of the Indexed DB API (I didn’t think there were – and we’re much more interested in writing tutorials that people can see working in live browsers). But cheers for pointing it out.

  • […] in a “that doesn’t look crazy at all” sort of way. I found out about it from The HTML5 Doctors. You can now create SQL databases on the user’s local […]

  • cyberdog says:

    Nice, was just getting into that with my own iPhone App. But – I’ve found that these run asynchronously, which can lead to unexpected results. I’m generally used to PHP’s database calls, which wait for the server to return a result before carrying on. Is there a way to make these transactions wait until done, or do you have to make a long chain of callback functions?

    (On same vein: Javascript doesn’t have a “sleep” or “wait” function, does it? :( Even in HTML5? If it did, then maybe a wrapper for the SQL transactions could be made to make it behave synchronously…)

  • Remy Sharp says:

    @cyberdog – there’s no method to tell JavaScript to block (sleep/wait) – so in a word: no. The queries (inserts and selects) queue and run asynchronously. If you check out the Twitter time range example you can see how my code ensures that all the data has been run in before allowing the time range selects to work – even though the transactions run asynchronously. At first I found it a little odd – like you my SQL background was serverside and blocking, but once you get the hang of the idea of queued transactions it’s pretty simple.

  • cyberdog says:

    @Remy – Alright, thanks for confirming that. I can work around it with various call-backs and such (and thanks for example), it is just a little weird to get used to as you say. :)

    I just didn’t want to resort to a wasteful “do… while” loop while waiting on the transaction to end… it’s a shame, though, it’d be useful to have some blocking method so an efficient wrapper could be made. Oh well. :)

  • […] Introducing Web SQL Databases | HTML5 Doctor The Web SQL database API isn’t actually part of the HTML5 specification, but it is part of the suite of specifications that allows us developers to build fully fledged web applications, so it’s about time we dig in and check it out. […]

  • […] Shared Introducing Web SQL Databases | HTML5 Doctor. […]

  • […] Introducing Web SQL Databases | HTML5 Doctor […]

  • […] Full description is available at html5doctor. […]

  • Anton says:

    In transaction as base of ‘executeSql’ use ‘tx’ argument. What this argument mean? Can I use defined above ‘db’ or not?

  • […] parts of HTML5, like Web SQL Databases and <canvas>, are essentially JavaScript APIs. They may not cut down on your need for […]

  • […] side database and can involve asynchronous transactions for a more responsive user experience. http://html5doctor.com/introducing-web-sql-databases/ […]

  • […] the javascript database (SQLite), you can store more comprehensive and complex data needed for your […]

  • Fernando says:

    About the versions part, what I have found out, is that in webkit browsers you can change the version, yet, you need to refresh the browser in order for you to make further transactions to the database.
    See
    http://developer.apple.com/library/safari/#documentation/iPhone/Conceptual/SafariJSDatabaseGuide/UsingtheJavascriptDatabase/UsingtheJavascriptDatabase.html%23//apple_ref/doc/uid/TP40007256-CH3-SW2

  • Norma says:

    I was wondering about Insert callbacks. I have code so:

    db.transaction (
    function(transaction) {
    transaction.executeSql(
    ‘INSERT INTO myNums(num1, num2, num3) VALUES (?,?,?);’,
    [age, reps, calories],
    function(){
    refreshEntries();
    jQT.goback();
    },
    /errorHandler
    );
    }
    );
    return false;
    }

    This doesn’t work. If I remove the callbacks, the insert happens. If I move the callbacks to before the return, the refreshEntries happens, and the goBack causes the page to reload, adding another insert to the database.

    Is there no callback function for the insert? What am I doing wrong?

    Thanks,
    Norma

  • […] of ways that this bundle of innovations is making your web browser a lot more powerful. Things like local storage (allowing web applications to use the browser, instead of their own servers, for storing temporary […]

  • Developer says:

    Where does the database get stored in the client machine..and how can we change the location of the same to a new user defined location.

  • ioconnor says:

    This is a fantastic example. Sqlite is currently the only mechanism, probably for the next 10 years, allowing client side browser applications to scale.

    Are there any plans to write a series of articles discussing topics such as robust error handling, asynchronous callbacks, and the difficulties and quarks of writing large client side applications with say Opera and Sqlite?

    There is not much out there on these subjects and it would be great to find it all here at one place…

  • Zakaria says:

    @rem : I’m trying to open an exsiting sqlite database with the “openDatabase” function.
    It can’t load it! Is it normal?
    If yes, can I inject an “.sql” file containing sql scripts that allow table creation with some inserts to the database created with the “openDatabase” function ? (I’m working only on the client side).
    thanks.

  • Plnodi says:

    In W3C Working Group Note 18 November 2010 says that:
    Beware. This specification is no longer in active maintenance and the Web Applications Working Group does not intend to maintain it further.
    Web Sql database is DEAD???????

    Tell me your opinion
    Thank
    plnodi

  • Pratush says:

    Hi All,

    I am new in html5 and javascript.
    I am working on web app for smart phones(iphone, Android). I am facing some issues for insert/update values in the browser database.
    i.e.

    Code:
    db.transaction(function(tx) {

    db.transaction(function(tx) {
    tx.executeSql(‘INSERT/UPDATE ...’, [], function(tx, rs) {
    alert("Success/Failure");
    .............
    }
    });
    for some time the db.transaction skipped and due to this the data is not saved in the database.
    Please let me know if something is wrong in the code or another alternative way to do this.
    I am really stuck in this issue.

    Thanks in advance.

  • Pratush says:

    Hi All,

    I am new in html5 and javascript.
    I am working on web app for smart phones(iphone, Android). I am facing some issues for insert/update values in the browser database.
    i.e.

    Code:
    db.transaction(function(tx) {

    db.transaction(function(tx) {
    tx.executeSql(‘INSERT/UPDATE ...’, [], function(tx, rs) {
    alert("Success/Failure");
    .............
    }
    });
    for some time the db.transaction skipped and due to this the data is not saved in the database.
    Please let me know if something is wrong in the code or another alternative way to do this.
    I am really stuck in this issue.
    Please help me.
    Thanks in advance.

  • […] interested in getting your feet wet there’s a great article on how to use Web SQL Database at HTML5 Doctor .  It covers creating and connecting to a database, querying and dealing with the results, and […]

  • Sam Dutton says:

    Shameless self-promotion, but I’ve built a Chrome extension, using Web SQL Database to bookmark HTML video timecodes and capture framegrabs: http://bit.ly/framegrabberExtension.

    What impressed me was the ability of the database to quickly and reliably handle data URLs, which (in this case) are strings of up to 200,000 or more characters.

    Chrome is able, extremely quickly, to store and retrieve data URLs, and set them as the src attribute for images.

    I’m old enough to remember the days when JavaScript was a lot slower, so this kind of stuff still amazes me!

  • Ray Browning says:

    I am a newbie here in HTML5 land. I really like your Web SQL Database technology.

    I am trying to build a very simple app.

    I need to retrieve data from our SQL Server DB and put it into a Web SQL DB. Currently, I have been able to get data from our SQL Server DB using PHP and read data from the local Web SQL DB using Javascript.

    I am looking for a way to load the SQL Server data into the Web SQL DB but I just can’t seem to find an answer in the news groups.

    Can you help an old man out?

    Thanks for all your hard work, we really do appreciate it!

  • Ray Browning says:

    Pratush,

    I am new also.

    I didn’t know if you found a solution to your insert problem.
    Here is what I am doing and it seems to work.

    function onCreateCompany()
    {
    var sNo = document.itemForm.CompanyNo.value;
    var sName = document.itemForm.CompanyName.value;
    if (sNo == “” || sName == “”)
    {
    //display a message “Error: ‘Company No’ and ‘Company Name’ are required fields!”;
    }
    else
    {
    var query = “INSERT INTO Companies (Company_No, Name) VALUES (?, ?);”;
    try
    {
    localDB.transaction(function(transaction)
    {
    transaction.executeSql(query, [sNo, sName], function(transaction, results)
    {
    if (!results.rowsAffected)
    {
    // display a message “Error: could not insert data.”;
    }
    else
    {
    // Display a message “Inserted row: ” + results.insertId;
    }
    }, errorHandler);
    });
    }
    catch (e)
    {
    // display a message “Error: Unable to add data: ” + e + ” ” + query + “.”);
    }
    }
    }

  • Sam Dutton says:

    @Ray Browning

    > I need to retrieve data from our SQL Server DB and put it into a Web SQL DB. Currently, I have been able to get data from our SQL Server DB using PHP and read data from the local Web SQL DB using Javascript.

    > I am looking for a way to load the SQL Server data into the Web SQL DB but I just can’t seem to find an answer in the news groups.

    I’m not sure the best way to do this, but you could:
    – export your SQL Server data to and XML or JSON file
    – get the XML or JSON file with AJAX from a web page
    – parse the data and store it in the client side database.

    I don’t know if there’s a plan for downloadable ‘pre-built’ client-side databases.

  • Ray Browning says:

    I have found that WebSQL is a very nice and easy way to work with data on the local device WITHOUT INSTALLING ANYTHING!

    It would be nice to see someone continue the work on WebSQL and keep it going.

  • webaloman says:

    Regarding versioning:

    if you open w/o version:

    var db = openDatabase(‘mydb’, ”, ‘my first database’, 2 * 1024 * 1024);

    then you can obtain the current version (if DB exists) by
    db.version

    and then optionaly do the changeVersion

  • Infanty says:

    Hi,

    I got a quick question that is

    What is the difference between SQL Server 2008 and Web SQL???

  • Remy Sharp says:

    @Infanty – one is SQL Sever 2008, and the other is based on SQLite 3.6.19. So you can find out more about SQLite from their documentation: http://www.sqlite.org/

  • Infanty says:

    @ Dr. Remy Sharp : My question is.. can I replace SQL server 2008 to web sql??

    What is web sql?? where can I download it?? what is the use??

  • Remy Sharp says:

    1. No – Web SQL is a *client side* technology. SQL Server 2008 – I’m going to leap and guess it’s server side.

    2. See article above these comments

    3. It’s baked in to your browser if you’re using Safari, Chrome or Opera.

    4. Depends on your business case. Google use it to maintain Gmail on mobile platforms to store all the contact data and emails (subject, body, etc).

  • Infanty says:

    @ Dr. Remy Sharp : Thanks for your information..

    My last question is can we do backup or everything what we do in SQL server 2008??

  • Ustuka Doyran says:

    Hi
    Is it possible to import .csv file to web-sqlite-database?
    If yes, would you show with sample pls!

  • test says:

    Is it possible to share a db between two web apps?

  • Ray Browning says:

    I know that work has stopped on the project but there are several of us out here using it and love WebDB.

    I heard that Google is using some WebDB stuff for off-line GMail?

    I wouldn’t know how to do it but has any one thought about writing a plugin for IE or Fire Fox. I have users who use IE and would like to take advantage of the technology.

    I have been able to load data into “Select” combo boxes from SQL Server using PHP and write back using PHP as well. Without WebDB on the client, off-line work would be impossible.

    Thanks Doc!

    Ray

  • karthikraja says:

    Nice post . I have a doubt regarding Web SQL Databases size. Can you please tell me how to handle , when the given size exceeds . Please tell me the procedure to extend the given memory . I am working on iPhone , Android and Blackberry browser …. Thanks in advance …

  • Andy Blum says:

    I am so sad that this is not going to be part of the official spec. It seems to me an ideal solution to offloading work to local PC’s so centralized databases are not so overwhelmed. I can envision sophisticated workflow applications where db state is initialized in the beginning of a session via REST. State could be sent to the centralized server at the end of a session through REST as well.

  • PG says:

    Hi,

    Please can you tell me how can I find out how many bytes of data is there in my webSQL db. I need to check how many bytes of data can different broswers support. I need to find the size in bytes for the webSQL database.

  • Prats says:

    PG@ in don’t know how to calculate the size of database. But for the browser you don’t need to worry as when you call window.openDatabase(..,.,.., maxMemory) , on start u just set a initial size of the memory. If the database need more memory after that, then browser auto show a dialog box to that your db need memory.. and when you click on the “Ok” button the browser auto increase the memory..

  • PG says:

    Thanks Prats for your response. Just want to confirm if this is true for the mobile browsers for BB, Android and iphone safari?

  • Prats says:

    yes…

  • gaurav v bagga says:

    Hi,
    I was wondering about security issues, like I could easily go through your SQL code. What are the different ways SQL code can be protected so that its not that easily accessible.
    One thing I could think off is encryption but just wanted your point of view.

  • Ray Browning says:

    >Hi,
    >I was wondering about security issues, like I could easily go >through your SQL code. What are the different ways SQL code can >be protected so that its not that easily accessible.
    >One thing I could think off is encryption but just wanted your point >of view.
    Probably the best thing to do is to limit data on the SQL side using views and security.

  • Nad says:

    I have tried using web sql databases. In my code, i make opendatabase call at the time of user login. And when user proceeds il make some data inserts to this db and noticed that data is being saved. If the user logs out and logs in again, i noticed the db to be created again, resulting in a loss of data, that was previously stored. What can be the reason for that?

  • osc2nuke says:

    i want everyone who stick to webdb, provide with this excelent javascript to insert .sql files: http://html5sql.com/index.html in a webdb enviroment

  • DS says:

    Can someone please guide me as to, is the data stored in a web sql database persistent?

    I created a database and then added some records to it. I cannot access the database via any other page which justifies the “same origin” concept.

    But what if I need to access the database again to extract data how do I go about that? I mean from another page..

    Please help. Thank you

  • Ray Browning says:

    The whole idea of WebDB is to have it persist. I am not sure why your database would just go away. We use the following code and it seems to work. Are you sure your browser supports it?

    if (localDB==null)
    {
    var shortName = ‘MyDB’;
    var version = ‘1.0’;
    var displayName = ‘MyDatabase’;
    var maxSize = 65536; // in bytes
    localDB = window.openDatabase(shortName, version, displayName, maxSize);
    }

    if (!window.openDatabase)
    {
    DisplayMyError(“The browser you are using does not support this. Google Chrome is recommended.”);
    }
    else
    {
    ProcessSomeData();
    }

  • onlywebpro says:

    On November 18, 2010, the W3C announced that Web SQL database is a deprecated specification. This is a recommendation for web developers to no longer use the technology as effectively the spec will receive no new updates and browser vendors aren’t encouraged to support this technology.

    Regards,

    The Management of onlyWebPro.com

  • onlywebpro says:

    Developer is advised to use IndexedDB for the coming web application development.

  • Jumaru says:

    I’m a little confused about one thing. Since the executeSql runs asynchronously wouldn’t that mean that running:


    tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)');
    tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "synergies")');


    will produce unexpected results if the insert runs before the create table runs??? Should you not do it something along these lines?:


    tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)',[],function(result){
    tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "synergies")');
    });

  • andodew says:

    it is possible to use external database?

  • Sanchit says:

    Respected Members,
    I am having the database server and trying to insert the data from my HTML5 document to the SQL Server. Using the codes prescribes, I am not able to even connect to my database. How should i code for the problem. The content of my registration page is as below…

    <!–

    Registration in HTML5

    var fname = document.getElementById(“txtFirstName”);
    var lname = document.getElementById(“txtLastName”);
    var gender = document.getElementById(“gender”);
    var address = document.getElementById(“txtarAddress”);
    var emailid = document.getElementById(“emailID”);
    var dob = document.getElementById(“DOB”);
    var create = “CREATE TABLE IF NOT EXISTS REGUSINGHTML5(RegID INT ,FirstName TEXT,LastName TEXT,Gender TEXT, Address TEXT, EmailID TEXT, DateOfBirth TEXT, primarykey(RegID))”;
    var insert = “INSERT INTO REGUSINGHTML5 (FirstName TEXT,LastName TEXT,Gender TEXT, Address TEXT, EmailID TEXT, DateOfBirth TEXT) VALUES (?, ?,?,?,?,?”;
    Create();
    function Create() {
    var db= window.openDatabase(‘Sample’,’10.0.1600.22′,’HTML5 Reg’,2*1024*1024);

    db.transaction(function (tx){

    tx.executeSql(create);
    });
    alert(“Created Database”)
    }
    function Insert() {
    var db = openDatabase(‘Sample’, ‘10.0.1600.22’, ‘HTML5 Reg’, 2 * 1024 * 1024);

    db.transaction(function (tx) {

    tx.executeSql(insert, [fname.value, lname.value, gender.item, address.value, emailid.value, dob.value]);
    });
    alert(“Inserted Database”);
    }

    ONLINE REGISTRAION FORM

    First Name:

    Last Name:

    Gender:

    Male
    Female

    Address:

    Email ID:

    Date Of Birth:

    Insert in DB

    –>

    Moreover I am using SQL Server 2008. Please help me out.

    Thanking you

    Sanchit Choubey

  • Wytze says:

    Actually, Safari does support changeVersion (at least it does on my Windows computer), but there’s a catch: you must supply FIVE arguments or else it will fail.

    Arguments
    1: expected version
    2: new version
    3: transaction callback (you can execute SQL here as part of the version change)
    4: failure callback (if version change or transaction callback failed)
    5: succes callback (if version change and transaction callback succeeded)

    I haven’t tested this on iOS devices but it matches the Safari specifications provided here:
    https://developer.apple.com/library/safari/#documentation/iphone/conceptual/safarijsdatabaseguide/usingthejavascriptdatabase/usingthejavascriptdatabase.html

  • Remy Sharp says:

    @Wytze – lol. You said “actually” as if you’re correcting me. Notice that the article was written over 2 years ago – that was the current state.

    Thanks for testing latest support and offering up the latest info.

  • Andrew says:

    Are web SQL databases tied to a particular domain as LocalStorage is?

  • Andrew says:

    Although the web SQL specification is no longer being worked on, web SQL seems to be the only persistent data solution for supported both iOS and Android devices with one code base, beyond localstorage.

    And localstorage on iOS devices seems not to be reliably persistent following the last update.

  • MR says:

    Hi, I am using websql in my mobile app, I am using around 12-13 tables. In the beginning I create these tables, but the problem is when I start the application first time, only 6-7 tables get created and stops there. No error, no failure. Just stops there. Can you help me with that.

  • Ray Browning says:

    Good morning WebDB.

    Is it possible to get a plug-in or Polyfill that we could include in our HTML to load WebDB?

    I saw that the good doctor Remy had something on https://gist.github.com/350433

    I haven’t had time to review the code. Is this something we can do?
    I wish I knew 1/10th of what the great doctor knows.

    Thanks!

  • Alesha Fernandes says:

    var db = openDatabase(‘mydb’, ‘1.0’, ‘my first database’, 2 * 1024 * 1024);

    is the above database ‘mydb’ created in SQL Server or MYSQL, please tell me where this database is created.Or do we need a js to hold database info.Need to know this urgently for my project.

  • Vaibhav says:

    What is the maximum length in characters of the SQL query in Web SQL that I can pass?

  • Lluís Segura says:

    Hi,

    is it possible to accés to WebSQL database with any php library?

    Thanks!

  • Tobe says:

    Thank you, this helped me a lot. i’m new to javascript and i usually rely heavily on code like the ones here on my projects to give me a little bit of guidance

  • nery says:

    hi,
    I’m starting to use the database of chrome but i have a problem about the foreign keys, these are not actives.
    i readed a sqlite documentation and i need execute this command
    “PRAGMA foreign_keys = boolean;”
    but i dont know how do it in javascript. i tried use your function

    var db = openDatabase(‘mydb’, ‘1.0’, ‘my first database’, 2 * 1024 * 1024);
    db.transaction(function (tx) {
    tx.executeSql(‘PRAGMA foreign_keys = boolean’);
    });

    but didn’t work…

    can you helpme please??

    thankyou.

  • nery says:

    db.transaction(function (tx) {
    tx.executeSql(‘PRAGMA foreign_keys = ON’);
    });

  • bardu says:

    I’m not used to WebSQL nor SQLite, however, we have a use case where we need to download a SQLite DB to an Android tablet and fetch data via JavaScript.

    Will we able to open this downloaded DB via openDatabase(..) or must the DB created by WebSQL?

    Thanks in advance.

    Stephan

  • […] next API is the Web SQL Database. The Web SQL Database is a client-side database that allows you to store information to the client in much the same way […]

  • Umesh says:

    hi,
    I’m using sqlite database with phonegap.
    i have a problem about the foreign keys.

    ‘PRAGMA foreign_keys = ON;’

    that.db.transaction(function(tx) {
    that.db.executeSql(‘PRAGMA foreign_keys = ON’);

    }

    also tx.executeSql(‘PRAGMA foreign_keys = ON’); gives me error processing sql.

    can any one help me out???

  • Keerthi Kumar says:

    how to save the sql datbase tables values in indexed db using html 5

    Hi html 5 experts

    On load of a html5 page i wanted to fetch the values from sql database table
    and have to store in indexeddb in html5 (totally ofline operation )
    On submit button click i wanted to save using database connection(online operation)

    ex: I have to database tables district and taluk
    Now On Load am loading district and after selecting district once again am sending request to
    server and fetching taluk it is taking to much time to avoid this am using this approach
    but am new to this concept can any one please help me to solve this issue?

    thanks in advance
    (Keerthi Kumar)

  • Just.a.guy says:

    I just finished reading my first intro article about indexedDB.
    I find the indexedDB interface way too complicated.
    I realize the need for callback. It seems its goal is to
    embed all of the async work in the DOM agen.

    For me, this coding effort could have been handled
    more synchronously in a web worker thread, and
    then passed back to the DOM agent through its
    message call back.

    I find the Web SQL interface more reasonable.
    Yet the Web SQL interface is deprecated!!

    What a shame.

  • mohsen says:

    Hello,
    I am a beginner of HTML5.
    Can we convert HTML5 web SQL databae to a SQL server or access database?

    Thank you,
    Mohsen

  • Alin says:

    Where the SQLite database ‘mydb’ will be stored? How can I get the database as a file? I wanted to get data from SQLite database such as ‘databaseName.db’. Plz help me about that.

  • Join the discussion.

    Some HTML is ok

    You can use these tags:
    <a href="" title="">
    <abbr title="">
    <b>
    <blockquote cite="">
    <cite>
    <del datetime="">
    <em>
    <i>
    <q cite="">
    <strong>

    You can also use <code>, and remember to use &lt; and &gt; for brackets.