In the next example we are going to create a database (to save people parameters for example), insert and replace element, delete a table, and show data from database itself.
You can add new people data using the inputs and pressing “Save in database”, if the inputs have a red border it means you need to fill in before saving.
Live demo no longer available. JavaScript HTML CSSvar $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>';
}
$ol.innerHTML += html;
},
function(tx, error) {
alert(error.message)
});
});
}
// Add new person
document.querySelector('#save').addEventListener('click', function() {
event.preventDefault(); // we are not using form POST method
if(!$name.checkValidity() || !$surname.checkValidity() || !$dni.checkValidity())
return;
insert_replace_in_db({
name: $name.value,
surname: $surname.value,
dni: $dni.value
});
load_from_database();
});
// Delete table from the database
document.querySelector('#deleteTable').addEventListener('click', function() {
delete_table();
});
load_from_database();
<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>* {
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;
}