Database (Prisma)

Harpia utilizes Prisma as its ORM, leveraging a driver adapter approach. This allows the Prisma Client to connect to your database via lightweight, native adapters, resulting in significantly faster cold starts and seamless compatibility with the Bun runtime.

Configuration

The database is configured during project scaffolding. The schema.prisma file lives at the root prisma/ directory, and the generated client is output directly into app/database/prisma/.

my-project/
│
├── prisma/
│   └── schema.prisma         # Your database schema and models
├── prisma.config.ts          # Prisma runtime configuration
├── app/
│   └── database/
│       ├── index.ts           # Prisma Client instantiation, exports, and Database utility
│       ├── observer.ts        # Prisma event observer
│       ├── factories/         # Test data factories
│       └── seeds/             # Database seed scripts

prisma.config.ts

Harpia uses a prisma.config.ts file at the project root to configure Prisma’s runtime behaviour. This file is required when using driver adapters with the Bun runtime:

import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
    seed: "bun run app/database/seeds/index.ts",
  },
  datasource: {
    url: env("DB_URL"),
  },
});

The prisma/schema.prisma defines the generator output so that the client is always available inside the application:

datasource db {
  provider = "postgresql" // sqlite, mysql, etc.
}

generator client {
  provider = "prisma-client"
  output   = "../app/database/prisma"
}

The database connection string is defined in your .env file using the DB_URL variable:

# PostgreSQL / MySQL / MariaDB
# DB_URL=${DB_PROVIDER}://${DB_USER}:${DB_PASS}@${DB_HOST}:${DB_PORT}/${DB_NAME}

# SQLite (for local development)
# DB_URL="file:./prisma/dev.db"

The Prisma Client

Harpia uses driver adapters for each supported database. The app/database/index.ts file initialises the Prisma Client with the correct adapter based on your database choice and wires it through the Observer for lifecycle hooks.

PostgreSQL

// app/database/index.ts
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "./prisma/client";
import { Observer } from "./observer";

const connectionString = `${process.env.DB_URL}`;
const adapter = new PrismaPg({ connectionString });
const client = new PrismaClient({ adapter });

export const observer = new Observer<typeof client>(client);
export const prisma = observer.prisma;

// Export type
export type {
  User as UserType,
} from "./prisma/client";

// Export client
export const {
  user: User,
} = prisma;

MySQL / MariaDB

// app/database/index.ts
import { PrismaMariaDb } from "@prisma/adapter-mariadb";
import { PrismaClient } from "./prisma/client";
import { Observer } from "./observer";

const adapter = new PrismaMariaDb({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 5,
});

const client = new PrismaClient({ adapter });

export const observer = new Observer<typeof client>(client);
export const prisma = observer.prisma;

export type {
  User as UserType,
} from "./prisma/client";

export const {
  user: User,
} = prisma;

SQLite

// app/database/index.ts
import { PrismaLibSql } from "@prisma/adapter-libsql";
import { PrismaClient } from "./prisma/client";
import { Observer } from "./observer";

const adapter = new PrismaLibSql({ url: process.env.DB_URL ?? "" });
const client = new PrismaClient({ adapter });

export const observer = new Observer<typeof client>(client);
export const prisma = observer.prisma;

export type {
  User as UserType,
} from "./prisma/client";

export const {
  user: User,
} = prisma;

Instance exports (export const { user: User }) and type aliases (export type { User as UserType }) within the app/database/index.ts file are automatically generated by the bun harpia migrate command. After defining a new model in schema.prisma, simply run the migration; the Harpia CLI will handle the injection of the correct exports so they are immediately available for use.

The Database Utility

The app/database/index.ts file also exposes a Database object that wraps Prisma’s low-level methods for raw queries, transactions, and direct SQL execution.

Transactions

Use Database.transaction when you need to group multiple operations atomically:

import { Database } from "app/database";

await Database.transaction(async (tx) => {
  await tx.user.create({ data: { ... } });
  await tx.post.create({ data: { ... } });
});

Raw Queries

For advanced scenarios where the Prisma query builder is insufficient, use the raw query methods:

import { Database } from "app/database";

// Returns typed results (Prisma maps the columns automatically)
const users = await Database.queryRaw`SELECT id, name FROM "User" WHERE active = true`;

// Execute a raw data manipulation statement
const affected = await Database.executeRaw`UPDATE "User" SET active = false WHERE last_login < NOW() - INTERVAL '90 days'`;

console.log(`${affected} users deactivated.`);

[!WARNING] queryRawUnsafe and executeRawUnsafe accept dynamic SQL strings, which makes them vulnerable to SQL injection if user input is interpolated directly. Only use them when absolutely necessary and always sanitise input.

The Repository Pattern

Harpia strongly recommends using the Repository Pattern to isolate all database logic. Each module has its own repositories/ directory where individual operations are defined as standalone functions.

The CLI generates the full CRUD set automatically when you create a new module. Repositories import model clients and types directly from app/database:

list.ts — Paginated query

// modules/user/repositories/list.ts
import { User } from "app/database";

export async function list(page = 1, perPage = 10, filter = "") {
  return await User.findMany({
    // select: {},
    // where: {},
    take: perPage,
    skip: (page - 1) * perPage,
  });
}

show.ts — Find by ID

// modules/user/repositories/show.ts
import type { UserType } from "app/database";
import { User } from "app/database";

export async function show(id: UserType["id"]) {
  return await User.findFirst({ where: { id } });
}

create.ts — Insert a record

// modules/user/repositories/create.ts
import { User } from "app/database";
import type { SchemaCreateBodyType } from "../validations/create";

export async function create(data: SchemaCreateBodyType) {
  return await User.create({ data });
}

update.ts — Update a record

// modules/user/repositories/update.ts
import { User } from "app/database";
import type { UserType } from "app/database";
import type { SchemaUpdateBodyType } from "../validations/update";

export async function update(id: UserType["id"], data: SchemaUpdateBodyType) {
  return await User.update({ where: { id }, data });
}

destroy.ts — Delete a record

// modules/user/repositories/destroy.ts
import type { UserType } from "app/database";
import { User } from "app/database";

export async function destroy(id: UserType["id"]) {
  return await User.delete({ where: { id } });
}

Notice that the inferred types from your validation schemas (SchemaCreateBodyType and SchemaUpdateBodyType) are reused directly in the repositories. This is the pattern that connects Zod’s type inference to Prisma’s data layer, ensuring end-to-end type safety.

Migrations

Harpia wraps Prisma’s standard migration workflow. After modifying schema.prisma, run:

# Generate and apply pending migrations in development
bun harpia migrate

# Deploy all pending migrations to production (no prompts)
bun harpia deploy

The migrate command also regenerates the Prisma Client automatically, so you do not need to run prisma generate separately.