jeep-sqliteis a Stencil component to create SQLite database and query it in the browser. The entire database is stored in an IndexedDB store named jeepSQLiteStore in a table databases. Multiple databases can be stored on this table.

jeep-sqlite is based on sql.jsfor SQLite queries and localforagefor database storage in IndexedDB.

This component might be used in PWA applications. It will also be used in the web implementation of the @capacitor-community-sqlite.

This is the reason for having similar API than the @capacitor-community-sqlite.

This is the initial version ALPHA and does not include all the functionalities especially in the import and export of databases.

It will be used at that stage to test the integration with the @capacitor-community-sqlite but can also be used in development of Stencil or Ionic/Angular applications.

Integration in other frameworks (Vue, React, Ionic/Vue, Ionic/React) will be looked at later but if some of you want to contribute feel free.

Stencil is also great for building entire apps. For that, use the stencil-app-starter instead.

Stencil

Stencil is a compiler for building fast web apps using Web Components.

Stencil combines the best concepts of the most popular frontend frameworks into a compile-time rather than run-time tool. Stencil takes TypeScript, JSX, a tiny virtual DOM layer, efficient one-way data binding, an asynchronous rendering pipeline (similar to React Fiber), and lazy-loading out of the box, and generates 100% standards-based Web Components that run in any browser supporting the Custom Elements v1 spec.

Stencil components are just Web Components, so they work in any major framework or with no framework at all.

Getting Started

Script tag

Put a script tag similar to this

<script type="module" src="https://unpkg.com/jeep-sqlite/dist/jeep-sqlite.esm.js"></script>
<script nomodule src="https://unpkg.com/jeep-sqlite/dist/jeep-sqlite.js"></script>

in the head of your index.html

Then you can use the element anywhere in your template, JSX, html etc

Node Modules

Run npm install jeep-sqlite --save
Put a script tag similar to this <script src='node_modules/jeep-sqlite/dist/jeep-sqlite.esm.js'></script> in the head of your index.html
Then you can use the element anywhere in your template, JSX, html etc

In a stencil-starter app

Run npm install jeep-sqlite --save
Add an import to the npm packages import jeep-sqlite;
Then you can use the element anywhere in your template, JSX, html etc

Supported methods

Name	Web
createConnection	✅
closeConnection	✅
open (non-encrypted DB)	✅
close	✅
execute	✅
run	✅
query	✅
deleteDatabase	✅
isDBExists	✅
isDBOpen	✅
isStoreOpen	✅

Usage

<!DOCTYPE html>
<html dir="ltr" lang="en">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0, minimum-scale=1.0, maximum-scale=5.0" />
    <title>Stencil Component Starter</title>

    <script type="module" src="https://unpkg.com/jeep-sqlite/dist/jeep-sqlite.esm.js"></script>
    <script nomodule src="https://unpkg.com/jeep-sqlite/dist/jeep-sqlite.js"></script>
  </head>
  <body>
    <jeep-sqlite></jeep-sqlite>
  </body>
