rado

A fully typed, lightweight TypeScript query builder for SQLite, PostgreSQL and MySQL.

import {eq} from 'rado'

const greatPosts = await db.select().from(Post).where(eq(Post.rating, 5))

If you've used Drizzle ORM, the code above looks familiar. That's intentional. Rado deliberately aligns its query building syntax with Drizzle, while making a few different choices under the hood: simpler types, immutable queries, a much smaller footprint and queries that can run on any of the supported databases. Read about the project's history in Taking the Drizzle challenge.

Features

• Fully typed: schema definitions drive query and result types, with plain, readable TypeScript types
• Immutable queries: every method returns a new query, so you can branch and reuse query fragments without surprises
• Universal queries: write a query once, run it on SQLite, PostgreSQL or MySQL, chosen at runtime
• First-class JSON columns: select and filter on typed JSON fields with plain property access
• include instead of an ORM: fetch related rows as nested arrays or objects inside the query builder, no relations setup required
• Auto-migrations: diff your schema against the database and update it anywhere, including the browser
• Zero dependencies, small bundle: rado + the PostgreSQL utilities bundle to roughly 8.5 kB gzipped
• No code generation step: your schema is just TypeScript

Installation

npm install rado

Rado is also published on JSR as @rado/rado for Deno users.

Quick start

import {eq} from 'rado'
import {integer, pgTable, text} from 'rado/postgres'
import {connect} from 'rado/driver/pg'
import {Pool} from 'pg'

// Define a schema
const User = pgTable('user', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: text().notNull(),
  email: text().unique()
})

// Connect a database
const db = connect(new Pool({connectionString: process.env.DATABASE_URL}))

// Create the table and start querying
await db.create(User)

await db.insert(User).values({name: 'Ada', email: 'ada@example.com'})

const users = await db.select().from(User).where(eq(User.name, 'Ada'))

Documentation

The full documentation lives in docs. A map of the territory:

Section	What's inside
Getting started	Install, connect, define a schema, run your first queries
Drivers	Every supported driver and how to connect it
Tables	Defining tables, column modifiers, defaults, references
Column types	Per dialect: SQLite, PostgreSQL, MySQL, universal
Indexes & constraints	Primary keys, unique constraints, foreign keys, indexes
Views	Views and PostgreSQL materialized views
Schemas & enums	pgSchema and pgEnum
Custom column types	Mapping your own types to and from the database
Select	Selections, filtering, ordering, grouping, pagination, locking
Joins	Inner/left/right/full/cross joins and lateral variants
Insert	Values, bulk inserts, upserts, insert-from-select
Update / Delete	Modifying and removing rows, returning
Filter operators	eq, and, or, inArray, like, between and friends
Aggregates	count, sum, avg, min, max and grouping
Include	Nested results without an ORM
JSON	Typed JSON columns, selecting and filtering nested fields
Set operations	union, intersect, except
Subqueries & CTEs	.as(), $with, recursive CTEs
The sql tag	Raw SQL escape hatches, safely
Transactions & batch	Atomic operations, rollbacks, batching
Prepared statements	Placeholders, prepare, inspecting generated SQL
Migrations	db.create, db.migrate and how diffing works
Universal queries	One query, three databases
Coming from Drizzle	Side-by-side comparison and migration notes

Supported drivers

Pass a database client instance to the matching connect function:

import Database from 'better-sqlite3'
import {connect} from 'rado/driver/better-sqlite3'

const db = connect(new Database('app.db'))

Database	Package	Import	Sync/async
PostgreSQL	pg	rado/driver/pg	async
PostgreSQL	@electric-sql/pglite	rado/driver/pglite	async
PostgreSQL	@neondatabase/serverless	rado/driver/pg	async
PostgreSQL	@vercel/postgres	rado/driver/pg	async
SQLite	better-sqlite3	rado/driver/better-sqlite3	sync
SQLite	node:sqlite	rado/driver/node-sqlite	sync
SQLite	bun:sqlite	rado/driver/bun-sqlite	sync
SQLite	sql.js	rado/driver/sql.js	sync
SQLite	@libsql/client	rado/driver/libsql	async
SQLite	Cloudflare D1	rado/driver/d1	async
MySQL	mysql2	rado/driver/mysql2	async

Synchronous drivers give you a fully synchronous database. No await required, although awaiting queries still works. See Drivers for details.

A taste of the API

Immutable queries

Queries are values. Branch them, reuse them, store them in variables. Nothing mutates:

import {count, gt} from 'rado'

const allUsers = db.select(count()).from(User)
const filtered = allUsers.where(gt(User.id, 1))

const [total] = await allUsers // unaffected by the where above
const [matching] = await filtered

Nested results with include

No relations file, no separate query API. Declare the relationship inline:

import {eq, include} from 'rado'

const usersWithPosts = await db
  .select({
    ...User,
    posts: include(db.select().from(Post).where(eq(Post.authorId, User.id)))
  })
  .from(User)

Each row comes back with a typed posts array. Use include.one for a single related row. More in Include.

Typed JSON columns

import {eq} from 'rado'
import {jsonb, pgTable, serial, text} from 'rado/postgres'

const User = pgTable('user', {
  id: serial().primaryKey(),
  name: text(),
  settings: jsonb<{theme: 'light' | 'dark'; notifications: boolean}>()
})

const darkSide = await db
  .select(User.name)
  .from(User)
  .where(eq(User.settings.theme, 'dark'))

That User.settings.theme is a real, typed expression that compiles to the correct JSON access syntax for your database. More in JSON.

Universal queries

Define your schema with rado/universal column types and pick the database at runtime:

import {table} from 'rado'
import {id, text} from 'rado/universal'

const User = table('user', {
  id: id(), // auto-incrementing primary key on every dialect
  name: text()
})

const db = useSqlite ? sqliteDb : postgresDb
const names = await db.select(User.name).from(User)

More in Universal queries.

Auto-migrations

// Compares the defined schema to the actual database and applies the diff
await db.migrate(User, Post)

Works in Node.js, Bun, Deno and the browser. It is powerful and a little direct, so read Migrations before pointing it at production.

Coming from Drizzle?

Most queries port by changing imports:

import {sql, eq, and, or} from 'rado' // was 'drizzle-orm'
import {pgTable, integer, text} from 'rado/postgres' // was 'drizzle-orm/pg-core'
import {sqliteTable} from 'rado/sqlite' // was 'drizzle-orm/sqlite-core'
import {mysqlTable} from 'rado/mysql' // was 'drizzle-orm/mysql-core'

Rado also exposes compatibility helpers such as InferSelectModel, InferInsertModel, getTableColumns and TransactionRollbackError. See Coming from Drizzle for a full side-by-side comparison of what's the same, what's better and what's missing.

License

MIT