rado

Fully typed, lightweight TypeScript query builder.

Features

• Fully typed queries using TypeScript
• Composable and reusable query structures
• First-class support for JSON columns
• No code generation step required
• Zero dependencies
• Universal query support for multiple database engines

Installation

Quick Start

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

// Define your schema
const User = table({
  id: integer().primaryKey(),
  name: text().notNull(),
  email: text().unique()
})

// Connect to the database
const db = connect(new Pool({
  host: 'localhost',
  database: 'my_database'
}))

// Perform a query
const users = await db.select().from(User)
console.log(users)

Supported Databases

Currently supported drivers:

| PostgreSQL | import | | -------------------------- | ------------------------------ | | pg | rado/driver/pg | | @electric-sql/pglite | rado/driver/pglite | | @neondatabase/serverless | rado/driver/pg | | @vercel/postgres | rado/driver/pg |

| SQLite | import | | -------------------------- | ------------------------------ | | better-sqlite3 | rado/driver/better-sqlite3 | | bun:sqlite | rado/driver/bun-sqlite | | sql.js | rado/driver/sql.js | | @libsql/client | rado/driver/libsql | | Cloudflare D1 | rado/driver/d1 |

| MySQL | import | | -------------------------- | ------------------------------ | | mysql2 | rado/driver/mysql2 |

Pass an instance of the database to the connect function to get started:

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

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

Querying

Select

Select operations allow you to retrieve data from your database.

// Basic select
const allUsers = await db.select().from(User)

// Select specific columns
const userNames = await db.select({id: User.id, name: User.name}).from(User)

Where conditions

Where conditions help you filter your query results.

import {eq, and, or, gt, isNull} from 'rado'

// Simple equality
const john = await db.select().from(User).where(eq(User.name, 'John'))

// Complex conditions
const users = await db.select().from(User)
  .where(
    gt(User.age, 18),
    or(
      eq(User.name, 'Alice'),
      isNull(User.email)
    )
  )

Joins

Joins allow you to combine data from multiple tables.

const usersWithPosts = await db.select({
  userName: User.name,
  postTitle: Post.title,
})
.from(User)
.leftJoin(Post, eq(User.id, Post.authorId))

Ordering and Grouping

You can order and group your query results:

import {desc, asc, count} from 'rado'

const orderedUsers = await db.select()
  .from(User)
  .orderBy(asc(User.name))

const userPostCounts = await db.select({
  userName: User.name,
  postCount: count(Post.id)
})
.from(User)
.leftJoin(Post, eq(User.id, Post.authorId))
.groupBy(User.name)

Pagination

Pagination helps you manage large datasets by retrieving results in smaller chunks:

const pageSize = 10
const page = 2

const paginatedUsers = await db.select()
  .from(User)
  .limit(pageSize)
  .offset((page - 1) * pageSize)

JSON columns

Rado provides first-class support for JSON columns:

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

const User = pgTable({
  id: serial().primaryKey(),
  name: text(),
  metadata: jsonb<{subscribed: boolean}>()
})

const subscribedUsers = await db.select()
  .from(User)
  .where(eq(User.metadata.subscribed, true))

Subqueries

const subquery = db.select({authorId: Post.authorId})
  .from(Post)
  .groupBy(Post.authorId)
  .having(gt(count(Post.id), 5))
  .as('authorIds')

const prolificAuthors = await db.select()
  .from(User)
  .where(inArray(User.id, subquery))

Include

Aggregate rows using the include function:

import {include} from 'rado'

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

// Use include.one for a single related record
const usersWithLatestPost = await db.select({
  ...User,
  latestPost: include.one(
    db.select()
      .from(Post)
      .where(eq(Post.authorId, User.id))
      .orderBy(desc(Post.createdAt))
      .limit(1)
  )
}).from(User)

SQL Operator

The sql operator allows you to write raw SQL and interpolate values safely:

import {sql} from 'rado'

const minAge = 18
const adultUsers = await db.select()
  .from(User)
  .where(sql`${User.age} >= ${minAge}`)

Modifying Data

Insert

Insert operations allow you to add new records to your database:

// Single insert
const newUser = await db.insert(User)
  .values({name: 'Alice', email: '[email protected]'})
  .returning()

// Bulk insert
const newUsers = await db.insert(User)
  .values([
    {name: 'Bob', email: '[email protected]'},
    {name: 'Charlie', email: '[email protected]'},
  ])
  .returning()

Update

Update operations modify existing records:

const updatedCount = await db.update(User)
  .set({name: 'Johnny'})
  .where(eq(User.name, 'John'))

Delete

Delete operations remove records from your database:

await db.delete(User).where(eq(User.name, 'John'))

Transactions

Transactions allow you to group multiple operations into a single atomic unit.

const result = await db.transaction(async (tx) => {
  const user = await tx.insert(User)
    .values({ name: 'Alice', email: '[email protected]' })
    .returning()
  const post = await tx.insert(Post)
    .values({ title: 'My First Post', authorId: user.id })
    .returning()
  return {user, post}
})

Universal Queries

Rado provides a universal query builder that works across different database engines, whether they run synchronously or asynchronously. This is useful for writing database-agnostic code.

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

const User = table('user', {
  id: id(),
  name: text() 
})

const db = process.env.SQLITE ? sqliteDb : postgresDb

const userNames = await db.select(User.name).from(User)

Custom Column Types

You can define custom column types, such as a boolean column that is stored as a tinyint in the database:

import {Column, column, table, sql} from 'rado'

export function bool(name?: string): Column<boolean | null> {
  return column({
    name,
    type: sql`tinyint(1)`,
    mapFromDriverValue(value: number): boolean {
      return value === 1
    },
    mapToDriverValue(value: boolean): number {
      return value ? 1 : 0
    }
  })
}

// Usage
const User = table('user', {
  // ...
  isActive: bool()
})