</html>
<script>
  (async () => {
    await customElements.whenDefined('jeep-sqlite');
    const jeepSqlite = document.querySelector('jeep-sqlite');
    console.log("jeepSqlite " + JSON.stringify(jeepSqlite));
      console.log("$$$ in script before createConnection");
      let result = await jeepSqlite.echo("Hello World from Jeep");
      console.log("from Echo " + result.value);
      if(await jeepSqlite.isStoreOpen()) {
          try {
            await jeepSqlite.createConnection({
                      database:"testNew",
                      version: 1
                  });
            // open db testNew
              await jeepSqlite.open({database: "testNew"});
              const isDB = await jeepSqlite.isDBOpen({database: "testNew"})
              console.log(`in script ${JSON.stringify(isDB)}`);
              let sql = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL,email TEXT UNIQUE NOT NULL,name TEXT,company TEXT,size REAL,age INTEGER,last_modified INTEGER DEFAULT (strftime('%s', 'now')));";
              sql += "CREATE INDEX IF NOT EXISTS users_index_name ON users (name);";
              sql += "CREATE INDEX IF NOT EXISTS users_index_last_modified ON users (last_modified);";
              sql += "CREATE TRIGGER IF NOT EXISTS users_trigger_last_modified AFTER UPDATE ON users FOR EACH ROW WHEN NEW.last_modified <= OLD.last_modified BEGIN UPDATE users SET last_modified= (strftime('%s', 'now')) WHERE id=OLD.id; END;";
              sql += "PRAGMA user_version = 1;";
              console.log("@@@ sql " + sql);
              let ret = await jeepSqlite.execute({database: "testNew", statements: sql});
              console.log(`after Execute 1 ${JSON.stringify(ret)}`);
              // Insert some Users
              const row = [["Whiteley","Whiteley.com",30,1.83],["Jones","Jones.com",44,1.75]];
              let delUsers = `DELETE FROM users;`;
              delUsers += `VACUUM;`;
              ret = await jeepSqlite.execute({database: "testNew", statements: delUsers, transaction: false});
              console.log(`after Execute 2 ${JSON.stringify(ret)}`);
              let twoUsers = `INSERT INTO users (name,email,age,size) VALUES ("${row[0][0]}","${row[0][1]}",${row[0][2]},${row[0][3]});`;
              twoUsers += `INSERT INTO users (name,email,age,size) VALUES ("${row[1][0]}","${row[1][1]}",${row[1][2]},${row[1][3]});`;
              ret = await jeepSqlite.execute({database: "testNew", statements: twoUsers});
              console.log(`after Execute 3 ${JSON.stringify(ret)}`);
              if (ret.changes.changes !== 2) {
                throw new Error("Execute 3 users failed");
              }
              // Select all users
              ret = await jeepSqlite.query({database: "testNew",
                                            statement: "SELECT * FROM users;"});
              console.log(`after Query 1 ${JSON.stringify(ret)}`);
              // Select users where size > 1.80
              ret = await jeepSqlite.query({database: "testNew",
                                            statement: "SELECT * FROM users where size > ?;",
                                            values:[1.80]});
              console.log(`after Query 2 ${JSON.stringify(ret)}`);
              // add one user with statement and values
              let sqlcmd = "INSERT INTO users (name,email,age,size,company) VALUES (?,?,?,?,?)";
              let values = ["Simpson","Simpson@example.com",69,1.82,null];
              ret = await jeepSqlite.run({database: "testNew",
                                            statement: sqlcmd,
                                            values: values});
              console.log(`after run 1: ${JSON.stringify(ret)} `);
              if(ret.changes.lastId !== 3) {
                throw new Error("Run 1 user failed");
              }
              // add one user with statement
              sqlcmd = `INSERT INTO users (name,email,age,size,company) VALUES ` +
                                `("Brown","Brown@example.com",15,1.75,null)`;
              ret = await jeepSqlite.run({database: "testNew",
                          statement: sqlcmd});
              if(ret.changes.lastId !== 4) {
                throw new Error("Run 2 user failed");
              }
              // Select all users
              ret = await jeepSqlite.query({database: "testNew",
                                            statement: "SELECT * FROM users;"});
              console.log(`after Query 3 ${JSON.stringify(ret)}`);
              if(ret.values.length != 4) {
                throw new Error("Query 3 user failed");
              }


              await jeepSqlite.closeConnection({database:"testNew"});
              console.log("db success");
          } catch (err) {
            console.log(`Error ${err}`);
          }
      } else {
        console.log("store creation failed")
      }
  })();
</script>

FAQs

What is jeep-sqlite?

Is jeep-sqlite popular?

Is jeep-sqlite well maintained?

Last updated on 28 Jul 2021

Did you know?

Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.

Install