I recently rewrote Rado from scratch. Why?
In 2022 I started work on Rado, a Typescript query builder. The project was based on my contributions to tink_sql and an earlier attempt at writing an ORM for Haxe. The existing solutions deviated too much from SQL or had overly complex codebases.
During the same time Drizzle ORM gained a lot of popularity. Interestingly its api settled on something that's extremely similar to Rado. Earlier this year I needed to support PostgreSQL and instead of adding the support in Rado I decided to attempt porting the project to Drizzle. Drizzle has support for PostgreSQL and MySQL, which I never bothered implementing for Rado. SQLite had been enough for my use cases.
Drizzle has a separate ORM style interface to query the database with methods
such as findMany in which you can include other rows to be
retrieved based on a relationship. This forces you to learn a separate api in
which to do things like included or excluding fields works different from the
query builder. Rado you can instead build the same query within the query
builder itself, declaring the relationship on the fly. The actual query that
is executed is the same.
In Rado 0.x queries are immutable. They do not require the database instance to be created and can then be retrieved by calling the database instance with the query. There were lots of shorthand ways to construct them. But all in all they are very similarly constructed.
// Rado 0.x
const query = select(User.name).from(User).where(is(User.id, 1))
const results = await db(query)
// Drizzle
const results = await db.select({name: User.name}).from(User).where(
eq(User.id, 1)
)
Rado built up an AST of the queries while chaining the methods. A simple select would look like the following:
{
"type": "select",
"from": {...table}
"where": {
"type": "binop",
"a": {...field},
"op": "=",
"b": {...value}
}
}
The thought behind this was the AST could be serialized, validated and manipulated. This for example allows you to build a query client-side and send the AST over to the backend for validation and execution. It however also made changes quite involved and I never actually used it in this manner.
SQLite does not have a boolean type. But it does handle booleans correctly in
its JSON methods. For this reason I decided to select results as a JSON object
to support boolean values. In hindsight this disabled support for selecting
binary columns and had serious performance implications. Drizzle has a much
more elegant solution with mapFromDriverValue which allows you to
define how the a column is parsed once queried.
Rado packaged one auto-migration method which makes sure your database schema and indexes are up to date.
await db.migrateSchema(Table1, Table2, ...)
Drizzle provides a separate package for migrations which writes
.sql files to disk that can later be executed. It requires a
database instance to be configured.
Warning: this section might become stale fast.
These may have been
fixed after publishing this note.
Drizzle requires you to define a column name even if it is the same as the property you use to declare it. Minor inconvenience.
const User = table('user', {
id: integer('id')
// Why not infer the name id from the property if omitted?
// id: integer()
})
Rado supports selecting fields directly which I used suprisingly often. In
Drizzle this is not possible. For example: select(User.id)
Drizzle does not support generated columns and Postgres identity columns.
The Typescript types with which Drizzle is built are quite convoluted. Following the examples from their documentation the types require a whole lot of "any" parameters to be filled in order to be useful. There is a slew of type information carried by every class in the Drizzle codebase, most of which is not at all used later on.
Drizzle does not support writing a query that can be executed on any database. The choice of database must be made at build time. It's not (easily) possible to write a query and then at runtime select whether to execute it on a PostgreSQL, SQLite or MySQL database.
Drizzle developed a separate package to manage migrations. It writes
migrations to .sql files on your filesytem which can be loaded
and executed later on.
The package itself is closed source and very heavy (packagephobia, if you're lucky to not run into a 500 error, reports 27.6MB install size). Not something you can mark as a dependency if you're shipping libraries.
The migrations being stored on the file-system and read by Node.js apis means it is not useful for SQLite or PostgreSQL in the browser or the Deno runtime.
The above shortcomings made me decide to attempt a rewrite of Rado with the following goals in mind:
The result of the rewrite is published as Rado 1.0.0. While it is not a 100% drop in replacement for Drizzle it gets very close. Let's have a look at how the goals were met.
Rado types have at most three generic type parameters and use the least amount of conditional types possible. As of this writing a total of 23, where Drizzle has 247 in its codebase. This keeps type-checking fast and the mental overhead minimal. It still achieves the same type-safety.
Rado supports the same methods as Drizzle for building queries. The SQL specific imports are replaced by:
import {sql, eq, and, or, ...} from 'rado' // 'drizzle-orm'
import {pgTable, bigint, ...} from 'rado/postgres' // 'drizzle-orm/pg-core'
import {sqliteTable, integer, ...} from 'rado/sqlite' // 'drizzle-orm/sqlite-core'
import {mysqlTable, varchar, ...} from 'rado/mysql' // 'drizzle-orm/mysql-core'
Drizzle's ORM features are not included. Instead a simpler syntax is included to fetch related rows in an ORM fashion without making you define the relationship ahead of time. It's not needed to learn an alternative syntax to select and filter results.
// Rado 1.x
import {include} from 'rado'
const results = await db
.select({
id: User.id,
name: User.name,
posts: include(
db.select({
id: Post.id,
title: Post.title
})
.from(Post)
.where(eq(Post.userId, User.id))
)
})
.from(User)
.where(eq(User.id, user1))
// Drizzle
const usersRelations = relations(User, ({many}) => ({
posts: many(Post),
}))
const results = await db.query.users.findMany({
columns: {
id: true,
name: true,
},
with: {
posts: {
columns: {
id: true,
title: true,
}
}
}
})
Rado queries are immutable. This avoids confusing bugs.
import {count, gt} from 'rado'
const all = db.select(count()).from(User)
const greaterThanOne = all.where(gt(User.id, 1))
const greaterThanTwo = all.where(gt(User.id, 2))
// In Rado you can start multiple queries from base
// - or just query either directly
const [totalUsers] = await all
const [filteredUsers] = await greaterThanOne
// In Drizzle all queries would point to the same query
// invalidating the types well
Rado exports rado/universal with which you can build queries that
run on any database engine whether it runs synchronous or asynchronous. A
handful of useful column types are exported. This also allows Rado's test
suite to run on all database drivers.
import {table} from 'rado'
import {id, text} from 'rado/universal'
const User = table('user', {
id: id(), // Integer auto-incrementing primary key
name: text()
})
const db = process.env.SQLITE ? sqliteDb : postgresDb
const userNames = await db.select(User.name).from(User)
Note: this is currently experimental
Rado provides a single method to migrate the schema. It will compare the schema that you defined to the one in the database you're connected to and update it on the spot. This works in the browser, Deno and Node.js. It can possible do quite destructive things and should only be used with careful consideration. If you need more control over the migrations it's recommended to to use a separate tool such as dbmate.
// Auto upgrade the User and Post tables await db.migrate(User, Post)
Bundling the base Rado package including all Postgres utilities totals 8.5 kB gzipped. If we bundle the same for Drizzle it comes to 20.9 kB.
// 8.5 kB gzipped export * from 'rado' export * from 'rado/postgres' // 20.9 kB gzipped export * from 'drizzle-orm' export * from 'drizzle-orm/pg-core'
Performance-wise I've added Rado to the Drizzle northwind benchmark. The benchmark is interesting because all it really tests is the time it takes to construct the resulting row objects. After tweaking Rado a little it outperforms Drizzle and the other ORMS in the majority of tests in that benchmark. I decided not to publish these results because they're quite meaningless and Drizzle could be tweaked to achieve the same performance with minor adjustments.
Rado 1.0.0 supports SQLite, PostgreSQL and MySQL. The currently supported
drivers are: better-sqlite3, bun:sqlite,
mysql2, pg, pglite and
sql.js.
If you like Drizzle but:
But mind the pitfalls: