Database

Guren reads and writes the database through models built on Drizzle tables, with a query API shaped like Laravel's Eloquent and typed from your schema. A new app uses SQLite and needs no setup; PostgreSQL, MySQL and Aurora Serverless are a configuration change away.

The page follows the order you meet things in: connect, declare the schema and migrate it, seed it, then define models and query them.

Connecting to the Database

The scaffold writes config/database.ts for the driver you picked with create-guren-app --db, and createApp({ config }) lists it, so the ORM connects at boot with the app's validated environment. Every driver exposes the same functions (getDatabase, migrateDatabase, closeDatabase, configureOrm, seedDatabase, resetDatabase, migrationStatus) and an AppSeederContext type, so models and queries do not change when you switch.

Driver	--db	Factory in config/database.ts
SQLite (the default)	sqlite	createSqliteDatabase
PostgreSQL	postgres	createPostgresDatabase
MySQL	mysql	createMySqlDatabase
Aurora Serverless (AWS Data API)	none: write it by hand	createAwsDataApiDatabase
Cloudflare D1	none: see the Cloudflare guide	D1's own factory

Each factory reads its connection settings from config/env.ts through a resolver; Configuration explains the resolver's context argument.

SQLite

New projects use SQLite through Bun's built-in driver, with no Docker or external database:

// config/database.ts
import { createSqliteDatabase, defineDatabaseConfig, type SqliteSeederContext } from '@guren/core'
import env from './env.js'

const database = createSqliteDatabase({
  migrationsFolder: new URL('../db/migrations', import.meta.url),
  seedersFolder: new URL('../db/seeders', import.meta.url),
  filename: (context) => {
    const values = context?.env ?? env.parse(undefined, { mode: 'report' }).values
    // `bun test` sets NODE_ENV=test, so the suite uses its own file.
    return process.env.NODE_ENV === 'test'
      ? values.TEST_DATABASE_URL ?? './data/guren.test.db'
      : values.DATABASE_URL ?? './data/guren.db'
  },
})

export const { getDatabase, migrateDatabase, closeDatabase, configureOrm, seedDatabase, resetDatabase, migrationStatus } = database

export type AppSeederContext = SqliteSeederContext

export default defineDatabaseConfig(database, { seedOnBoot: process.env.NODE_ENV !== 'production' })

With seedOnBoot true, the app runs the seeders at boot once the migrations folder holds migrations. Scaffolds turn it off in production, where you run bunx guren db:seed explicitly. Tests use a separate file; see Testing.

PostgreSQL

DATABASE_URL is declared in the scaffolded config/env.ts:

// config/database.ts
import { createPostgresDatabase, defineDatabaseConfig, type PostgresSeederContext } from '@guren/core'
import env from './env.js'

const database = createPostgresDatabase({
  migrationsFolder: new URL('../db/migrations', import.meta.url),
  seedersFolder: new URL('../db/seeders', import.meta.url),
  connectionString: (context) => (context?.env ?? env.parse(undefined, { mode: 'report' }).values).DATABASE_URL
    ?? 'postgres://guren:guren@localhost:54322/guren',
})

export const { getDatabase, migrateDatabase, closeDatabase, configureOrm, seedDatabase, resetDatabase, migrationStatus } = database

export type AppSeederContext = PostgresSeederContext

export default defineDatabaseConfig(database, { seedOnBoot: process.env.NODE_ENV !== 'production' })

Tables are declared with pgTable:

// db/schema.ts
import { pgTable, serial, text, timestamp } from '@guren/orm/drizzle/pg'

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  body: text('body').notNull(),
  status: text('status').notNull().default('draft'),
  publishedAt: timestamp('published_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow(),
})

On PostgreSQL, give every timestamp column { withTimezone: true }. A timestamp without time zone stores a bare wall clock with no offset, so defaultNow() records it in the database session's zone while your app reads it back as UTC, and any client other than your app sees a different instant. Scaffolds already emit it, and bunx guren check warns about a column declared without it. That warning reads the schema statically, so a clean result means nothing was found, not that every column is right.

MySQL

Use createMySqlDatabase when your app runs on MySQL-compatible databases.

// config/database.ts
import { createMySqlDatabase, defineDatabaseConfig, type MySqlSeederContext } from '@guren/core'
import env from './env.js'

const database = createMySqlDatabase({
  migrationsFolder: new URL('../db/migrations', import.meta.url),
  seedersFolder: new URL('../db/seeders', import.meta.url),
  connectionString: (context) => (context?.env ?? env.parse(undefined, { mode: 'report' }).values).DATABASE_URL
    ?? 'mysql://guren:guren@localhost:33306/guren',
})

export const { getDatabase, migrateDatabase, closeDatabase, configureOrm, seedDatabase, resetDatabase, migrationStatus } = database

export type AppSeederContext = MySqlSeederContext

export default defineDatabaseConfig(database, { seedOnBoot: process.env.NODE_ENV !== 'production' })

Aurora Serverless (AWS Data API)

