stacks-database

# Stacks Database ## Key Paths - Database package: `storage/framework/core/database/src/` - Configuration: `config/database.ts` - QB config: `config/query-builder.ts` - Migrations: `database/migrations/` (237 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) ```typescript 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` - `createTursoDatabase(url: string, authToken?: 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 runtime facade. It initializes the query builder on use and resolves application configuration through ensureDatabaseConfigLoaded. Use @stacksjs/database/runtime for the narrow query/transaction entry; the package root additionally exports migrations, seeders and driver tooling. ```typescript 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. For reads which cannot tolerate replication lag, use `db.primary.selectFrom(...)`. It stays on the primary when automatic replica routing is enabled, and uses the active transaction connection inside a transaction. It does not mark the request as a writer or change routing for unrelated reads. Session authentication uses this handle so a revoked session cannot authenticate from a stale replica. ## SQL Template Tag (types.ts) ```typescript 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) ```typescript 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) ```typescript 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) ```typescript 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) ```typescript interface SqliteConfig { database: string, prefix?: string } interface TursoConfig { url: string, authToken?: 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? } } ``` ## Model-driven migrations Change the model, generate migrations, review the resulting SQL, then migrate. The native runner loads application config, selects the dialect corpus, handles feature-gated migration resources, and acquires the migration lock. It returns a Result; inspect failures rather than treating a resolved call as success. RunDatabaseMigration, generateMigrations and resetDatabase are exported tooling, but their CLI actions supply the application's operation policy and environment. Read stacks-migrations before using full regeneration, snapshot recovery, preprocessing or destructive fresh/reset commands. SQLite preprocessing and provider-specific DDL rules are implemented in core/database/src/migrations.ts; installed package sources are copied before their SQL is rewritten. The migration ledger and snapshot are different state, not interchangeable proof that a table exists. ## Model factories and application seeders Models opt into the factory pass with traits.useSeeder/seedable. Attribute factories receive faker and the attributes generated so far; column names are converted to snake_case, and password fields are hashed by the seeder's bcrypt path. The source defines factory fallback behavior: a failed factory can use its attribute default, so inspect the summary and warnings as well as row count. seed(options?) returns a SeedSummary; seedModel$(name, options?) targets one model; freshSeed truncates before generation; listSeedableModels inspects the available definitions; makeModelRecords produces factory data without insertion. These are public exports from @stacksjs/database. When app/Models has user models, the default seed pass uses those definitions. includeDefaults explicitly merges framework defaults; an app with no user models uses defaults as fallback. User identity overrides a default even when the app model opts out of seeding. The current model-seeder app scan is flat, unlike recursive model resolution elsewhere; verify nested models at this seam. Options also include only/except, defaultCount, fresh, append, allowProtected, attachAccounts and includeDefaults. Default non-empty tables are skipped; append is explicit. Protected auth/OAuth data and attachment to existing accounts have their own guards because fixture generation can alter live credentials or associate invented records with real people. Read buddy seed --help and inspect the target connection before requesting a destructive or bypassing mode. Application seeders default-export a class extending the native Seeder: ~~~ts import { Seeder } from '@stacksjs/database' export default class WorkspaceSeeder extends Seeder { static override order = 10 static override tags = ['deploy'] async run(): Promise<void> { // Find the application's existing workspace before creating missing state. } } ~~~ runApplicationSeeders applies order (lower first), then relative path for ties. Tag/only/except selection lets deploy bootstrap remain separate from a large demo corpus. Buddy seed and migrate:fresh --seed run the model and application passes; model dependencies determine parent-first generation rather than a hard-coded User/Team/Project order. fresh empties in reverse dependency order. Source: storage/framework/core/database/src/seeder.ts and core/buddy/src/commands/seed.ts. Retained seeder tests cover override opt-out, dependency order, protected models and non-empty/append behavior. See stacks-models for fixtures/factory declarations and stacks-testing for factories that produce test records. ## 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 ## Turso / libSQL (`DB_CONNECTION=turso`, alias `libsql`) SQLite's SQL on a Turso or `sqld` server. Configure `TURSO_DATABASE_URL` (`libsql://<db>-<org>.turso.io`, or `http://127.0.0.1:8080` for `turso dev`) and `TURSO_AUTH_TOKEN` (a secret, never logged). - Everything that renders SQL or DDL treats it as `sqlite`: `dialectCapabilities('turso').wire === 'sqlite'`, `getDatabaseDialect()` returns `'sqlite'`, and it shares the SQLite migration corpus and `model-snapshot.sqlite.json`. Switching between `sqlite` and `turso` needs no regeneration. - The transport is bun-query-builder's: `setConfig({ dialect: 'sqlite', database: { url, authToken } })` with a libSQL URL selects its dependency-free Hrana-over-HTTP connection (`createLibSQLSQL`). - Code that opens the database FILE must ask `isLibsqlDriver(driver)` first - there is none. The SQLite migration preprocessor reads an in-memory replica of the server's schema instead (`loadLibsqlSchemaMirror` in migrations.ts), and the migration lock is a row in `stacks_migration_lock` (`acquireLibsqlMigrationLock`), since migrating hosts share no filesystem. - libSQL rejects SQLite's double-quoted-string fallback, so a statement naming a missing column fails instead of storing the column name as text. `tests/corpus-rebuild-sources.test.ts` guards the corpus. - Inside `transaction()`, write through the `tx` handle; model writes use their own connection. ## config/database.ts Shape ```typescript { default: env.DB_CONNECTION || 'sqlite', connections: { sqlite, turso, mysql, singlestore, vitess, postgres, dynamodb }, migrations: 'migrations', migrationLocks: 'migration_locks', queryLogging: { // Defaults on outside production and off in production. Production also // skips query hooks unless persistent history is explicitly enabled, // except one onQueryError hook on PostgreSQL and MySQL that reports a // pool broken by oven-sh/bun#42804. enabled: env.DB_QUERY_LOGGING_ENABLED ?? !['production', 'prod'].includes(env.APP_ENV || ''), captureAllTraces: false, // slow and failed queries always keep traces // Bound values in query_logs.bindings, credentials stored as `<redacted>` // (by name and shape, so not every secret); production keeps only each // value's type unless this is enabled. Env takes true/false, 1/0, yes/no, on/off.