Building a SQLite Playground
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.
Databases in the browser
In 2014, I was doing a lot of work with Meteor and was blown away when I learned about Minimongo.
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
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 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.
Getting the code
There are a few options to choose from depending on whether you:
- are using a bundler
- want to use the UI thread or a worker
- 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.
Since we aren't using OPFS, the third shouldn't be needed but the bundler fails without it.
We'll copy the files into the
src directory where they can be
main.ts. Then we can initialize SQLite and create a
: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
The SQLite API provides a few ways to build and run queries but we'll stick
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.
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.
We're going to lean on Monaco to do all of the heavy lifting in terms of UI.
$ npm install monaco-editor
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.
We'll create two objects of type
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.
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.
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
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.
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:
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.
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.