Hello! I’m what you might call a technology enthusiast. When I see a new trend in the tech world, I tend to dive in, driven by my desire to explore it.

In recent years, several fields have been gaining momentum, and WebAssembly (Wasm) is definitely one of them, particularly in the software engineering community.

Since I also do web frontend development, I’m naturally drawn to Wasm and its practical applications. However, whenever I come across an interesting technology, I always try to think of ways to use it that could benefit my business. In other words, I want to make money using that technology.

This tendency is probably influenced by my personality. When I work with a technology purely for fun, I often stop after creating something small and then rarely touch it again. But if I force myself to use it for work, I take on a certain amount of pressure, which in turn improves my learning efficiency.

I’ve seen this pattern before. For instance, I could never remember much about Go when I used it just as a hobby. But once I adopted Go for one of my products, I became proficient enough to use it, and that product still generates revenue today.

Going back to Wasm, I’ve long been curious about how I could use it to make money. But unlike some other developers, I don’t have any grand ideas about building rich web applications like Figma, and I haven’t been able to come up with any cool, profitable ideas that involve Wasm.

Combining SQLite Wasm and OPFS Brings Persistent Storage to the Web Frontend

Then one day, I came across a discussion about how combining SQLite Wasm with the Origin Private File System (OPFS) could enable persistent storage on the web frontend.

In simple terms, SQLite Wasm is a WebAssembly-based version of SQLite that runs in the browser. (Okay, maybe that’s a bit too simple!)

You can refer to the following MDN article for more information on OPFS, but here’s a brief overview:

https://developer.mozilla.org/en-US/docs/Web/API/File_System_API/Origin_private_file_system

Key Features of OPFS

• OPFS is a storage endpoint provided as part of the File System API.
• It is highly optimized for performance.
• It allows web apps to handle folders and files in a way that is invisible to the user.
• It is tied to the origin (domain) of the web application.
• It offers synchronous API operations within web workers.
• It is subject to browser storage quota limits.
  • (For more on storage quotas, see the link below.)
  • https://developer.mozilla.org/en-US/docs/Web/API/Storage_API/Storage_quotas_and_eviction_criteria

Differences Between OPFS and localStorage

At first, I wondered how OPFS differed from localStorage, so I’ve summarized the main differences here (though the above explanation may have already made it clear).

a. Data Structure:

• OPFS: Uses a file system structure with files and folders.
• localStorage: A simple key-value pair structure.

b. Storage Capacity:

• OPFS: Can handle larger storage (within browser limits).
• localStorage: Typically limited to 5–10MB.

c. Performance:

• OPFS: Suited for large datasets and frequent read/write operations.
• localStorage: Works well for small data but isn’t suitable for large-scale data.

d. API:

• OPFS: Offers an API similar to file system operations and supports asynchronous functions.
• localStorage: Provides simple synchronous getter/setter APIs for key-value pairs.

e. Data Types:

• OPFS: Can handle various data types, including binary data.
• localStorage: Stores strings only, requiring other data types to be converted to strings.

f. Use Cases:

• OPFS: Ideal for large datasets, databases, and applications that require frequent updates.
• localStorage: Suitable for storing small amounts of data, like user preferences or session information.

OPFS Limitations

Despite its advantages, OPFS does have some limitations:

• It is a relatively new technology, and browser support is limited.
  • Safari might struggle to support it, which is disappointing since I wanted to use it on iPhone/iPad. 😭
• Currently, you can’t inspect it as easily as you can inspect localStorage in Chrome’s developer tools.
  • Will this change in the future? For now, I use the following script to debug in the developer tools: https://gist.github.com/shinshin86/365ae2b841d1a37cf4e2317c13bff971
• You can’t easily delete data from OPFS through Chrome’s developer tools.
  • Clearing data via the browser is overkill during development, so I use this script to clear OPFS data: https://gist.github.com/shinshin86/42681c722c5c835e8628e9c797744fca
• There is also a Chrome extension available for viewing OPFS data: https://chromewebstore.google.com/detail/opfs-explorer/acndjpgkpaclldomagafnognkcgjignd

