Database | PEST.js

PEST.js supports three ORMs. Each one generates its own config, schema, client setup, and a working CRUD route at /api/examples so you can see how it all connects.

Supported providers

All three ORMs work with:

PostgreSQL (recommended for production)
MySQL
SQLite (file-based, no server needed)

ORM setup

Generated files

schema.prisma

prisma.ts

example.ts

prisma.config.ts

prisma.ts exports a singleton PrismaClient instance. The example route uses it directly for queries.

prisma.config.ts is the Prisma configuration file where the database URL is set via env("DATABASE_URL"). The schema.prisma file does not contain the URL directly — this is the modern Prisma pattern.

Scripts

Script	Command
`db:generate`	`prisma generate`
`db:migrate`	`prisma migrate dev`
`db:push`	`prisma db push`
`db:studio`	`prisma studio`

First migration

After generating your project, run:

Terminal

npx prisma migrate dev --name init

Generated files

schema.ts

index.ts

example.ts

drizzle.config.ts

schema.ts defines your tables. index.ts creates and exports the drizzle client. The config file tells drizzle-kit where to find your schema and how to connect.

Scripts

Script	Command
`db:generate`	`drizzle-kit generate`
`db:migrate`	`drizzle-kit migrate`
`db:push`	`drizzle-kit push`
`db:studio`	`drizzle-kit studio`

Push your schema

Terminal

npm run db:push

MySQL doesn't support RETURNING clauses. The generated code uses $returningId() instead of .returning() for MySQL.

Generated files

data-source.ts

index.ts

user.ts

example.ts

data-source.ts configures the TypeORM connection. Entity columns use explicit types (e.g. @Column("varchar")) for compatibility with tsx/esbuild, which doesn't emit decorator metadata.

The generated tsconfig.json enables experimentalDecorators and emitDecoratorMetadata.

Scripts

Script	Command
`db:migrate`	`typeorm migration:run`
`db:generate`	`typeorm migration:generate`

SQLite uses datetime instead of timestamp for date columns because better-sqlite3 doesn't support the timestamp type.

DATABASE_URL

When a database is selected, DATABASE_URL is added to .env and .env.example. The src/config/env.ts file validates that it's set at startup. See Environment Variables for more on how env config works.

Provider	Example
PostgreSQL	`postgresql://user:password@localhost:5432/mydb`
MySQL	`mysql://user:password@localhost:3306/mydb`
SQLite	`./dev.db` or `file:./dev.db` (Prisma)