HTML Web SQL Retrieve

Introduction

Web SQL stores structured data in a local SQLite database. To read that data, you run SQL SELECT queries inside db.transaction and process the returned ResultSet. Although Web SQL is deprecated, knowing how to retrieve rows helps you maintain legacy apps.

This tutorial covers SELECT syntax, looping through results.rows, filtering with WHERE, and displaying data on a web page.

What You’ll Learn

What Is Web SQL?

Web SQL Database is a deprecated browser API for client-side relational storage. It uses SQLite, so you query data with familiar SQL—SELECT reads rows, INSERT adds them, UPDATE changes them, and DELETE removes them.

The W3C deprecated Web SQL in favor of IndexedDB. Chrome removed it in version 97. Use this page for learning and legacy maintenance.

Understanding Data Retrieval

Data retrieval means executing SELECT statements and processing the ResultSet returned in the success callback. The ResultSet has a rows collection—use rows.length for the count and rows.item(i) to read each row as a plain object.

Setting Up the Database

Before retrieving data, open the database and ensure your table exists. Seed sample rows so your SELECT queries return something to display.

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

db.transaction(function (tx) {
  tx.executeSql(
    'CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)'
  );
  tx.executeSql(
    'INSERT OR IGNORE INTO users (id, name, age) VALUES (?, ?, ?)',
    [1, 'John Doe', 25]
  );
});

📝 SELECT Syntax

Standard SQL for reading data:

SELECT column1, column2 FROM table_name WHERE condition;
SELECT * FROM table_name ORDER BY column LIMIT 10 OFFSET 0;

In Web SQL, call SELECT through executeSql:

tx.executeSql(
  'SELECT * FROM users WHERE id = ?',
  [userId],
  function (tx, results) {
    for (var i = 0; i < results.rows.length; i++) {
      var row = results.rows.item(i);
      console.log(row.name, row.age);
    }
  },
  function (tx, error) { /* handle error */ }
);

ResultSet properties

• results.rows.length — number of rows returned.
• results.rows.item(i) — row object at index i (columns as properties).
• results.insertId — meaningful for INSERT, not SELECT.
• results.rowsAffected — meaningful for INSERT/UPDATE/DELETE, not SELECT.

Executing SELECT Queries

Retrieve all records from a table and log each name to the console:

db.transaction(function (tx) {
  tx.executeSql(
    'SELECT * FROM users',
    [],
    function (tx, results) {
      for (var i = 0; i < results.rows.length; i++) {
        console.log(results.rows.item(i).name);
      }
    },
    function (tx, error) {
      console.error('Query error:', error.message);
      return true;
    }
  );
});

The empty array [] is the bindings list—no ? placeholders needed for SELECT * without a WHERE clause.

Processing Query Results

Filter with WHERE and read column values from each row object:

db.transaction(function (tx) {
  tx.executeSql(
    'SELECT * FROM users WHERE age > ?',
    [30],
    function (tx, results) {
      for (var i = 0; i < results.rows.length; i++) {
        var row = results.rows.item(i);
        console.log('ID:', row.id, 'Name:', row.name, 'Age:', row.age);
      }
    }
  );
});

Only users with age > 30 are returned. You can also access rows with results.rows[i] in some browsers, but item(i) is the portable approach.

Handling Errors

Errors occur for invalid SQL, missing tables, or database access failures. Zero rows returned is not an error—check rows.length separately.

db.transaction(function (tx) {
  tx.executeSql(
    'SELECT * FROM nonExistentTable',
    [],
    null,
    function (tx, error) {
      console.error('Error occurred:', error.message);
      return true;
    }
  );
});

• Error callback — fires on SQL or schema errors; return true to roll back.
• Empty result — rows.length === 0 means no matches; show a friendly empty state.
• Null columns — missing values may be null; handle before displaying.

Examples Gallery