Use createAwsDataApiDatabase when your app runs on AWS Lambda against Aurora Serverless v2 with the RDS Data API enabled. The Data API is HTTP-based, so there is no connection pool to manage and the Lambda function does not need to run inside a VPC.

// config/database.ts
import { createAwsDataApiDatabase, defineDatabaseConfig, type ConnectionContext } from '@guren/core'
import env from './env.js'

const values = (context?: ConnectionContext) => context?.env ?? env.parse(undefined, { mode: 'report' }).values

const database = createAwsDataApiDatabase({
  migrationsFolder: new URL('../db/migrations', import.meta.url),
  seedersFolder: new URL('../db/seeders', import.meta.url),
  // A resolver returning undefined falls back to the environment variable:
  // DATABASE_NAME, DATABASE_RESOURCE_ARN, DATABASE_SECRET_ARN
  database: (context) => values(context).DATABASE_NAME,
  resourceArn: (context) => values(context).DATABASE_RESOURCE_ARN,
  secretArn: (context) => values(context).DATABASE_SECRET_ARN,
})

export const { getDatabase, migrateDatabase, closeDatabase, configureOrm, seedDatabase } = database

export default defineDatabaseConfig(database, { seedOnBoot: process.env.NODE_ENV !== 'production' })

Declare the three keys in config/env.ts (see Configuration), for example as Env.string().optional().

Install the driver package alongside it:

bun add @aws-sdk/client-rds-data

The adapter exposes the same runtime API as the other drivers, and migrations use the standard drizzle-kit folders. One deliberate difference: getDatabase() does not run pending migrations automatically. On Lambda that check would cost several serialized Data API round trips on every cold start. Run migrations out of band (bun run db:migrate, or the console handler once deployed), or opt back in with migrateOnStart: true. For drizzle-kit generate/push against the Data API, set driver: 'aws-data-api' in drizzle.config.ts with the same database/resourceArn/secretArn credentials.

Schema and Migrations

Tables are declared in db/schema.ts with Drizzle's builders for your driver. A fresh SQLite app starts with a users table:

// db/schema.ts
import { sqliteTable, integer, text } from '@guren/orm/drizzle/sqlite'

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull(),
  createdAt: text('created_at').notNull().$defaultFn(() => new Date().toISOString()),
})

After changing the schema, generate a migration from it and apply it:

bunx guren make:migration --name add_posts_table
bun run db:migrate

Generating migrations

The command reads the first of drizzle.config.ts, .mts, .js, .mjs and .json that exists, in that order, for the schema path, output directory, and dialect. You can override the paths:

bunx guren make:migration --schema ./custom/schema.ts --out ./custom/migrations

Overrides cannot be combined with drizzle-kit's --config, so Guren reads the config itself and restates its dialect (and driver) on the command line. Fields you did not override keep their configured values, so --schema alone still writes to the config's out. If a config field cannot be restated as a flag, the command names it rather than letting the migration differ silently. A config whose schema is a list is one such case: drizzle-kit would keep only the last entry, so an override without --schema stops with an error.

An app with no drizzle config has nothing to declare the dialect, so state it directly:

bunx guren make:migration --dialect postgresql

The command runs the drizzle-kit your app installs, found in its node_modules or a parent's (a hoisted workspace install). With none installed it stops and asks for bun install; it never fetches a copy from npm, whose version may take other flags.

When migrations run

db:migrate is not the only thing that applies them. Every driver except the Data API and D1 adapters applies pending migrations on the first getDatabase() or configureOrm() call, in every environment: bun run dev, bun test, and the container the scaffolded Dockerfile starts with bun bin/serve.ts all migrate at boot, which is what lets a deploy that ships only a new image arrive migrated. A boot that applies something prints one line naming what it applied and where it came from; an up-to-date database boots silently.

The cost of that convenience is that a migration you did not mean to keep still reaches the database. A generator writes one as a new, untracked folder under db/migrations/, and neither git switch nor git branch -D removes an untracked file, so a migration generated on a branch you threw away is still on disk and still pending. Remove it with git clean -fd (git clean -fdn first shows what that would take) rather than expecting the branch to take it with it.

Removing the folder only helps before a boot has applied it, and a dev server that reloads while the generator is still editing files can get there first. After that, the tracker keeps a row for a migration no folder carries, the migrator skips it, and whatever it created stays in the database. db:status lists such a row as orphaned, and every boot warns about it before migrating.

The two exceptions never migrate at boot, for different reasons. The Data API adapter is opt-in (migrateOnStart) because the check costs serialized round trips on every Lambda cold start. D1 has no runtime path at all: migrations go through wrangler d1 migrations apply <database>, and migrateDatabase() says so rather than trying.

Destructive migrations

drizzle-kit writes whatever the schema diff implies. When a column is removed and another added in one edit, it asks whether that is a rename, and only in an interactive terminal; run by an agent or a script, it writes a DROP COLUMN, and the next boot applies it. Guren reads every pending migration before it applies anything and reports these statements:

Kind	What it matches
drop-table, drop-column, drop-schema	DROP TABLE, ALTER TABLE … DROP COLUMN, DROP SCHEMA / DROP DATABASE
alter-column-type	PostgreSQL ALTER COLUMN … SET DATA TYPE, MySQL MODIFY COLUMN / CHANGE COLUMN
table-rebuild	SQLite's __new_<table> copy, drop and rename, which drizzle-kit writes for most column changes. A rebuild whose CREATE TABLE __new_<table> copies every column with the same or a wider type (an added default or foreign key) passes, unless another table references it with ON DELETE CASCADE, SET NULL or SET DEFAULT and foreign keys stay on while it runs (Cloudflare D1), where its DROP TABLE deletes or rewrites those rows
rename	RENAME TO, RENAME COLUMN, RENAME TABLE
truncate, delete-all	TRUNCATE (a MySQL TRUNCATE PARTITION too), and DELETE FROM with no WHERE
drop-partition	MySQL ALTER TABLE … DROP PARTITION, which drops the partition's rows
drop-cascade	DROP TYPE … CASCADE, DROP DOMAIN … CASCADE and any other DROP … CASCADE, which also drop the columns that depend on them, and PostgreSQL DROP OWNED
unreadable	SQL the guard cannot read: an unterminated string, comment or body, and SQL built at run time (EXECUTE of a concatenation or format(), PREPARE … FROM @variable)

The guard reads the SQL as the database will: MySQL's backslash escapes and # comments, PostgreSQL's E'…' strings and dollar-quoted bodies. The dialect comes from the factory at boot and from snapshot.json elsewhere; where neither names one, every dialect's reading counts. Statements inside a CREATE PROCEDURE, FUNCTION or TRIGGER body, and a function body written as a string, are judged like any other.

A rename loses no rows, but the previous deploy keeps reading the old name until it is replaced. A type change that only widens (a longer varchar, smallint to integer) passes when the snapshot.json drizzle-kit writes beside each migration shows both types; without that evidence it is reported. A statement against a table that an earlier pending migration creates is never reported, so a fresh database, which applies the whole history at once, is not refused over an old drop.

To apply a destructive migration, acknowledge it on the first line of its migration.sql, with the reason a reviewer should read:

-- guren:allow-destructive: posts moved to articles in 20261012_copy_posts
DROP TABLE "posts";

Keep the comment on the first line. drizzle splits the file at --> statement-breakpoint, and a comment alone after the last one is an empty statement that SQLite rejects.

Where the guard applies:

• bunx guren db:migrate refuses an unacknowledged statement in every environment. --dry-run reports without applying, and --allow-destructive applies once without editing the file.
• At boot, the factory option decides. destructiveMigrations: 'refuse', which new apps set in config/database.ts, throws before anything is applied; 'warn', the default for apps that do not set it, prints the statements and applies them. GUREN_ALLOW_DESTRUCTIVE_MIGRATIONS=1 lets one refused run through. A db:reset re-applies everything into an emptied database and is never judged.
• bunx guren check and bunx guren gate report the migrations not yet on main (committed on the branch, uncommitted or untracked), so the agent's Stop hook and CI catch one before it is merged. A drop that already shipped is never reported.

const database = createPostgresDatabase({
  migrationsFolder: new URL('../db/migrations', import.meta.url),
  destructiveMigrations: 'refuse',
})

The comment records that a person decided the data can go; an agent can type it as easily as the statement, so the harness tells it to ask first, and review is where a reason that does not hold up gets caught.

Seeding

Seeders live in db/seeders/. The simplest one exports an async run() function; this one uses the Post model described in Defining Models:

// db/seeders/PostsSeeder.ts
import { Post } from '@/app/Models/Post'

export async function run() {
  await Post.create({ title: 'Hello', body: 'Welcome to Guren!' })
}

bun run db:seed runs every seeder:

bun run db:seed

Seeding through Drizzle

defineSeeder() hands the seeder the Drizzle database itself, which is useful for bulk inserts and upserts that bypass the model layer. Its type depends on the dialect, so annotate the context with AppSeederContext, the alias config/database.ts exports for whichever database the app is configured with:

// db/seeders/PostsSeeder.ts
import { defineSeeder } from '@guren/core'
import type { AppSeederContext } from '../../config/database.js'
import { posts } from '../schema.js'

export default defineSeeder(async ({ db }: AppSeederContext) => {
  await db.insert(posts).values({ title: 'Hello', body: 'Welcome to Guren!' })
})

SeederContext without a type argument means PostgreSQL, so on MySQL or SQLite it rejects the app's own schema. AppSeederContext is exported by config/database.ts in apps scaffolded from this release onwards; an older app imports its dialect's alias from @guren/core directly instead, one of PostgresSeederContext, MySqlSeederContext, SqliteSeederContext, or AwsDataApiSeederContext:

import { defineSeeder, type MySqlSeederContext } from '@guren/core'

Defining Models

Use defineModel() to derive a typed model directly from a Drizzle table:

// app/Models/Post.ts
import { defineModel } from '@guren/core'
import { posts } from '@/db/schema'

