Cookie Consent by Free Privacy Policy Generator ๐Ÿ“Œ From Web SQL to SQLite Wasm: the database migration guide

๐Ÿ  Team IT Security News

TSecurity.de ist eine Online-Plattform, die sich auf die Bereitstellung von Informationen,alle 15 Minuten neuste Nachrichten, Bildungsressourcen und Dienstleistungen rund um das Thema IT-Sicherheit spezialisiert hat.
Ob es sich um aktuelle Nachrichten, Fachartikel, Blogbeitrรคge, Webinare, Tutorials, oder Tipps & Tricks handelt, TSecurity.de bietet seinen Nutzern einen umfassenden รœberblick รผber die wichtigsten Aspekte der IT-Sicherheit in einer sich stรคndig verรคndernden digitalen Welt.

16.12.2023 - TIP: Wer den Cookie Consent Banner akzeptiert, kann z.B. von Englisch nach Deutsch รผbersetzen, erst Englisch auswรคhlen dann wieder Deutsch!

Google Android Playstore Download Button fรผr Team IT Security



๐Ÿ“š From Web SQL to SQLite Wasm: the database migration guide


๐Ÿ’ก Newskategorie: Programmierung
๐Ÿ”— Quelle: developer.chrome.com

Required background

The post Deprecating and removing Web SQL announced the deprecation of the Web SQL database technology. While the technology itself may be deprecated, the use cases addressed by the technology very much are not, so the follow-up post SQLite Wasm in the browser backed by the Origin Private File System, outlines a replacement set of technologies based on the SQLite database, compiled to Web Assembly (Wasm), and backed by the origin private file system. To close the circle, this article shows how to migrate databases over from Web SQL to SQLite Wasm.

Migrating your databases

The following four steps demonstrate the conceptual idea of migrating a Web SQL database over to SQLite Wasm, with the SQLite database backed by the origin private file system. This can serve as the foundation for your own code customized to your Web SQL migration needs.

The to-be-migrated Web SQL database(s)

The baseline assumption of this migration guide is that you have one (or several) existing Web SQL databases that hold data relevant to your app. In the screenshot below, you see an example database called mydatabase with a rainstorms table that maps moods to severities. Chrome DevTools allow you to view Web SQL databases for debugging, as shown in the following screenshot.

A Web SQL database inspected in Chrome's DevTools. The database is called mydatabase and hosts a table with three columns: row ID, mood, and severity. There are three rows of sample data.

Translating the Web SQL database to SQL statements

To migrate the data in a way that is transparent to the user, that is, without requiring them to perform any of the migration steps on their own, the pieces of data in the database need to be translated back to the original SQL statements that created them in the first place. This challenge has come up before, and the migration script used in this articleโ€”mywebsqldump.jsโ€”is based on a community library called websqldump.js, with some minor adjustments. The following code sample shows the code required to translate the Web SQL database mydatabase to a set of SQL statements.

websqldump.export({
database: 'mydatabase',
version: '1.0',
success: function(sql) {
// The SQL statements.
},
error: function(err) {
// Handle the error.
}
});

Running this code results in the SQL statements string below.

CREATE TABLE IF NOT EXISTS rainstorms (mood text, severity int);
INSERT INTO rainstorms(mood,severity) VALUES ('somber','6');
INSERT INTO rainstorms(mood,severity) VALUES ('rainy','8');
INSERT INTO rainstorms(mood,severity) VALUES ('stormy','2');
Warning

The outlined steps show the conceptual idea tested on a small database. For migrating production-size databases, additional performance tuning steps may be necessary. For example, preprocessing the SQL statements string. One optimization would be to group the INSERT statements: INSERT INTO rainstorms VALUES ('somber',6), ('rainy',8), ('stormy',2). You may also have to further patch mywebsqldump.js, so it correctly deals with corner cases present in your Web SQL data.

Importing the data into SQLite Wasm

All that remains is executing these SQL commands in the context of SQLite Wasm. For all details regarding setting SQLite Wasm up, I refer you to the article SQLite Wasm in the browser backed by the Origin Private File System, but the gist is again below. Remember that this code needs to run in a Worker, with the required HTTP headers set correctly.

