Can I user sql.js in a server-less manner?

Question

Can I user sql.js in a server-less manner?

206 Views Asked by Bill Bell At 08 November 2021 at 18:06

I want to do read-only access to a SQLite database in a browser, where I get the necessary sql.js codes from the Windows folder (rather than a server) where the database is housed.

Is this possible and, if so, how? I could show you 'my code' in the time-honoured way but nothing gets close.

Original Q&A

There are 1 best solutions below

**kofeigen** · Answer 1 · 2022-07-24T10:10:55.887000

Yes it is possible. The HTML and JavaScript sample below works on my localhost web server. These two references helped me find the answer:

Example: Using object URLs to display images
Implement SQLite on web with JavaScript — both Node & Browser with sql.js plugin

I combined parts of both references to create the sample. Please note that the sql-wasm.wasm and sql-wasm.js files are expected to be in the same directory as the HTML and JavaScript files. I hope the demo explains itself. If you have problems making it work, post a comment and I will try to provide it online somewhere.

index.html

<!DOCTYPE html>
<html>
    <head>
        <title>SQL.js sqlite3 Upload Test</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <link rel="icon" href="data:,">
        <script type="text/javascript" src="sql-wasm.js"></script>
    </head>
    <body>
        <label for="query">SQL Query</label>: <input id="query" type="text" size="60" maxlength="200" value="" placeholder="SELECT * FROM [employees]"  /><br/>
        <label for="resultColumn">Column Name</label>: <input id="resultColumn" type="text" size="30" maxlength="30" value="" placeholder="first_name" /><br/>
    
        <input type="file" id="fileElem" multiple accept="application/octet-stream" style="display:none">
        <a href="#" id="fileSelect">Select some files</a>
        <div id="fileList">
          <p>No files selected!</p>
        </div>

        <script type="text/javascript" src="sqljs-import.js"></script>
        <noscript> Please enable javascript to view the site </noscript>
    </body>
</html>

sqljs-import.js

if (!window.FileReader) {
    alert('Your browser does not support HTML5 \'FileReader\' function required to open a file.');
    throw new Error('Your browser does not support HTML5 \'FileReader\' function required to open a file.');
}

const fileSelect = document.getElementById("fileSelect"),
        fileElem = document.getElementById("fileElem"),
        fileList = document.getElementById("fileList"),
        queryElem = document.getElementById("query"),
        resultElem = document.getElementById("resultColumn");

/**
 * Event listener callback for the FileReader onload event.
 *
 */
function onLoadCallback(fileEvent) {
    const initSqlJsPromise = initSqlJs({
        locateFile: function(file){ return `${file}`; }
    });

    initSqlJsPromise.then(function(SQL) {
        const query = queryElem.value;
        const resultColumn = resultElem.value;

        fileList.innerHTML = "";
        const list = document.createElement("ul");
        fileList.appendChild(list);

        const uInt8Array = new Uint8Array( fileEvent.target.result );
        const db = new SQL.Database(uInt8Array);

        const stmt = db.prepare(query);
        while (stmt.step()) {
            const row = stmt.getAsObject()
            const li = document.createElement("li");
            list.appendChild(li);
            const info = document.createElement("span");
            info.innerHTML = `${resultColumn}: ${row[resultColumn]}`;
            li.appendChild(info);
        }
    });
}

/**
 * Event listener callback for the file upload input field.
 *
 */
function handleFiles() {
    if (!this.files.length) {
        fileList.innerHTML = "<p>No files selected!</p>";
    } else {
        var filereader = new FileReader();
        filereader.onload = onLoadCallback;

        for (let i = 0; i < this.files.length; i++) {
            filereader.readAsArrayBuffer(this.files[i]);
        }
    }
}

fileSelect.addEventListener("click", function (e) {
    if (fileElem) {
        fileElem.click();
    }
    e.preventDefault(); // prevent navigation to "#"
}, false);

fileElem.addEventListener("change", handleFiles, false);

Can I user sql.js in a server-less manner?

There are 1 best solutions below

index.html

sqljs-import.js

Related Questions in JAVASCRIPT

Related Questions in SQLITE

Related Questions in BROWSER

Related Questions in SQL.JS

Trending Questions

Popular # Hahtags

Popular Questions