export type PostRecord = typeof posts.$inferSelect
export type NewPostRecord = typeof posts.$inferInsert

export class Post extends defineModel(posts) {}

That is all you need. Post now has find, create, where, paginate, and dozens more methods.

Reshaping the create payload

The type defineModel() infers for create() requires every column without a database default. When the model derives a column itself, say so in the same call rather than hand-writing a type:

export class User extends defineModel(users, {
  base: AuthenticatableModel,
  optionalOnCreate: ['passwordHash'],  // derived from `password`, so callers need not pass it
  requireOnCreate: ['password'],     // demand the virtual field instead
}) {}

optionalOnCreate makes columns optional: they keep their type, callers just need not supply them. requireOnCreate goes the other way, and accepts both table columns (Drizzle marks defaulted ones optional) and fields contributed by base. Both are type-level only and are checked against the real keys, so a typo fails to compile.

Neither closes the payload: a create type always admits unknown keys as unknown, so fillable remains what rejects an unwanted field at runtime.

Querying Data

Start simple, then build up:

// Fetch everything
const allPosts = await Post.all()

// Find by primary key
const post = await Post.find(1)          // returns null if missing
const post = await Post.findOrFail(1)    // throws ModelNotFoundException (404) if missing

// First matching record
const latest = await Post.first()

Fluent QueryBuilder

Chain conditions, ordering, and limits for more complex queries:

const published = await Post.where('status', 'published').get()

const trending = await Post.where('status', 'published')
  .where('views', '>', 100)
  .orWhere('featured', true)
  .orderBy('createdAt', 'desc')
  .limit(10)
  .get()

// Object syntax for simple equality
const drafts = await Post.where({ status: 'draft', authorId: 1 }).get()

// Callback syntax groups conditions in parentheses:
// (title LIKE ? OR excerpt LIKE ?) AND status = 'published'
const hits = await Post.where((q) => {
  q.where('title', 'like', '%bun%').orWhere('excerpt', 'like', '%bun%')
})
  .where('status', 'published')
  .get()

Method	Description
.where(column, value)	Filter by equality
.where(column, op, value)	Filter with an operator: '=', '!=', '>', '<', '>=', '<=', 'like', 'in', 'not in', 'is null', 'is not null'
.where(object)	Filter by multiple equalities
.where(callback)	Parenthesized condition group, AND-ed with the rest
.orWhere(column, value)	OR condition
.orWhere(column, op, value)	OR condition with an operator
.orWhere(callback)	OR a parenthesized condition group
.orderBy(column, direction?)	Sort results
.limit(n) / .offset(n)	Limit and skip
.get()	Execute and return array
.first()	Return first result or null
.count()	Return count of matching records
.sum(column) / .avg(column)	Sum or average of a column over matching records
.min(column) / .max(column)	Smallest or largest value of a column
.exists()	Whether any record matches
.toDrizzle(query?)	A Drizzle select that carries the model's conditions and scopes

Aggregates

const revenue = await Order.where('status', 'paid').sum('total')
const averageViews = await Post.where('status', 'published').avg('views')
const newest = await Post.newQuery().max('createdAt')
const hasDrafts = await Post.where('status', 'draft').exists()

Aggregates run through the model's global scopes, so a model with SoftDeletes leaves trashed rows out of a sum just as it leaves them out of get().

The result keeps the column's type. An integer or real column sums to a number, a bigint({ mode: 'bigint' }) column to a bigint, and a numeric or decimal column to a string, which is how Drizzle types those columns so that no digit is lost. avg() returns a number for a number column and a decimal string otherwise. When nothing matches, sum() returns the zero of that type (0, 0n or '0') and the other three return null. Like count(), aggregates ignore limit() and offset().

A sum of a number column past Number.MAX_SAFE_INTEGER throws instead of rounding. Declare that column with mode: 'bigint'.

Dropping to Drizzle

toDrizzle() hands a query to Drizzle with the model's conditions and global scopes already in its WHERE clause. Use it for joins, custom selections, and anything else the builder cannot express:

import { getDatabase } from '@/config/database'
import { posts, users } from '@/db/schema'
import { desc, eq, gt } from 'drizzle-orm'

const db = await getDatabase()
const rows = await Post.where('status', 'published')
  .toDrizzle(
    db.select({ id: posts.id, title: posts.title, author: users.name })
      .from(posts)
      .leftJoin(users, eq(posts.authorId, users.id)),
  )
  .where(gt(posts.views, 100))
  .orderBy(desc(posts.id))

• Without an argument, toDrizzle() starts from select().from(table) and runs inside the open transaction when there is one. A query you pass runs on the handle you built it from, so inside Model.transaction() build it from trx.
• A .where() on the result is AND-ed with the model's conditions and cannot drop a scope. To pass a query that already has a where(), call $dynamic() before it.
• The builder's orderBy(), limit() and offset() carry over. select() carries over only without an argument, since a query you pass keeps its own selection.
• Rows come back as Drizzle reads them. Casts, accessors and eager loads do not apply.
• toSql() returns the same conditions as one SQL fragment for a query you assemble yourself. On a plain Drizzle select a second .where() replaces the first, so combine the fragment with and():

