Skip to content

Instantly share code, notes, and snippets.

@code-boxx
Created September 16, 2023 11:59
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save code-boxx/60b523b2d5054fa03b9ca638637b2057 to your computer and use it in GitHub Desktop.
Save code-boxx/60b523b2d5054fa03b9ca638637b2057 to your computer and use it in GitHub Desktop.
Javascript SQLite

JAVASCRIPT SQLITE

https://code-boxx.com/sqlite-javascript/

LICENSE

Copyright by Code Boxx

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

<!DOCTYPE html>
<html>
<head>
<!-- https://cdnjs.com/libraries/sql.js -->
<!-- https://github.com/sql-js/sql.js -->
<title>Javascript SQLite</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/sql-wasm.js"></script>
<script src="sqljs.js"></script>
</head>
<body>
</body>
</html>
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())
),
// (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();
},
// (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);
}
}
};
// (E) GO!
window.addEventListener("load", demo.init);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment