24
The Web SQL Database API provides a way to store structured data in a client-side database using SQL queries.
Although Web SQL is not part of modern standards and has been deprecated in favor of IndexedDB, it is still supported in some browsers and can be used for legacy projects or learning purposes.
1. What is Web SQL?
Web SQL Database is an API for storing data in a relational database on the client side using SQL syntax. It uses three main methods:
- openDatabase: Creates or opens a database.
- transaction: Executes SQL commands within a transaction.
- executeSql: Executes a specific SQL query.
Browser Support:
Web SQL is supported by:
- Google Chrome
- Safari
- Opera
Not supported in:
- Firefox
- Microsoft Edge
2. Creating a Web SQL Database
Example 1: Create a Database and Table
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Create Database</title>
</head>
<body>
<h1>Web SQL Database - Create Example</h1>
<script>
// Open or create a database
const db = openDatabase('myDB', '1.0', 'Test Database', 2 * 1024 * 1024);
// Execute a transaction to create a table
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
console.log('Table created successfully');
});
</script>
</body>
</html>
Explanation:
- openDatabase: Creates a database named myDB with version 1.0 and a size of 2MB.
- CREATE TABLE: Creates a table named users if it doesn’t already exist.
3. Inserting Data
You can insert records into the database using SQL commands.
Example 2: Insert Data into a Table
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Insert Data</title>
</head>
<body>
<h1>Web SQL Database - Insert Example</h1>
<script>
const db = openDatabase('myDB', '1.0', 'Test Database', 2 * 1024 * 1024);
// Insert data into the table
db.transaction(function (tx) {
tx.executeSql('INSERT INTO users (name, age) VALUES (?, ?)', ['Alice', 25]);
tx.executeSql('INSERT INTO users (name, age) VALUES (?, ?)', ['Bob', 30]);
console.log('Data inserted successfully');
});
</script>
</body>
</html>
Explanation:
- INSERT INTO: Adds records to the users table.
- VALUES (?, ?): Parameterized query to prevent SQL injection.
4. Retrieving Data
You can retrieve records from the database using the SELECT query.
Example 3: Retrieve Data from a Table
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Retrieve Data</title>
</head>
<body>
<h1>Web SQL Database - Retrieve Example</h1>
<ul id="userList"></ul>
<script>
const db = openDatabase('myDB', '1.0', 'Test Database', 2 * 1024 * 1024);
// Retrieve data from the table
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM users', [], function (tx, results) {
const len = results.rows.length;
const userList = document.getElementById('userList');
for (let i = 0; i < len; i++) {
const li = document.createElement('li');
li.textContent = `ID: ${results.rows.item(i).id}, Name: ${results.rows.item(i).name}, Age: ${results.rows.item(i).age}`;
userList.appendChild(li);
}
});
});
</script>
</body>
</html>
Explanation:
- SELECT * FROM users: Retrieves all records from the users table.
- results.rows.item(i): Accesses each row of the result set.
5. Updating Data
You can update existing records using the UPDATE query.
Example 4: Update Data in a Table
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Update Data</title>
</head>
<body>
<h1>Web SQL Database - Update Example</h1>
<script>
const db = openDatabase('myDB', '1.0', 'Test Database', 2 * 1024 * 1024);
// Update data in the table
db.transaction(function (tx) {
tx.executeSql('UPDATE users SET age = ? WHERE name = ?', [35, 'Alice']);
console.log('Data updated successfully');
});
</script>
</body>
</html>
Explanation:
- UPDATE users: Updates the age field for the user named Alice.
6. Deleting Data
You can delete specific records or entire tables using the DELETE or DROP queries.
Example 5: Delete a Record
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Delete Data</title>
</head>
<body>
<h1>Web SQL Database - Delete Example</h1>
<script>
const db = openDatabase('myDB', '1.0', 'Test Database', 2 * 1024 * 1024);
// Delete a record from the table
db.transaction(function (tx) {
tx.executeSql('DELETE FROM users WHERE name = ?', ['Bob']);
console.log('Data deleted successfully');
});
</script>
</body>
</html>
Explanation:
- DELETE FROM users: Removes records where the name is Bob.
7. Clearing the Table
To remove all records from a table, use the DELETE query without a condition.
Example 6: Clear All Records
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Clear Table</title>
</head>
<body>
<h1>Web SQL Database - Clear Table</h1>
<script>
const db = openDatabase('myDB', '1.0', 'Test Database', 2 * 1024 * 1024);
// Clear all records from the table
db.transaction(function (tx) {
tx.executeSql('DELETE FROM users');
console.log('All data cleared successfully');
});
</script>
</body>
</html>
8. Error Handling
It’s important to handle errors in Web SQL to ensure your application can respond to issues gracefully.
Example 7: Error Handling
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Error Handling</title>
</head>
<body>
<h1>Web SQL Database - Error Handling</h1>
<script>
const db = openDatabase('myDB', '1.0', 'Test Database', 2 * 1024 * 1024);
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM non_existing_table', [], function (tx, results) {
console.log('Query executed successfully');
}, function (tx, error) {
console.error('Error occurred:', error.message);
return true; // Indicates that the error was handled
});
});
</script>
</body>
</html>
Explanation:
- Error Callback: The third parameter of executeSql handles errors, allowing you to log or recover from issues.
9. Best Practices
- Use IndexedDB: Web SQL is deprecated and should be avoided in new projects.
- Error Handling: Always provide error callbacks for executeSql.
- Use Transactions: Group multiple queries into a single transaction for better performance.
- Limit Usage: Only use Web SQL for small, client-side data storage.
10. Conclusion
In this tutorial, you learned:
- How to create and interact with a Web SQL database.
- How to insert, retrieve, update, and delete records.
- How to handle errors effectively.
While Web SQL is easy to use and similar to server-side SQL, it is deprecated, and IndexedDB is the recommended alternative for client-side storage in modern web applications.