DEV Community

101samovar
101samovar

Posted on

JavaScript Web Sql

Hi
In this article we will create a simple use case for the web-SQL.
The web-SQL is a client-side relational database.
This database can be used to store data in offline mode.

So let’s create a site.
Suppose we need to store offline the price list for gems and available amount of gems.
We will save the data in two separate tables.

First of all we need to open the database. We use the openDatabase method with params: short name, version, display name and the maximum size in bytes.
If the database does not exist it will be created.

After the database has been created we use the transaction method of the database object.
The transaction method has a callback param in which we can make queries to the database with executeSql method.
A query to create a table.
A query to insert values to the table.
And a query to select data from the table.

So let’s create a table “gems” with columns: name and price.
And a table “store” with columns: gem and amount.

Then let’s insert values into tables “gems” and “store”.

To get the data from the database we use another transaction and another executeSql method.
So let’s select for each gem name the total sum of gem price multiplied by amount of gems.

Here you are.

const gems = [
    {name: 'ruby', price: 10},
    {name: 'emerald', price: 20},
    {name: 'diamond', price: 30}
];
const store = [
    {gem: 'ruby', amount: 10},
    {gem: 'emerald', amount: 12},
    {gem: 'ruby', amount: 15},
    {gem: 'diamond', amount: 24}
];
const db = window.openDatabase('data', '1.0', 'data', 1*1024*1024);
db.transaction(t => {
    t.executeSql('CREATE TABLE gems (name TEXT, price INTEGER)');
    t.executeSql('CREATE TABLE store (gem TEXT, amount INTEGER)');
    for (let g of gems) {
        t.executeSql('INSERT INTO gems (name, price) VALUES (?, ?)',
        [g.name, g.price]);
    }
    for (let s of store) {
        t.executeSql('INSERT INTO store (gem, amount) VALUES (?, ?)', 
        [s.gem, s.amount]);
    }
}, e => console.error(e));
db.transaction(t => t.executeSql(
    'SELECT g.name, SUM(g.price*s.amount) total FROM gems g ' +
    'INNER JOIN store s ON g.name = s.gem GROUP BY 1', [],
    (t, result) => console.log(result.rows)
));
Enter fullscreen mode Exit fullscreen mode

I hope you found this article useful, if you need any help please let me know in the comment section.

👋 See you next time. Have a nice day!

Subscribe to our channel:
https://www.youtube.com/c/Samovar101

Discussion (0)