Save people parameters in database using Web SQL

Create database, insert and replace element, delete table, and show data from database

by Génesis García Morilla

HTML

<h1>Web SQL</h1>
<form>
  <label for="name">Name</label>
  <input id="name" type="text" required>
  <label for="surname">Surname</label>
  <input id="surname" type="text" required>
  <label for="dni">DNI</label>
  <input id="dni" atype="text" required>
  <button id="save">Save in database</button>
</form>
<h2>People</h2>
<ol></ol>
<button id="deleteTable">Delete people</button>

CSS

* {
  color: #2b2b2b;
  font-family: "Roboto Condensed";
}

input {
  margin-right: 15px;
}

input:invalid {
  border: 1px solid red;
}

input:valid {
  border: 1px solid green;
}

button {
  cursor: pointer;
}

JavaScript

var $name = document.querySelector('#name');
var $surname = document.querySelector('#surname');
var $dni = document.querySelector('#dni');
var $ol = document.querySelector('ol');

/**
 * Opens an existing database or creates a new one
 *
 * @returns {db} Data base
 */
function open_create_database() {
  // Opens or creates database
  var db = openDatabase('mydb', '1.0', 'mydb description', 2 * 1024 * 1024);

  // If table doesn't exist, we will create a new one with the following template
  db.transaction(function(tx) {
    tx.executeSql("CREATE TABLE IF NOT EXISTS people " +
      "( dni                          BIGINT      PRIMARY KEY" +
      ", name                         CHAR(8)     DEFAULT ''" +
      ", surname                      CHAR(16)    DEFAULT ''" +
      ")");
  });
  return db;
}

/**
 * Delete table from database
 *
 */
function delete_table() {
  var db = open_create_database();

  db.transaction(function(tx) {
    tx.executeSql("DROP TABLE people");
    $ol.innerHTML = "";
  }, function(tx, error) {
    alert(error.message)
  });
}

/**
 * Inserts or replaces person in db
 *
 * @param person Person
 * @param [filename] File name
 */
function insert_replace_in_db(person) {
  var db = open_create_database();

  db.transaction(function(tx) {
    tx.executeSql("REPLACE INTO people " +
      "( dni" +
      ", name" +
      ", surname" +
      ") VALUES (?,?,?)", [
        person.dni,
        person.name,
        person.surname
      ]);
  }, function(tx, error) {
    alert(error.message)
  });
}

/**
 * Load people from the data base
 */
function load_from_database() {
  var db = open_create_database();
  var html = "";

  $ol.innerHTML = "";

  db.transaction(function(tx) {
    tx.executeSql("SELECT * FROM people ORDER BY name ASC", [],
      function(tx, r) {
        var e;

        for (var i = 0; i < r.rows.length; i++) {
          e = r.rows.item(i);
          html += '<li>' + e.name + ' ' + e.surname + ', ' + e.dni + '</li>';
        }
    ...