The Advantages of OPFS

In short, OPFS allows you to read and write files in a way that is invisible to the user, while being tied to the domain of the web application. Excluding the lack of Safari support, it’s a very useful tool!

Combining SQLite Wasm and OPFS

Now, as for SQLite Wasm, combining it with OPFS allows you to achieve data persistence entirely within the browser.

So, How Is SQLite Wasm + OPFS Useful in Business?

You might be wondering, “How is this combination of SQLite Wasm + OPFS useful from a business perspective?”

To give some context, I run a small-scale business where I offer B2B products I’ve developed. Although my business is very small, I operate in spaces that larger companies don’t usually target, allowing me to thrive in niche markets.

For businesses like mine, keeping monthly expenses low is crucial. Saving on server costs by making small adjustments pays off in the long run. By limiting operational costs, I’ve been able to keep my business profitable, even with modest revenue. For example, I provide one of my products as a desktop application, cutting down on server expenses.

In this context, SQLite Wasm + OPFS can help minimize costs by enabling persistent storage within the user’s browser, without needing to install a desktop app. The user can simply visit a URL and utilize data that is stored persistently in their browser.

Of course, there are limitations, such as the inability to synchronize data between different browsers. However, this can be addressed by providing features from the product side to handle these limitations, and I see potential business opportunities here.

How to Use SQLite Wasm

There are several SQLite Wasm projects out there, but I use sqlite-wasm for my purposes.

https://github.com/sqlite/sqlite-wasm

Here’s an example of how it can be used.

This is the code I wrote when creating a simple TODO app using SQLite Wasm and OPFS:

https://github.com/shinshin86/todo-opfs-sqlite

https://github.com/shinshin86/todo-opfs-sqlite/blob/main/src/db.ts

In the code below, I handle OPFS and fall back to in-memory storage if OPFS isn’t supported:

// OPFS
let openResponse;
try {
  openResponse = await promiser('open', {
    filename: 'file:todo.sqlite3?vfs=opfs',
  });
  console.log('OPFS database opened:', openResponse.result.filename);
} catch (opfsError) {
  console.warn('OPFS is not available, falling back to in-memory database:', opfsError);
  openResponse = await promiser('open', {
    filename: ':memory:',
  });
  console.log('In-memory database opened');
}

With this simple implementation, you can persist data entirely on the frontend. It’s quite convenient!

Streamlining the Application Development Flow (neverchange + sqlc)

The SQLite Wasm + OPFS setup works well with the implementation I shared above, but I wanted a more streamlined way to write code during the development process. That’s why I created a library that wraps around SQLite Wasm + OPFS.

Introducing neverchange – A Library for Persistent Storage on the Web Frontend

The library is called neverchange.

It’s still in alpha, but I’ve been dogfooding it myself and plan to continue developing it until it becomes stable.

https://github.com/shinshin86/neverchange

A side note: the name “neverchange” is inspired by the song “Some Things Never Change” from the movie Frozen II.
(I highly recommend giving it a listen—it’s a great song!)

Using this library, you can implement SQLite Wasm + OPFS on the web frontend with ease. Here’s a sample implementation (for more detailed usage, check the repository later):

import { NeverChangeDB } from 'neverchange';

