Stacks Database
Key Paths
- Database package:
storage/framework/core/database/src/
- Configuration:
config/database.ts
- QB config:
config/query-builder.ts
- Migrations:
database/migrations/ (96+ migration files, .sql format)
- QB state:
.qb/
- ORM:
storage/framework/orm/
Source Files
database/src/
├── database.ts # Database class + factory functions
├── driver-config.ts # Driver types, defaults, validation, env detection
├── defaults.ts # DB_HOST_DEFAULT, DB_PORTS, DB_NAMES, DB_USERS constants
├── utils.ts # Lazy `db` proxy (main query builder entry point)
├── types.ts # sql template tag, Generated/Insertable/Updateable types
├── sql-helpers.ts # Cross-dialect helpers (now/boolTrue/param/etc.)
├── migrations.ts # runDatabaseMigration, resetDatabase, generateMigrations
├── seeder.ts # seed, seedModel$, freshSeed, listSeedableModels
├── validators.ts # Column type inference from validator types
├── column.ts # Column definition helpers
├── schema.ts # Schema definition helpers
├── table.ts # Table definition helpers
├── query-parser.ts # Query parsing utilities
├── query-logger.ts # Query logging/monitoring
├── auth-tables.ts # Auth-related table migrations (OAuth, passkeys)
├── custom/ # Custom migrations (jobs.ts, errors.ts)
├── drivers/ # sqlite.ts, mysql.ts, postgres.ts, dynamodb.ts
│ └── defaults/ # Default migration helpers (traits.ts, passwords.ts)
└── index.ts # Re-exports everything
Database Class (database.ts)
const db = new Database(options: DatabaseOptions)
db.driver // 'sqlite' | 'mysql' | 'postgres'
db.connection // DatabaseConnectionConfig
db.isInitialized
db.query // QueryBuilder (lazy-initialized via createQueryBuilder())
db.initialize() // Calls setConfig() + createQueryBuilder() from bun-query-builder
db.switchDriver(driver, connection) // Close + reinitialize with new driver
db.close() // Close connection, reset state
// Static factories
Database.fromConfig(config, env?) // From stacks config object
Database.fromEnv() // From env vars (DB_CONNECTION, DB_DATABASE, etc.)
Factory Functions (database.ts)
createDatabase(options: DatabaseOptions): Database
createSqliteDatabase(database: string, options?): Database
createPostgresDatabase(connection: DatabaseConnectionConfig, options?): Database
createMysqlDatabase(connection: DatabaseConnectionConfig, options?): Database
Driver Configuration (driver-config.ts)
detectDriver(): SupportedDialect -- checks DB_CONNECTION env, then DATABASE_URL prefix, defaults to 'sqlite'
validateDriverConfig(driver, config): { valid: boolean, errors: string[] }
mergeWithDefaults(driver, config): Config
getConfigFromEnv(driver): Config -- reads DB_DATABASE, DB_HOST, DB_PORT, DB_USERNAME, DB_PASSWORD, DB_PREFIX, DB_SCHEMA
getConnectionString(driver, config): string -- builds sqlite://, mysql://, postgres:// URL
Global db Instance (utils.ts)
The db export is a lazy Proxy that auto-initializes on first property access:
- At module load: reads env vars via
@stacksjs/env, calls setConfig() on bun-query-builder
- Background: attempts
import('@stacksjs/config') to override with app config
- On first property access: calls
createQueryBuilder() from bun-query-builder
import { db } from '@stacksjs/database'
// db auto-initializes here
const users = await db.selectFrom('users').where('active', '=', true).get()
initializeDbConfig(config) can be called to update the backing config at runtime.
SQL Template Tag (types.ts)
import { sql } from '@stacksjs/database'
const query = sql`SELECT * FROM users WHERE id = ${userId}`
// Returns: { sql: 'SELECT * FROM users WHERE id = ?', parameters: [userId] }
sql.raw('NOW()') // Raw SQL (NOT parameterized) -- returns { raw: 'NOW()' }
sql.ref('users.name') // Column reference -- returns { raw: 'users.name' }
How it works: template values are replaced with ? placeholders and collected into parameters[]. Values wrapped in sql.raw() or sql.ref() are inlined directly into the SQL string.
SQL Dialect Helpers (sql-helpers.ts)
import { sqlHelpers } from '@stacksjs/database'
const h = sqlHelpers('sqlite') // or 'mysql' or 'postgres'
h.isPostgres // false
h.isMysql // false
h.isSqlite // true
h.now // "datetime('now')" for sqlite, 'NOW()' for mysql/postgres
h.boolTrue // '1' for sqlite/mysql, 'true' for postgres
h.boolFalse // '0' for sqlite/mysql, 'false' for postgres
h.autoIncrement // 'INTEGER' for sqlite, 'SERIAL' for postgres
h.primaryKey // 'PRIMARY KEY AUTOINCREMENT' | 'PRIMARY KEY AUTO_INCREMENT' | 'PRIMARY KEY'
h.param(1) // '?' for sqlite/mysql, '$1' for postgres
h.params('a', 'b') // { sql: '?, ?', values: ['a', 'b'] } or { sql: '$1, $2', values: ['a', 'b'] }
Connection Defaults (defaults.ts)
DB_HOST_DEFAULT = '127.0.0.1'
DB_PORTS = { mysql: 3306, postgres: 5432, sqlite: 0 }
DB_NAMES = { default: 'stacks', sqlitePath: 'database/stacks.sqlite', sqliteTestingPath: 'database/stacks_testing.sqlite' }
DB_USERS = { mysql: 'root', postgres: 'postgres', sqlite: '' }
REDIS_DEFAULTS = { host: 'localhost', port: 6379 }
AWS_DEFAULTS = { region: 'us-east-1' }
getConnectionDefaults(driver: string, envProxy?): ConnectionDefaults
DatabaseOptions Type (database.ts)
interface DatabaseOptions {
driver: 'sqlite' | 'mysql' | 'postgres'
connection: { database: string, host?: string, port?: number, username?: string, password?: string, url?: string }
verbose?: boolean
timestamps?: { createdAt?: string, updatedAt?: string, defaultOrderColumn?: string }
softDeletes?: { enabled?: boolean, column?: string, defaultFilter?: boolean }
hooks?: QueryBuilderConfig['hooks']
}
Connection Types (driver-config.ts)
interface SqliteConfig { database: string, prefix?: string }
interface MysqlConfig { name: string, host?: string, port?: number, username?: string, password?: string, prefix?: string, charset?: string, collation?: string }
interface PostgresConfig { name: string, host?: string, port?: number, username?: string, password?: string, prefix?: string, schema?: string, sslMode?: 'disable' | 'require' | 'verify-ca' | 'verify-full' }
interface DynamoDbConfig { key: string, secret: string, region?: string, prefix?: string, endpoint?: string, tableName?: string, singleTable?: { enabled?, pkAttribute?, skAttribute?, entityTypeAttribute?, keyDelimiter?, gsiCount? } }
Migrations (migrations.ts)
Migration Functions
runDatabaseMigration(): Promise<Result<string, Error>> -- ensures DB exists (postgres/mysql), configures QB, preprocesses SQLite migrations, then calls qbExecuteMigration()
resetDatabase(): Promise<Result<string, Error>> -- drops framework tables (OAuth, passkeys, jobs, etc.) then calls qbResetDatabase()
generateMigrations(): Promise<Result<string, Error>> -- compares models to DB state, generates .sql diff files
generateMigrations2(): Promise<Result<string, Error>> -- full regeneration ignoring previous state ({ full: true })
SQLite Migration Preprocessing
Before running migrations on SQLite, preprocessSqliteMigrations():
- Rewrites
ALTER TABLE ADD CONSTRAINT to no-ops (SQLite does not support this)
- Rewrites
CREATE UNIQUE INDEX to no-ops (redundant when table already has inline UNIQUE)
- Filters out
DROP COLUMN for non-existent columns (checks via PRAGMA table_info)
Framework Tables Dropped on Reset
oauth_refresh_tokens, oauth_access_tokens, oauth_clients, passkeys, failed_jobs, jobs, notifications, password_reset_tokens
Seeding (seeder.ts)
Seed Functions
seed(config?: SeederConfig): Promise<SeedSummary> -- loads models from both storage/framework/defaults/app/Models/ (recursive) and app/Models/ (flat), user models override defaults by name
seedModel$(modelName, options?): Promise<SeedResult> -- seed one model by name
freshSeed(config?): Promise<SeedSummary> -- calls seed({ ...config, fresh: true }) (truncates before seeding)
listSeedableModels(): Promise<Array<{ name, table, count, source: 'default' | 'user' }>> -- list without seeding
SeederConfig
interface SeederConfig {
modelsDir?: string // defaults to path.userModelsPath()
defaultCount?: number // default 10
verbose?: boolean // default true
fresh?: boolean // truncate tables first
only?: string[] // specific models to seed
except?: string[] // models to exclude
}
Seeding Behavior
- Models must have
traits.useSeeder (or traits.seedable) set to true or { count: N }
- Attributes with
factory: (faker) => ... generate fake data via @stacksjs/faker
- Password fields are auto-detected (by name pattern or
hidden: true + name includes "pass") and hashed with bcrypt
- Field names are converted from camelCase to snake_case for DB columns
- Records inserted in batches of 100
- Models sorted by dependency: User (0), Team (1), Project (2), everything else (10)
- Missing tables are skipped gracefully
SeedResult / SeedSummary
interface SeedResult { model: string, table: string, count: number, success: boolean, error?: string, duration: number }
interface SeedSummary { total: number, successful: number, failed: number, results: SeedResult[], duration: number }
Validator Type Guards (validators.ts)
isStringValidator, isNumberValidator, enumValidator, isBooleanValidator, isDateValidator, isUnixValidator, isFloatValidator, isDatetimeValidator, isTimestampValidator, isTimestampTzValidator, isDecimalValidator, isSmallintValidator, isIntegerValidator, isBigintValidator, isBinaryValidator, isBlobValidator, isJsonValidator
checkValidator(validator, driver): string -- converts validator type to SQL column type string (e.g., 'integer', 'text', 'varchar(255)')
- SQLite uses
'text' for all strings, 'integer' for numbers; MySQL uses 'varchar(N)', native enum()
DynamoDB Support (drivers/dynamodb.ts)
Entity-centric API for single-table design:
createDynamo(config), dynamo (default instance)
EntityQueryBuilder -- query builder for DynamoDB entities
generateKeyPattern, parseKeyPattern, buildKey -- key pattern utilities
marshall, unmarshall -- DynamoDB data type conversion
Re-exports from bun-query-builder
createQueryBuilder, setConfig -- core QB functions
QueryBuilder, QueryBuilderConfig, Seeder, SupportedDialect -- types
Compatibility Type Aliases (types.ts)
Generated<T>, GeneratedAlways<T> -- column generation markers (both alias to T)
Insertable<T>, Selectable<T>, Updateable<T> -- CRUD type utilities
RawBuilder<T>, Sql -- raw SQL expression types
CLI Commands
buddy migrate -- run pending migrations
buddy migrate:fresh -- drop all + re-migrate (add --seed to also seed)
buddy make:migration <name> -- create migration file
buddy seed -- seed database
buddy generate:migrations -- generate migration diffs from models
config/database.ts Shape
{
default: env.DB_CONNECTION || 'mysql',
connections: { sqlite, mysql, postgres, dynamodb },
migrations: 'migrations',
migrationLocks: 'migration_locks',
queryLogging: {
enabled: true,
slowThreshold: 100, // ms
retention: 7, // days
pruneFrequency: 24, // hours
excludedQueries: ['query_logs'],
analysis: { enabled: true, analyzeAll: false, explainPlan: true, suggestions: true }
}
}
config/query-builder.ts (Query Builder Config)
{
verbose: true,
dialect: env.DB_CONNECTION || 'sqlite',
database: { database, username?, password?, host?, port? },
timestamps: { createdAt: 'created_at', updatedAt: 'updated_at', defaultOrderColumn: 'created_at' },
pagination: { defaultPerPage: 25, cursorColumn: 'id' },
aliasing: { relationColumnAliasFormat: 'table_column' },
relations: { foreignKeyFormat: 'singularParent_id', maxDepth: 10, maxEagerLoad: 50, detectCycles: true },
transactionDefaults: { retries: 2, isolation: 'read committed', sqlStates: ['40001', '40P01'], backoff: { baseMs: 50, factor: 2, maxMs: 2000, jitter: true } },
sql: { randomFunction: 'RANDOM()', sharedLockSyntax: 'FOR SHARE', jsonContainsMode: 'operator' },
features: { distinctOn: true },
debug: { captureText: true },
softDeletes: { enabled: false, column: 'deleted_at', defaultFilter: true }
}
Gotchas
- The actual default driver in
config/database.ts is 'mysql' (not 'sqlite'), but utils.ts and driver-config.ts fall back to 'sqlite' when DB_CONNECTION is unset
- The
db export is a lazy Proxy -- it auto-initializes on first property access, which means errors are deferred until first use
- Query builder config lives in
config/query-builder.ts, and reads DB_CONNECTION / DB_* from the env - it is not a second copy of config/database.ts
- The
.qb/ directory at project root stores query builder state for migration diffing
resetDatabase() drops ALL tables including framework tables (OAuth, passkeys, jobs, etc.) -- only use in development
freshSeed() truncates tables before seeding using deleteFrom() (not DROP TABLE)
- SQLite migration preprocessing mutates
.sql files in-place (rewrites them to no-ops)
- The
ensureDatabaseExists() function connects to admin DB (postgres or mysql) to run CREATE DATABASE before switching to the target DB
Database.fromConfig() appends _testing to database name/path when env === 'testing'
- DynamoDB support uses a separate entity-centric API, not the standard query builder
- Soft deletes are disabled by default in qb.ts config (
enabled: false)
- Keep the process-wide raw query-builder soft-delete filter disabled. Raw
db.selectFrom() calls do not carry a model definition, so they cannot know
whether a table has useSoftDeletes or a deleted_at column. Model queries
and generated useApi routes apply the trait-aware scope themselves.
- Transaction defaults: 2 retries,
read committed isolation, with exponential backoff + jitter
- The ORM lives in TWO locations:
storage/framework/core/orm/ (package) and storage/framework/orm/ (implementation)
1---2name: stacks-database-43description: Use when working with databases in a Stacks application — configuring connections, running queries, migrations, seeding, SQL helpers, or using SQLite/MySQL/PostgreSQL/DynamoDB. Covers @stacksjs/database, bun-query-builder, config/database.ts, and the database/ migrations directory.4license: MIT5compatibility: Bun >= 1.3.0, TypeScript, SQLite >= 3.47.26allowed-tools: Read Edit Write Bash Grep Glob7metadata:8 source: https://skillmd.com/skills/stacksjs/stacks-database-49---1011# Stacks Database1213## Key Paths14- Database package: `storage/framework/core/database/src/`15- Configuration: `config/database.ts`16- QB config: `config/query-builder.ts`17- Migrations: `database/migrations/` (96+ migration files, `.sql` format)18- QB state: `.qb/`19- ORM: `storage/framework/orm/`2021## Source Files22```23database/src/24├── database.ts # Database class + factory functions25├── driver-config.ts # Driver types, defaults, validation, env detection26├── defaults.ts # DB_HOST_DEFAULT, DB_PORTS, DB_NAMES, DB_USERS constants27├── utils.ts # Lazy `db` proxy (main query builder entry point)28├── types.ts # sql template tag, Generated/Insertable/Updateable types29├── sql-helpers.ts # Cross-dialect helpers (now/boolTrue/param/etc.)30├── migrations.ts # runDatabaseMigration, resetDatabase, generateMigrations31├── seeder.ts # seed, seedModel$, freshSeed, listSeedableModels32├── validators.ts # Column type inference from validator types33├── column.ts # Column definition helpers34├── schema.ts # Schema definition helpers35├── table.ts # Table definition helpers36├── query-parser.ts # Query parsing utilities37├── query-logger.ts # Query logging/monitoring38├── auth-tables.ts # Auth-related table migrations (OAuth, passkeys)39├── custom/ # Custom migrations (jobs.ts, errors.ts)40├── drivers/ # sqlite.ts, mysql.ts, postgres.ts, dynamodb.ts41│ └── defaults/ # Default migration helpers (traits.ts, passwords.ts)42└── index.ts # Re-exports everything43```4445## Database Class (database.ts)4647```typescript48const db = new Database(options: DatabaseOptions)49db.driver // 'sqlite' | 'mysql' | 'postgres'50db.connection // DatabaseConnectionConfig51db.isInitialized52db.query // QueryBuilder (lazy-initialized via createQueryBuilder())53db.initialize() // Calls setConfig() + createQueryBuilder() from bun-query-builder54db.switchDriver(driver, connection) // Close + reinitialize with new driver55db.close() // Close connection, reset state5657// Static factories58Database.fromConfig(config, env?) // From stacks config object59Database.fromEnv() // From env vars (DB_CONNECTION, DB_DATABASE, etc.)60```6162## Factory Functions (database.ts)63- `createDatabase(options: DatabaseOptions): Database`64- `createSqliteDatabase(database: string, options?): Database`65- `createPostgresDatabase(connection: DatabaseConnectionConfig, options?): Database`66- `createMysqlDatabase(connection: DatabaseConnectionConfig, options?): Database`6768## Driver Configuration (driver-config.ts)69- `detectDriver(): SupportedDialect` -- checks `DB_CONNECTION` env, then `DATABASE_URL` prefix, defaults to `'sqlite'`70- `validateDriverConfig(driver, config): { valid: boolean, errors: string[] }`71- `mergeWithDefaults(driver, config): Config`72- `getConfigFromEnv(driver): Config` -- reads `DB_DATABASE`, `DB_HOST`, `DB_PORT`, `DB_USERNAME`, `DB_PASSWORD`, `DB_PREFIX`, `DB_SCHEMA`73- `getConnectionString(driver, config): string` -- builds `sqlite://`, `mysql://`, `postgres://` URL7475## Global `db` Instance (utils.ts)7677The `db` export is a **lazy Proxy** that auto-initializes on first property access:781. At module load: reads env vars via `@stacksjs/env`, calls `setConfig()` on bun-query-builder792. Background: attempts `import('@stacksjs/config')` to override with app config803. On first property access: calls `createQueryBuilder()` from bun-query-builder8182```typescript83import { db } from '@stacksjs/database'8485// db auto-initializes here86const users = await db.selectFrom('users').where('active', '=', true).get()87```8889`initializeDbConfig(config)` can be called to update the backing config at runtime.9091## SQL Template Tag (types.ts)9293```typescript94import { sql } from '@stacksjs/database'9596const query = sql`SELECT * FROM users WHERE id = ${userId}`97// Returns: { sql: 'SELECT * FROM users WHERE id = ?', parameters: [userId] }9899sql.raw('NOW()') // Raw SQL (NOT parameterized) -- returns { raw: 'NOW()' }100sql.ref('users.name') // Column reference -- returns { raw: 'users.name' }101```102103How it works: template values are replaced with `?` placeholders and collected into `parameters[]`. Values wrapped in `sql.raw()` or `sql.ref()` are inlined directly into the SQL string.104105## SQL Dialect Helpers (sql-helpers.ts)106107```typescript108import { sqlHelpers } from '@stacksjs/database'109110const h = sqlHelpers('sqlite') // or 'mysql' or 'postgres'111h.isPostgres // false112h.isMysql // false113h.isSqlite // true114h.now // "datetime('now')" for sqlite, 'NOW()' for mysql/postgres115h.boolTrue // '1' for sqlite/mysql, 'true' for postgres116h.boolFalse // '0' for sqlite/mysql, 'false' for postgres117h.autoIncrement // 'INTEGER' for sqlite, 'SERIAL' for postgres118h.primaryKey // 'PRIMARY KEY AUTOINCREMENT' | 'PRIMARY KEY AUTO_INCREMENT' | 'PRIMARY KEY'119h.param(1) // '?' for sqlite/mysql, '$1' for postgres120h.params('a', 'b') // { sql: '?, ?', values: ['a', 'b'] } or { sql: '$1, $2', values: ['a', 'b'] }121```122123## Connection Defaults (defaults.ts)124125```typescript126DB_HOST_DEFAULT = '127.0.0.1'127DB_PORTS = { mysql: 3306, postgres: 5432, sqlite: 0 }128DB_NAMES = { default: 'stacks', sqlitePath: 'database/stacks.sqlite', sqliteTestingPath: 'database/stacks_testing.sqlite' }129DB_USERS = { mysql: 'root', postgres: 'postgres', sqlite: '' }130REDIS_DEFAULTS = { host: 'localhost', port: 6379 }131AWS_DEFAULTS = { region: 'us-east-1' }132133getConnectionDefaults(driver: string, envProxy?): ConnectionDefaults134```135136## DatabaseOptions Type (database.ts)137138```typescript139interface DatabaseOptions {140 driver: 'sqlite' | 'mysql' | 'postgres'141 connection: { database: string, host?: string, port?: number, username?: string, password?: string, url?: string }142 verbose?: boolean143 timestamps?: { createdAt?: string, updatedAt?: string, defaultOrderColumn?: string }144 softDeletes?: { enabled?: boolean, column?: string, defaultFilter?: boolean }145 hooks?: QueryBuilderConfig['hooks']146}147```148149## Connection Types (driver-config.ts)150151```typescript152interface SqliteConfig { database: string, prefix?: string }153interface MysqlConfig { name: string, host?: string, port?: number, username?: string, password?: string, prefix?: string, charset?: string, collation?: string }154interface PostgresConfig { name: string, host?: string, port?: number, username?: string, password?: string, prefix?: string, schema?: string, sslMode?: 'disable' | 'require' | 'verify-ca' | 'verify-full' }155interface DynamoDbConfig { key: string, secret: string, region?: string, prefix?: string, endpoint?: string, tableName?: string, singleTable?: { enabled?, pkAttribute?, skAttribute?, entityTypeAttribute?, keyDelimiter?, gsiCount? } }156```157158## Migrations (migrations.ts)159160### Migration Functions161- `runDatabaseMigration(): Promise<Result<string, Error>>` -- ensures DB exists (postgres/mysql), configures QB, preprocesses SQLite migrations, then calls `qbExecuteMigration()`162- `resetDatabase(): Promise<Result<string, Error>>` -- drops framework tables (OAuth, passkeys, jobs, etc.) then calls `qbResetDatabase()`163- `generateMigrations(): Promise<Result<string, Error>>` -- compares models to DB state, generates `.sql` diff files164- `generateMigrations2(): Promise<Result<string, Error>>` -- full regeneration ignoring previous state (`{ full: true }`)165166### SQLite Migration Preprocessing167Before running migrations on SQLite, `preprocessSqliteMigrations()`:1681. Rewrites `ALTER TABLE ADD CONSTRAINT` to no-ops (SQLite does not support this)1692. Rewrites `CREATE UNIQUE INDEX` to no-ops (redundant when table already has inline UNIQUE)1703. Filters out `DROP COLUMN` for non-existent columns (checks via `PRAGMA table_info`)171172### Framework Tables Dropped on Reset173`oauth_refresh_tokens`, `oauth_access_tokens`, `oauth_clients`, `passkeys`, `failed_jobs`, `jobs`, `notifications`, `password_reset_tokens`174175## Seeding (seeder.ts)176177### Seed Functions178- `seed(config?: SeederConfig): Promise<SeedSummary>` -- loads models from both `storage/framework/defaults/app/Models/` (recursive) and `app/Models/` (flat), user models override defaults by name179- `seedModel$(modelName, options?): Promise<SeedResult>` -- seed one model by name180- `freshSeed(config?): Promise<SeedSummary>` -- calls `seed({ ...config, fresh: true })` (truncates before seeding)181- `listSeedableModels(): Promise<Array<{ name, table, count, source: 'default' | 'user' }>>` -- list without seeding182183### SeederConfig184```typescript185interface SeederConfig {186 modelsDir?: string // defaults to path.userModelsPath()187 defaultCount?: number // default 10188 verbose?: boolean // default true189 fresh?: boolean // truncate tables first190 only?: string[] // specific models to seed191 except?: string[] // models to exclude192}193```194195### Seeding Behavior196- Models must have `traits.useSeeder` (or `traits.seedable`) set to `true` or `{ count: N }`197- Attributes with `factory: (faker) => ...` generate fake data via `@stacksjs/faker`198- Password fields are auto-detected (by name pattern or `hidden: true` + name includes "pass") and hashed with bcrypt199- Field names are converted from camelCase to snake_case for DB columns200- Records inserted in batches of 100201- Models sorted by dependency: User (0), Team (1), Project (2), everything else (10)202- Missing tables are skipped gracefully203204### SeedResult / SeedSummary205```typescript206interface SeedResult { model: string, table: string, count: number, success: boolean, error?: string, duration: number }207interface SeedSummary { total: number, successful: number, failed: number, results: SeedResult[], duration: number }208```209210## Validator Type Guards (validators.ts)211`isStringValidator`, `isNumberValidator`, `enumValidator`, `isBooleanValidator`, `isDateValidator`, `isUnixValidator`, `isFloatValidator`, `isDatetimeValidator`, `isTimestampValidator`, `isTimestampTzValidator`, `isDecimalValidator`, `isSmallintValidator`, `isIntegerValidator`, `isBigintValidator`, `isBinaryValidator`, `isBlobValidator`, `isJsonValidator`212213- `checkValidator(validator, driver): string` -- converts validator type to SQL column type string (e.g., `'integer'`, `'text'`, `'varchar(255)'`)214- SQLite uses `'text'` for all strings, `'integer'` for numbers; MySQL uses `'varchar(N)'`, native `enum()`215216## DynamoDB Support (drivers/dynamodb.ts)217Entity-centric API for single-table design:218- `createDynamo(config)`, `dynamo` (default instance)219- `EntityQueryBuilder` -- query builder for DynamoDB entities220- `generateKeyPattern`, `parseKeyPattern`, `buildKey` -- key pattern utilities221- `marshall`, `unmarshall` -- DynamoDB data type conversion222223## Re-exports from bun-query-builder224- `createQueryBuilder`, `setConfig` -- core QB functions225- `QueryBuilder`, `QueryBuilderConfig`, `Seeder`, `SupportedDialect` -- types226227## Compatibility Type Aliases (types.ts)228- `Generated<T>`, `GeneratedAlways<T>` -- column generation markers (both alias to `T`)229- `Insertable<T>`, `Selectable<T>`, `Updateable<T>` -- CRUD type utilities230- `RawBuilder<T>`, `Sql` -- raw SQL expression types231232## CLI Commands233- `buddy migrate` -- run pending migrations234- `buddy migrate:fresh` -- drop all + re-migrate (add `--seed` to also seed)235- `buddy make:migration <name>` -- create migration file236- `buddy seed` -- seed database237- `buddy generate:migrations` -- generate migration diffs from models238239## config/database.ts Shape240```typescript241{242 default: env.DB_CONNECTION || 'mysql',243 connections: { sqlite, mysql, postgres, dynamodb },244 migrations: 'migrations',245 migrationLocks: 'migration_locks',246 queryLogging: {247 enabled: true,248 slowThreshold: 100, // ms249 retention: 7, // days250 pruneFrequency: 24, // hours251 excludedQueries: ['query_logs'],252 analysis: { enabled: true, analyzeAll: false, explainPlan: true, suggestions: true }253 }254}255```256257## config/query-builder.ts (Query Builder Config)258```typescript259{260 verbose: true,261 dialect: env.DB_CONNECTION || 'sqlite',262 database: { database, username?, password?, host?, port? },263 timestamps: { createdAt: 'created_at', updatedAt: 'updated_at', defaultOrderColumn: 'created_at' },264 pagination: { defaultPerPage: 25, cursorColumn: 'id' },265 aliasing: { relationColumnAliasFormat: 'table_column' },266 relations: { foreignKeyFormat: 'singularParent_id', maxDepth: 10, maxEagerLoad: 50, detectCycles: true },267 transactionDefaults: { retries: 2, isolation: 'read committed', sqlStates: ['40001', '40P01'], backoff: { baseMs: 50, factor: 2, maxMs: 2000, jitter: true } },268 sql: { randomFunction: 'RANDOM()', sharedLockSyntax: 'FOR SHARE', jsonContainsMode: 'operator' },269 features: { distinctOn: true },270 debug: { captureText: true },271 softDeletes: { enabled: false, column: 'deleted_at', defaultFilter: true }272}273```274275## Gotchas276- The actual default driver in `config/database.ts` is `'mysql'` (not `'sqlite'`), but `utils.ts` and `driver-config.ts` fall back to `'sqlite'` when `DB_CONNECTION` is unset277- The `db` export is a lazy Proxy -- it auto-initializes on first property access, which means errors are deferred until first use278- Query builder config lives in `config/query-builder.ts`, and reads `DB_CONNECTION` / `DB_*` from the env - it is not a second copy of `config/database.ts`279- The `.qb/` directory at project root stores query builder state for migration diffing280- `resetDatabase()` drops ALL tables including framework tables (OAuth, passkeys, jobs, etc.) -- only use in development281- `freshSeed()` truncates tables before seeding using `deleteFrom()` (not `DROP TABLE`)282- SQLite migration preprocessing mutates `.sql` files in-place (rewrites them to no-ops)283- The `ensureDatabaseExists()` function connects to admin DB (`postgres` or `mysql`) to run `CREATE DATABASE` before switching to the target DB284- `Database.fromConfig()` appends `_testing` to database name/path when `env === 'testing'`285- DynamoDB support uses a separate entity-centric API, not the standard query builder286- Soft deletes are disabled by default in qb.ts config (`enabled: false`)287- Keep the process-wide raw query-builder soft-delete filter disabled. Raw288 `db.selectFrom()` calls do not carry a model definition, so they cannot know289 whether a table has `useSoftDeletes` or a `deleted_at` column. Model queries290 and generated `useApi` routes apply the trait-aware scope themselves.291- Transaction defaults: 2 retries, `read committed` isolation, with exponential backoff + jitter292- The ORM lives in TWO locations: `storage/framework/core/orm/` (package) and `storage/framework/orm/` (implementation)