Data migration on Postgres

[mauriciogior]

Found a bug? Please fill out the sections below. 👍

Issue Summary

Just following the readme file, can't get through the yarn workspace @calcom/prisma db-migrate command.

Steps to Reproduce

1. Follow the documentation
2. Install Postgresql 16

Any other relevant information. For example, why do you consider this a bug and what did you expect to happen instead?

Actual Results

Environment variables loaded from .env
Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "calcom", schema "public" at "localhost:5432"

Error: P3006

Migration `20240321143215_move_avatars_cols_to_avatar_table` failed to apply cleanly to the shadow database. 
Error:
ERROR: function gen_random_uuid() does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
   0: sql_schema_connector::validate_migrations
           with namespaces=None
             at schema-engine/connectors/sql-schema-connector/src/lib.rs:324
   1: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:270

Expected Results

• Command runs properly

[dosubot[bot]]

The error you're encountering is due to the pgcrypto extension not being installed or enabled in your PostgreSQL database. This extension provides the gen_random_uuid() function, which your migration script requires but is currently missing. To resolve this issue, you can install the pgcrypto extension by running the following SQL command in your PostgreSQL database:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

This will ensure the gen_random_uuid() function is available for your migrations.

Additionally, the error related to the missing public.User table suggests that your database might not be properly initialized with the expected schema. Before running migrations, make sure your Prisma schema reflects your database structure accurately and that all required tables, including public.User, are present in the database. If necessary, you can use the npx prisma db push command to align your database schema with your Prisma schema, but be cautious with this approach in production environments as it can alter your database schema directly and potentially lead to data loss.

If these steps do not resolve your issue, please check your Prisma schema and the specific migration script that's failing for any discrepancies or errors, and ensure your database is correctly set up to match your schema's requirements.

#### Details

**Sources** - [packages/prisma/migrations/20240321143215_move_avatars_cols_to_avatar_table/migration.sql](https://github.com/calcom/cal.com/blob/main/packages/prisma/migrations/20240321143215_move_avatars_cols_to_avatar_table/migration.sql) - [packages/platform/examples/base/prisma/schema.prisma](https://github.com/calcom/cal.com/blob/main/packages/platform/examples/base/prisma/schema.prisma) - [packages/prisma/migrations/20210605225044_init/migration.sql](https://github.com/calcom/cal.com/blob/main/packages/prisma/migrations/20210605225044_init/migration.sql) - [packages/prisma/package.json](https://github.com/calcom/cal.com/blob/main/packages/prisma/package.json) - [packages/app-store/caldavcalendar/package.json](https://github.com/calcom/cal.com/blob/main/packages/app-store/caldavcalendar/package.json) - [packages/config/package.json](https://github.com/calcom/cal.com/blob/main/packages/config/package.json) - [packages/app-store/vimcal/package.json](https://github.com/calcom/cal.com/blob/main/packages/app-store/vimcal/package.json) - [packages/platform/atoms/package.json](https://github.com/calcom/cal.com/blob/main/packages/platform/atoms/package.json) - [packages/features/ee/package.json](https://github.com/calcom/cal.com/blob/main/packages/features/ee/package.json) - [packages/prisma/schema.prisma](https://github.com/calcom/cal.com/blob/main/packages/prisma/schema.prisma) - [package.json](https://github.com/calcom/cal.com/blob/main/package.json) - [packages/prisma/docker-compose.yml](https://github.com/calcom/cal.com/blob/main/packages/prisma/docker-compose.yml) - [packages/prisma/migrations/20240329084749_platform_snake_case_to_pascal_case/migration.sql](https://github.com/calcom/cal.com/blob/main/packages/prisma/migrations/20240329084749_platform_snake_case_to_pascal_case/migration.sql)
**About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

[mauriciogior]

@dosu already ran the CREATE EXTENSION IF NOT EXISTS pgcrypto; statement like shown in the attached screenshot, still same error.

[dosubot[bot]]

If you've confirmed the pgcrypto extension is installed in the correct database but still face the issue, consider these steps:

1. Check Prisma Schema and Migration Files: Ensure your Prisma schema accurately reflects your database structure, especially for the User model. Verify that all migration files are correctly set up and have been applied.

2. Database Permissions: Make sure the database user utilized by Prisma has the necessary permissions to create extensions and modify the database schema.

3. Prisma Migration State: If migrations were partially applied or failed previously, consider resetting the migrations in a development environment with npx prisma migrate reset. Be cautious as this will drop all data in your database.

4. Prisma Version Compatibility: Ensure your local setup is using Prisma version ^5.4.2 to match the cal.com repository requirements.

5. PostgreSQL Configuration: Verify the PGSSLMODE environment variable is correctly set, especially if deploying on platforms like Heroku or using self-signed certificates.

If after these checks the issue persists, providing more details about your setup, including the sequence of commands used to install the pgcrypto extension, PostgreSQL permissions, and any other relevant configuration details, would be helpful for further diagnosis.

#### Details

**Sources** - [packages/prisma/migrations/migration_lock.toml](https://github.com/calcom/cal.com/blob/main/packages/prisma/migrations/migration_lock.toml) - [packages/platform/examples/base/prisma/schema.prisma](https://github.com/calcom/cal.com/blob/main/packages/platform/examples/base/prisma/schema.prisma) - [packages/prisma/package.json](https://github.com/calcom/cal.com/blob/main/packages/prisma/package.json) - [.env.example](https://github.com/calcom/cal.com/blob/main/.env.example) - [app.json](https://github.com/calcom/cal.com/blob/main/app.json)
**About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

[PeerRich]

thats weird. can you try a fresh pull and latest docker version?