Skip to main content
The Bloom Housing API uses PostgreSQL 15 as its database and Prisma as its ORM. Prisma handles schema definition, migrations, and client generation.

​
Local setup

1

Install PostgreSQL

Install PostgreSQL 15 using Homebrew:
brew install postgresql@15
Then start the service:
brew services start postgresql@15
2

Create the database

If this is your first time setting up, you may need to create a default database for your system user. If you see the error psql: error: FATAL: database "<username>" does not exist, run:
createdb <username>
3

Configure the connection URL

Copy api/.env.template to api/.env and set DATABASE_URL to include your system username:
DATABASE_URL="postgres://<username>@localhost:5432/bloom_prisma"
4

Set up the database and seed data

From the api directory, run the setup script. This generates the Prisma client, runs all pending migrations, and seeds the database with staging data:
yarn setup
The seeded data closely matches the jurisdictions used in the core environment with appropriate feature flags set.If you want randomly generated data instead (for more varied local development), run:
yarn setup:dev
If you are using VS Code, install the Postgres explorer extension to inspect your local database. When creating a new connection, use localhost as the host, your system username, leave the password blank, port 5432, standard SSL mode, bloom_prisma as the database name, and a descriptive label such as local-bloom.

​
Seeded local credentials

After running yarn setup, the following admin account is available in the local database:
FieldValue
Email[email protected]
Passwordabcdef

​
Prisma commands

CommandDescription
yarn setupGenerates Prisma client, runs all migrations, and seeds staging data
yarn setup:devSame as yarn setup but seeds randomly generated data
yarn generate:clientRegenerates the Prisma client after schema changes
yarn prisma migrate dev --name <name>Creates a new migration file based on changes to schema.prisma
yarn db:migration:runRuns all pending migrations against the connected database
yarn db:seed:stagingSeeds the database with staging data without running migrations

​
Modifying the schema

The Prisma schema file is located at api/prisma/schema.prisma. It defines the structure of all database models, the relationships between them, database enums, and how Prisma connects to the database.
Install the Prisma VS Code extension for syntax highlighting and automatic formatting of .prisma files.
To add a new field to an existing model:
1

Add the field to the DTO

Update the relevant DTO file under api/src/dtos/ to include the new field with its validators and transformer decorators.
2

Regenerate the Prisma client

Run the following command to update the TypeScript types and the OpenAPI/Swagger spec:
yarn generate:client
3

Add the field to schema.prisma

Manually add the new field to the appropriate model in api/prisma/schema.prisma, following the naming conventions described below.
4

Create and run the migration

Generate a migration file for the schema change:
yarn prisma migrate dev --name <migration-name>
Use a descriptive, kebab-case name for the migration (for example, add-listing-file-number).

​
Naming conventions

All Prisma models and fields follow a consistent naming convention that bridges TypeScript conventions with PostgreSQL conventions:
ElementTypeScript styleDatabase styleExample
Model namesPascalCasesnake_case via @@map()HelloWorld → hello_world
Enum namesPascalCasesnake_case via @@map()ListingStatus → listing_status
Field namescamelCasesnake_case via @map()helloWorld → hello_world
This keeps the API layer idiomatic TypeScript while respecting PostgreSQL’s naming conventions. Example from schema.prisma: 
model ActivityLog {
  id        String   @id() @default(dbgenerated("uuid_generate_v4()")) @db.Uuid
  createdAt DateTime @default(now()) @map("created_at") @db.Timestamp(6)
  updatedAt DateTime @updatedAt @map("updated_at") @db.Timestamp(6)

  action   String  @db.VarChar
  metadata Json?
  module   String  @db.VarChar
  recordId String? @map("record_id") @db.Uuid
  userId   String? @map("user_id") @db.Uuid

  @@map("activity_log")
}

​
Translation migration helper

To generate SQL for inserting or updating rows in the translations table, use the yarn translations:sql command: 
yarn translations:sql --input scripts/db-translation-input.example.json
By default, updates target generic translation rows where jurisdiction_id IS NULL for each language. The script generates SQL for en, es, tl, vi, zh, ar, bn, ko, hy, fa and machine-translates missing non-English values using the Google Translate API. Common options:
FlagDescription
--output <name-or-path>Write SQL to a file instead of stdout. Use a name like 52_my_migration to write to prisma/migrations/52_my_migration/migration.sql
--languages en,es,tlLimit output to specific language codes
--jurisdiction "Bloomington"Target jurisdiction-specific rows by name instead of generic rows
--no-machine-translateDisable Google Translate for non-English strings
Examples: 
# Generic/default rows
yarn translations:sql --input scripts/db-translation-input.example.json --output 54_generic_translation_update

# Jurisdiction-specific rows
yarn translations:sql --input scripts/db-translation-input.example.json --jurisdiction "Bloomington" --output 55_bloomington_translation_update
When writing output to a migration file, ensure you increment the migration number prefix beyond the last existing migration in prisma/migrations/.

Build docs developers (and LLMs) love

Get started for freeTalk to us