Change the Database Schema

Use this guide for fields, constraints, relationships, and models in the backend's PostgreSQL database. Complete Backend Quickstart first, using a disposable local database.

OverviewCopy Anchor Link

The schema defines the model contract, migration SQL updates PostgreSQL, and the generated Prisma Client exposes that contract to controllers:

Prisma schema → Migration → PostgreSQL
       ↓
Generated Prisma Client → Backend controllers

Changing schema.prisma alone does not update an existing database. Generating a client also does not apply migrations. Both database state and generated types must match the application code.

Step 1 — Locate the schemaCopy Anchor Link

Edit backend/prisma/schema.prisma in the application repository when implementing a schema change. Its datasource uses PostgreSQL and DATABASE_URL; its generator uses prisma-client-js. Models use string IDs with cuid() defaults and @@map for table names. Relations declare their backing fields and referenced identifiers. Read Data Model for the entities and constraints.

Six checked-in migrations live under backend/prisma/migrations/. Inspect their SQL as well as the schema, especially for foreign-key deletion actions.

Sourcebackend/prisma/schema.prisma

Sourcebackend/prisma/migrations/

Step 2 — Plan the changeCopy Anchor Link

Trace affected fields through controllers, validators, responses, and frontend consumers before editing. Check:

• Which rows already exist and what value they should have after migration.
• Required versus optional fields (?), defaults, and whether callers send null, omit a field, or send an empty string.
• @unique and @@unique constraints, including existing duplicates.
• Relation fields, foreign keys, and deletion actions for related data.
• Queries using select/include, create/update payloads, and returned model scalars.

Do not infer a relationship from a field name: Club.clubHeadId is a unique optional scalar with no foreign key. User registrations and blogs restrict user deletion; mentor likes cascade. These details affect migration planning. See deletion behavior.

Sourcebackend/prisma/migrations/20250829203812_initial_migration/migration.sql

Sourcebackend/prisma/migrations/20250901211052_add_blogs/migration.sql

Sourcebackend/prisma/migrations/20251025152715_add_mentor_likes/migration.sql

Step 3 — Modify the Prisma schemaCopy Anchor Link

As an illustrative change, add an optional location field to the existing Event model, leaving its other fields unchanged:

location String?

This field does not exist in the inspected application. It demonstrates an additive change following current string/optional-field conventions. Existing events can retain null; decide separately whether new API requests may omit it. Do not edit generated client files by hand.

Step 4 — Create a migrationCopy Anchor Link

Run all commands in this guide from ElixirV4/backend/, after installing its locked dependencies with npm ci. The lockfile resolves both prisma and @prisma/client to 6.15.0. Use the package's local CLI after installation rather than fetching a different Prisma version.

Configure backend/.env with a development DATABASE_URL and start PostgreSQL. Development migration generation uses a shadow database, so the local role needs permission to create it, as described in Quickstart. --create-only still needs database access and can encounter drift/reset prompts.

For the illustrative field, generate SQL without applying the new migration immediately:

npm run db:migrate -- --name add_event_location --create-only

The script invokes npx prisma migrate dev; npm forwards options after --. Inspect the new prisma/migrations/<timestamp>_add_event_location/migration.sql. For this addition, expect a nullable text column on the mapped events table. Confirm the actual generated SQL and absence of unrelated drops or constraint changes.

After reviewing it, apply pending migrations to your local development database:

npm run db:migrate

If prompted to reset, stop and investigate drift and the selected database before accepting. Commit the intended schema and new migration together; do not rewrite migrations already applied to shared environments.

Deployment uses the Prisma CLI command npx prisma migrate deploy to apply already-created migrations. It does not create migrations or generate the client. This is a deployment distinction, not an instruction to deploy now: the repository defines no db:deploy script and CI does not apply migrations. Deployment & Configuration describes what is wired up. Command behavior is documented in the Prisma v6 CLI reference.

Sourcebackend/package.json

Sourcebackend/package-lock.json

Source.github/workflows/ci.yml

Step 5 — Regenerate Prisma ClientCopy Anchor Link

Run explicitly after the schema/migration change:

npm run db:generate

This invokes npx prisma generate. Prisma 6 development migrations normally trigger generators, but explicit generation also covers a pulled schema change or a create-only workflow. Client generation writes artifacts, not database rows. Restart the development backend after changing generated types. See the Prisma v6 CLI reference for generation behavior.

Step 6 — Update application codeCopy Anchor Link

Review controller queries and create/update payloads, Zod or manual validation, relations, returned response shapes, frontend types/forms, and API examples. For the illustrative field, decide which operations accept location and whether existing responses should include it. Controllers returning full model scalars can expose a new field even without an explicit response edit.

The validation middleware discards parsed Zod values, so schema defaults/coercions do not become controller inputs. A database default, a Zod default, and a controller fallback have different effects here. Use Add or Change an API Endpoint for accompanying API work.

Sourcebackend/src/controllers/eventController.ts

Sourcebackend/src/middleware/validate.ts

Step 7 — Verify the changeCopy Anchor Link

With backend dependencies installed and the local environment configured, run:

npx prisma validate
npx prisma migrate status
npm run db:generate
npx tsc --noEmit
npm run build

validate checks the schema; it does not prove database compatibility. migrate status checks migration history against the configured database. Verify the intended SQL was applied and the generated client exposes the changed field. tsc --noEmit checks compilation without emitting JavaScript; build generates the client and runs emitting compilation. Neither applies migrations. These are valid commands, not a guarantee that the current backend compiles without errors.

Run npm run dev and exercise affected endpoints against the migrated database. Optionally use npm run db:studio to inspect local data. Check new and existing rows, omitted/null values, required values, duplicate unique keys, missing related records, and delete behavior. For retained-data changes, rehearse against representative sanitized data and compare row counts and important values before and after. The optional location example should preserve existing events with a null location.

The backend has no checked-in endpoint test suite or test script, so record manual requests, outcomes, and database observations explicitly. Do not use /health alone as migration verification: it performs no query. The build/start output mismatch also means compilation success alone does not verify production startup.

Sourcebackend/tsconfig.json

Sourcebackend/src/app.ts

Step 8 — Review migration safetyCopy Anchor Link

These are general migration-review recommendations applied to this schema; the repository has no automated migration-review job.

Review both a fresh installation and an upgrade with existing rows. Reverting application code does not restore dropped data; describe recovery and rollout ordering in the PR. CONTRIBUTING.md asks contributors to explain migrations, account for existing production data, and test against realistic data.

SourceCONTRIBUTING.md

Step 9 — Document the changeCopy Anchor Link

Update Data Model, affected API Reference pages and examples, and relevant Concepts. Update Deployment & Configuration when operational requirements change and Known Limitations when a documented constraint changes.

Include migration purpose, data compatibility, verification evidence, and rollout/recovery considerations in the application PR. Documentation lives in elixir-docs; run npm run lint, npm run types:check, and npm run build from its root after editing pages.

Completion checklistCopy Anchor Link

• Models, consumers, existing data, uniqueness, relations, and deletion actions were inspected.
• Schema and migration agree; generated SQL was reviewed for destructive or unrelated changes.
• Migration succeeded locally and existing data compatibility was checked.
• Prisma validation, migration status, client generation, and compilation/build checks were run.
• Affected API behavior, constraints, and frontend consumers were verified; failures are recorded.
• PR explains migration safety and documentation updates; documentation checks passed.