Raypx

Raypx uses Drizzle ORM for all database operations. Drizzle provides a type-safe query builder that infers types from your schema definitions.

Creating a Database Instance

Import createDb from @raypx/database:

import { createDb } from "@raypx/database"

const db = createDb()

createDb() returns a singleton connection. Calling it multiple times returns the same database client. Use it directly in your procedures and server functions.

Importing Schema and Operators

import { createDb, schema, eq, and, desc, sql } from "@raypx/database"

const { profiles, documents, conversations, messages } = schema

Common operators exported from @raypx/database:

Operator	Description
`eq`	Equality (`column = value`).
`and`	Logical AND.
`or`	Logical OR.
`desc`	Descending order.
`sql`	Raw SQL template literal.
`gte`	Greater than or equal.
`count`	Count aggregate.
`sum`	Sum aggregate.

Select

Basic Select

const [profile] = await db
  .select()
  .from(profiles)
  .where(eq(profiles.userId, userId))
  .limit(1)

Select Specific Columns

const docs = await db
  .select({
    id: documents.id,
    title: documents.title,
    contentType: documents.contentType,
    status: documents.status,
    createdAt: documents.createdAt,
  })
  .from(documents)
  .where(eq(documents.userId, userId))
  .orderBy(desc(documents.createdAt))

Select with Multiple Conditions

const [doc] = await db
  .select()
  .from(documents)
  .where(and(eq(documents.id, input.id), eq(documents.userId, userId)))
  .limit(1)

Select with Raw SQL

import { sql } from "@raypx/database"

const rows = await db
  .select({
    aiTokens: sql<number>`COALESCE(SUM(CASE WHEN metric = 'ai_tokens' THEN quantity ELSE 0 END), 0)`,
    aiRequests: sql<number>`COALESCE(SUM(CASE WHEN metric = 'ai_requests' THEN quantity ELSE 0 END), 0)`,
    costUsd: sql<string>`COALESCE(SUM(cost_usd), 0)`,
  })
  .from(usageEvents)
  .where(and(eq(usageEvents.userId, userId), sql`${usageEvents.createdAt} >= ${periodStart}`))

Insert

const [convo] = await db
  .insert(conversations)
  .values({
    userId: context.session.user.id,
    title: input.title,
    task: input.task ?? "summarize",
  })
  .returning()

Always use .returning() to get the inserted row back. This is needed to return the generated id and timestamp fields.

Upsert (Insert or Update)

Drizzle supports upsert via .onConflictDoUpdate(). For a pattern like "insert if not exists, update if exists":

const [profile] = await db
  .insert(profiles)
  .values({ userId, ...input })
  .onConflictDoUpdate({
    target: profiles.userId,
    set: { ...input, updatedAt: new Date() },
  })
  .returning()

Or use the select-then-insert/update pattern as shown in the profile router:

const [existing] = await db
  .select()
  .from(profiles)
  .where(eq(profiles.userId, userId))
  .limit(1)

if (existing) {
  const [updated] = await db
    .update(profiles)
    .set({ ...input, updatedAt: new Date() })
    .where(eq(profiles.userId, userId))
    .returning()
  return updated
}

const [created] = await db
  .insert(profiles)
  .values({ userId, ...input })
  .returning()
return created

Update

const [updated] = await db
  .update(profiles)
  .set({ defaultModel: "gpt-4", updatedAt: new Date() })
  .where(eq(profiles.userId, userId))
  .returning()

Delete

const [deleted] = await db
  .delete(documents)
  .where(and(eq(documents.id, input.id), eq(documents.userId, userId)))
  .returning()

if (!deleted) {
  throw new Error("Document not found")
}

Raw SQL

For queries that are difficult to express with the query builder, use the sql template literal:

import { sql } from "@raypx/database"

// Health check
await db.execute(sql`SELECT 1`)

// Complex aggregation
const rows = await db.execute(sql`
  SELECT metric, SUM(quantity) as total
  FROM usage_event
  WHERE user_id = ${userId}
  GROUP BY metric
`)

Type Inference

Drizzle infers types from your schema. When you use .select(), the return type is automatically inferred:

const docs = await db.select().from(documents)
// docs is inferred as Document[] (from typeof documents.$inferSelect)

For selected columns, the type matches exactly what you requested:

const docs = await db.select({
  id: documents.id,
  title: documents.title,
}).from(documents)
// docs is inferred as { id: string; title: string }[]

Queries