async function main() {
  // Initialize the database
  const db = new NeverChangeDB('myDatabase');
  await db.init();

  // Create a table
  await db.execute(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT UNIQUE NOT NULL
    )
  `);

  // Insert data
  await db.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    ['John Doe', 'john@example.com']
  );

  // Query data
  const users = await db.query('SELECT * FROM users');
  console.log('Users:', users);

  // Close the database connection
  await db.close();
}

main().catch(console.error);

I’ve also added migration capabilities to the database.

When using SQLite Wasm, the user’s browser (the web frontend) is the only place where the database can be managed.
However, by bundling the migrations in a folder, you can accommodate changes to the table structure as your app evolves.
(I wonder if there are better ideas for this? I feel my knowledge is a bit limited, so any suggestions would be appreciated! 🙇‍♂️)

import { NeverChangeDB } from 'neverchange';

// Define migrations
const migrations = [
  {
    version: 1,
    up: async (db) => {
      await db.execute(`
        CREATE TABLE users (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL
        )
      `);
    }
  },
  {
    version: 2,
    up: async (db) => {
      await db.execute(`
        ALTER TABLE users ADD COLUMN email TEXT
      `);
    }
  }
];

async function main() {
  // Initialize the database with migrations
  const db = new NeverChangeDB('myDatabase', { isMigrationActive: true });
  db.addMigrations(migrations);
  await db.init();

  // The database will now have the latest schema
  const tableInfo = await db.query('PRAGMA table_info(users)');
  console.log('Users table schema:', tableInfo);

  await db.close();
}

main().catch(console.error);

These are the basic features of the library. (There’s also a dump feature, but I’ll omit that here.)

Using sqlc for Code Generation with SQL

However, even with this setup, you still have to write SQL manually. So, I wondered if there was a more efficient way to generate code for database operations. That’s when I discovered sqlc.

I decided to fork this project and modify it to support neverchange. Here’s the repo:
https://github.com/shinshin86/sqlc-gen-typescript/tree/neverchange

To use it, follow these steps:

# Clone the branch
git clone -b neverchange https://github.com/shinshin86/sqlc-gen-typescript.git
cd sqlc-gen-typescript
npm install

# Build the output file
make out.js

# You’ll need Javy to generate the Wasm file (assuming it’s already installed and in your path)
make examples/plugin.wasm

After running the commands above, the plugin.wasm file will be generated in the examples directory.

Next, you can specify the following configuration in sqlc.yaml and run sqlc generate to generate code compatible with neverchange:

version: '2'
plugins:
- name: ts
  wasm:
    url: file://{path_to_plugin.wasm}
    sha256: {sha256_of_your_wasm_file} # This field may not be necessary
sql:
- schema: "{path_to_schema.sql}"
  queries: "{path_to_query.sql}"
  engine: "sqlite"
  codegen:
  - out: "{path_to_output}"
    plugin: ts
    options:
      runtime: node
      driver: neverchange

I created a TODO list app using this setup, which you can find here:

https://github.com/shinshin86/mytodolist

The generated code can be found here:

https://github.com/shinshin86/mytodolist/blob/main/src/db/query_sql.ts

Since most web frontends don’t require extremely complex or large-scale database operations, I believe that combining sqlc with neverchange meets most development needs.

Solving the OPFS Issue on GitHub Pages or Netlify

The repository I shared earlier works on GitHub Pages.

If you access it with a browser other than Safari, you can use your own persistent TODO list:
https://shinshin86.github.io/mytodolist/

However, initially, the project didn’t run as expected on GitHub Pages. When I first tried to deploy it, I encountered the following error, which prevented OPFS from being enabled:

Ignoring inability to install OPFS sqlite3_vfs: Cannot install OPFS: Missing SharedArrayBuffer and/or Atomics. The server must emit the COOP/COEP response headers to enable those

To fix this, I used the following library:

https://github.com/gzuidhof/coi-serviceworker

For specific implementation details, check the gh-pages branch of the TODO list repository I mentioned earlier.

This issue wasn’t limited to GitHub Pages—it also occurred on Netlify. So, it’s likely that other similar hosting services have the same issue.

If you plan to build applications using SQLite Wasm + OPFS, this is something to keep in mind.

Exploring Interesting Opportunities with Wasm

As I mentioned at the start, Wasm is a rapidly evolving field, and for tech enthusiasts, it’s a fascinating area to explore. Personally, I want to find ways to make use of it in a business context, and that’s what led me to the flow I’ve outlined in this post.

Some might say, “In the end, you’re just moving traditional DB operations to the frontend, right?” And while that may be true, I think it’s my own lack of imagination that’s the problem. If you have any ideas for leveraging Wasm, I’d love to hear them!

Thanks for reading this far—I really appreciate it!