const popular = await db.select().from(posts).where(and(Post.newQuery().toSql(), gt(posts.views, 100)))

A query written against db alone never goes through the model and applies none of its scopes. Model.query() returned exactly that kind of query and is deprecated for this reason (see Upgrading).

Creating and Updating

// Create
const post = await Post.create({
  title: 'Hello World',
  body: 'Welcome to Guren!',
})

// Update
await Post.update({ id: post.id }, { title: 'Updated Title' })

// Delete
await Post.delete({ id: post.id })

Bulk update(), forceUpdate(), and delete() reject limit(), offset(), and orderBy(). Select the intended IDs first, then write with whereIn(). On a SoftDeletes model, builder delete() also marks rows deleted; use forceDelete() for permanent removal.

Transactions

Use Model.transaction() when multiple writes must succeed or fail together:

await Post.transaction(async (trx) => {
  const post = await Post.create({
    title: 'Atomic write',
    body: 'Everything in one transaction',
  }, { trx })

  await Post.update({ id: post.id }, { status: 'published' }, { trx })
})

If an error is thrown in the callback, Guren rolls back the transaction.

The second argument to the callback is a transaction-bound copy of the model, which passes { trx } for you:

await Post.transaction(async (_trx, txPost) => {
  const post = await txPost.create({
    title: 'Scoped write',
    body: 'No manual { trx } forwarding',
  })

  await txPost.update({ id: post.id }, { status: 'published' })
})

Things to know:

• A call without { trx } still joins the transaction. Inside the callback, a model call that leaves the handle off runs on the open transaction instead of going to the pool, where a single-connection pool would make it wait on the transaction that owns the connection.
• A nested Model.transaction() is a savepoint. Its writes commit or roll back with the outer transaction, and an inner error the outer callback catches discards only the inner writes. Await each nested transaction before starting the next: savepoints on one connection are released in the order they were taken, so two running at once discard each other's frames.
• On SQLite, transactions run one at a time. SQLite holds a single connection, so concurrent transactions are queued, each committing or rolling back on its own. Model reads and writes made outside a transaction wait until it settles, and so does awaiting toDrizzle() without an argument. Its synchronous .all(), .get(), .run() and .values() cannot wait, so they throw while another request holds a transaction open. A Drizzle query you build on db yourself does not wait and would run inside that transaction; await the transaction first.

Mass Assignment Protection

Control which fields can be set through create() and update():

export class Post extends defineModel(posts, {
  // Allowlist — only these fields are assignable.
  // Checked against the table's columns: a typo is a compile error.
  fillable: ['title', 'body', 'status'],
}) {}

The same allowlist can be declared as static fillable = ['title', 'body', 'status'] on the class instead; the option form is preferred because TypeScript verifies every name against the table (a static declaration on the subclass shadows the option). When fillable is set, passing any field outside the allowlist to create() or update() throws a MassAssignmentException (exported from @guren/core). The message names the blocked fields, so a typo or an injection attempt fails loudly at the call site instead of being silently discarded and resurfacing later as a confusing NOT NULL violation:

await Post.create({ title: 'Hello', body: '...', status: 'draft', authorId: 1 })
// MassAssignmentException: Post: mass assignment blocked for field(s) "authorId"

Columns the server chooses

Some columns belong in the row but must never come from a request: the post's author, the post a comment belongs to, a default status. Keep them out of fillable and pass them in the set option, beside the validated data:

const author = await this.auth.userOrFail<UserRecord>()
const data = await this.validateBody(PostPayloadSchema)

const post = await Post.create(data, { set: { authorId: author.id } })
await Post.update({ id: post.id }, data, { set: { status: 'draft' } })

The query builder's bulk update takes the same option: Post.where('status', 'draft').update(data, { set: { reviewedAt: new Date() } }).

data is still filtered by fillable; only the keys in set skip it. Because set exists to keep the two apart, it throws a MassAssignmentException when:

• a set key is in fillable (a request could already set it, so it belongs in data);
• data carries a key that set also sets;
• a set key is id or a credential column;
• the model declares no fillable.

{ set: { ...data, authorId } } usually throws under the first rule, because the spread moves fillable keys into set. It does not when the spread carries no fillable key, so do not rely on the throw: never spread request input into set. guren audit warns on a spread written inside a set literal (set-spread:*).

Force writes

For writes that carry no request data at all (OAuth account linking, seeders, system records), bypass the allowlist with forceCreate() / forceUpdate():

const user = await User.forceCreate({
  name: profile.name,
  email: profile.email,
  passwordHash: `oauth:${provider}:${profile.id}`,
})

await User.forceUpdate({ id: user.id }, { emailVerifiedAt: new Date() })

Two protections apply regardless of fillable:

• The primary key (id) is always silently stripped from mass-assignment input — a form that round-trips it cannot retarget the write.
• On models extending AuthenticatableModel, credential columns (the password hash and remember token) always throw. Listing them in fillable does not open them; pass a plain password and let the model hash it, or use forceCreate()/forceUpdate() for trusted server-side values.