These examples use the Web SQL API pattern. Use the Try It Yourself links to run live demos. Web SQL works only in browsers that still expose openDatabase; otherwise the demo shows a clear status message.

db.transaction(function (tx) {
  tx.executeSql('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');

  tx.executeSql('SELECT * FROM users', [], function (tx, results) {
    var len = results.rows.length;
    for (var i = 0; i < len; i++) {
      console.log(results.rows.item(i).name);
    }
  });
});

John Doe
Jane Smith
Alex Kim

db.transaction(function (tx) {
  tx.executeSql(
    'SELECT * FROM users WHERE age > ? ORDER BY age',
    [30],
    function (tx, results) {
      for (var i = 0; i < results.rows.length; i++) {
        var row = results.rows.item(i);
        console.log(row.name + ' — age ' + row.age);
      }
    }
  );
});

Jane Smith — age 32
Mari Selvan — age 35

function getUserById(id, callback) {
  db.transaction(function (tx) {
    tx.executeSql(
      'SELECT * FROM users WHERE id = ?',
      [id],
      function (tx, results) {
        if (results.rows.length === 0) {
          callback(null);
        } else {
          callback(results.rows.item(0));
        }
      }
    );
  });
}

getUserById(2, function (user) {
  if (user) console.log(user.name, user.age);
  else console.log('User not found');
});

Jane Smith 32

var pageSize = 10;
var page = 0; /* 0-based page index */

db.transaction(function (tx) {
  tx.executeSql(
    'SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?',
    [pageSize, page * pageSize],
    function (tx, results) {
      console.log('Page', page, '—', results.rows.length, 'rows');
    }
  );
});

Page 0 — 3 rows

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Web SQL Retrieve Example</title>
</head>
<body>
  <h1>Data from users table</h1>
  <ul id="dataList"></ul>
  <p id="status"></p>

  <script>
    if (!window.openDatabase) {
      document.getElementById('status').textContent =
        'Web SQL is not supported in this browser.';
    } else {
      const db = openDatabase('myDatabase', '1.0', 'Test DB', 2 * 1024 * 1024);

      db.transaction(function (tx) {
        tx.executeSql('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
        tx.executeSql('INSERT OR IGNORE INTO users (id, name, age) VALUES (?, ?, ?)', [1, 'John Doe', 25]);
        tx.executeSql('INSERT OR IGNORE INTO users (id, name, age) VALUES (?, ?, ?)', [2, 'Jane Smith', 32]);
      });

      db.transaction(function (tx) {
        tx.executeSql('SELECT * FROM users', [], function (tx, results) {
          const list = document.getElementById('dataList');
          for (let i = 0; i < results.rows.length; i++) {
            const row = results.rows.item(i);
            const li = document.createElement('li');
            li.textContent = 'ID: ' + row.id + ', Name: ' + row.name + ', Age: ' + row.age;
            list.appendChild(li);
          }
          document.getElementById('status').textContent =
            'Loaded ' + results.rows.length + ' row(s).';
        }, function (tx, error) {
          document.getElementById('status').textContent = 'Error: ' + error.message;
        });
      });
    }
  </script>
</body>
</html>

• ID: 1, Name: John Doe, Age: 25
• ID: 2, Name: Jane Smith, Age: 32
Loaded 2 row(s).

🚀 Common Use Cases

• Offline reading lists — display saved articles or notes from local storage.
• Cart display — SELECT cart items and render product names and prices.
• Search filters — WHERE clauses match user search terms (with bound parameters).
• Settings screens — read preference rows and populate form fields.
• Legacy app maintenance — debug data in older Web SQL databases.

📝 Notes

• Web SQL is deprecated—use IndexedDB for new apps.
• SELECT is read-only; it does not change data.
• Zero rows is success, not failure—handle empty lists in the UI.
• Always bind dynamic values in WHERE with ?.
• Use LIMIT for large tables to avoid loading everything at once.
• Pair with UPDATE and DELETE for full CRUD.

💡 Best Practices