This document explains the database layer implementation in the RealWorld reference API, which uses Prisma ORM to provide type-safe database access. It covers the Prisma schema structure, database models, client generation workflow, seeding mechanisms, and integration patterns with Nitro route handlers.
For information about how route handlers use the database layer, see Route Handler Implementation. For authentication-specific database queries, see Authentication Implementation.
The database layer uses the following technologies:
| Component | Purpose | Version |
|---|---|---|
| Prisma ORM | Type-safe database client generator | 6.0.1+ |
| @prisma/client | Generated runtime client | 6.0.1+ |
| SQLite | Development database | N/A |
| TypeScript | Type definitions from schema | N/A |
Prisma serves as the Object-Relational Mapping (ORM) tool that translates the database schema into type-safe TypeScript client code, eliminating runtime type errors and providing IDE autocompletion for database queries.
Sources: apps/api/package.json1-26 apps/api/package-lock.json1-50
The Prisma schema is located at apps/api/server/prisma/schema.prisma and defines three critical components:
The generator configuration specifies that the Prisma Client should be output to node_modules/@prisma/client, making it available as a standard npm package import throughout the application.
Sources: Makefile43-44 apps/api/package.json9
The Prisma schema defines five core models that map directly to the OpenAPI specification data models:
The User model stores user account information and authentication credentials. Key characteristics:
id (string/UUID)email, usernamearticles (one-to-many with Article as author)favorites (many-to-many with Article)comments (one-to-many with Comment)followedBy / following (self-referential many-to-many)The Article model represents blog posts with slugified URLs:
id (string/UUID)slugauthor (many-to-one with User)comments (one-to-many with Comment)favoritedBy (many-to-many with User)tags (many-to-many with Tag)The Comment model stores article comments:
id (string/UUID)article (many-to-one with Article)author (many-to-one with User)The Tag model provides article categorization:
id (string/UUID)namearticles (many-to-many with Article)Sources: apps/api/package.json1-26
db:generate CommandPrisma Client generation is a build-time step that reads the schema file and generates TypeScript types and runtime client code. The process is invoked through npm scripts:
The Makefile provides a convenient wrapper for client generation:
apps/api/server/package.json9 defines the script:
Makefile43-44 invokes it during setup:
The prisma generate command creates:
| Artifact | Location | Purpose |
|---|---|---|
| PrismaClient class | node_modules/@prisma/client/index.d.ts | Main client interface |
| Model types | node_modules/@prisma/client/index.d.ts | TypeScript types for each model |
| Query helpers | node_modules/@prisma/client/runtime/ | Runtime query execution |
| Prisma engines | node_modules/@prisma/client/ | Native query engine binaries |
The generated client provides compile-time type checking for:
Sources: Makefile43-44 apps/api/package.json9 apps/api/package-lock.json1176-1197
The database seeding mechanism populates the development database with sample data. The configuration is defined in apps/api/package.json12-14:
This configuration tells Prisma to execute prisma/seed.ts using ts-node when the db:seed command is invoked.
apps/api/package.json10 defines the npm script:
The seeding workflow:
The seed script likely uses the @ngneat/falso package (listed in apps/api/package-lock.json750-760) to generate realistic fake data:
Sources: apps/api/package.json10-14 apps/api/package-lock.json750-760
The Prisma Client is typically instantiated as a singleton to reuse database connections:
The generated Prisma Client provides a fluent API for database operations:
| Operation | Method Pattern | Example Use Case |
|---|---|---|
| Find One | findUnique({ where: { ... } }) | Get user by email |
| Find Many | findMany({ where: { ... } }) | List articles with filters |
| Create | create({ data: { ... } }) | Register new user |
| Update | update({ where: { ... }, data: { ... } }) | Update user profile |
| Delete | delete({ where: { ... } }) | Remove comment |
| Count | count({ where: { ... } }) | Count favorite articles |
Prisma enables type-safe relation loading through the include parameter:
The TypeScript compiler enforces:
article, not articles)where clausesincludeComplex queries with filtering support:
Sources: apps/api/package-lock.json1176-1197
The Prisma Client is imported and used within Nitro route handlers. Since Nitro uses a file-based routing system under server/routes/, each route handler can import the client:
Prisma manages database connections through a connection pool. Best practices:
PrismaClient instance per application lifecycleprisma.$disconnect() during application shutdownA typical Nitro route handler using Prisma:
Prisma throws typed errors that can be caught and handled:
PrismaClientKnownRequestError: Unique constraint violations, record not foundPrismaClientValidationError: Invalid query parametersPrismaClientInitializationError: Connection failuresSources: Makefile57-58 apps/api/package.json5
The complete setup workflow integrates Prisma client generation into the reference implementation setup process:
The Makefile orchestrates this process through the reference-implementation-setup target, which executes three sequential steps:
@prisma/client and prismaSources: Makefile46-52 apps/api/package.json1-26
The Postman test suite validates the database layer indirectly by testing API endpoints that perform database operations. The test workflow Makefile66-77 starts the API server and executes the test suite:
The test suite api/run-api-tests.sh1-20 runs against the local API server, validating:
Sources: Makefile66-77 api/run-api-tests.sh1-20
Refresh this wiki