Relationships

Declare relationships next to the model that owns them, then eager-load them everywhere.

hasMany / belongsTo

// app/Models/User.ts
export class User extends defineModel(users) {
  static override relationTypes: { posts: HasManyRecord<PostRecord> } = {
    posts: [],
  }
}

User.hasMany('posts', () => import('./Post.js').then((m) => m.Post), 'authorId', 'id')

// app/Models/Post.ts
export class Post extends defineModel(posts) {
  static override relationTypes: { author: BelongsToRecord<UserRecord> } = {
    author: null,
  }
}

Post.belongsTo('author', () => import('./User.js').then((m) => m.User), 'authorId', 'id')

relationTypes tells TypeScript what each relation loads as; the values ([], null) are runtime placeholders. Typing loaded relations shows the declare form that needs none. The related model is passed as a function that imports it, so two models can refer to each other without a circular import.

Other relationship types

import { userRoles } from '@/db/schema'

User.hasOne('profile', Profile, 'userId', 'id')
User.belongsToMany('roles', Role, userRoles, 'userId', 'roleId')
Country.hasManyThrough('posts', Post, User, 'countryId', 'authorId')

Method	Arguments
hasMany(name, Related, foreignKey, localKey)	The foreign key on the related table, and this model's key it points at (usually id). Loads an array, [] when nothing matches
belongsTo(name, Related, foreignKey, ownerKey)	The foreign key on this model, and the related model's key. Loads one record or null
hasOne(name, Related, foreignKey, localKey)	As hasMany, but loads one record or null
belongsToMany(name, Related, pivotTable, foreignPivotKey, relatedPivotKey, parentKey?, relatedKey?)	Many-to-many through a pivot table
hasManyThrough(name, Related, Through, firstKey, secondKey)	Records reached through an intermediate model
morphMany(name, Related, morphName, localKey) / morphTo(name, morphName)	Polymorphic relations, below

For belongsToMany, pivotTable is the Drizzle table object exported from @/db/schema, not the table's name. foreignPivotKey and relatedPivotKey are both columns on the pivot, referencing this model and the related one; parentKey and relatedKey are the local keys they point at, and both default to 'id'.

Polymorphic Relationships

Polymorphic relationships let a model belong to more than one type of parent using a single relation. For example, both posts and videos can have comments.

Define the type/id columns on the related table:

// db/schema.ts
export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  body: text('body').notNull(),
  commentableType: text('commentable_type').notNull(),
  commentableId: integer('commentable_id').notNull(),
})

Register the relationships:

// Parent models
Post.morphMany('comments', Comment, 'commentable', 'id')
Video.morphMany('comments', Comment, 'commentable', 'id')

// Child model (inverse)
Comment.morphTo('commentable', 'commentable')

// Map type strings to model classes
Model.morphMap = {
  'Post': Post,
  'Video': Video,
}

Query them like any other relation:

const postWithComments = await Post.with('comments')
const comment = await Comment.findWith(1, 'commentable')
console.log(comment.commentable) // Post or Video record

Eager Loading

const users = await User.with('posts')             // users[0].posts is PostRecord[]
const posts = await Post.with('author')             // posts[0].author is UserRecord | null
const filtered = await Post.with('author', { authorId: [1, 2] })

Eager loading also works on the QueryBuilder, so you can combine it with filters and ordering. The record-returning methods (get(), first(), firstOrFail(), and paginate()) all attach the relations:

const activeUsers = await User.where('active', true)
  .with('posts')
  .orderBy('name')
  .get()

const user = await User.newQuery().with('posts').first()

// Each row of the page carries its author
const page = await Post.newQuery().with('author').orderBy('id', 'desc').paginate({ page: 1, perPage: 10 })

Nested relations

Nested relations use dot notation:

const users = await User.with('posts.comments')

Several paths may branch off the same relation. The shared head is read once, so every branch lands on the same records:

const users = await User.newQuery().with('posts.comments', 'posts.tags').get()

users[0].posts[0].comments // loaded
users[0].posts[0].tags     // loaded too

Constraining what loads

On the QueryBuilder, the object form of with() takes a callback per relation to constrain the query that loads it. The callback receives that relation's query builder with the foreign-key filter already on it, so a where() narrows which related rows load:

const users = await User.newQuery()
  .with({ posts: (q) => q.where('published', true) })
  .get()

users[0].posts // only the published posts

Each object key constrains exactly the level it names, and the order of the keys does not matter. A dotted key constrains the leaf and leaves the head unfiltered; list both keys to constrain both levels:

// `posts` loads unfiltered, `comments` are filtered
await User.newQuery()
  .with({ 'posts.comments': (q) => q.where('approved', true) })
  .get()

// both levels filtered
await User.newQuery()
  .with({
    posts: (q) => q.where('published', true),
    'posts.comments': (q) => q.where('approved', true),
  })
  .get()

For belongsToMany and hasManyThrough, the callback constrains the query for the related model, not the pivot or through-table lookup that finds which rows to fetch. Filter on the related model's own columns:

