FitCheck Database

Here is a step-by-step guide to set up database with Prisma, and a Local Database for easy development.

1. Install Prerequisites

• Install VSCode (Version Latest)
• Install Node.js (Version ~22.14.0)
• Install Git
• Install Git Bash (optional, for better terminal experience)

2. Clone The repo using SSH

Setup all prerequisites prior to clone the repo and use the command:

	git clone [email protected]:Helix-Capital/fitcheck40-db.git
	cd fitcheck40-db
	# setup node-modules by running this comment at root folder
	npm install

3. Setup local PostgreSQL Database

- **Install [PostgreSQL](https://www.postgresql.org/download/) (Version Latest)**

Run the following commands in the CLI or pgadmin portal

	CREATE ROLE fitcheckdevad WITH
		LOGIN
		SUPERUSER
		CREATEDB
		CREATEROLE
		INHERIT
		REPLICATION
		BYPASSRLS
		CONNECTION LIMIT -1
		PASSWORD 'xxxxxx';

	CREATE DATABASE fitcheckdev
		WITH
		OWNER = fitcheckdevad
		ENCODING = 'UTF8'
		LOCALE_PROVIDER = 'libc'
		CONNECTION LIMIT = -1
		IS_TEMPLATE = False;

4. Turn database schema to Prisma Schema

Set the DATABASE_URL in the .env file to point to fitcheckdev database.

	DATABASE_URL="postgresql://fitcheckdevad:your_unique_password@localhost:5432/fitcheckdev?schema=public&sslmode=disable"

Setup prisma schema for the database and migrate to database.

	npx prisma db push
	# create prisma client
    npx prisma generate
	npx prisma migrate dev

5. Schema updates and modifications

🛠 Step 0: Dont's:

• [ ] Do not modify the schema directly on postgres

• [ ] Do not modify the schema through typescript either.

• [Later] we will use regular user role for all application transactions not admin.

• [ ] Pull the latest from GitHub repo prior to modification.

✅ Step 1: Update schema.prisma

Add or change your model in prisma/schema.prisma:

For example, to add a new Post table:

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  createdAt DateTime @default(now())
}

Or to modify an existing model:

 model User {
   id    Int    @id @default(autoincrement())
   email String @unique
+  name  String?
 }

✅ Step 2: Create a Migration

Generate a migration based on the change:

	npx prisma migrate dev --name add-post-model

This will: - Generate a new folder in prisma/migrations - Apply the changes to your local database - Update Prisma Client (auto-generated code for querying)

✅ Step 3: Use the Updated Prisma Client After migration, Prisma regenerates the client so you can now use the updated model:

const newPost = await prisma.post.create({
	data: {
		title: "Hello World",
		published: true,
	},
});

No need to manually regenerate Prisma Client — migrate dev does that for you.

🧪 Step 4: Test the New Table Use npx prisma studio to inspect your database via a GUI.