import { default as sqlite3InitModule } from './sqlite3.mjs';

const createSQLiteDatabase = (sqlite3, database, sql) => {
let db;
const fileName = `/${database}.db`;
if (sqlite3.opfs) {
db = new sqlite3.oo1.OpfsDb(fileName);
} else {
db = new sqlite3.oo1.DB(fileName, 'ct');
}
try {
sql.split(';').forEach((sqlStatement) => {
sqlStatement += ';';
db.exec(sqlStatement);
});
} catch (err) {
db.close();
console.error(err.name, err.message);
return;
}
};

self.sqlite3InitModule().then(function (sqlite3) {
// Hardcoded for brevity. You pass the two variables `database` and `sql`
// to the Worker from the main thread.
const database = 'mydatabase';
const sql = `
CREATE TABLE IF NOT EXISTS rainstorms (mood text, severity int);
INSERT INTO rainstorms(mood,severity) VALUES ('somber','6');
INSERT INTO rainstorms(mood,severity) VALUES ('rainy','8');
INSERT INTO rainstorms(mood,severity) VALUES ('stormy','2');
`
;
try {
createSQLiteDatabase(sqlite3, database, sql);
} catch (e) {
error('Exception:', e.message);
}
});

After running this code, inspect the imported database file with the OPFS Explorer Chrome DevTools extension. There are two files now, one with the actual database, and one with journaling information. Note that these two files live in the origin private file system, so you need to use the OPFS Explorer extension to see them.

Inspecting the origin private file system with the OPFS Explorer Chrome DevTools. There are two files, one called mydatabase.db and one called mydatabase.db-journal.

To actually verify that the imported data is the same as the initial Web SQL data, click the file mydatabase.db and the OPFS Explorer extension will show a Save File dialog to let you save the file in the user-visible file system. With the database file saved, use a SQLite viewer app to explore the data. The Project Fugu API Showcase features several apps for working with SQLite in the browser. For example, Sqlime โ€” SQLite Playground lets you open a SQLite database file from your hard disk and run queries on the database. As you see in the screenshot below, the rainstorm table has been correctly imported into SQLite.

Exploring the mydatabase.db file in the Sqlime SQLite Playground tool. The app is shown with the SQL query select star from rainstorms limit 10 being run, resulting in the three rows from the initial sample data from Web SQL.

Freeing Web SQL storage

While it's (maybe surprisingly) impossible to delete a Web SQL database, you should still free some storage by dropping the now obsolete Web SQL tables after you have migrated the data into SQLite Wasm. To list all tables in a Web SQL database and drop them using JavaScript, use code as in the following snippet:

const dropAllTables = () => {
try {
db.transaction(function (tx) {
tx.executeSql(
"SELECT name FROM sqlite_master WHERE type='table' AND name !='__WebKitDatabaseInfoTable__'",
[],
function (tx, result) {
const len = result.rows.length;
const tableNames = [];
for (let i = 0; i < len; i++) {
const tableName = result.rows.item(i).name;
tableNames.push(`'${tableName}'`);
db.transaction(function (tx) {
tx.executeSql('DROP TABLE ' + tableName);
});
}
console.log(`Dropped table${tableNames.length > 1 ? 's' : ''}: ${tableNames.join(', ')}.`);
}
);
});
} catch (err) {
console.error(err.name, err.message);
}
};

Working with the data after the migration

After you have migrated the data, work with the data as outlined in this Getting started code sample. See the SQLite Wasm API reference for details. Again a reminder that you need to access SQLite Wasm from a Worker if you use the origin private file system as your storage backend.

Test it out

This demo lets you populate a Web SQL database with sample data, then dumps the Web SQL data as SQL statements, which next get imported into SQLite Wasm backed by the origin private file system. Finally, you free storage by deleting the obsolete Web SQL data. Check the source code for the full implementation, including the patched mywebsqldump.js file.

The demo app at web-sql-to-sqlite-wasm.glitch.me.

Conclusions

