Tutorials

HTML Web SQL

Posted in HTML Tutorial

Updated on Sep 03, 2024

By Mari Selvan

👁️ 149 - Views

⏳ 4 mins

💬 1 Comment

Photo Credit to CodeToFun

🙋 Introduction

Web SQL Database is a deprecated web standard for storing data in a structured manner using a SQL-based relational database. While it's no longer recommended for new projects, understanding Web SQL can be valuable for maintaining legacy applications or working with older browser environments that still support this technology.

❓ What Is Web SQL?

Web SQL is a database API that allows web applications to store data using a relational database model. It uses SQL (Structured Query Language) for accessing and manipulating the data, making it familiar to developers who have experience with traditional relational databases like MySQL or SQLite.

🛑 Why Web SQL Was Deprecated

Web SQL was deprecated because it lacked a standardized implementation across different browsers, leading to inconsistencies. The W3C decided not to pursue Web SQL as an official standard, favoring IndexedDB as the more robust and future-proof solution for client-side storage.

🧠 Basic Concepts

Database: A structured collection of data organized in tables.
Table: A set of data elements organized in rows and columns.
SQL: A language for managing and querying relational databases.
Transaction: A sequence of operations performed as a single unit, ensuring data consistency.

🗄️ Creating a Database

To create a Web SQL database, you use the openDatabase method. This method opens an existing database or creates a new one if it doesn't exist.

javascript

Copied

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

db.transaction(function(tx) {
  tx.executeSql('CREATE TABLE IF NOT EXISTS myTable (id unique, name)');
});

💻 Executing SQL Queries

You can execute SQL queries using the executeSql method within a transaction. This allows you to create tables, insert data, update records, and more<./p>

javascript

Copied

db.transaction(function(tx) {
  tx.executeSql('INSERT INTO myTable (id, name) VALUES (1, "John Doe")');
});

💰 Transactions and Error Handling

Web SQL operations are wrapped in transactions, which ensure that a series of queries are executed as a single unit. If any part of the transaction fails, the entire transaction is rolled back, maintaining data integrity.

javascript

Copied

db.transaction(function(tx) {
  tx.executeSql('UPDATE myTable SET name = "Jane Doe" WHERE id = 1');
}, function(error) {
  console.error('Transaction error:', error.message);
}, function() {
  console.log('Transaction successful');
});

👨‍🎨 Working with Results

When you execute a query that returns data (like SELECT), you can handle the results using a callback function. The results are provided as a ResultSet object.

javascript

Copied

db.transaction(function(tx) {
  tx.executeSql('SELECT * FROM myTable', [], function(tx, results) {
    const len = results.rows.length;
    for (let i = 0; i < len; i++) {
      console.log('Record:', results.rows.item(i));
    }
  });
});

🏆 Best Practices

Use IndexedDB for New Projects: Since Web SQL is deprecated, prefer IndexedDB for new projects.
Error Handling: Implement robust error handling to manage any issues that arise during transactions.
Data Persistence: Be cautious with the amount of data stored, as Web SQL is intended for lightweight client-side storage.

📝 Example

Here’s a complete example demonstrating the creation of a database, adding a record, and retrieving all records:

HTML

Copied

<!DOCTYPE html>
<html>
<head>
  <title>Web SQL Example</title>
</head>
<body>
  <h1>Web SQL Example</h1>
  <button id="add">Add Record</button>
  <button id="get">Get All Records</button>

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

    db.transaction(function(tx) {
      tx.executeSql('CREATE TABLE IF NOT EXISTS myTable (id unique, name)');
    });

    document.getElementById('add').addEventListener('click', () => {
      db.transaction(function(tx) {
        tx.executeSql('INSERT INTO myTable (id, name) VALUES (1, "John Doe")');
      });
    });

    document.getElementById('get').addEventListener('click', () => {
      db.transaction(function(tx) {
        tx.executeSql('SELECT * FROM myTable', [], function(tx, results) {
          const len = results.rows.length;
          for (let i = 0; i < len; i++) {
            console.log('Record:', results.rows.item(i));
          }
        });
      });
    });
  </script>
</body>
</html>

🎉 Conclusion

While HTML Web SQL is no longer recommended for new projects due to its deprecation, it remains a useful tool for maintaining legacy applications. Understanding how Web SQL works can help you manage and update older projects effectively, but for modern web development, IndexedDB is the preferred choice for client-side storage.