HTML5 Web SQL Database
Web SQL Status:The Web SQL API has been deprecated and is no longer recommended. New browser standards prefer IndexedDB for client-side storage. Web SQL is only still supported in some older browsers.
Alternative:
Since the future of Web SQL is uncertain and it is no longer widely supported, it is recommended to switch to IndexedDB, which is a modern browser API that supports transactions and key-value pair storage.
The Web SQL Database API is not part of the HTML5 specification, but it is an independent specification that introduces a set of APIs for using SQL to operate client-side databases.
If you are a Web backend programmer, you should easily understand SQL operations.
You can also refer to ourSQL Tutorialto learn more about database operations.
Web SQL Database works in the latest versions of Safari, Chrome, and Opera browsers.
Core Methods
The following are the three core methods defined in the specification:
- openDatabase: This method creates a database object using an existing database or a newly created database.
- transaction: This method allows us to control a transaction and perform commit or rollback based on the situation.
- executeSql: This method is used to execute actual SQL queries.
Open Database
We can use the openDatabase() method to open an existing database. If the database does not exist, a new database will be created. The usage code is as follows:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
The five parameters of the openDatabase() method are described as follows:
- Database name
- Version number
- Description text
- Database size
- Creation callback
The fifth parameter, the creation callback, will be called after the database is created.
Execute Query Operations
Use the database.transaction() function to execute operations:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
});
After the above statement is executed, a table named LOGS will be created in the 'mydb' database.
Insert Data
After executing the above table creation statement, we can insert some data:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "Example Tutorial")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.example.com")');
});
We can also use dynamic values to insert data:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS (id,log) VALUES (?, ?)', [e_id, e_log]);
});
In the example, e_id and e_log are external variables. executeSql maps each entry in the array parameter to "?".
Read Data
The following example demonstrates how to read data that already exists in the database:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "Example Tutorial")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.example.com")');
});
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
var len = results.rows.length, i;
msg = "<p>Query the number of records:" + len + "</p>";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i < len; i++){
alert(results.rows.item(i).log );
}
}, null);
});
Full Example
Example
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var msg;
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "Example Tutorial")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.example.com")');
msg = '<p>The data table has been created, and two records have been inserted.</p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
var len = results.rows.length, i;
msg = "<p>Query the number of records:" + len + "</p>";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i < len; i++){
msg = "<p><b>" + results.rows.item(i).log + "</b></p>";
document.querySelector('#status').innerHTML += msg;
}
}, null);
});
Try it Yourself »
The running result of the above example is shown in the following figure:
Delete Records
The format for deleting records is as follows:
db.transaction(function (tx) {
tx.executeSql('DELETE FROM LOGS WHERE id=1');
});
The id of the specified data to delete can also be dynamic:
db.transaction(function(tx) {
tx.executeSql('DELETE FROM LOGS WHERE id=?', [id]);
});
Update Records
The format for updating records is as follows:
db.transaction(function (tx) {
tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=2');
});
The id of the specified data to update can also be dynamic:
db.transaction(function(tx) {
tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=?', [id]);
});
Full Example
Example
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var msg;
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "Example Tutorial")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.example.com")');
msg = '<p>The data table has been created, and two records have been inserted.</p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('DELETE FROM LOGS WHERE id=1');
msg = '<p>Delete the record with id 1.</p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=2');
msg = '<p>Update the record with id 2.</p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
var len = results.rows.length, i;
msg = "<p>Query the number of records:" + len + "</p>";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i < len; i++){
msg = "<p><b>" + results.rows.item(i).log + "</b></p>";
document.querySelector('#status').innerHTML += msg;
}
}, null);
});
Try it Yourself »
The running result of the above example is shown in the following figure:
Other Extensions