// keeps the `news` tags of each post; the pivot lookup is untouched
await Post.newQuery()
  .with({ tags: (q) => q.where('label', 'news') })
  .get()

The static Model.with() is a different signature: its second argument filters the parent records, not the relation. Use Model.newQuery().with({ ... }) for constraint callbacks.

Typing loaded relations

Nested paths type only the head segment from relationTypes. To get typed children, declare the nested shape inside the head relation's record type:

export class User extends defineModel(users) {
  declare static relationTypes: {
    posts: HasManyRecord<PostRecord & { comments: CommentRecord[] }>
  }
}

const loaded = await User.with('posts.comments')
loaded[0].posts[0].comments // CommentRecord[] — typed end to end

BelongsToRecord<T> is always T | null — the loader cannot know the row exists. When the foreign key is NOT NULL and the parent is guaranteed, declare the relation with BelongsToRequiredRecord<T> instead. Using the declare modifier skips the runtime placeholder value:

export class Comment extends defineModel(comments) {
  declare static relationTypes: {
    author: BelongsToRequiredRecord<UserRecord>
  }
}

const comments = await Comment.with('author')
comments[0].author.name // no null check required

Relation Counts

withCount() attaches a ${name}Count field without loading the related rows, ideal for list pages that only display totals:

const users = await User.withCount('posts')        // users[0].postsCount is number
const posts = await Post.withCount(['comments', 'author'], { published: true })

Supported for hasMany, hasOne, morphMany (children per record) and belongsTo (0 or 1).

Query Scopes

Scopes encapsulate common filters so you can reuse them by name:

export class Post extends defineModel(posts) {
  static scopes = {
    published: (q: QueryBuilder<PostRecord>) => q.where('status', 'published'),
    popular: (q: QueryBuilder<PostRecord>) => q.where('views', '>', 1000),
    recent: (q: QueryBuilder<PostRecord>) => q.orderBy('createdAt', 'desc').limit(10),
  }
}

Apply them with .scope(), chained with each other or with any other condition:

const trending = await Post.scope('published').scope('popular').get()

const myPopular = await Post.scope('published')
  .where('authorId', currentUser.id)
  .get()

Global Scopes

Global scopes apply automatically to every query on a model. Use them for multi-tenancy, soft deletes, or any filter that should always be active.

// Always filter by the current tenant
User.addGlobalScope('tenant', (q) => q.where('tenantId', currentTenantId()))

// Always exclude inactive users
User.addGlobalScope('active', (q) => q.where('active', true))

Every query entry point applies both scopes automatically: all(), find(), first(), where() and its whereIn/whereNull/select siblings, scope(), orderBy(), paginate() (the count as well as the rows), newQuery(), and the queries that eager-load a relation, which apply the related model's scopes.

Writes honor them too: update(), forceUpdate(), and delete() add the same scopes to their where, so a tenant scope stops one tenant from updating or deleting another tenant's row, not just from reading it.

The only way past them is to ask explicitly, below.

Bypassing Global Scopes

Remove a specific scope for one query:

const allUsers = await User.withoutGlobalScope('active').get()

Remove all global scopes:

const everyone = await User.withoutGlobalScopes().get()

Remove a scope permanently:

User.removeGlobalScope('active')

Model Hooks

Hooks run logic at specific points in a record's lifecycle. Use them for auto-generated slugs, timestamps, audit logs, or cache invalidation:

import { slugify } from '@/app/utils/string'

export class Post extends defineModel(posts) {
  static hooks = {
    creating: async (data) => {
      data.slug = slugify(data.title)
    },
    updating: async (data) => {
      data.updatedAt = new Date()
    },
    deleted: async (data) => {
      console.log('Post deleted:', data.id)
    },
  }
}

Hook	Timing
creating / created	Before / after insert
updating / updated	Before / after update
deleting / deleted	Before / after delete
saving / saved	Before / after both insert and update

Model Observers

When hook logic grows complex, extract it into a dedicated observer class. Observers respond to the same lifecycle events as hooks but live in their own file.

// app/Observers/PostObserver.ts
import type { ModelObserver, PlainObject } from '@guren/core'

export class PostObserver implements ModelObserver {
  creating(data: PlainObject) {
    data.slug = slugify(data.title as string)
  }

  async created(data: PlainObject) {
    await notifySubscribers(data)
  }

  async deleting(data: PlainObject) {
    await clearPostCache(data.id)
  }
}

Register the observer on the model:

import { PostObserver } from '@/app/Observers/PostObserver'

Post.observe(PostObserver)

Returning false from a before-event (creating, updating, deleting, saving) aborts the operation, just like inline hooks.

Observers and inline hooks coexist: hooks fire first, then observers.

Soft Deletes

Instead of permanently removing records, soft deletes set a deletedAt timestamp. Users can recover deleted content, and queries automatically exclude trashed records.

import { SoftDeletes, defineModel } from '@guren/core'
import { posts } from '@/db/schema'

export class Post extends SoftDeletes(defineModel(posts)) {}

