Building a SQLite Playground

Mar 7, 2023

A read–eval–print loop (REPL) is an interactive environment that takes user inputs, executes them, and returns the result. I made a TypeScript REPL using Monaco and SWC to show how notry-ts works and thought it would be fun to do the same for SQLite.

The good news is I got it working.

SQLite REPL Svelte Edition

Unfortunately, by the time I got everything working with SvelteKit it was a little too complex to describe in a post. So I distilled it down to a quick-and-dirty REPL that can help you get started with Monaco or SQLite in the browser.

Some background

Databases in the browser

In 2014, I was doing a lot of work with Meteor and was blown away when I learned about Minimongo.

Minimongo is essentially an in-memory, non-persistent implementation of Mongo in pure JavaScript. It serves as a local cache that stores just the subset of the database that this client is working with. Queries (find) on these collections are served directly out of this cache, without talking to the server.

I thought it would be great if something similar existed based on a relational database. It turns out that once upon a time there was a browser API specification called Web SQL. Unfortunately, it was deprecated back in 2010. The reason is captured well in a StackOverflow answer.

Short version: Web SQL was deprecated because standards are really important and turning Web SQL into a proper standard would have been prohibitively difficult.

Fair enough. Don't let that get you down though, we're living in the future now. It's a time when you don't need a built-in API to get native performance. It's the time of WebAssembly.

Everything is Wasm

One technology that I'm super excited about is WebAssembly. Years ago I decided to trade the power of C for the ubiquity of JavaScript. For the most part, it has felt like the right decision and having a C background was a good foundation for work on the web.

But now I don't have to compromise. Languages like C and Rust already compile to Wasm. SWC is a Rust-based web transpiler that compiles to Wasm. It's what I used to perform in-browser transpilation for the TypeScript REPL.

Amazingly, SQLite (written in C) is already compiled to Wasm.

Monaco

Monaco is the code editor that powers VSCode. It provides SQL syntax highlighting, hotkey registration, and a ton of other features that we'll only scratch the surface of. If you want to get a better idea of Monaco's capabilities, be sure to check out the Monaco Playground.

Let's Build

SQLite3

Getting the code

Not only is SQLite already precompiled to Wasm but there are a handful of demos showing how to use it. We just need to grab the WebAssembly & JavaScript archive from here and then figure out which files to use.

There are a few options to choose from depending on whether you:

1. are using a bundler
2. want to use the UI thread or a worker
3. want a synchronous or promise-based API (only applies to workers)

We're going to use a bundler since it makes working with Monaco easier. It's a REPL so we won't worry about UI responsiveness while the DB is running queries. We are interested in the persistence options though. From the SQLite Wasm docs:

This API provides database persistence via localStorage/sessionStorage and, in compatible browsers, the Origin-Private FileSystem.

kvvfs is an sqlite3_vfs implementation conceived and created to store a whole sqlite3 database in the localStorage or sessionStorage objects. Those objects are only available in the main UI thread, not Worker threads, so this feature is only available in the main thread.

The Origin-Private FileSystem, OPFS, is an API providing browser-side persistent storage which, not coincidentally, sqlite3 can use for storing databases.

As of late 2022, only bleeding-edge versions of Chromium-derived browsers have the necessary APIs.

So, in the interest of maximizing compatibility, we'll run SQLite in the main thread which requires these files.

1. sqlite3.wasm
2. sqlite3-bundler-friendly.mjs
3. sqlite3-opfs-async-proxy.js

Since we aren't using OPFS, the third shouldn't be needed but the bundler fails without it.

Initializing

We'll copy the files into the src directory where they can be imported by main.ts. Then we can initialize SQLite and create a database.

The filename :localStorage: tells SQLite to use local storage for persistence. We pass the flag c to create the database if it doesn't exist and t to enable tracing (appears in the developer console).

Running queries

The SQLite API provides a few ways to build and run queries but we'll stick to exec.

We get the start time to help detect slow queries and then call db.exec, passing it SQL to run and an options object containing rowMode: "object". This tells exec to return the result rows as an array of key value-pairs.

We'll revisit run later when it's time to integrate with Monaco.

Clearing the database

Apart from creating a database and executing queries, the only other SQLite functionality we need is for clearing the database.

This removes all SQLite data from local storage.

Monaco

We're going to lean on Monaco to do all of the heavy lifting in terms of UI.

Installation

Via npm:

$ npm install monaco-editor

Initializing

Monaco uses the global variable MonacoEnvironment to specify where to find the worker code. This is how that looks with Vite.

Without it, this warning will appear in the console:

Could not create web worker(s). Falling back to loading web worker code in main thread, which might cause UI freezes.

Models

We'll create two objects of type monaco.editor.ITextModel representing text files. One will contain query history and be displayed in a read-only editor. The other will contain the active query being edited.

The first argument is the initial content of the model and the second is the language.

Editor

Now that we have models, we need to display them somewhere. Since there will be two nearly-identical editors, we'll define a function to initialize them.

We won't go into all of the options here, most are self-explanatory and you to play around with them all in the Monaco Playground. What's important is that we pass a div where the editor should be mounted, a model to display, and the readOnly option.

Resizing

We want the editor to resize with its content so we set the option scrollBeyondLastLine: false and register an event handler.

When the content changes size, the container height will be updated and the editor will re-layout. Here the height is limited to 600px but it could be anything (like function of the window size).

It would also be a good idea to listen for resize events on the window and trigger an update. In the name of keeping it quick-and-dirty we'll leave that as an exercise. Here's the finished initEditor function.

Those are the basic components needed to put together our REPL. Before we do, let's take a quick look at some nice-to-have features.

Markers

We can use markers to highlight text in a model. We'll do this when a query fails (rather than writing to the console).

This will draw a squiggly red line under the entire content of the editor. When the user hovers over the query, exceptionVar will appear in a tooltip like so:

Actions

As our final feature, we'll register an action to call run when shift + enter is pressed.

Putting It Together

At this point we've seen all of the important bits so we're ready to build our crude REPL.

index.html

main.ts

That's it! For project configuration and setup instructions, check out the GitHub repository.

For a slightly more feature-rich SQLite REPL to play with, here's the original that I created in Svelte.

Resources