Migrating your Web SQL databases to SQLite Wasm backed by the origin private file system is possible in a way transparent to your users. They will not notice that their data is now hosted in the origin private file system in a SQLite database, and no longer lives in Web SQL. Overall, migrating from Web SQL to SQLite is a necessary step for web developers who want to ensure the long-term stability and scalability of their applications. While the process may require some initial effort, the benefits of a more robust, flexible, and, above all, future-proof database solution make it well worth the investment.

...



๐Ÿ“Œ From Web SQL to SQLite Wasm: the database migration guide


๐Ÿ“ˆ 66.56 Punkte

๐Ÿ“Œ Vuln: radare2 '/format/wasm/wasm.c' Heap Buffer Overflow Vulnerability


๐Ÿ“ˆ 39.64 Punkte

๐Ÿ“Œ radare2 WASM File wasm.c wasm_dis() Pufferรผberlauf


๐Ÿ“ˆ 39.64 Punkte

๐Ÿ“Œ radare2 WASM File wasm.c wasm_dis() memory corruption


๐Ÿ“ˆ 39.64 Punkte

๐Ÿ“Œ Binaryen 1.38.22 wasm/wasm-binary.cpp processFunctions() denial of service


๐Ÿ“ˆ 39.64 Punkte

๐Ÿ“Œ Binaryen 1.38.22 wasm/wasm-binary.cpp processFunctions() memory corruption


๐Ÿ“ˆ 39.64 Punkte

๐Ÿ“Œ Binaryen 1.38.22 wasm/wasm.cpp getFunctionOrNull denial of service


๐Ÿ“ˆ 39.64 Punkte

๐Ÿ“Œ Binaryen 1.38.22 WASM File wasm-binary.cpp getType() denial of service


๐Ÿ“ˆ 39.64 Punkte

๐Ÿ“Œ CVE-2023-27114 | radare2 5.8.3 p/wasm/wasm.c wasm_dis memory corruption (ID 21363)


๐Ÿ“ˆ 39.64 Punkte

๐Ÿ“Œ SQLite Wasm in the browser backed by the Origin Private File System


๐Ÿ“ˆ 33.4 Punkte

๐Ÿ“Œ SQLite in Wasm: A Glimpse into the Future


๐Ÿ“ˆ 33.4 Punkte

๐Ÿ“Œ Database Migration Plan: Avoiding Common Pitfalls in Open Source Migration


๐Ÿ“ˆ 29.92 Punkte

๐Ÿ“Œ Medium CVE-2019-5018: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2019-8457: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2019-16168: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2019-20218: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2020-9327: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2020-11655: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2020-11656: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2020-13434: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2020-13435: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2020-13632: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2020-13631: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2020-13630: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2020-13871: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Medium CVE-2020-15358: Sqlite Sqlite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Die besten Alternativen zu SQLite, diese Software und Apps sind รคhnlich wie SQLite


๐Ÿ“ˆ 27.16 Punkte

๐Ÿ“Œ Migrating to SQL: Cloud Migration Strategies and Phases in Migration Journey (Ep. 1) | Data Exposed


๐Ÿ“ˆ 26.46 Punkte

๐Ÿ“Œ Migrating to SQL: Cloud Migration Strategies and Phases in Migration Journey (Ep. 1) | Data Exposed


๐Ÿ“ˆ 26.46 Punkte

๐Ÿ“Œ From Proprietary to Open Source: The Complete Guide to Database Migration


๐Ÿ“ˆ 25.32 Punkte

๐Ÿ“Œ SQLite 3.40.1 - SQL database connection development library.


๐Ÿ“ˆ 24.9 Punkte

๐Ÿ“Œ radare2 1.3.0 Web Assembly File wasm.c consume_init_expr denial of service


๐Ÿ“ˆ 23.73 Punkte

๐Ÿ“Œ heise-Angebot: Wasm 2021: Heise prรคsentiert WebAssembly, den Game Changer auch jenseits des Web


๐Ÿ“ˆ 23.73 Punkte

๐Ÿ“Œ Deploying your Rust WASM Game to Web with Shuttle & Axum


๐Ÿ“ˆ 23.73 Punkte

๐Ÿ“Œ radare2 1.3.0 Web Assembly File wasm.c consume_init_expr Denial of Service


๐Ÿ“ˆ 23.73 Punkte











matomo