Tutorials

HTML Web SQL Delete

Posted in HTML Tutorial

Updated on Sep 03, 2024

By Mari Selvan

👁️ 39 - Views

⏳ 4 mins

💬 1 Comment

Photo Credit to CodeToFun

🙋 Introduction

Web SQL Database is a web API for storing structured data using SQL databases in web browsers. Although Web SQL is deprecated and not recommended for new projects (in favor of IndexedDB), understanding its operations can be useful for maintaining or updating existing applications.

This page focuses specifically on how to delete data from a Web SQL database.

❓ What Is Web SQL?

Web SQL is a web standard that provides a way to interact with a SQL database directly in the browser. It uses the SQLite database engine to enable the storage and manipulation of structured data with SQL queries. Despite its deprecation, many existing applications still use Web SQL for local data storage.

🧠 Understanding Web SQL Schema

Before deleting records, it's essential to understand the structure of your database. This typically involves knowing the tables and fields where your data is stored. While this section won't explain table creation or other operations, it's crucial to have a clear schema in mind to perform effective deletions.

🗑️ Deleting Records

To delete records from a Web SQL database, you use the SQL DELETE statement. This statement allows you to remove one or more rows from a table based on a specified condition.

Here’s a basic example of how to delete records from a table using Web SQL:

javascript

Copied

const db = openDatabase('myDatabase', '1.0', 'Test Database', 2 * 1024 * 1024);

function deleteRecord(id) {
  db.transaction(function(tx) {
    tx.executeSql('DELETE FROM myTable WHERE id = ?', [id], function(tx, results) {
      console.log('Record deleted successfully');
    }, function(tx, error) {
      console.error('Error deleting record:', error.message);
    });
  });
}

In this example:

openDatabase: Opens or creates a Web SQL database.
transaction: Starts a new transaction.
executeSql: Executes an SQL command. Here, it uses the DELETE statement to remove records where the id matches the provided value.

🤝 Handling Deletion Errors

Errors during deletion operations can occur due to various reasons, such as invalid SQL syntax or issues with the transaction. It’s important to handle these errors to ensure smooth operation and provide feedback to users.

javascript

Copied

tx.executeSql('DELETE FROM myTable WHERE id = ?', [id], function(tx, results) {
  console.log('Record deleted successfully');
}, function(tx, error) {
  console.error('Error deleting record:', error.message);
});

In the code above, the error callback function captures any issues that arise during the execution of the DELETE statement.

🏆 Best Practices

Validate Input: Ensure that the input values used in deletion queries are validated to avoid SQL injection attacks.
Error Handling: Implement robust error handling to manage issues that may arise during the deletion process.
Transaction Management: Use transactions to group related operations and ensure data integrity.
Deprecation Notice: Consider migrating to IndexedDB or other modern storage solutions, as Web SQL is deprecated and may not be supported in future browsers.

📝 Example

Here’s a complete example demonstrating how to set up a Web SQL database and delete records from it:

HTML

Copied

<!DOCTYPE html>
<html>
<head>
  <title>Web SQL Delete Example</title>
</head>
<body>
  <h1>Delete Records from Web SQL</h1>
  <button onclick="deleteRecord(1)">Delete Record with ID 1</button>

  <script>
    const db = openDatabase('myDatabase', '1.0', 'Test Database', 2 * 1024 * 1024);

    // Setup database and table
    db.transaction(function(tx) {
      tx.executeSql('CREATE TABLE IF NOT EXISTS myTable (id unique, name)');
      tx.executeSql('INSERT INTO myTable (id, name) VALUES (1, "Sample Record")');
    });

    function deleteRecord(id) {
      db.transaction(function(tx) {
        tx.executeSql('DELETE FROM myTable WHERE id = ?', [id], function(tx, results) {
          console.log('Record deleted successfully');
        }, function(tx, error) {
          console.error('Error deleting record:', error.message);
        });
      });
    }
  </script>
</body>
</html>

🎉 Conclusion

Deleting records from a Web SQL database involves executing a DELETE SQL command within a transaction.

Although Web SQL is deprecated, understanding how to perform these operations can be useful for maintaining existing systems. For new projects, consider using IndexedDB for better support and features.