tekir

Database: Getting Started

Configure database connections, register the provider, and run raw or builder-style queries.

Installation

The database package is @tekir/db. It ships with built-in support for SQLite (via Bun's native bun:sqlite), PostgreSQL (via pg), and MySQL (via mysql2). Install the package and any driver you need:

bun add @tekir/db

# SQLite needs no driver install (Bun has bun:sqlite built in)

# For PostgreSQL: install the driver
bun add pg

# For MySQL: install the driver
bun add mysql2

Configuration

Create config/database.ts and export a configuration object. The top-leveldefault key names which connection is used when no connection is specified explicitly. The connections map holds one entry per named connection.

SQLite

config/database.ts
import type { DatabaseConfig } from '@tekir/db'
import env from '#env'

export default {
  default: 'sqlite',
  connections: {
    sqlite: {
      driver: 'sqlite',
      connection: {
        path: env.DB_PATH,  // e.g. './database/app.sqlite'
        wal: true          // WAL mode for better concurrency (default: true)
      }
    }
  }
} satisfies DatabaseConfig

SQLite options include path (file path or :memory:), wal (enable WAL journal mode, highly recommended for concurrent reads), readonly, and strict.

PostgreSQL

config/database.ts
import type { DatabaseConfig } from '@tekir/db'

export default {
  default: 'pg',
  connections: {
    pg: {
      driver: 'postgres',
      connection: {
        host:     env.DB_HOST,
        port:     env.DB_PORT,
        user:     env.DB_USER,
        password: env.DB_PASSWORD,
        database: env.DB_DATABASE,
        // ssl: true connects with TLS and verifies the server certificate.
        // Pass an object for a custom CA, or to opt out of verification.
        ssl: true,
        pool: {
          max:               10,    // maximum pooled connections
          idleTimeout:       30000, // ms an idle connection is kept (pg)
          connectionTimeout: 10000  // ms to wait for a connection before failing
        }
      }
    }
  }
} satisfies DatabaseConfig

You can also pass a single url string (e.g. postgres://user:pass@host:5432/db) instead of individual fields.

When SSL is enabled, tekir verifies the server certificate by default. Passing ssl: true connects with TLS and full verification; to use a custom CA, pass ssl: { ca: env.DB_CA_CERT }. Verification is only turned off when you explicitly set ssl: { rejectUnauthorized: false }, which should be reserved for trusted local development. The optional pool block tunes the connection pool; both PostgreSQL and MySQL ship with safe defaults (max: 10, a 10s connection timeout, and for PostgreSQL a 30s idle timeout) so connections cannot exhaust the server under load. Driver connection errors are also surfaced with credentials masked, so passwords never leak into logs.

MySQL

config/database.ts
import type { DatabaseConfig } from '@tekir/db'

export default {
  default: 'mysql',
  connections: {
    mysql: {
      driver: 'mysql',
      connection: {
        host:     env.DB_HOST,
        port:     env.DB_PORT,
        user:     env.DB_USER,
        password: env.DB_PASSWORD,
        database: env.DB_DATABASE
      }
    }
  }
} satisfies DatabaseConfig

Multi-Connection Support

Define as many named connections as you need. tekir initializes them all at boot and keeps them in a connection pool. Switch connections at any time with db.connection(name).

config/database.ts
import type { DatabaseConfig } from '@tekir/db'

export default {
  default: 'sqlite',
  connections: {
    sqlite: {
      driver: 'sqlite',
      connection: { path: './database/app.sqlite' }
    },
    analytics: {
      driver: 'postgres',
      connection: {
        url: env.ANALYTICS_DB_URL
      }
    },
    readonly: {
      driver: 'sqlite',
      connection: { path: './database/app.sqlite', readonly: true }
    }
  }
} satisfies DatabaseConfig
import { db } from '#services'

// Uses the default connection
const users = await db.query('SELECT * FROM users')

// Switch to a named connection
const stats = await db.connection('analytics').query('SELECT count(*) FROM events')

// Models can also specify a connection
export class AnalyticsEvent extends BaseModel {
  static table = 'events'
  static connection = 'analytics'
  // ...
}

db.connection(name) returns a lightweight proxy with the same API as db, but all operations target the named connection. The original db object is not mutated.

You can also inspect all configured connection names at runtime:

db.connectionNames // ['sqlite', 'analytics', 'readonly']

Registering the Provider

DatabaseProvider reads config/database.ts, creates the Database instance, and binds it to the container as 'db'. Register it in start/kernel.ts:

start/kernel.ts
import type { TekirApp } from '@tekir/core'
import { DatabaseProvider } from '@tekir/db'

export default function({ app }: TekirApp) {
  app.registerAll([
    DatabaseProvider
    // other providers...
  ])
}

After registration, the db singleton (imported from #services) is available everywhere in your application without any additional setup.

// The 'db' export from '#services' is a lazy singleton proxy.
// It resolves the container binding 'db' on first access, which means
// you can safely import it at the top of any file: it will never
// throw "app not initialized" at import time.
import { db } from '#services'

Raw Queries

For quick, low-level access you can run SQL strings directly. All four methods return Promises and work with every driver:

import { db } from '#services'

// query(): returns all matching rows as an array
const users = await db.query<{ id: number; name: string }>('SELECT * FROM users WHERE role = ?', ['admin'])

// queryOne(): returns the first row or null
const user = await db.queryOne<{ id: number; name: string }>('SELECT * FROM users WHERE id = ?', [1])

// run(): executes a statement with bound parameters (INSERT/UPDATE/DELETE)
await db.run('UPDATE users SET role = ? WHERE id = ?', ['admin', 1])

// exec(): executes raw SQL with no parameters (DDL, PRAGMA, etc.)
await db.exec('PRAGMA journal_mode = WAL;')
await db.exec('CREATE TABLE IF NOT EXISTS logs (id INTEGER PRIMARY KEY, message TEXT)')
  • db.query<T>(sql, ...params): returns Promise<T[]>
  • db.queryOne<T>(sql, ...params): returns Promise<T | null>
  • db.run(sql, ...params): returns Promise<void>
  • db.exec(sql): returns Promise<void>, raw SQL with no parameters

Access the underlying Bun Database instance (SQLite) or Pool (PostgreSQL/MySQL) via db.raw when you need driver-specific APIs.

Query Builder

db.from(table) returns a fluent query builder for SELECT, UPDATE, and DELETE operations. Chain conditions and call .all(), .first(), or an aggregate to execute.

import { db } from '#services'

// Select all rows
const users = await db.from('users').all()

// Where conditions
const admins = await db.from('users').where('role', 'admin').all()
const young = await db.from('users').where('age', '<', 30).all()

// Chain multiple conditions (AND)
const rows = await db.from('users')
  .where('role', 'user')
  .where('age', '>', 25)
  .orderBy('name')
  .limit(10)
  .all()

// First row or null
const user = await db.from('users').where('email', '[email protected]').first()

// First row or throw
const user2 = await db.from('users').where('id', 1).firstOrFail()

// Where variants
await db.from('users').whereIn('id', [1, 2, 3]).all()
await db.from('users').whereNull('deleted_at').all()
await db.from('users').whereNotNull('email').all()
await db.from('users').whereBetween('age', [18, 65]).all()
await db.from('users').whereLike('name', '%Ali%').all()
await db.from('posts').whereNot('status', 'draft').all()

// Joins
const posts = await db.from('posts')
  .join('users', 'users.id', '=', 'posts.user_id')
  .select('posts.title', 'users.name')
  .all()

// Aggregates
const count = await db.from('users').count()
const total = await db.from('orders').sum('amount')
const avg = await db.from('users').avg('age')

// Pagination
const page = await db.from('users').orderBy('id').paginate(1, 20)
// page.data: rows
// page.meta: { total, page, perPage, lastPage, hasMore }

// Update / Delete
await db.from('users').where('id', 1).update({ name: 'Updated' })
await db.from('users').where('id', 1).increment('login_count')
await db.from('logs').where('created_at', '<', '2020-01-01').delete()

// Debug
const { sql, params } = db.from('users').where('role', 'admin').toSQL()
const query = db.from('users').where('role', 'admin').toQuery()

Insert Builder

db.table(name) returns an insert builder with upsert support via onConflict().

import { db } from '#services'

// Single insert
await db.table('users')
  .values({ name: 'Ali', email: '[email protected]' })
  .exec()

// Multi insert
await db.table('users')
  .multiInsert([
    { name: 'Ali', email: '[email protected]' },
    { name: 'Veli', email: '[email protected]' }
  ])
  .exec()

// Upsert: insert or update on conflict
await db.table('users')
  .values({ name: 'Ali', email: '[email protected]', role: 'admin' })
  .onConflict('email')
  .merge()
  .exec()

// Insert or ignore on conflict
await db.table('users')
  .values({ name: 'Ali', email: '[email protected]' })
  .onConflict('email')
  .ignore()
  .exec()

Transactions

Wrap multiple statements in a transaction to ensure atomicity. On PostgreSQL, MySQL, and SQLite, transaction() issues a real BEGIN and COMMIT on success; if the callback throws, the transaction is rolled back with ROLLBACK. Every db query, raw statement, and query-builder call made inside the callback runs on the same transaction connection, so they commit or roll back together:

import { db } from '#services'

await db.transaction(async () => {
  await db.run('INSERT INTO accounts (user_id, balance) VALUES (?, ?)', [1, 1000])
  await db.run('INSERT INTO accounts (user_id, balance) VALUES (?, ?)', [2, 500])
})