Database Architecture

Overview

The application uses PostgreSQL as its database with Prisma as the ORM. The schema is minimal and focused on project management.

Database Configuration

Prisma Schema

// backend/prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Project {
  id          Int      @id @default(autoincrement())
  title       String
  description String
  tags        String[] // PostgreSQL native array support
  image       String?
  links       Json     // Native JSON field
  github      String?
  order       Int      @default(0)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
}

Connection Configuration

The database URL is read from the DATABASE_URL environment variable:

# Example .env
DATABASE_URL="postgresql://user:password@localhost:5432/portfolio?schema=public"

Format: postgresql://[user]:[password]@[host]:[port]/[database]?schema=[schema]

Models

Project Model

Prisma Client Usage

Initialization

// backend/src/server.js:20
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

Best Practices:

Single client instance per application
Automatically manages connection pool
Gracefully handles disconnection

Common Queries

Find All
Find One
Create
Update
Delete

// Get all projects ordered by 'order' field
const projects = await prisma.project.findMany({
  orderBy: { order: 'asc' }
});

SQL Equivalent:

SELECT * FROM "Project" ORDER BY "order" ASC;

// Get project by ID
const project = await prisma.project.findUnique({
  where: { id: 1 }
});

SQL Equivalent:

SELECT * FROM "Project" WHERE "id" = 1;

// Create new project
const project = await prisma.project.create({
  data: {
    title: "New Project",
    description: "A cool project",
    tags: ["React", "TypeScript"],
    links: [
      { url: "https://example.com", label: "Demo" }
    ],
    github: "https://github.com/user/repo",
    order: 0
  }
});

Note: createdAt and updatedAt are automatically set

// Update existing project
const project = await prisma.project.update({
  where: { id: 1 },
  data: {
    title: "Updated Title",
    tags: ["React", "TypeScript", "Vite"]
  }
});

Note: updatedAt is automatically updated

// Delete project
await prisma.project.delete({
  where: { id: 1 }
});

Throws: PrismaClientKnownRequestError if record not found

Migrations

Migration Files

Migrations are stored in backend/prisma/migrations/:

backend/prisma/migrations/
├── 20251229035927_init/
│   └── migration.sql
└── migration_lock.toml

Initial Migration

The init migration creates the Project table:

-- backend/prisma/migrations/20251229035927_init/migration.sql
CREATE TABLE "Project" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "tags" TEXT[],
    "image" TEXT,
    "links" JSONB NOT NULL,
    "github" TEXT,
    "order" INTEGER NOT NULL DEFAULT 0,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "Project_pkey" PRIMARY KEY ("id")
);

Key SQL Features:

SERIAL - Auto-incrementing integer
TEXT[] - PostgreSQL native array
JSONB - Binary JSON (faster than JSON)
TIMESTAMP(3) - Millisecond precision

Running Migrations

# Apply migrations and generate client
npm run backend:migrate
# or
cd backend && prisma migrate dev

Creating New Migrations

Modify backend/prisma/schema.prisma

Run migration command:

cd backend
npx prisma migrate dev --name add_featured_field

Prisma generates migration SQL automatically
Review generated SQL in migrations/ directory
Commit migration files to version control

Database Seeding

Seed script located at backend/scripts/seed.ts:

# Run seed script
npm run backend:seed
# or
cd backend && npm run seed

Purpose: Populate database with sample projects for development/testing Implementation Pattern:

// backend/scripts/seed.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  await prisma.project.createMany({
    data: [
      {
        title: "Sample Project 1",
        description: "Description...",
        tags: ["React", "TypeScript"],
        links: [{ url: "https://...", label: "Demo" }],
        order: 0
      },
      // More projects...
    ]
  });
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Prisma Client Generation

The Prisma Client must be regenerated after schema changes:

npm run backend:generate
# or
cd backend && npx prisma generate

When to regenerate:

After modifying schema.prisma
After pulling schema changes from git
After running migrations

Generated files location: node_modules/@prisma/client/

Type Safety

Auto-Generated Types

Prisma generates TypeScript types from the schema:

import { Project } from '@prisma/client';

// Project type has all fields typed correctly
const project: Project = {
  id: 1,
  title: "My Project",
  description: "...",
  tags: ["React"],
  image: "https://...",
  links: [{ url: "...", label: "..." }],
  github: null,
  order: 0,
  createdAt: new Date(),
  updatedAt: new Date()
};

JSON Field Types

JSON fields are typed as Prisma.JsonValue by default:

import { Prisma } from '@prisma/client';

type ProjectLink = {
  url: string;
  label: string;
};

// Cast JsonValue to specific type
const project = await prisma.project.findUnique({ where: { id: 1 } });
const links = project.links as ProjectLink[];

Prisma Studio

Visual database browser:

cd backend
npx prisma studio

Features:

Browse all tables
Edit records inline
Run queries
View relationships
Opens at http://localhost:5555

Database Constraints

Current schema has minimal constraints:

Primary Key: id (auto-increment)
No foreign keys: Single-table design
No unique constraints: Multiple projects can have same title
No check constraints: Validation handled in application layer

Adding Constraints Example

To add unique constraint on title:

model Project {
  // ... other fields
  title       String   @unique
  // ... rest of schema
}

Then run prisma migrate dev --name add_unique_title

Performance Considerations

Indexing

Consider adding indexes for frequently queried fields:

model Project {
  // ... fields
  order       Int      @default(0)
  
  @@index([order])  // Index for ORDER BY queries
}

Connection Pooling

Prisma manages connection pool automatically:

// Connection pool configured via DATABASE_URL
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=10"

Default pool size: Based on available database connections

Backup and Restore

Backup

# Export database to SQL file
pg_dump -U username -d portfolio > backup.sql

Restore

# Restore from SQL file
psql -U username -d portfolio < backup.sql

Overview

Getting Started

Architecture

Features

Components

Overview

Database Configuration

Prisma Schema

Connection Configuration

Models

Project Model

Prisma Client Usage

Initialization

Common Queries

Migrations

Migration Files

Initial Migration

Running Migrations

Creating New Migrations

Database Seeding

Prisma Client Generation

Type Safety

Auto-Generated Types

JSON Field Types

Prisma Studio

Database Constraints

Adding Constraints Example

Performance Considerations

Indexing

Connection Pooling

Backup and Restore

Backup

Restore

Next Steps

API Reference

Backend Architecture

Build docs developers (and LLMs) love

Overview

Getting Started

Architecture

Features

Components

​Overview

​Database Configuration

​Prisma Schema

​Connection Configuration

​Models

​Project Model

​Prisma Client Usage

​Initialization

​Common Queries

​Migrations

​Migration Files

​Initial Migration

​Running Migrations

​Creating New Migrations

​Database Seeding

​Prisma Client Generation

​Type Safety

​Auto-Generated Types

​JSON Field Types

​Prisma Studio

​Database Constraints

​Adding Constraints Example

​Performance Considerations

​Indexing

​Connection Pooling

​Backup and Restore

​Backup

​Restore

​Next Steps

API Reference

Backend Architecture

Build docs developers (and LLMs) love

Overview

Database Configuration

Prisma Schema

Connection Configuration

Models

Project Model

Prisma Client Usage

Initialization

Common Queries

Migrations

Migration Files

Initial Migration

Running Migrations

Creating New Migrations

Database Seeding

Prisma Client Generation

Type Safety

Auto-Generated Types

JSON Field Types

Prisma Studio

Database Constraints

Adding Constraints Example

Performance Considerations

Indexing

Connection Pooling

Backup and Restore

Backup

Restore

Next Steps