--- title: Database Package description: "A powerful database abstraction layer providing seamless driver switching between SQLite, Turso, MySQL, PostgreSQL, SingleStore, and Vitess, built on top of bun-query-builder." --- # Database Package A powerful database abstraction layer providing seamless driver switching between SQLite, Turso, MySQL, PostgreSQL, SingleStore, and Vitess, built on top of bun-query-builder. For connection pooling, read replicas, and sharding, see [Scaling the Database](/guide/database-scaling). ## Installation ```bash bun add @stacksjs/database ``` ## Basic Usage ```typescript import { db, Database, createSqliteDatabase } from '@stacksjs/database' // Use the default db instance (configured from environment) const users = await db.selectFrom('users') .where('active', '=', true) .get() // Create a custom database instance const customDb = new Database({ driver: 'postgres', connection: { database: 'myapp', host: 'localhost', port: 5432, username: 'postgres', password: 'secret' } }) ``` ## Configuration ### Environment Variables ```env DB_CONNECTION=sqlite DB_DATABASE=database/stacks.sqlite # For MySQL DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=stacks DB_USERNAME=root DB_PASSWORD= # For PostgreSQL DB_CONNECTION=postgres DB_HOST=127.0.0.1 DB_PORT=5432 DB_DATABASE=stacks DB_USERNAME=postgres DB_PASSWORD=secret # For SingleStore (MySQL wire protocol, port 3306) DB_CONNECTION=singlestore DB_HOST=svc-xxxx.svc.singlestore.com DB_PORT=3306 DB_SSL=true # For Turso / libSQL (SQLite over the network). `libsql://` uses TLS; # a local `turso dev` server is http://127.0.0.1:8080 with no token. DB_CONNECTION=turso TURSO_DATABASE_URL=libsql://app-org.turso.io TURSO_AUTH_TOKEN= # For Vitess. DB_DATABASE is a KEYSPACE, and 15306 is vtgate's port - # 3306 would reach one tablet's MySQL and bypass sharding entirely. DB_CONNECTION=vitess DB_HOST=vtgate.internal DB_PORT=15306 DB_DATABASE=commerce # Connection pool (ignored for sqlite, which is embedded) DB_POOL_MAX=10 DB_POOL_IDLE_TIMEOUT_MS=30000 DB_POOL_ACQUIRE_TIMEOUT_MS=10000 # Read replicas (comma-separated hosts; credentials inherit from the primary) DB_READ_HOSTS=replica-a.internal,replica-b.internal DB_READ_AUTO_ROUTE=false ``` ### Programmatic Configuration ```typescript import { Database, createDatabase } from '@stacksjs/database' // SQLite const sqliteDb = new Database({ driver: 'sqlite', connection: { database: 'database/app.sqlite' } }) // MySQL const mysqlDb = new Database({ driver: 'mysql', connection: { database: 'myapp', host: 'localhost', port: 3306, username: 'root', password: 'secret' } }) // PostgreSQL const postgresDb = new Database({ driver: 'postgres', connection: { database: 'myapp', host: 'localhost', port: 5432, username: 'postgres', password: 'secret' } }) ``` ### Database Options ```typescript const db = new Database({ driver: 'sqlite', connection: { database: 'app.sqlite' }, // Enable verbose logging verbose: true, // Timestamp column names timestamps: { createdAt: 'created_at', updatedAt: 'updated_at', defaultOrderColumn: 'created_at' }, // Soft delete configuration softDeletes: { enabled: true, column: 'deleted_at', defaultFilter: true }, // Model hooks hooks: { beforeCreate: async (data) => { /_ ... _/ }, afterCreate: async (result) => { /_ ... _/ } } }) ``` ## Query Builder ### Select Queries ```typescript // Select all columns const users = await db.selectFrom('users').selectAll().execute() // Select specific columns const users = await db.selectFrom('users') .select(['id', 'name', 'email']) .execute() // Select with alias const users = await db.selectFrom('users') .select(['id', 'name as userName']) .execute() // Distinct const cities = await db.selectFrom('users') .distinct() .select('city') .execute() ``` ### Where Clauses ```typescript // Simple where const users = await db.selectFrom('users') .where('status', '=', 'active') .execute() // Multiple conditions const users = await db.selectFrom('users') .where('status', '=', 'active') .where('role', '=', 'admin') .execute() // Or where const users = await db.selectFrom('users') .where('role', '=', 'admin') .orWhere('role', '=', 'moderator') .execute() // Where in const users = await db.selectFrom('users') .whereIn('id', [1, 2, 3]) .execute() // Where null / not null const unverified = await db.selectFrom('users') .whereNull('email_verified_at') .execute() const verified = await db.selectFrom('users') .whereNotNull('email_verified_at') .execute() // Where between const users = await db.selectFrom('users') .whereBetween('age', 18, 65) .execute() // Where like const users = await db.selectFrom('users') .where('name', 'like', '%john%') .execute() ``` ### Joins ```typescript // Inner join const posts = await db.selectFrom('posts') .innerJoin('users', 'posts.user_id', 'users.id') .select(['posts._', 'users.name as author_name']) .execute() // Left join const posts = await db.selectFrom('posts') .leftJoin('comments', 'posts.id', 'comments.post_id') .selectAll() .execute() // Right join const posts = await db.selectFrom('posts') .rightJoin('users', 'posts.user_id', 'users.id') .selectAll() .execute() ``` ### Ordering and Grouping ```typescript // Order by const users = await db.selectFrom('users') .orderBy('created_at', 'desc') .execute() // Multiple order by const users = await db.selectFrom('users') .orderBy('last_name', 'asc') .orderBy('first_name', 'asc') .execute() // Group by const stats = await db.selectFrom('orders') .select(['status', 'count(_) as total']) .groupBy('status') .execute() // Having const stats = await db.selectFrom('orders') .select(['user_id', 'sum(total) as total_spent']) .groupBy('user_id') .having('total_spent', '>', 1000) .execute() ``` ### Pagination and Limits ```typescript // Limit and offset const users = await db.selectFrom('users') .limit(10) .offset(20) .execute() // Pagination helper const result = await db.selectFrom('users') .paginate(15, 1) // 15 per page, page 1 console.log(result.data) // Array of records console.log(result.total) // Total record count console.log(result.perPage) // Items per page console.log(result.currentPage) // Current page console.log(result.lastPage) // Last page number ``` ### Aggregates ```typescript // Count const count = await db.selectFrom('users') .count() .executeTakeFirst() // Sum const total = await db.selectFrom('orders') .sum('amount') .executeTakeFirst() // Average const avg = await db.selectFrom('products') .avg('price') .executeTakeFirst() // Min / Max const minPrice = await db.selectFrom('products').min('price').executeTakeFirst() const maxPrice = await db.selectFrom('products').max('price').executeTakeFirst() ``` ### Insert Queries ```typescript // Single insert const result = await db.insertInto('users') .values({ name: 'John Doe', email: 'john@example.com' }) .execute() // Insert with returning const [user] = await db.insertInto('users') .values({ name: 'John', email: 'john@test.com' }) .returning('id') .execute() // Bulk insert await db.insertInto('users') .values([ { name: 'User 1', email: 'user1@test.com' }, { name: 'User 2', email: 'user2@test.com' }, { name: 'User 3', email: 'user3@test.com' } ]) .execute() // Insert or ignore await db.insertInto('users') .values({ name: 'John', email: 'john@test.com' }) .onConflict() .doNothing() .execute() // Upsert await db.insertInto('users') .values({ id: 1, name: 'John', email: 'john@test.com' }) .onConflict('id') .doUpdateSet({ name: 'John Updated' }) .execute() ``` ### Update Queries ```typescript // Simple update await db.updateTable('users') .set({ status: 'inactive' }) .where('id', '=', 1) .execute() // Update multiple columns await db.updateTable('users') .set({ name: 'Jane Doe', updated_at: new Date() }) .where('id', '=', 1) .execute() // Increment / Decrement await db.updateTable('products') .set({ stock: sql`stock - 1` }) .where('id', '=', 1) .execute() ``` ### Delete Queries ```typescript // Delete by condition await db.deleteFrom('users') .where('status', '=', 'inactive') .execute() // Delete all (careful!) await db.deleteFrom('temp_logs').execute() // Truncate table await db.truncateTable('sessions').execute() ``` ## Migrations ### Creating Migrations Migrations are **derived from your models**, not hand-written. Describe the schema once in the model, and Stacks diffs it against the database: ```typescript // app/Models/User.ts import { defineModel } from '@stacksjs/orm' import { schema } from '@stacksjs/validation' export default defineModel({ name: 'User', table: 'users', traits: { useTimestamps: true }, attributes: { name: { fillable: true, validation: { rule: schema.string() } }, email: { fillable: true, unique: true, validation: { rule: schema.string() } }, password: { fillable: true, validation: { rule: schema.string() } }, is_active: { fillable: true, default: true }, }, }) ``` ```bash buddy generate:migrations # diff models vs schema, emit SQL into database/migrations/ buddy migrate # apply (--diff to preview the SQL first) buddy migrate:fresh --seed # dev: drop everything, re-migrate, seed ``` There is no `Migration` base class to extend. For a migration the differ cannot derive - a data backfill, say - `buddy make:migration ` scaffolds a plain SQL file. ### Column Types ```typescript table.id() // Auto-incrementing primary key table.uuid('id').primary() // UUID primary key table.string('name', 255) // VARCHAR table.text('description') // TEXT table.integer('age') // INTEGER table.bigInteger('views') // BIGINT table.float('price') // FLOAT table.decimal('amount', 10, 2) // DECIMAL table.boolean('active') // BOOLEAN table.date('birth_date') // DATE table.datetime('published_at') // DATETIME table.timestamp('created_at') // TIMESTAMP table.json('metadata') // JSON table.binary('data') // BLOB/BINARY ``` ### Column Modifiers ```typescript table.string('email') .unique() // Unique constraint .nullable() // Allow null .default('default') // Default value .references('id', 'roles') // Foreign key table.timestamps() // created_at and updated_at table.softDeletes() // deleted_at column ``` ### Running Migrations ```bash # Run all pending migrations buddy migrate # Reset and re-run all migrations buddy migrate:fresh ``` ## Seeding Seed data is declared on the model, not in separate seeder files. The `useSeeder` trait says how many rows to generate, and each attribute's `factory` function says what a value looks like. ### Declaring seed data ```typescript import { defineModel } from '@stacksjs/orm' import { schema } from '@stacksjs/validation' export default defineModel({ name: 'User', table: 'users', traits: { useSeeder: { count: 50, // Optional: pin specific rows over the generated ones, keyed by the // model's camelCase attribute names. fixtures: [ { name: 'Admin', email: 'admin@example.com' }, ], }, }, attributes: { name: { fillable: true, validation: { rule: schema.string().max(255) }, factory: faker => faker.person.fullName(), }, email: { fillable: true, unique: true, validation: { rule: schema.string().email() }, factory: faker => faker.internet.email(), }, }, } as const) ``` A model without a `useSeeder` trait is never seeded. ### Running seeders ```bash buddy seed # every model carrying a useSeeder trait buddy seed --fresh # truncate each table first buddy seed --only User,Post # only these models buddy seed --except User # everything but these buddy seed --include-defaults # the framework's built-in models too buddy migrate:fresh --seed # drop, re-migrate, then seed ``` `buddy db:seed` is an alias for `buddy seed`. Auth and OAuth models are skipped on a non-fresh database, so re-seeding cannot silently invalidate live sessions by re-rolling the Personal Access Client secret. Pass `--allow-protected` when you do want them re-seeded. A foreign key to `User`, `Team` or `Customer` is filled only from the accounts the same run seeded, never from one that was already there and may belong to a real person. `--attach-accounts` uses existing accounts too, for a scratch database whose accounts are fixtures from an earlier run. By default a table that already has rows is left alone. `--fresh` truncates first, which is the usual way to re-roll a development dataset. ## Transactions ```typescript import { db } from '@stacksjs/database' import { transaction } from '@stacksjs/orm' // Using transaction helper await transaction(async (trx) => { await trx.insertInto('orders') .values({ user_id: 1, total: 100 }) .execute() await trx.updateTable('users') .set({ balance: sql`balance - 100` }) .where('id', '=', 1) .execute() // If any query fails, all changes are rolled back }) // Observe successful completion through the callback API await db.transaction(async (trx) => { await trx.insertInto('orders').values({ user_id: 1, total: 100 }).execute() }, { afterCommit: () => { console.info('Order committed') }, }) ``` At outermost transaction completion, Stacks flushes queued after-commit effects before invoking its `afterCommit` observer. Nested effects remain buffered until the outer transaction commits, even after a savepoint has been released. The observer can return a promise, which is awaited. If it fails, its error propagates but the committed writes and flushed effects remain committed; do not retry the transaction in response to that error. Genuine transaction failures still discard queued effects. The ORM transaction helper uses this same completion boundary. See [#2609](https://github.com/stacksjs/stacks/issues/2609). ## Raw Queries ```typescript import { db, sql } from '@stacksjs/database' // Raw select const users = await db.raw('SELECT _ FROM users WHERE status = ?', ['active']) // Raw expression in query const users = await db.selectFrom('users') .select([ 'name', sql`CONCAT(first_name, ' ', last_name) as full_name` ]) .execute() // Raw where condition const users = await db.selectFrom('users') .where(sql`YEAR(created_at) = ${2024}`) .execute() ``` ## Driver Switching ```typescript const database = new Database({ driver: 'sqlite', connection: { database: 'app.sqlite' } }) // Switch to PostgreSQL at runtime database.switchDriver('postgres', { database: 'myapp', host: 'localhost', port: 5432, username: 'postgres', password: 'secret' }) ``` ### Supported drivers | Driver | Wire protocol | Default port | Notes | | --- | --- | --- | --- | | `sqlite` | embedded | - | Single connection, no pooling. | | `turso` | libSQL (Hrana over HTTP) | - | SQLite's SQL on a Turso or `sqld` server. Same migrations as `sqlite`. Alias: `libsql`. | | `mysql` | MySQL | 3306 | | | `postgres` | PostgreSQL | 5432 | | | `singlestore` | MySQL | 3306 | Distributed. No foreign keys. TLS required on managed (Helios) endpoints. | | `vitess` | MySQL | 15306 | MySQL behind vtgate. Unsharded keyspaces retain MySQL relational features; sharded keyspaces require application-generated IDs and a [VSchema](/guide/database-scaling#generating-a-vschema). | `turso` is SQLite behind a network server, so it renders, migrates and introspects exactly as `sqlite` does and uses the same migration corpus - switching between the two needs no regeneration. Only the connection differs. Requests travel over libSQL's Hrana-over-HTTP protocol with no client library: a statement outside a transaction is one request, so concurrent queries run concurrently, and `db.transaction()` holds one server stream for its duration. Integers beyond JavaScript's safe range come back as `bigint`, and a refused token fails with `HTTP 401` and a message that never contains it. Inside `transaction()`, write through the `tx` handle: on SQLite, PostgreSQL and MySQL a model write made from the callback is part of the transaction, but that has not been measured on libSQL yet. Turso and `sqld` refuse `VACUUM`, `ATTACH`, temporary tables and most `PRAGMA`s. The MySQL-wire drivers all render identical SQL - placeholders, backtick quoting, upserts, `LAST_INSERT_ID()`. They differ only in what DDL the engine accepts, which Stacks checks before running a migration rather than discovering halfway through one. ::: warning Migrations are dialect-specific Stacks ships one set of migration files, emitted for a single dialect. Switching `DB_CONNECTION` to a different database means regenerating them: ```bash ./buddy migrate:regenerate mysql ``` `buddy migrate` refuses to replay a mismatched corpus rather than failing partway through it. ::: ## Connection Management ```typescript // Get current driver console.log(database.driver) // 'sqlite' // Check if initialized console.log(database.isInitialized) // true // Close connection database.close() // Reinitialize database.initialize() ``` ## Query Logging Query logging is configuration, not a runtime switch - it has to be on before the first query for the tracker to be installed: ```typescript // config/database.ts export default { queryLogging: { // On in development, opt-in in production - the history is write-heavy. enabled: true, // Milliseconds above which a query is marked slow. slowThreshold: 100, // Days of history to keep, pruned by a scheduled job. retention: 7, // Patterns to skip. `query_logs` is here to avoid logging the log. excludedQueries: ['query_logs'], logHousekeeping: false, // true also records the queue's own polling of jobs, queue_workers, queue_circuit_state }, } ``` Logged queries land in the `query_logs` table and in the dashboard. See [Query Monitoring](/guide/query-monitoring) for the full configuration, including EXPLAIN-plan collection. To route the log somewhere of your own, replace the tracker: ```typescript import { setQueryTracker } from '@stacksjs/database' setQueryTracker(async (event) => { metrics.histogram('db.query.duration', event.queryDurationMillis) }) ``` ## Edge Cases ### Handling Connection Errors ```typescript import { Database } from '@stacksjs/database' try { const db = new Database({ driver: 'postgres', connection: { host: 'invalid-host', database: 'myapp' } }) await db.query.selectFrom('users').execute() } catch (error) { if (error.code === 'ECONNREFUSED') { console.error('Database connection refused') } } ``` ### Handling Large Datasets ```typescript // Use streaming for large datasets const stream = db.selectFrom('logs') .where('created_at', '>', '2024-01-01') .stream() for await (const row of stream) { processRow(row) } ``` ### Concurrent Connections Client-server connections (MySQL, PostgreSQL, SingleStore, Vitess) are pooled. Configure the pool per connection in `config/database.ts`: ```typescript connections: { mysql: { // ... pool: { max: 10, // maximum simultaneous connections min: 2, // idle connections kept warm idleTimeoutMs: 30_000, acquireTimeoutMs: 10_000, maxLifetimeMs: 1_800_000, }, }, }, ``` Or from the environment: ```env DB_POOL_MAX=10 DB_POOL_IDLE_TIMEOUT_MS=30000 DB_POOL_ACQUIRE_TIMEOUT_MS=10000 ``` Omit the `pool` block entirely to use the driver's defaults. SQLite is embedded and single-connection, so a pool block there is ignored. Size the pool against your database's `max_connections`, remembering to multiply by the number of application processes. See [Scaling the Database](/guide/database-scaling#sizing-the-pool). ## API Reference ### Database Class | Method | Description | |--------|-------------| | `query` | Get query builder instance | | `driver` | Get current driver name | | `connection` | Get connection config | | `isInitialized` | Check initialization state | | `initialize()` | Initialize connection | | `switchDriver()` | Switch database driver | | `close()` | Close connection | ### Factory Functions | Function | Description | |----------|-------------| | `createDatabase(options)` | Create database with options | | `createSqliteDatabase(path)` | Create SQLite database | | `createTursoDatabase(url, authToken?)` | Create Turso / libSQL database | | `createPostgresDatabase(config)` | Create PostgreSQL database | | `createMysqlDatabase(config)` | Create MySQL database | | `Database.fromConfig(config)` | Create from Stacks config | | `Database.fromEnv()` | Create from environment | ### Query Builder Methods | Method | Description | |--------|-------------| | `selectFrom(table)` | Start select query | | `insertInto(table)` | Start insert query | | `updateTable(table)` | Start update query | | `deleteFrom(table)` | Start delete query | | `where()` | Add where clause | | `orderBy()` | Add ordering | | `limit()` | Limit results | | `offset()` | Skip results | | `join()` | Add join | | `groupBy()` | Group results | | `having()` | Having clause |