jeep-sqlite
jeep-sqlite
is 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.js
for SQLite queries and localforage
for 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 | ✅ |
executeSet | ✅ |
run | ✅ |
query | ✅ |
deleteDatabase | ✅ |
isDBExists | ✅ |
isDBOpen | ✅ |
isStoreOpen | ✅ |
isTableExists | ✅ |
createSyncTable | ✅ |
getSyncDate | ✅ |
setSyncDate | ✅ |
isJsonValid | ✅ |
importFromJson | ✅ |
isJsonValid | ✅ |
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
});
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)}`);
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");
}
ret = await jeepSqlite.query({database: "testNew",
statement: "SELECT * FROM users;"});
console.log(`after Query 1 ${JSON.stringify(ret)}`);
ret = await jeepSqlite.query({database: "testNew",
statement: "SELECT * FROM users where size > ?;",
values:[1.80]});
console.log(`after Query 2 ${JSON.stringify(ret)}`);
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");
}
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");
}
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.createConnection({
database:"testSet",
version: 1
});
ret = await jeepSqlite.isDBExists({database:"testSet"});
console.log(`is "testSet" database exist : ${ret.result}`);
if (ret.result) {
await jeepSqlite.deleteDatabase({database:"testSet"});
}
const createSchemaContacts = `
CREATE TABLE IF NOT EXISTS contacts (
id INTEGER PRIMARY KEY NOT NULL,
email TEXT UNIQUE NOT NULL,
name TEXT,
FirstName TEXT,
company TEXT,
size REAL,
age INTEGER,
MobileNumber TEXT
);
CREATE INDEX IF NOT EXISTS contacts_index_name ON contacts (name);
CREATE INDEX IF NOT EXISTS contacts_index_email ON contacts (email);
PRAGMA user_version = 1;
`;
await jeepSqlite.open({database: "testSet"});
const isDBSet = await jeepSqlite.isDBOpen({database: "testSet"})
const setContacts = [
{ statement:"INSERT INTO contacts (name,FirstName,email,company,age,MobileNumber) VALUES (?,?,?,?,?,?);",
values:["Simpson","Tom","Simpson@example.com",null,69,"4405060708"]
},
{ statement:"INSERT INTO contacts (name,FirstName,email,company,age,MobileNumber) VALUES (?,?,?,?,?,?);",
values:[
["Jones","David","Jones@example.com",,42.1,"4404030201"],
["Whiteley","Dave","Whiteley@example.com",,45.3,"4405162732"],
["Brown","John","Brown@example.com",null,35,"4405243853"]
]
},
{ statement:"UPDATE contacts SET age = ? , MobileNumber = ? WHERE id = ?;",
values:[51.4,"4404030202",2]
}
];
ret = await jeepSqlite.execute({database: "testSet", statements: createSchemaContacts});
console.log(`after Contact Execute 1 ${JSON.stringify(ret)}`);
ret = await jeepSqlite.executeSet({database: "testSet", set: setContacts});
console.log(`after Contact Execute 1 ${JSON.stringify(ret)}`);
if (ret.changes.changes !== 5) {
return Promise.reject(new Error("ExecuteSet 5 contacts failed"));
}
await jeepSqlite.closeConnection({database:"testNew"});
await jeepSqlite.closeConnection({database:"testSet"});
console.log("db success");
} catch (err) {
console.log(`Error ${err}`);
}
} else {
console.log("store creation failed")
}
})();
</script>