Database

Pearl uses Drizzle ORM under the hood, wrapped in a DatabaseManager that lives in Pearl's IoC container. Define tables as TypeScript, query with full type safety and autocomplete, and manage schema changes with Drizzle Kit migrations.

Supported databases

Database	Driver package	Config value
PostgreSQL	`pg`	`driver: 'postgres'`
MySQL	`mysql2`	`driver: 'mysql'`
SQLite	`better-sqlite3`	`driver: 'sqlite'`

Setup

Register DatabaseManager in your service provider. In register() you define the connection config; in boot() you open the connection (it's async, so it has to happen there). Set runMigrationsOnBoot: true to apply pending migrations automatically on startup.

src/providers/AppServiceProvider.ts

import { ServiceProvider, DatabaseManager } from '@pearl-framework/pearl'
import { DrizzleAdapter } from '@pearl-framework/database'

export class AppServiceProvider extends ServiceProvider {
  register(): void {
    this.container.singleton(DatabaseManager, () =>
      new DatabaseManager(new DrizzleAdapter({
        driver:   'postgres',
        host:     process.env.DB_HOST     ?? 'localhost',
        port:     Number(process.env.DB_PORT ?? 5432),
        user:     process.env.DB_USER     ?? 'postgres',
        password: process.env.DB_PASSWORD ?? '',
        database: process.env.DB_NAME     ?? 'my_api',
        migrationsFolder:    './database/migrations',
        runMigrationsOnBoot: true,  // apply pending migrations on startup
      }))
    )
  }

  override async boot(): Promise<void> {
    await this.container.make(DatabaseManager).connect()
  }
}

Defining a schema

Table definitions live in src/schema/. All column helpers are re-exported from @pearl-framework/pearl — no need to import from drizzle-orm directly.

src/schema/users.ts

import { pgTable, serial, varchar, text, boolean, timestamp } from '@pearl-framework/pearl'

export const users = pgTable('users', {
  id:        serial('id').primaryKey(),
  name:      varchar('name',  { length: 255 }).notNull(),
  email:     varchar('email', { length: 255 }).notNull().unique(),
  password:  text('password').notNull(),
  active:    boolean('active').default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

src/schema/posts.ts

import { pgTable, serial, text, integer, boolean, timestamp } from '@pearl-framework/pearl'
import { users } from './users.js'

export const posts = pgTable('posts', {
  id:        serial('id').primaryKey(),
  title:     text('title').notNull(),
  content:   text('content').notNull(),
  published: boolean('published').notNull().default(false),
  userId:    integer('user_id').references(() => users.id).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

Querying

Inject DatabaseManager into your controller and access the Drizzle instance via db.db. All common Drizzle query operators are re-exported from @pearl-framework/pearl.

···

import { eq, and, desc, like } from '@pearl-framework/pearl'

// Select all rows
const allPosts = await db.db.select().from(posts)

// Select with filter + sort + limit
const recent = await db.db
  .select()
  .from(posts)
  .where(and(eq(posts.published, true), like(posts.title, '%Pearl%')))
  .orderBy(desc(posts.createdAt))
  .limit(10)

// Insert and return the new row
const [post] = await db.db
  .insert(posts)
  .values({ title: 'Hello', content: 'World', userId: 1 })
  .returning()

// Update
await db.db
  .update(posts)
  .set({ title: 'Updated' })
  .where(eq(posts.id, 1))

// Delete
await db.db.delete(posts).where(eq(posts.id, 1))

Migrations

Pearl uses Drizzle Kit for migrations. Generate a migration from your schema changes, then apply it:

···

# Generate a migration from schema changes
npx drizzle-kit generate --schema=./src/schema

# Apply pending migrations
npx drizzle-kit migrate
# or via pearl CLI:
pearl migrate

Migrations also run automatically on app.boot() when runMigrationsOnBoot: true is set in your config.

Re-exported Drizzle helpers

These are all available directly from @pearl-framework/pearl:

···

import {
  // Column types (PostgreSQL)
  pgTable, serial, varchar, text, boolean,
  integer, bigserial, timestamp, date, jsonb, uuid,

  // Query operators
  eq, ne, gt, gte, lt, lte,
  and, or, not,
  isNull, isNotNull,
  inArray, notInArray,
  like, ilike,

  // Utilities
  sql, count, asc, desc,
} from '@pearl-framework/pearl'