WebSQL
WebSQL is a SQL-based client-side database storage technology proposed in the HTML5 specification, which allows the creation and operation of relational databases on the browser side. Although WebSQL was included in the HTML5 draft, due to various reasons (such as stagnation of the standardization process and lack of cross-browser support), it has not become an official W3C standard and has been abandoned by mainstream browser manufacturers. Currently, it is recommended to use IndexedDB as a browser-side database solution.
Database creation and opening
Use window.openDatabase() function to create or open a WebSQL database:
let db = null;
try {
db = openDatabase('myDatabase', '1.0', 'My Database Description', 2 * 1024 * 1024); // The last parameter is the database size limit in bytes
} catch (err) {
console.error('Error creating/opening database:', err);
}Execute SQL
WebSQL provides transaction() method to open a transaction and execute SQL query in its callback function. Queries are executed through executeSql() method, which accepts SQL statement and parameter array (if any), as well as two callback functions: one for processing query results and the other for handling execution errors.
db.transaction(function(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)', [], function(tx, result) {
console.log('Table "users" created or already exists.');
}, function(tx, error) {
console.error('Error creating table:', error);
});
});Insert, query, update, delete data
// Inserting Data
db.transaction(function(tx) {
tx.executeSql('INSERT INTO users (name, email) VALUES (?, ?)', ['John Doe', 'john.doe@example.com'], function(tx, result) {
console.log('User inserted successfully:', result.insertId);
}, function(tx, error) {
console.error('Error inserting user:', error);
});
});
// Query data
db.transaction(function(tx) {
tx.executeSql('SELECT * FROM users WHERE name = ?', ['John Doe'], function(tx, result) {
for (let i = 0; i < result.rows.length; i++) {
const row = result.rows.item(i);
console.log('User:', row.id, row.name, row.email);
}
}, function(tx, error) {
console.error('Error querying users:', error);
});
});
// Update data
db.transaction(function(tx) {
tx.executeSql('UPDATE users SET email = ? WHERE id = ?', ['new.email@example.com', 1], function(tx, result) {
console.log('User updated successfully:', result.rowsAffected);
}, function(tx, error) {
console.error('Error updating user:', error);
});
});
// Deleting Data
db.transaction(function(tx) {
tx.executeSql('DELETE FROM users WHERE id = ?', [1], function(tx, result) {
console.log('User deleted successfully:', result.rowsAffected);
}, function(tx, error) {
console.error('Error deleting user:', error);
});
});Transaction Processing
All operations in WebSQL must be performed in a transaction. Transactions ensure that a set of operations either all succeed or all fail (rollback). In the above example, each operation has been encapsulated in the callback function provided by the transaction() method.
Limitations and Deprecations
Although WebSQL provides the convenience of relational databases for offline storage on the browser side, it has not become a widely accepted standard due to the following reasons:
- Stagnation of standardization: The standardization work of WebSQL has stagnated in the W3C, resulting in its failure to become an official HTML5 standard.
- Cross-browser support issues: Only Safari, Chrome and Opera browsers have provided support for WebSQL. Firefox, IE/Edge have never implemented this technology.
- Alternative technologies have emerged: IndexedDB, a more flexible and more suitable key-value storage mechanism for modern web applications, has gradually become the preferred solution for browser-side databases.
IndexDB
IndexedDB is used to store large amounts of structured data on the browser side. Unlike traditional storage methods based on string key-value pairs such as cookies, localStorage, and sessionStorage, IndexedDB allows the storage of complex objects (including binary data) and provides indexing, querying, and transaction processing functions, making it more suitable for building complex offline applications and rich web client experiences.
Open the database
Use the indexedDB.open() method to open or create a database. This method accepts the database name and version number as parameters and returns an IDBRequest object. In the onsuccess event handler, you can access the IDBDatabase object for subsequent database operations.
let db;
const request = indexedDB.open('myDatabase', 1);
request.onerror = function(event) {
console.error('Error opening IndexedDB:', event.target.error);
};
request.onsuccess = function(event) {
db = event.target.result;
console.log('Database opened successfully.');
};
request.onupgradeneeded = function(event) {
// If the database version is upgraded or created for the first time, you need to create or modify the object store here.
const db = event.target.result;
const objectStore = db.createObjectStore('users', { keyPath: 'id' });
objectStore.createIndex('nameIndex', 'name', { unique: false });
};Create/modify objects
Object stores are the basic unit for storing data in IndexedDB, similar to tables in relational databases. In the onupgradeneeded event handler, you can use the createObjectStore() method to create a new object store, or delete the existing object store through the deleteObjectStore() method. At the same time, you can create indexes for object stores for efficient querying.
Insert data
To insert data, you need to obtain a transaction first, and then use the put() or add() method in the transaction context to write the data to the object store. put() will overwrite the data of the existing key value, while add() will throw an error if the key value already exists.
const transaction = db.transaction(['users'], 'readwrite');
const objectStore = transaction.objectStore('users');
const newUser = {
id: 1,
name: 'John Doe',
email: 'john.doe@example.com'
};
objectStore.add(newUser).onsuccess = function(event) {
console.log('User added successfully:', newUser);
};
transaction.oncomplete = function() {
console.log('Transaction completed.');
};
transaction.onerror = function(event) {
console.error('Transaction error:', event.target.error);
};Query data
To query data, you also need to obtain the transaction and object store, and then use get(), getAll(), openCursor() and other methods to query.
const transaction = db.transaction(['users'], 'readonly');
const objectStore = transaction.objectStore('users');
// Query a single user
const request = objectStore.get(1);
request.onsuccess = function(event) {
const user = event.target.result;
if (user) {
console.log('Found user:', user);
} else {
console.log('User not found.');
}
};
// Query all users
const getAllRequest = objectStore.getAll();
getAllRequest.onsuccess = function(event) {
const allUsers = event.target.result;
console.log('All users:', allUsers);
};
// Use a cursor to iterate over all users
const cursorRequest = objectStore.openCursor();
cursorRequest.onsuccess = function(event) {
const cursor = event.target.result;
if (cursor) {
console.log('User:', cursor.value);
cursor.continue();
} else {
console.log('End of cursor.');
}
};Updating and deleting data
To update data, use the put() method, and to delete data, use the delete() method. Similar to inserting data, these operations should be performed in the context of a transaction.
const transaction = db.transaction(['users'], 'readwrite');
const objectStore = transaction.objectStore('users');
// Update User
const updatedUser = {
id: 1,
name: 'John Smith',
email: 'john.smith@example.com'
};
objectStore.put(updatedUser).onsuccess = function(event) {
console.log('User updated successfully:', updatedUser);
};
// Deleting a User
objectStore.delete(1).onsuccess = function(event) {
console.log('User deleted successfully.');
};
// Transaction processing and inserting data exampleIndex Optimization Query
After creating an index, you can specify the index to speed up the search when querying. For example, use the index() method to get the index object, and then call the query method of the index object.
const transaction = db.transaction(['users'], 'readonly');
const objectStore = transaction.objectStore('users');
const nameIndex = objectStore.index('nameIndex');
// Find all users named 'John Doe'
const request = nameIndex.getAll('John Doe');
request.onsuccess = function(event) {
const usersWithNameJohnDoe = event.target.result;
console.log('Users named John Doe:', usersWithNameJohnDoe);
};
