SQLITE

What do SQLite, StackBlitz, and NAORM have in common? WASM

How WebAssembly is powering some of the coolest new tools for TypeScript + SQLite developers

Three weeks ago, I announced the launch of Not an ORM for SQLite in my first blog post. The most recent version of NAORM now includes a new interactive demo on StackBlitz, which, like SQLite and StackBlitz themselves, works in the browser by leveraging WebAssembly (WASM). The growing browser-friendliness of these and other WASM-powered tools is especially good news for developers in the TypeScript + SQLite space.

If you haven’t seen it yet, NAORM (pronounced “norm”) is a Node.js command-line tool that generates TypeScript models from SQLite files in your code base. I’ve wanted to put together a browser demo from the beginning, but I knew there would be challenges. While NAORM itself is written in TypeScript, it depends on the better-sqlite3 library, which contains C++ bindings executed via Node.js, and of course SQLite itself, which is written in C. It also leverages Node.js APIs like the File System module, which are implemented in native code and not available in browser environments.

NAORM depends on other libraries meant for Node.js environments, most notably better-sqlite3

Knowing this, I thought it would be next to impossible to create a fully working NAORM demo that ran entirely in the browser. But thanks to WebAssembly, I was happily proven wrong.

Using Node.js APIs within StackBlitz WebContainers

If you weren’t aware, StackBlitz has been, for a number of years, a leading browser-based “web IDE” platform, alongside others like CodePen, Plunker, and JSFiddle. While the features vary between them, all of these work in a similar way — you develop web pages or apps in HTML, CSS, and JavaScript (and in some cases, TypeScript). As you code, you can preview results instantly and share with others. Unlike server-side online IDEs such as CodeSandbox, CodeAnywhere, Replit, and others that run your code on the backend, these tools all execute your code client-side, meaning that it is sandboxed by your browser, and displayed to you using iFrames.

But in 2021, StackBlitz launched a WASM-based feature called WebContainers that, in my view, totally transformed the web IDE landscape by introducing support for Node.js, including backend frameworks like Express. This means that you’re no longer limited to browser development — as their “What is StackBlitz” guide now states: “StackBlitz is an instant fullstack web IDE for the JavaScript ecosystem.”

These features made StackBlitz the obvious choice for my demo site, since a big portion of NAORM’s work is reading SQL files and writing TypeScript files. To be clear, if you follow NAORM’s Getting Started guide and use the CLI in your local development environment, there is no WebAssembly involved — your Node.js runtime will execute JavaScript and compiled native code. But the demo project on StackBlitz uses the WebContainer’s Node.js APIs, which executes WASM instead, without any code changes needed within NAORM.

Stackblitz WebContainers support the use of Node.js APIs for virtual file operations, web servers, and more, as seen in the NAORM demo project

I was even able to to mimic the “Run on Save” capability that a developer using NAORM might set up in their local IDE. On Stackblitz, I just configured Nodemon to invoke the TypeScript generation command on changes to SQL files, and it worked seamlessly. This really lets potential users experience the full power of NAORM without having to install anything locally.

Creating a Demo App for NAORM using Sql.js

After the initial StackBlitz setup, my next step was to create a demo app that users could leverage with their own SQL statements in a test database. Usually, my go-to library for using SQLite within Node environments is better-sqlite3. Of course, StackBlitz isn’t a true Node runtime, and libraries with native bindings are still not supported unless they are available with a WASM compilation. Stackblitz itself has explored enabling SQLite support with the node-sqlite3 library, but it’s still experimental.

If you’ve been using SQLite in JavaScript projects for a while, you’ve probably heard of sql.js, a library that allows you to use SQLite in the browser using an in-memory virtual database. It’s been around for over 10 years now — SQLite themselves attribute it as “the first-ever published use of sqlite3 for the web.” Before WebAssembly was around, sql.js worked by using Emscripten to compile SQLite to asm.js, but the library moved to using WASM as their default in 2019.

Sql.js was a great choice for my demo project since it’s easy to use, ubiquitous, and has no native dependencies. And while the library’s lack of a mechanism for persistent database storage makes it unsuitable for many solutions, it’s not an issue for a self-contained demo. In the screenshot below, you can see how the demo app initializes a database, creates a table, inserts some data, and queries it.

The demo app shows NAORM’s key features, including the use of generated TypeScript models to assert types for query results from sql.js.

The easy setup and API allow my demo app to show off NAORM’s key use cases: using generated TypeScript to manage the inputs (SQL statements) and outputs (query results) to a SQLite library such as sql.js. See my first blog post for more detail on these. As an added bonus, sql.js works great in both browser and Node environments, so anyone can simply download the project from StackBlitz and run it locally.

Using NAORM with SQLite’s WASM Subproject

The newest and most exciting WebAssembly project for SQLite is not a standalone library, but actually a “subproject” of SQLite itself, with collaboration from the Google Chrome team. It aims to further enable SQLite functionality in modern browsers, including optimization for both main and worker threads, and persistent storage using the Origin-Private FileSystem API. Like sql.js, this project uses Emscripten to compile SQLite’s C code to WebAssembly. A public beta version was released in November of 2022.

My demo project uses sql.js for the “demo app” that a user can work with. But for NAORM’s database logic — the SQL statements that are being compiled and executed behind the scenes to generate TypeScript models — I wanted to see if I could use the latest SQLite WASM technology. There were definitely some hurdles, in part because the library is pretty new, but I was able to make it work — the NAORM logic runs smoothly, with only a few minor limitations compared to the native version.

The NAORM demo project leverages SQLite’s WASM Subproject to generate TypeScript models from SQL files.

I’ll circle back to more details about the SQLite’s WASM Subproject in another post —for now, my take is that it already contains some great features, and seems like it has tons of potential to grow further. As Thomas Steiner from the Google Team put it in his post about Chrome’s deprecation plan for the obsolete WebSQL API, “People want SQL on the web for a reason.” Hopefully as the library matures, more projects will begin begin using it. And since JavaScript developers overwhelmingly use TypeScript over plain JS, NAORM will be a key tool to help bridge the gap to SQLite.

What I’ve Learned

When I began thinking about building a demo for NAORM in the browser, I thought that there might be some workarounds or hacks that I could put in place to get the idea across to potential users, but that enabling the full experience wouldn’t be possible. I was really happy to see that it was not only viable to set up, but in fact quite easy. I think this speaks to how quickly the web is continuing to evolve and open new avenues for cool technologies.

What surprised me the most about this WASM-based stack is just how fast the experience is. In the old days, I remember building a custom solution for a SQL Server database that achieved a similar purpose as NAORM does for SQLite — first, a new database would be spun up, and SQL statements executed in a particular order to build the database’s schema. Then, a tool called SQL Metal would analyze the database and generate C# models that were compatible with the LINQ to SQL ORM. This would run in a continuous integration pipeline and, for a 50-table database, could take upwards of 15 minutes.

NAORM performs extremely quickly in both an online WASM demo environment (left) and a native Node.js environment (right)

NAORM accomplishes the analogous task for SQLite + TypeScript, in both my local environment and on StackBlitz, in well under half a second. In my testing, using Microsoft’s Northwind database as an example, the WASM version performs on par with the native code running on my laptop. This instant feedback is incredibly important to the development experience in both environments, so I’m thrilled that StackBlitz and SQLite have the capability— built using WebAssembly — to support it.