SQLite In Javascript (Local Database In Browser)

Once upon a time, there were plans to implement an SQL database directly in browsers. But things fall apart with “security concerns”, and WebSQL is eventually deprecated. If you are in the same boat of requiring a database for your web app, there is a way around it – Read on for an example!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist | Example on CodePen

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

PART 1) USING THE SQLJS LIBRARY

• SQLJS is a library that runs SQLite using Web Assembly.
• There are two ways to “use” the library:
  • Load the library from a CDN.
  • Download and host the script yourself.
• In this example, we will just load it off the CDN. If you want to host it on your own server, download the “WASM version”.

 

PART 2) DATABASE INIT

var demo = {
  // (A) INITIALIZE
  db     : null,           // database object
  cache  : null,           // storage cache object
  cname  : "SQLDB",        // cache storage name
  dbname : "/demo.sqlite", // database storage name
  init : async () => {
    // (A1) STORAGE CACHE + SQLJS
    demo.cache = await caches.open(demo.cname);
    const SQL = await initSqlJs({
      locateFile: filename => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/${filename}`
    });

    // (A2) ATTEMPT TO LOAD DATABASE FROM STORAGE CACHE
    demo.cache.match(demo.dbname).then(async r => {
      // (A2-1) NOT FOUND - CREATE A NEW DATABASE
      if (r==undefined) {
        demo.db = new SQL.Database();
        demo.db.run(`CREATE TABLE events (
          id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
          date TEXT NOT NULL,
          text TEXT NOT NULL
        )`);
        demo.db.run("CREATE INDEX date ON events (date)");
        await demo.export();
        await demo.run();
        demo.get();
      }

      // (A2-2) LOAD EXISTING DATABASE
      else {
        const buf = await r.arrayBuffer();
        demo.db = new SQL.Database(new Uint8Array(buf));
        demo.get();
      }
    });
  },
 
  // (B) EXPORT TO CACHE STORAGE
  export : async () => await demo.cache.put(
    demo.dbname, new Response(demo.db.export())
  ),
};

// (E) GO!
window.addEventListener("load", demo.init);

• (A1 & A2-1) To create a new database:
  • Is as simple as const SQL = await initSqlJs() and db = new SQL.Database().
  • But take note, the database only exists in the memory. Once the page is reloaded, all data is gone.
• (B) To create a persistent database, we “export the database to a cache storage”… Follow up with your own studies if you don’t know what that is. Links below.
• (A1 & A2-2) On subsequent visits, we will “restore” the database from the cache storage.

 

 

PART 3) INSERT UPDATE DELETE

var demo = {
  // (C) DEMO - INSERT/UPDATE EVENT
  run : async () => {
    // (C1) INSERT
    demo.db.run(
      `INSERT INTO events (date, text) VALUES (?,?), (?,?), (?,?)`, [
      "2077-06-05 00:00:00", "First event.",
      "2077-06-06 00:00:00", "Second event.",
      "2077-06-07 00:00:00", "Third event."
    ]);

    // (C2) UPDATE
    demo.db.run(
      `UPDATE events SET date=?, text=? WHERE id=?`,
      ["2077-06-06 00:00:00", "Second event EDITED.", 2]
    );

    // (C3) DELETE
    demo.db.run("DELETE FROM events WHERE id=?", [1]);

    // (C4) "COMMIT"
    await demo.export();
  },
};

This may look massive, but keep calm and study closely.

• Use db.run(SQL, DATA) to run INSERT UPDATE REPLACE DELETE queries.
• Remember to run export() to “commit” the database to the storage cache.

 

 

PART 4) SELECT

var demo = {
  // (D) DEMO - GET EVENT
  get : () => {
    // (D1) GET SINGLE ENTRY
    let stmt = demo.db.prepare("SELECT * FROM events WHERE id=$id"),
    res = stmt.getAsObject({$id:2});
    console.log(res);

    // (D2) GET MULTIPLE ENTRIES
    stmt = demo.db.prepare(
      `SELECT * FROM events
      WHERE date BETWEEN $start AND $end`);
    stmt.bind({$start:"2077-06-01 00:00:00", $end:"2077-06-08 00:00:00"})
    while (stmt.step()) { 
      res = stmt.getAsObject();
      console.log(res);
    }
  }
};

• To SELECT a single row from the database:
  • let stmt = db.prepare(SELECT * FROM table WHERE VAR=$var);
  • let row = stmt.getAsObject({$var:"VAR"});
• To get multiple rows from the database:
  • let stmt = db.prepare(SELECT * FROM table WHERE VAR=$var);
  • stmt.bind({$var:"VAR"});
  • while (stmt.step()) { let r = stmt.getAsObject(); }

 

 

 

HOW TO “RESET” THE TABLE

• It’s SQL… TRUNCATE TABLE works as usual.
• Or delete the database file from the storage cache – demo.cache.delete("/demo.sqlite").

 

COMPATIBILITY CHECKS

• Arrow Functions – CanIUse
• Web Assembly – CanIUse
• Cache Storage – CanIUse

This example will work on all modern “Grade A” browsers.

 

LINKS & REFERENCES

• SQLJS – GitHub
• SQLJS Documentation
• Cache Storage – MDN