reinforcedlearningwithaisupport:sqliteinthebrowserpoc [The Rheology of Software]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Proof of Concept: Can I run SQLite in the browser without a server? ======

Last time I looked into this I tried taking the [[https://sql.js.org/examples/GUI/|online example for Sql.js]] and run it as a standalone file.  It didn't go well, and reading up on WASM I get the impression that WASM was not going to let me run locally, but I couldn't find any solid confirmation of whether or not it was posssible.

If I'm to embed SQLite within TiddlyWiki I need to run it locally as a standalone, so I ask ChatGPT if it can be done:

>Is it possible to run SqlIte in the browser from a file, without any web server?

>>Yes! You can run SQLite directly in the browser without any web server, using SQLite Wasm (WebAssembly). This lets you open a database file, run queries, and even save changes — all in the client side.
>>
>>Here’s a simple way to do it:
>>* **Download SQLite Wasm**  Get the SQLite Wasm bundle from the official SQLite site or a CDN.
>>* **Create an HTML file**  Save this as index.html:
<code html>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SQLite in Browser</title>
</head>
<body>
    <h1>SQLite in Browser</h1>
    <input type="file" id="fileInput" />
    <pre id="output"></pre>

    <script src="https://cdn.jsdelivr.net/npm/sql.js@1.8.0/dist/sql-wasm.js"></script>
    <script>
        let db;

        document.getElementById('fileInput').addEventListener('change', async (event) => {
            const file = event.target.files[0];
            if (file) {
                const buffer = await file.arrayBuffer();
                const SQL = await initSqlJs({ locateFile: filename => `https://cdn.jsdelivr.net/npm/sql.js@1.8.0/dist/${filename}` });
                db = new SQL.Database(new Uint8Array(buffer));

                const result = db.exec("SELECT name FROM sqlite_master WHERE type='table';");
                document.getElementById('output').textContent = JSON.stringify(result, null, 2);
            }
        });
    </script>
</body>
</html>
</code>
>>* **Open the file in a browser**  Just double-click the index.html. No need for a web server — it runs purely on the client side.
>>
>>//How it works//
>>* The SQLite Wasm engine processes the .sqlite file.
>>* Queries run directly in the browser.
>>* The output shows available tables — you can expand this to support custom queries.

I follow the instructions but I hit a problem.  It want's me to select a local database file and i don't have one.  So I ask for a fix.

===== I don't want a local database file =====

> Is a file necessary?  Can I create a purely inmemory db?
>> Yes! You can create a purely in-memory SQLite database in the browser — no file needed. Here’s a simple example using sql.js:
<code html>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>In-Memory SQLite</title>
</head>
<body>
    <h1>SQLite In-Memory DB</h1>
    <button id="runQuery">Run Query</button>
    <pre id="output"></pre>

    <script src="https://cdn.jsdelivr.net/npm/sql.js@1.8.0/dist/sql-wasm.js"></script>
    <script>
        (async () => {
            const SQL = await initSqlJs({ locateFile: file => `https://cdn.jsdelivr.net/npm/sql.js@1.8.0/dist/${file}` });
            const db = new SQL.Database(); // In-memory database

            document.getElementById('runQuery').addEventListener('click', () => {
                db.run("CREATE TABLE test (id INTEGER, name TEXT);");
                db.run("INSERT INTO test VALUES (1, 'Alice'), (2, 'Bob');");

                const result = db.exec("SELECT * FROM test;");
                document.getElementById('output').textContent = JSON.stringify(result, null, 2);
            });
        })();
    </script>
</body>
</html>
</code>

I run this and it works, giving me the following result set in a JSON format:
<code javascript>
[
  {
    "columns": [
      "id",
      "name"
    ],
    "values": [
      [
        1,
        "Alice"
      ],
      [
        2,
        "Bob"
      ]
    ]
  }
]
</code>

===== Loading WASM from a Base64 encoded string =====

It's still loading code and WASM from a server.  I need it to be entirely offline.

> I'd like to run this offline.  Can I download the js and wasm files and reference them locally?
>> Yes! You can absolutely run SQLite (with sql.js) offline by downloading the necessary files. Let’s set this up step by step.

The instructions a straight forward.  I download the file and I change the uri in the initSqlJs constructor to this:

<code javascript>
// Initialize sql.js with local wasm file
initSqlJs({
  locateFile: file => `lib/${file}`
}).then(SQL => { ... }
</code>

This is when I run into the same CORS Policy problems that blocked me in the past:

<code>
Access to fetch at 'file:///C:/Users/gedby/OneDrive/Desktop/sqljs/lib/sql-wasm.wasm' from origin 'null' has been blocked by CORS policy: Cross origin requests are only supported for protocol schemes: chrome-extension, chrome-untrusted, data, edge, http, https, isolated-app.

Failed to load resource: net::ERR_FAILED (lib/sql-wasm.wasm:1)

Uncaught (in promise) TypeError: Failed to fetch (sql-wasm.js:159)
    at sql-wasm.js:159:276
    at sql-wasm.js:159:499
    at sql-wasm.js:160:11
    at new Promise (<anonymous>)
    at initSqlJs (sql-wasm.js:22:24)
    at Test.html:16:31
    at Test.html:26:11
</code>

I tell ChatGPT about the error, and it confidently provides me with a fix.

===== Fixing the CORS issue =====

>> Ah, yes — this happens because modern browsers block fetch requests for local files due to CORS restrictions. Even though you’re running things locally, loading ''.wasm'' files via ''%%file://%%'' doesn’t work by default.
>> Let’s fix it! 🚀

It then gives me three possible options:
1. Run a simple local server (Recommended)
1. Use a VS Code extension (even simpler)
1. Not recommended) Disable CORS checks temporarily

None of these really work for me.  For the WASM to work in TiddlyWiki I'll need to load the content from within the file.  This is how TiddlyWiki manages all of it's resources, including Javascript.  I wonder if this simply isn't possible with WASM.

I explain my problem to ChatGPT, and it confidently provides the answer:

> I don't really like any of these options.  The goal is to load the WASM from within my TiddlyWiki.
> Is there anyway of loading WASM from data embedded in my HTML file, perhaps BASE64 encoded?
>> Yes! You can absolutely embed the WebAssembly (WASM) directly into your TiddlyWiki without needing any external files or servers. Let’s tackle this step by step. 🚀

I am loving this AI's can do attitude, so I follow the steps provided.

First of all I convert my WASM file to Base64.  To do this I'm given this powershell one liner.:
<code powershell>
[convert]::ToBase64String([IO.File]::ReadAllBytes("sql-wasm.wasm")) | Out-File "sql-wasm.base64.txt"
</code>

Then I copy the Base64 text into a string so that I can loading it into a binary array.

One thing I like at this point is that ChatGPT doesn't generate the whole page for me.  Instead it just provides the code related to the task at hand, which I'm able to adapt into my own code base.

<code javascript>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Embedded SQLite with WASM</title>
</head>
<body>
  <h1>SQLite with Embedded WASM</h1>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/sql-wasm.js"></script>
  <script>
    // Paste the long Base64-encoded WASM here
    const wasmBase64 = "BASE64_ENCODED_STRING_HERE";

    // Convert Base64 to binary
    const binaryString = atob(wasmBase64);
    const bytes = new Uint8Array(binaryString.length);
    for (let i = 0; i < binaryString.length; i++) {
      bytes[i] = binaryString.charCodeAt(i);
    }
    const wasmBinary = bytes.buffer;

    // Initialize sql.js using the in-memory WASM binary
    initSqlJs({
      locateFile: () => URL.createObjectURL(new Blob([wasmBinary], { type: "application/wasm" }))
    }).then(SQL => {
      const db = new SQL.Database(); // In-memory SQLite DB
      db.run("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);");
      db.run("INSERT INTO test (name) VALUES ('Alice');");

      const res = db.exec("SELECT * FROM test;");
      console.log("Query result:", res);
    }).catch(err => {
      console.error("Failed to initialize sql.js:", err);
    });
  </script>
</body>
</html>
</code>

There are two problems with this code.

  - ChatGPT has decided to drop functionality from the code we are writing.  In the previous version, the code was integrated with the HTML, with the query being run when the "Run Query" button is pressed and the results written to a preformatted "output" element.  Now the code is run on load and the result is written to console.  It is this type of inconsistency that frustrates me when using ChatGPT to write code for me.  It leads to disconnected code that is hard to maintain.
  - It doesn't work, so I tell ChatGPT about the error.

> I'm getting the error: wasm streaming compile failed: TypeError: Failed to execute 'compile' on 'WebAssembly': Incorrect response MIME type. Expected 'application/wasm'.

And now we see how ChatGPT shines as a research assistant and guide, because it immediately identified the problem and provides the fix:

===== Fixing the MIME type =====

>>Ah, that's happening because the WebAssembly streaming compiler expects the MIME type to be application/wasm — but when you're embedding the WASM as a Base64 string, the browser doesn't get the right content type automatically.
>>
>>Let’s fix this by using %%WebAssembly.compile%% with the decoded binary, bypassing streaming:

It gives me a full page of code, but the only part that I'm interested in is this:
<code>
// Initialize sql.js using the in-memory WASM binary
initSqlJs({
  locateFile: () => {
    // Create a blob for the binary and generate a URL
    const blob = new Blob([bytes], { type: "application/wasm" });
    return URL.createObjectURL(blob);
  }
}).then(SQL => { ... });
</code>

I list this %%locateFile%% definition, so that my final code looks like this:

<code html>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>In-Memory SQLite</title>
</head>
<body>
    <h1>SQLite In-Memory DB</h1>
    <button id="runQuery">Run Query</button>
    <pre id="output"></pre>

    <script src="lib/sql-wasm.js"></script>
    <script>
        const base64wasm = "...";
        const binaryString = atob(base64wasm);
        const bytes = new Uint8Array(binaryString.length);
        for (let i = 0; i < binaryString.length; i++) {
            bytes[i] = binaryString.charCodeAt(i);
        }

        (async () => {
            // Initialize sql.js using the in-memory WASM binary
            const sql = await initSqlJs({
                locateFile: () => {
                    // Create a blob for the binary and generate a URL
                    const blob = new Blob([bytes], { type: "application/wasm" });
                    return URL.createObjectURL(blob);
                }
            });

            const db = new sql.Database(); // In-memory SQLite DB

            document.getElementById('runQuery').addEventListener('click', () => {

                db.run("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);");
                db.run("INSERT INTO test (name) VALUES ('Alice'), ('Bob');");

                const result = db.exec("SELECT * FROM test;");
                document.getElementById('output').textContent = JSON.stringify(result, null, 2);
            });
        })();

    </script>
</body>
</html>
</code>

This completes my proof of concept.  I have a HTML file that can I open in Windows Explorer and it runs SQLite locally without any server.

The code is simple, and I mostly understand what it is doing.  There is one line that uses a JavaScript feature that I'm unfamiliar with: %%URL.createObjectURL(blob)%%.  Along with the code I was provided with a brief explanation:

>> **✅ Why this works:**
>>   * **BlobI** — creates a proper WASM binary blob with the correct MIME type.
>>   * **URL.createObjectURL** — gives a fake "file" URL the WebAssembly compiler can work with.
>>   * **Streaming bypassed** — since there’s no remote file, this sidesteps the CORS issue.

That isn't enough for me.  I want to know more.

===== Understanding URL.createObjectURL() =====

> Could you tell me more about what URL.createObjectURL does?

>> Absolutely! Let’s break down what URL.createObjectURL() does and why it’s useful, especially for what we’re doing.
>> 
>> **🔥 What is URL.createObjectURL?**
>> %%URL.createObjectURL()%% is a method that takes a Blob or File object and returns a temporary URL that points to the data in memory. ??>> This allows you to treat in-memory data — like a WASM binary — as if it were a file on a server.
>>
>> **Syntax:**
>> <code javascript>
const blob = new Blob([data], { type: 'application/wasm' });
const url = URL.createObjectURL(blob);
</code>
>>
>> The %%url%% now looks something like this:
>> <code>
blob:null/1f9c0c36-1b9a-4d7f-99f6-58f2d2e0a5b8
</code>
>>
>> **✅ Why use it here?**
>> When loading WebAssembly, the browser expects a proper file URL (usually ending in .wasm). However, if you’re embedding the WASM binary directly — like with a Base64 string — there’s no file to point to.
>>
>> By converting the binary into a Blob, and then using createObjectURL, we:
>> 
>>   * **Simulate a file**: The browser thinks the WASM is coming from a proper file, bypassing MIME-type and CORS issues.
>>   * **Avoid network requests**: Everything happens in memory — perfect for offline or embedded apps.
>>   * **Match WebAssembly expectations**: WASM compilation works seamlessly because the blob URL has the correct MIME type (application/wasm).
>>
>> ** Cleaning up the URL:**
>> These blob URLs are only valid while the page is open. If you don’t need them anymore (to free up memory):
>>
>> <code javascript>
URL.revokeObjectURL(url);
</code>
>>
>> You’d typically do this after the WebAssembly module is loaded.

That's a good explanation.  I know feel confident enough to accept the code that has been written.

My next question is about that final point, about clean up.

===== Clearning up the object URL =====

> Looking back at the SQLite code, we don’t cleanup our blob url.

>> You're absolutely right — we didn't clean up the blob URL after loading the SQLite WASM module. Let’s fix that!
>>
>> In the original code, we did something like this:
>> 
>><code javascript>
const blob = new Blob([wasmBinary], { type: 'application/wasm' });
const url = URL.createObjectURL(blob);

const sqlPromise = initSqlJs({ locateFile: () => url });

sqlPromise.then(SQL => {
  const db = new SQL.Database();
  console.log('SQLite database loaded in memory');
});
</code>
>>
>> **✅ Adding cleanup:**
>> We should revoke the blob URL once the WASM module is loaded:
>>
>> <code javascript>
const blob = new Blob([wasmBinary], { type: 'application/wasm' });
const url = URL.createObjectURL(blob);

const sqlPromise = initSqlJs({
  locateFile: () => url
});

sqlPromise.then(SQL => {
  const db = new SQL.Database();
  console.log('SQLite database loaded in memory');
  URL.revokeObjectURL(url); // Clean up blob URL after use
});
</code>
>> **✅ Why clean up?**
>> Memory leaks: Blobs can stick around in memory until you explicitly revoke them or the page is closed.
>> Best practice: Keeping your resources tidy is just good hygiene — especially for long-running apps like TiddlyWiki.
>> Would you like me to slip this fix into the plugin code? Let me know if you want the cleanup tied to a particular lifecycle event! 🚀

This is good guidance, and the implementation approach is well described, so I add the clean up code.  It creates a promise, which we already have in our code so I ask:

> initSwlJs() appears to return a promise.  Can we add the cleanup into a then()?

>> Yes, exactly. Since initSqlJs() returns a promise, you can add the cleanup in the .then() callback once the module is loaded. Here’s an updated code snippet that demonstrates this:
>> 
>> <code javascript>
// Convert the Base64-encoded WASM string to a binary array
const wasmBase64 = `
BASE64_LINE_1
BASE64_LINE_2
BASE64_LINE_3
`.replace(/\s+/g, '');
const binaryString = atob(wasmBase64);
const bytes = new Uint8Array(binaryString.length);
for (let i = 0; i < binaryString.length; i++) {
  bytes[i] = binaryString.charCodeAt(i);
}
const wasmBinary = bytes.buffer;

// Create a Blob from the binary data and get a blob URL
const blob = new Blob([wasmBinary], { type: 'application/wasm' });
const blobUrl = URL.createObjectURL(blob);

// Initialize sql.js using the in-memory WASM binary
initSqlJs({
  locateFile: () => blobUrl
}).then(SQL => {
  // The SQL module is ready to use.
  const db = new SQL.Database();
  db.run("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);");
  db.run("INSERT INTO test (name) VALUES ('Alice');");
  
  const res = db.exec("SELECT * FROM test;");
  console.log("Query result:", res);

  // Cleanup the blob URL after loading the WASM module
  URL.revokeObjectURL(blobUrl);
}).catch(err => {
  console.error("Failed to initialize sql.js:", err);
});
</code>
>>
>> **Explanation**
>> **Blob and URL:** We create a Blob from the WASM binary with the correct MIME type (%%application/wasm%%) and get a URL using %%URL.createObjectURL()%%.
>> **initSqlJs**: We pass a function that returns this blob URL to %%initSqlJs%% via the %%locateFile%% option.
>> **Cleanup**: Once the promise resolves (i.e., the module is loaded), we call URL.revokeObjectURL(blobUrl) to clean up the blob URL, ensuring that memory isn’t wasted on it.
>>
>> This approach keeps everything tidy and ensures that the blob URL is only kept for as long as needed. Let me know if you have any more questions!

Eager to please the chatbot has followed my suggestion and used the %%then%% block.  I ask a question to highlight the obvious problem with doing this:

> Will cleanup occur if an exception is thrown?

>> By default, if you only put cleanup in the %%.then()%% block, an exception in the promise chain might bypass it. To ensure that the blob URL is always revoked regardless of success or failure, you can use the %%.finally()%% method, which runs whether the promise resolves or rejects. Here’s an updated example:
>>
>> <code javascript>
initSqlJs({
  locateFile: () => blobUrl
}).then(SQL => {
  const db = new SQL.Database();
  db.run("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);");
  db.run("INSERT INTO test (name) VALUES ('Alice');");
  const res = db.exec("SELECT * FROM test;");
  console.log("Query result:", res);
}).catch(err => {
  console.error("Failed to initialize sql.js:", err);
}).finally(() => {
  // This will run whether the promise was fulfilled or rejected.
  URL.revokeObjectURL(blobUrl);
});
</code>
>>
>> In this setup, if an exception is thrown at any point during initialization, the %%.finally()%% block ensures the blob URL is revoked.

I'm happy with this code.  After a couple of hours at the keyboard I call it a night.  I have learned a lot and I have some working code to show for it.

===== Tidying Up =====

I just want two more things to complete my proof of concept.
  - A test area where I can input the SQL to be executed.
  - Display the response as a table instead of JSON.

Since I am ignoring most of the code being generated by ChatGPT, I ask it to be more concise.

> From now on, just show me the enough code snippet for me to alter the code myself.  Don't give me the full file.

It obliges, and repeats the saliant code for the cleanup.  I'm impressed by it's ability to pick out the relevant part of the code.

The first item is my text area.

> I currently have a standalone html file that implements this.  
> 
> Could you give me the code to show a TEXTAREA where the query is entered, and the code to extract the sql to be run?

>> <code javascript>
<textarea id="sqlInput" rows="5" cols="50"></textarea>
<button id="runQueryButton">Run Query</button>
<script>
  document.getElementById("runQueryButton").addEventListener("click", () => {
    const sqlQuery = document.getElementById("sqlInput").value;
    // Now use sqlQuery, e.g.:
    // db.run(sqlQuery);
    console.log("SQL to run:", sqlQuery);
  });
</script>
</code>

I copy this code into my page, adapt it slightly and it works.

> Finally, could you give me some code to take the Sqlite result and turn it into a html table?

>> <code javascript>
function resultToHTMLTable(result) {
  if (!result || result.length === 0) {
    return "<p>No results found.</p>";
  }
  // Use the first result set (if there are multiple)
  const data = result[0];
  let html = "<table border='1'><thead><tr>";
  
  // Create header row
  data.columns.forEach(col => {
    html += `<th>${col}</th>`;
  });
  html += "</tr></thead><tbody>";
  
  // Create data rows
  data.values.forEach(row => {
    html += "<tr>";
    row.forEach(cell => {
      html += `<td>${cell}</td>`;
    });
    html += "</tr>";
  });
  
  html += "</tbody></table>";
  return html;
}

// Example usage:
// Assuming 'db' is your SQL.js Database instance and you run a query:
// let result = db.exec("SELECT * FROM test;");
// Then insert the table into an element:
// document.getElementById("resultContainer").innerHTML = resultToHTMLTable(result);
</code>

Again, this is easily copied and adapted into my code.

====== Conclusion ======

I've heard a lot about how LLMs about the productivity gains from using AI as a co-pilot.  My experience has been that using it this way allows me to quickly create a lot of code I don't understand.  After a quick start I find myself with a codebase I cannot maintain.

The approach I have had great success with is the one used here.  Leaning on AI to help me understand the libraries I am working with.  After establishing the initial file, I think ask the Chatbot for the information I need to implement the next feature.  It provides me with snippets of code that I can easily adapt into my codebase.

Rather than asking the LLM to write code for me, I use it to reinforce my learning.  This is the real productivity boost that AI can provide, because developers spend most of their time learning, not typing. 

This is how AI will boost developer productivity: by accelerating our learning.

====== The code ======

If you want to take a look at what has been crated so far, here it is:

I've taken out the large Base64 string.  The full string is available [[>SqliteWasmBase64|here]] for you to copy and paste in.

<code html>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>In-Memory SQLite</title>
</head>
<body>
    <h1>SQLite In-Memory DB</h1>
    <textarea id="sqlInput" rows="5" cols="100">
DROP TABLE IF EXISTS test;
CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO test (name) VALUES ('Alice'), ('Bob');
SELECT * FROM test;
    </textarea>
    <button id="runQuery">Run Query</button>
    <pre id="output"></pre>

    <script src="lib/sql-wasm.js"></script>
    <script>
        const base64wasm = "BASE64_ENCODING"; -- 
        const binaryString = atob(base64wasm);
        const bytes = new Uint8Array(binaryString.length);
        for (let i = 0; i < binaryString.length; i++) {
            bytes[i] = binaryString.charCodeAt(i);
        }

        (async () => {
            // Initialize sql.js using the in-memory WASM binary
            const sql = await initSqlJs({
                locateFile: () => {
                    // Create a blob for the binary and generate a URL
                    const blob = new Blob([bytes], { type: "application/wasm" });
                    return URL.createObjectURL(blob);
                }
            });

            const db = new sql.Database(); // In-memory SQLite DB

            document.getElementById('runQuery').addEventListener('click', () => {
                const sqlQuery = document.getElementById("sqlInput").value;
                const result = db.exec(sqlQuery);
                document.getElementById('output').innerHTML = resultToHTMLTable(result);
            });
        })();

        function resultToHTMLTable(result) {
            if (!result || result.length === 0) {
                return "<p>No results found.</p>";
            }
            // Use the first result set (if there are multiple)
            const data = result[0];
            let html = "<table border='1'><thead><tr>";
            
            // Create header row
            data.columns.forEach(col => {
                html += `<th>${col}</th>`;
            });
            html += "</tr></thead><tbody>";
            
            // Create data rows
            data.values.forEach(row => {
                html += "<tr>";
                row.forEach(cell => {
                html += `<td>${cell}</td>`;
                });
                html += "</tr>";
            });
            
            html += "</tbody></table>";
            return html;
        }


    </script>
</body>
</html>
</code>