Database Migrations

Database Migrations

This project uses Drizzle ORM and Drizzle Kit to manage the PostgreSQL database schema. Instead of manual SQL scripts, the schema is defined in TypeScript, ensuring type safety and easy synchronization between your code and the database.

Prerequisites

Before running migrations, ensure your .env file contains a valid DATABASE_URL:

DATABASE_URL=postgresql://<user>:<password>@localhost:5432/hcgai_db

Schema Definition

The database structure is defined in shared/schema.ts. This file contains the definitions for:

• users: User profiles and authentication data.
• hcg_entries: Blood test results (β-hCG levels).
• ultrasound_entries: Measurement data from ultrasound scans.
• analysis_results: AI-generated risk assessments and diagnoses.
• sessions: Persistent session storage for authentication.

Syncing the Schema

To synchronize your database with the current TypeScript schema, use the db:push command. This is the recommended approach for development as it directly updates the database to match the schema.ts file.

Initial Setup or Standard Update

Run the following command to push schema changes to your PostgreSQL instance:

npm run db:push

Forcing Changes

If you make breaking changes to the schema (such as renaming columns or changing data types) and are working on a fresh or development database, you may need to force the synchronization:

# WARNING: This may result in data loss if columns are removed
npm run db:push --force

Schema Management Workflow

To modify the database structure, follow these steps:

1. Modify the Schema: Open shared/schema.ts and update the table definitions (e.g., add a new column to hcgEntries).
2. Validate Types: Ensure your code in server/storage.ts or server/routes.ts is compatible with the new schema.
3. Push Changes: Run npm run db:push to apply the changes to your local or production database.

Session Table Management

The application uses connect-pg-simple for session management. While the sessions table is defined in the Drizzle schema for consistency, the server is also configured to check for this table on startup.

If you encounter session errors, ensure the sessions table was correctly created during the db:push process.

Troubleshooting

| Issue | Solution | | :--- | :--- | | Connection Refused | Ensure PostgreSQL is running and the DATABASE_URL in .env is correct. | | Schema Mismatch | Run npm run db:push to ensure the database matches the latest code. | | Permission Denied | Ensure the database user provided in the connection string has CREATE and ALTER permissions. |

For advanced schema introspection or manual migrations, you can use the Drizzle Studio UI:

npx drizzle-kit studio