SQLITE
Why I wrote Not an ORM for SQLite (and why you should use it)
A new CLI utility for TypeScript + SQLite projects
Around Christmas, I decided that I would take some time to build out an idea that had been in my head for a while. And now I’m glad to be sharing the release of Not an ORM for SQLite, or NAORM, for short.
I won’t re-hash the repo’s whole README in this post, but here’s the short version: NAORM (pronounced “norm”) is a CLI tool that bridges the gap between SQLite and TypeScript. It allows you to maintain your database schema, queries, and other statements in SQL files, then generates corresponding TypeScript for you to import into your application. It is a fast and lightweight alternative to using an ORM.
UPDATE: You can now try NAORM directly in your browser on StackBlitz. For more details, check out my subsequent blog post.
NAORM is open-source and available on NPM. I published it for two reasons. First, NAORM solves a few key problems in the TypeScript + SQLite space, and so will be useful for a lot of developers. And second, the solution is small enough that writing and maintaining the code seems like an achievable goal.
But it’s not enough to just publish the package. In order to see it succeed and bring value, others need to know about the tool and why it’s useful. So I also started this blog, of which this is the first post, to share my thoughts.
TypeScript + SQLite
NAORM is framework-agnostic — you can use it in any TypeScript + SQLite project that you please. But why and how would you be using SQLite and TypeScript together in the first place?
As it turns out, there are tons of use cases for SQLite, and a healthy ecosystem of libraries that make it possible to execute SQLite commands from JavaScript environments. If you are using SQLite in a JavaScript project, you’re probably be leveraging one of these, depending on your runtime:
- If your project runs in a Node.js environment, such as a server-side app or an Electron.js app, you can use sqlite3 or, my favorite, better-sqlite3.
- If you have a hybrid web app, your framework probably has a plugin, such as cordova-sqlite-storage, nativescript-sqlite, or react-native-sqlite-storage.
- If your app runs in the browser, you might be using SQLite’s new WASM/JS Subproject or sql.js.
All of these libraries work in a similar way: you feed them SQL statements, they execute them in your database and return any results. But while all of these environments support the use of TypeScript during development, the libraries themselves are essentially JavaScript-based. They don’t contain any tooling that lets you use TypeScript to work with your database, SQL statements, queries, or data.
This is where NAORM comes in — letting you take advantage of TypeScript for the inputs and outputs to your SQLite library of choice.
The Inputs — SQL Statements within TypeScript
Every one of the SQLite libraries mentioned has some example like the one below in their documentation.
const results = db.prepare('SELECT * FROM myTable WHERE Id = ?;').all(['my-id']);Obviously these are just illustrative, this is not a scalable way to develop. In this instance, the query — the input to your SQLite library — is not even saved to a variable for reuse. You can improve the readability and reusability of your code by moving the query into its own TypeScript file as an exported string variable and defining a parameterized function to execute it.
import { myQuery } from './queries/my-query';
function executeMyQuery(id: string) {
return db.prepare(myQuery).all([id]);
}But even this style is problematic, since your SQL statement is still saved as a string within a TypeScript file. For all but the simplest statements, this quickly becomes a maintenance nightmare. Writing SQL within a TypeScript string means that you won’t benefit from SQL syntax highlighting, autocompletion, error detection, linting, debugging, or any of the other great developer experience features that we expect and appreciate when writing TypeScript or any other language.
This problem is not readily avoidable. Since all of your SQL statements must exist as strings within your app’s runtime, they must exist as strings within TypeScript. To be fair, there are some tools out there, such as the SQL Tag library and the VS Code extension SQL tagged template literals that can somewhat improve the editing experience, but it is still far from ideal.
NAORM takes a different approach, which I call SQL-first. Instead of maintaining your SQL statements within TypeScript, you develop them within SQL files. That means you can use any and all of the tooling that exists for SQLite development— I personally recommend the standalone tool, DB Browser for SQLite, and the VS Code extension, SQLite. When you’re ready to compile your TypeScript app, NAORM simply generates the TypeScript files containing the string variables.
The Outputs— Types for Query Results
As you would expect, executing a query using a SQLite library will return a list of results with column names mapped to object properties. This is, of course, done within the library’s runtime, when the query is executed.
If you want to assign a TypeScript type to a query result set, you need a model — a TypeScript class or interface that has properties and types matching those of the columns in the query result. Without a tool like NAORM, this could be duplicate work — you’d have to maintain the query itself, as well as the corresponding TypeScript model. Worse still, if you make a mistake in your model, it is often difficult to detect, unless you have some validation between the model and the query itself.
For example, consider the following query:
SELECT A.*, B.Col1, B.Col2 FROM A INNER JOIN B ON A.FK = B.PK;Any SQL developer knows that this query will return columns for all of the columns in table A, plus Col1 and Col2 from table B. If we knew the types associated to each of those columns from their table definitions, then we would know the entire model of the result set. In TypeScript, we’d need a separate model to define this query result compared the model for tables A and B. While in some cases we might be able to use composition or inheritance to avoid duplication, the type definitions surely wouldn’t be as succinct as the query itself.
To solve this problem, NAORM again follows the SQL-first approach. After you write your query in a SQL file, NAORM will generate a TypeScript model that corresponds to a row in the query’s result set, eliminating your need to maintain it. This doesn’t just work for queries, NAORM also generates models for tables, views, and even statements like UPDATE that utilize the RETURNING clause. For each of these, NAORM also outputs a list of the column definitions as a TypeScript array, so that you can use them in your application logic, if desired.
Of course, this process has some complexity to it, such as handling expression columns, NOT NULL constraints, untyped column definitions, and more. These are described in-depth in the README.
Finally, in case you thought that generating TypeScript would cause you to lose out on the ability to use JSDoc — NAORM allows you to write JSDoc comments in your SQL files, and will include them in your generated TypeScript.
Why Not an ORM?
At the beginning of the post, I described NAORM as “a fast and lightweight alternative to using an ORM.” But why would you need an alternative? What’s so bad about Object Relational Mapping (ORM) libraries? There are several available in the SQLite + TypeScript space, such as Sequelize and TypeORM, and they do, after all, have their own solutions to the problems that I’ve outlined above.
In my view, however, the limitations of ORMs are numerous, and the topic warrants its own blog post. For now, I’ll list a few advantages of NAORM that are specific to this project.
- Small package size at under 250kb unpacked
- No runtime components to clutter your application
- Highly customizable, thorough documentation
- Few SQLite limitations — ability to use almost all features
- SQL-first development approach, including JSDoc support
The last two of these are most important to me. After all, I wrote NAORM in order to improve the development experience for other developers in the SQLite + TypeScript space. And you should use it because it will do just that.