await Post.delete({ id: 1 })                // Sets deletedAt on a live row (the row remains)
const active = await Post.all()              // Excludes soft-deleted
const all = await Post.withTrashed().get()   // Includes soft-deleted
const trashed = await Post.onlyTrashed().get()
await Post.restore({ id: 1 })               // Clears deletedAt
await Post.forceDelete({ id: 1 })           // Permanent removal

Every one of these honors the model's other global scopes. delete() marks only a live row the current scopes can see — on an already-trashed row it matches nothing and leaves the original deletedAt alone. restore() and forceDelete() drop the softDelete filter so they reach trashed rows, and keep the rest: a tenant scope still stops a force delete, which cannot be undone, from reaching another tenant's row.

Inside a transaction, every one of them takes the handle. The transaction-bound scope covers delete(); the rest take it as a trailing argument, like any other write:

await Post.transaction(async (trx, txPost) => {
  await txPost.delete({ id: 1 })
  await Post.restore({ id: 2 }, { trx })
  await Post.forceDelete({ id: 3 }, { trx })
  const trashed = await Post.onlyTrashed({ trx }).get()
})

Attribute Casting

Automatically convert column values when reading from the database:

export class Post extends defineModel(posts) {
  static casts = {
    metadata: 'json',       // JSON string -> object
    publishedAt: 'date',    // string -> Date
    isActive: 'boolean',    // 0/1 -> true/false
    viewCount: 'number',    // string -> number
  }
}

The cast types are 'json', 'date', 'boolean', 'number' and 'string'.

Accessors & Mutators

Accessors compute virtual attributes when reading records. Mutators transform values before writing to the database.

Accessors

Define computed properties that are automatically applied when records are fetched:

export class User extends defineModel(users, {
  accessors: {
    // `record` is typed as the table's record — field typos are compile errors
    fullName: (record) => `${record.firstName} ${record.lastName}`,
    isAdmin: (record) => record.role === 'admin',
  },
}) {}

(static accessors = { ... } on the class works too, without the typed record parameter.)

const user = await User.find(1)
console.log(user.fullName)  // "John Doe"
console.log(user.isAdmin)   // true

Accessors run on every read path: all(), find(), where(), first(), and paginate().

Mutators

Transform input data before it hits the database:

export class User extends defineModel(users) {
  static mutators = {
    email: (value) => String(value).toLowerCase().trim(),
    name: (value) => String(value).trim(),
  }
}

await User.create({ email: '  JOHN@EXAMPLE.COM  ', name: '  John  ' })
// Stored as: email = "john@example.com", name = "John"

Mutators run on both create() and update(), before hooks and before cast serialization.

Serialization

Control how model records appear in API responses and Inertia props.

Hiding Fields

Exclude sensitive fields from serialized output:

export class User extends defineModel(users, {
  hidden: ['passwordHash', 'rememberToken'],
}) {}

Like fillable, the option is checked against the table's columns; static hidden = [...] also works.

const user = await User.find(1)
const json = User.serialize(user)
// { id: 1, name: "John", email: "john@example.com" }
// passwordHash and rememberToken are excluded

Fields listed in hidden are also stripped from the record returned by auth.user(), so they never leak into Inertia shared props or API responses that expose the authenticated user. See the Authentication guide for details.

Visible Fields

Use a whitelist instead of a blacklist:

export class User extends defineModel(users, {
  visible: ['id', 'name', 'email'],
}) {}

When visible is set, only those fields appear. visible takes precedence over hidden.

Appending Virtual Attributes

Include accessor-computed values in serialized output:

export class User extends defineModel(users, {
  accessors: {
    fullName: (record) => `${record.firstName} ${record.lastName}`,
  },
  appends: ['fullName'],
  hidden: ['firstName', 'lastName'],
}) {}

appends may only name accessors declared in the same options object: an undeclared name is a compile error.

const json = User.serialize(user)
// { id: 1, fullName: "John Doe", email: "john@example.com" }

Serializing Collections

const users = await User.all()
const json = User.serializeMany(users)

Pagination

Paginate query results and get metadata for building page controls:

const result = await Post.paginate({ page: 1, perPage: 10 })
// result.data  — PostRecord[] for the current page
// result.meta  — { total, perPage, currentPage, totalPages, hasMore, from, to }

Pass pagination data directly to an Inertia page:

import { Controller, paginate, type PaginatedPageProps } from '@guren/core'
import { PostResource, type PostResourceData } from '@/app/Http/Resources/PostResource'
import { pages } from '@/.guren/pages.gen'

type PostsIndexProps = PaginatedPageProps<PostResourceData>

async index() {
  const page = Number(this.query('page', '1'))
  const result = await Post.scope('published').paginate({ page, perPage: 15 })
  const paginator = paginate(result, { path: this.request.path ?? '/posts' })

  return this.inertia(pages.posts.Index, {
    data: result.data.map((post) => new PostResource(post).toJSON()),
    pagination: {
      meta: paginator.meta(),
      links: paginator.links(),
    },
  } satisfies PostsIndexProps)
}