in prisma orm i have schema imported from existing db like this model newsfeeds { ┃ 6 │…
- User
in prisma orm i have schema imported from existing db like this
model news_feeds {
┃ 6 │ id BigInt @id @default(autoincrement())
┃ 7 │ platform_id BigInt
┃ 8 │ title String? @db.VarChar
┃ 9 │ filters Json?
┃ 10 │ created_at DateTime @db.Timestamp(6)
┃ 11 │ updated_at DateTime @db.Timestamp(6)
┃ 12 │ color Int @default(0)
┃ 13 │ platforms platforms @relation(fields: [platform_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "fk_rails_c91a01ddc6")
┃ 14 │
┃ 15 │ @@index([platform_id], map: "index_news_feeds_on_platform_id")
┃ 16 }can i ommit map: "fk_rails_c91a01ddc6" if i want to create new database from this schema? i just importing schema from existing database to create a new project.
- Assistant
Yes, you can omit the
map: "fk_rails_c91a01ddc6"attribute if you are creating a new database from this Prisma schema and do not require the specific naming convention for the foreign key constraint that was used in the existing database.The
mapattribute in a relation specifies a custom name for the constraint in the database. It's particularly useful when you're reverse-engineering an existing database, and you want Prisma to maintain compatibility with an existing naming convention. However, if you are starting fresh and don't need to maintain any specific database constraint names, you can safely omit themapattribute. Prisma will then generate the constraint names automatically using its own naming conventions. - User
how to find and replace in neovim, what is the command?
- Assistant
In Neovim, you can find and replace text using the
:substitutecommand. Here's how you can use it:-
Basic Find and Replace on a Single Line:
:s/old_text/new_text/This replaces the first occurrence ofold_textwithnew_texton the current line.
-
Replace All Occurrences on a Single Line:
:s/old_text/new_text/gAdding thegflag (global) replaces all occurrences ofold_texton the current line.
-
Find and Replace in the Entire File:
:%s/old_text/new_text/gThe%before thesindicates that the substitution should be performed on all lines in the file.
-
Interactive Find and Replace in the Entire File:
:%s/old_text/new_text/gcAdding thecflag (confirm) makes it interactive, so you'll be prompted to confirm each replacement.
-
Find and Replace in a Range of Lines:
:10,20s/old_text/new_text/gThis performs the substitution only between lines 10 and 20.
Here's a quick rundown on how to use these commands:
- Enter command mode by pressing
ESC. - Type
:to enter command-line mode. - Enter the appropriate
substitutecommand and pressEnter.
Using these commands, you can efficiently perform find and replace operations in Neovim across different scopes and with varying levels of interaction.
-
- User
how to use regex
- Assistant
Regular expressions (regex) are powerful tools for pattern matching in text. In Neovim, you can leverage regex within the
:substitutecommand to perform complex find and replace operations. Below is a basic guide on how to use regex in Neovim:Basic Regex Syntax:
-
Literal Characters:
- Matches the character itself. For example,
amatches "a".
- Matches the character itself. For example,
-
Metacharacters:
.: Matches any single character except a newline.^: Matches the start of a line.$: Matches the end of a line.\d: Matches any digit (0-9).\w: Matches any word character (alphanumeric & underscore).\s: Matches any whitespace character.
-
Quantifiers:
*: Matches 0 or more of the preceding element.+: Matches 1 or more of the preceding element.?: Matches 0 or 1 of the preceding element.{n}: Matches exactly n occurrences of the preceding element.
-
Character Classes:
[abc]: Matches any one of "a", "b", or "c".[^abc]: Matches any character except "a", "b", or "c".
-
Groups and Alternation:
(abc): A group that matches "abc".a|b: Matches either "a" or "b".
Using Regex in Neovim:
-
Substitute using Regex:
- Use the
:scommand with regular expressions. For example, to replace all occurrences of a digit with "X"::%s/\d/X/g - This command replaces all digits in the file with "X".
- Use the
-
Capture Groups:
- You can use parentheses
( ... )to capture groups. Captured groups can be referenced in the replacement part with\1,\2, etc. For example, swapping two words::%s/\(word1\)\(word2\)/\2 \1/g - This command swaps "word1" and "word2".
- You can use parentheses
-
Exclusion with
\v:- Neovim uses "very magic" mode (
\v) to make the regex syntax more Perl-like, so you don't have to escape common regex special characters::%s/\v\d/X/g
- Neovim uses "very magic" mode (
Example:
To remove leading whitespace from each line in a file, you can use:
:%s/^\s\+//gThis matches lines that start with one or more whitespace characters, replacing them with nothing.
By understanding and using regular expressions, you can perform complex text processing tasks directly within Neovim effectively.
-
- User
/// This model contains an expression index which requires additional setup for migrations. Visit https://pris.ly/d/expression-indexes for more info.
┃ 7 model tags {
┃ 6 │ id BigInt @id @default(autoincrement())
┃ 5 │ title String @db.VarChar
┃ 4 │ slug String @db.VarChar
┃ 3 │ platform_id BigInt
┃ 2 │ created_at DateTime @db.Timestamp(6)
┃ 1 │ updated_at DateTime @db.Timestamp(6)
┃ 827 │ matter_count Int @default(0)
┃ 1 │ type String @default("Tag::Tag") @db.VarChar
┃ 2 │ h1_title String? @db.VarChar
┃ 3 │ lead String? @db.VarChar
┃ 4 │ settings Json? @default("{}")
┃ 5 │ format Int @default(0)
┃ 6 │ week_matters_count Int @default(0)
┃ 7 │ month_matters_count Int @default(0)
┃ 8 │ last_matter_date DateTime? @db.Timestamp(6)
┃ 9 │ old_uri String? @db.VarChar
┃ 10 │ from_resource Int?
┃ 11 │ processed_old_uri Boolean? @default(false)
┃ 12 │ processed_old_engine_redirects Boolean? @default(false)
┃ 13 │ yandex_news_rubric Boolean? @default(false)
┃ 14 │ google_news_rubric Boolean? @default(false)
┃ 15 │ archived Boolean? @default(false)
┃ 16 │ hidden Boolean? @default(false)
┃ 17 │ initiator_id Int?
┃ 18 │ editor_id Int?
┃ 19 │ show_in_matter Boolean? @default(false)
┃ 20 │ in_sitemap Boolean? @default(false)
┃ 21 │ content_blocks content_blocks[]
┃ 22 │ matters_tags matters_tags[]
┃ 23 │ platforms platforms @relation(fields: [platform_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
┃ 24 │
┃ 25 │ @@index([archived], map: "index_tags_on_archived")
┃ 26 │ @@index([editor_id], map: "index_tags_on_editor_id")
┃ 27 │ @@index([hidden], map: "index_tags_on_hidden")
┃ 28 │ @@index([initiator_id], map: "index_tags_on_initiator_id")
┃ 29 │ @@index([platform_id], map: "index_tags_on_platform_id")
┃ 30 │ @@index([show_in_matter], map: "index_tags_on_show_in_matter")
┃ 31 │ @@index([slug], map: "index_tags_on_slug")
┃ 32 │ @@index([title], map: "index_tags_on_title")
33 }this is my schema, but i need to create an expression index
CREATE INDEX index_tags_platforms_and_lower_title ON public.tags USING btree (platform_id, lower((title)::text));
how to do it?
- Assistant
To create an expression index using Prisma, you will have to take a slightly manual approach because Prisma currently doesn't directly support expression indexes within its schema files. Instead, you can use a native SQL migration to achieve this, which involves adding a custom SQL statement to your migration file.
Here's how you can go about it:
-
Create a Regular Migration:
First, make sure your Prisma schema is up-to-date without the expression index. Run:
npx prisma migrate dev --name "initial-setup"This will create the initial migration after comparing your schema with the current database state.
-
Add the Expression Index with a Native SQL Command:
After generating the initial migration, you will need to modify the migration files that Prisma creates. Find the newly created migration directory inside the
prisma/migrationsfolder. Let's assume it is named something like20231010123456_initial-setup/. -
Edit the SQL Migration File:
Open the SQL file in the generated migration directory, and add your custom SQL command for the expression index at the end of the file. It should look something like this:
-- This is your existing migration SQL that Prisma has generated -- Add the following line to create the expression index: CREATE INDEX "index_tags_platforms_and_lower_title" ON "public"."tags" USING btree ("platform_id", lower("title")); -
Apply the Migration:
Save the file and run the migration command again to apply your changes to the database:
npx prisma migrate dev
This approach adds a custom SQL line to the Prisma migration to create your expression index. It leverages the flexibility of SQL directly to accomplish what Prisma's DSL doesn't support natively yet. Always ensure that your migration scripts are carefully reviewed, especially when manual changes are involved.
-
- User
Database error: ERROR: operator class "gin_trgm_ops" does not exist for access method "gin"
this is from postgres, what is wrong?
- Assistant
The error message you're seeing indicates that PostgreSQL doesn't recognize the
gin_trgm_opsoperator class for the GIN (Generalized Inverted Index) access method, likely because thepg_trgmextension is not enabled in your database. Thepg_trgmextension provides functions and operators for determining the similarity of text based on trigram matching and is necessary for using thegin_trgm_opsoperator with GIN indexes.To resolve this issue, you need to enable the
pg_trgmextension in your PostgreSQL database. Here’s how you can do it:Steps to Enable the
pg_trgmExtension-
Connect to Your PostgreSQL Database:
Use a PostgreSQL client like
psql, DBeaver, or any other database management tool to connect to your database. -
Enable the Extension:
Run the following SQL command to enable the
pg_trgmextension:CREATE EXTENSION IF NOT EXISTS pg_trgm;This command creates the extension if it does not already exist, providing the necessary trigram functionality.
-
Re-Create Your Index:
After enabling the extension, drop the problematic index if it exists and create it again. Here’s an example SQL command for re-creating the index:
DROP INDEX IF EXISTS index_name; CREATE INDEX index_name ON your_table USING gin (your_column gin_trgm_ops);
By performing these steps, you should be able to use the
gin_trgm_opsoperator class with your GIN indexes. If you're deploying your application to multiple environments, make sure that thepg_trgmextension is enabled on each database instance. -
- User
bun --env.file=.env.development run prisma migrate dev Environment variables loaded from .env Prisma schema loaded from prisma/schema.prisma Datasource "db": PostgreSQL database "api", schema "public" at "localhost:5432"
Error: P3006
Migration
20241107070335_failed to apply cleanly to the shadow database. Error: ERROR: operator class "gin_trgm_ops" does not exist for access method "gin" 0: sql_schema_connector::validate_migrations with namespaces=None at schema-engine/connectors/sql-schema-connector/src/lib.rs:335 1: schema_core::state::DevDiagnostic at schema-engine/core/src/state.rs:276error: "prisma" exited with code 1 error: script "migrate" exited with code 1
- Assistant
The error you're encountering is related to the
gin_trgm_opsoperator, which requires thepg_trgmextension to be installed and activated in both your primary and your shadow database. Prisma uses a shadow database to validate migrations, and this error suggests that thepg_trgmextension is not installed in the shadow database.Here’s how you can resolve this issue:
Steps to Enable
pg_trgmin Both Databases-
Shadow Database Identification: By default, Prisma uses a shadow database with the same name as your primary database, suffixed with
-shadow. Make sure you’re aware of what the shadow database is called or how Prisma is configured to connect to it. -
Connect to Both Databases: Use a PostgreSQL client (like
psql, DBeaver, or pgAdmin) to connect to both your primary and shadow databases. -
Enable the
pg_trgmExtension: Run the following SQL command in both databases to ensure the extension is installed:CREATE EXTENSION IF NOT EXISTS pg_trgm; -
Retry the Migration: Once the extension is enabled on both databases, try running the migration command again:
bun --env.file=.env.development run prisma migrate dev
Additional Considerations
-
Database Permissions: Ensure that the user executing the migration has sufficient permissions to create extensions in both the primary and shadow databases.
-
Verify your Prisma Configuration: If you have customized Prisma's
shadowDatabaseUrlin yourprisma/schema.prismafile, make sure it correctly points to the actual shadow database you intend to use. -
Environment Variables: Check that your
.envfile specifies the correct connection strings for both databases, especially if you have any specific configurations for the shadow database.
By enabling the
pg_trgmextension in both the primary and shadow databases, you should be able to avoid this error and successfully apply your migrations. -
- User
but i just create initial migration on my dev machine. why pg_trgm is disabled?
- Assistant
The
pg_trgmextension might not be enabled by default on your PostgreSQL instance, especially if you or your database administrator have not explicitly installed it. The availability of extensions can depend on how PostgreSQL was installed, the version in use, or server configuration defaults. Here’s some background and potential reasons why thepg_trgmextension might not be enabled:Possible Reasons and Considerations
-
Default Installation:
- The
pg_trgmextension is not part of the core PostgreSQL installation and needs to be explicitly installed and enabled. If you set up a new PostgreSQL instance, extensions are not activated by default.
- The
-
Permissions:
- Enabling an extension requires superuser permissions. If you’re using a restricted user for your database operations, you might not have the necessary permissions to create extensions.
-
Separate Databases:
- Even if
pg_trgmis enabled on one database, new databases (like the shadow database) require you to enable it separately. Each database operates independently regarding extensions.
- Even if
-
Local Development Environment:
- If you're working in a local development environment, it's possible that you haven't configured it the same way as your production or other environments. Extensions need to be setup per database environment.
-
Docker/Containerized Setup:
- If you're using Docker or other containerized environments and spinning up fresh instances, each time the database is initialized, the extension must be set up if it’s not included in the Dockerfile or initialization scripts.
Steps to Verify and Enable Extensions
-
Check Installed Extensions: To see what extensions are enabled in your database, you can run:
SELECT * FROM pg_available_extensions; SELECT * FROM pg_extension; -
Install the Extension Globally: If you frequently start new databases for development or testing, consider scripting the creation of the
pg_trgmextension as part of your setup process. -
Include in Initialization: If you're using automated processes (like Docker Compose or scripts) to initialize your database for testing or development, make sure the script includes commands to enable
pg_trgm.
By ensuring that
pg_trgmis enabled on any PostgreSQL instance that will run your migrations, you can avoid issues related to missing operator classes and maintain consistency across different environments. -
- User
postgres is on docker-compose like this
services.databases
24 │ 1mi-postgres:
25 │ │ restart: always
26 │ │ image: postgres:17-alpine
27 │ │ container_name: 1mi-postgres
28 │ │ hostname: 1mi-postgres
29 │ │ environment:
30 │ │ │ - POSTGRES_USER=root
31 │ │ │ - POSTGRES_PASSWORD=password
32 │ │ │ - POSTGRES_MULTIPLE_DATABASES=auth,api
33 │ │ ports:
34 │ │ │ - '5432:5432'
35 │ │ volumes:
36 │ │ │ - ./ssl/:/etc/ssl/certs/
37 │ │ │ - ./db.sql:/docker-entrypoint-initdb.d/init_db.sql
38 │ │ │ - ./volumes/postgres:/var/lib/postgresql/data
39 │ │ healthcheck:
40 │ │ │ test: ['CMD-SHELL', 'pg_isready', '-d', 'auth', '-U' , 'postgres']
41 │ │ │ interval: '10s'
42 │ │ │ timeout: '30s'
43 │ │ │ retries: 5
44 │ │ │ start_period: '20s'
45 │ │ networks:
46 │ │ │ - 1mi.nethow to enable it
- Assistant
To enable the
pg_trgmextension in a PostgreSQL instance running within a Docker container, you can utilize the initialization scripts feature of PostgreSQL Docker images. This feature allows you to execute SQL commands automatically when the database is first initialized.Here's how you can modify your
docker-compose.ymlsetup to include the creation of thepg_trgmextension:Steps to Enable
pg_trgmvia Docker-Compose-
Create an Initialization SQL Script:
Create a new SQL file, for example
init-extensions.sql, with the following content to enable thepg_trgmextension in your databases:CREATE EXTENSION IF NOT EXISTS pg_trgm; -
Modify Your Docker-Compose Configuration:
Ensure the initialization script is copied into the Docker container and executed on startup. You do this by mounting the script into the
/docker-entrypoint-initdb.d/directory. This directory is automatically processed by the official PostgreSQL Docker image during the database initialization phase.Update your
docker-compose.ymlas follows:services: 1mi-postgres: image: postgres:17-alpine container_name: 1mi-postgres restart: always environment: - POSTGRES_USER=root - POSTGRES_PASSWORD=password - POSTGRES_MULTIPLE_DATABASES=auth,api ports: - '5432:5432' volumes: - ./ssl/:/etc/ssl/certs/ - ./db.sql:/docker-entrypoint-initdb.d/init_db.sql - ./init-extensions.sql:/docker-entrypoint-initdb.d/init-extensions.sql - ./volumes/postgres:/var/lib/postgresql/data healthcheck: test: ['CMD-SHELL', 'pg_isready', '-d', 'auth', '-U', 'root'] interval: 10s timeout: 30s retries: 5 start_period: 20s networks: - 1mi.net -
Recreate Your Docker Container:
For the changes to take effect, especially if your database has already been initialized, you’ll need to recreate your database container. Be aware that doing this will remove all data in the existing container unless you have persistent volumes configured.
Execute the following commands to stop, remove, and recreate your container:
docker-compose down docker-compose up --buildIf your data volume is persistent and already contains a database, you may need to execute the extension creation manually using a PostgreSQL client (like
psql) connected to the database:docker-compose exec 1mi-postgres psql -U root -d auth -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;" docker-compose exec 1mi-postgres psql -U root -d api -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
By following these steps, your PostgreSQL instance running in Docker should have the
pg_trgmextension enabled, allowing you to create indices usinggin_trgm_opswithout encountering the error you've seen. -
- User
is it enabled per database or per cluster?
- Assistant
The
pg_trgmextension, like most PostgreSQL extensions, is enabled per database, not per cluster. This means you need to create the extension individually in each database where you want its functions to be available.Here’s a breakdown of what this means for your setup:
-
Per Database:
- Each database that needs to use the
pg_trgmextension must have theCREATE EXTENSION pg_trgm;command run within it. - If you have multiple databases (e.g.,
authandapias mentioned in your setup), you must ensure the extension is enabled in each one.
- Each database that needs to use the
-
Initialization Script:
- If you're using an initialization script in Docker to automate this setup (
init-extensions.sql), the extension command will be run in each database listed inPOSTGRES_MULTIPLE_DATABASESif your init script handles each database explicitly.
- If you're using an initialization script in Docker to automate this setup (
-
Manual Enabling:
- If databases are already initialized, or if you add a new one later, you’ll need to manually connect to that database and run the
CREATE EXTENSION pg_trgm;command.
- If databases are already initialized, or if you add a new one later, you’ll need to manually connect to that database and run the
To simplify the process when working with Docker, you can modify your initialization scripts or entrypoint configuration to loop through each database and apply the extension. This approach ensures that all necessary databases have the
pg_trgmextension enabled as soon as they are created.For existing databases, you can use a tool like
psqlto connect to each database and enable the extension:docker-compose exec 1mi-postgres psql -U root -d auth -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;" docker-compose exec 1mi-postgres psql -U root -d api -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"These commands will ensure the extension is enabled for each of your specified databases.
-
- User
if i have this db.sql for init in docker-compose creation like this
CREATE DATABASE api ENCODING 'UTF8' LC_COLLATE 'ru_RU.UTF-8' LC_CTYPE 'ru_RU.UTF-8' TEMPLATE template0; CREATE DATABASE auth ENCODING 'UTF8' LC_COLLATE 'ru_RU.UTF-8' LC_CTYPE 'ru_RU.UTF-8' TEMPLATE template0;
how to add in this file?
- Assistant
To automate the process of enabling the
pg_trgmextension for multiple databases upon initialization using yourdb.sqlscript, you can extend your SQL script by adding the necessary commands to switch to each database and create the extension.Here's how you can modify your
db.sqlscript:-- Create the databases CREATE DATABASE api ENCODING 'UTF8' LC_COLLATE 'ru_RU.UTF-8' LC_CTYPE 'ru_RU.UTF-8' TEMPLATE template0; CREATE DATABASE auth ENCODING 'UTF8' LC_COLLATE 'ru_RU.UTF-8' LC_CTYPE 'ru_RU.UTF-8' TEMPLATE template0; -- Connect to the api database and create the pg_trgm extension \connect api CREATE EXTENSION IF NOT EXISTS pg_trgm; -- Connect to the auth database and create the pg_trgm extension \connect auth CREATE EXTENSION IF NOT EXISTS pg_trgm;Explanation:
-
Database Creation:
- You start by creating each database with your specified encoding and locale settings.
-
Connect to Each Database:
- The
\connect <database_name>command is used to switch the context to the specified database. This allows you to run subsequent commands in the context of that database.
- The
-
Create the Extension:
- For each database, you run the
CREATE EXTENSION IF NOT EXISTS pg_trgm;command to enable thepg_trgmextension.
- For each database, you run the
Usage:
-
Ensure this updated
db.sqlscript is correctly placed in your Docker setup:services: 1mi-postgres: image: postgres:17-alpine container_name: 1mi-postgres restart: always environment: - POSTGRES_USER=root - POSTGRES_PASSWORD=password ports: - '5432:5432' volumes: - ./ssl/:/etc/ssl/certs/ - ./db.sql:/docker-entrypoint-initdb.d/init_db.sql - ./volumes/postgres:/var/lib/postgresql/data healthcheck: test: ['CMD-SHELL', 'pg_isready', '-d', 'auth', '-U', 'root'] interval: 10s timeout: 30s retries: 5 start_period: 20s networks: - 1mi.net
With this setup, the
pg_trgmextension will be initialized and enabled automatically for both theapiandauthdatabases whenever you spin up your PostgreSQL service with Docker Compose. This approach ensures that any repeated start or fresh initialization will consistently configure the databases as needed. -
- User
how to check if it enables from psql
- Assistant
To check if the
pg_trgmextension is enabled in a PostgreSQL database using thepsqlcommand-line tool, you can query thepg_extensionsystem catalog. Here’s how you can do this:-
Open a Terminal and Connect to Your Database Using
psql:Run the following command, replacing
<user>,<database>, and<host>with your PostgreSQL username, database name, and host:psql -U <user> -d <database> -h <host> -WYou'll be prompted for the password if required. For local development, you might use
localhostas the host. -
Query the
pg_extensionCatalog:Once you are connected to the database, you can check for the
pg_trgmextension using the following query:SELECT * FROM pg_extension WHERE extname = 'pg_trgm'; -
Interpret the Results:
- If the query returns a row with details, it means the
pg_trgmextension is enabled in the current database. - If the query returns no rows, then the extension is not enabled in that database.
- If the query returns a row with details, it means the
-
Exit
psql:When you're finished, you can exit
psqlby typing:\q
Example
Here's what the process might look like in a terminal session:
$ psql -U root -d api -h localhost -W Password: psql (14.1) Type "help" for help. api=# SELECT * FROM pg_extension WHERE extname = 'pg_trgm'; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ---------+----------+--------------+----------------+------------+-----------+-------------- pg_trgm | 10 | 2200 | f | 1.5 | | (1 row) api=# \qIn this example, the presence of a row in the query result confirms that the
pg_trgmextension is indeed enabled for theapidatabase. If you need to check another database, connect to that usingpsqland repeat the query. -
- User
what is this shadow???
bun --env.file=.env.development run prisma migrate dev Environment variables loaded from .env Prisma schema loaded from prisma/schema.prisma Datasource "db": PostgreSQL database "api", schema "public" at "localhost:5432"
Error: P3006
Migration
20241107071830_failed to apply cleanly to the shadow database. Error: ERROR: operator class "gin_trgm_ops" does not exist for access method "gin" 0: sql_schema_connector::validate_migrations with namespaces=None at schema-engine/connectors/sql-schema-connector/src/lib.rs:335 1: schema_core::state::DevDiagnostic at schema-engine/core/src/state.rs:276error: "prisma" exited with code 1 error: script "migrate" exited with code 1
- Assistant
The term "shadow database" in the context of Prisma refers to a separate, temporary database that Prisma uses during development migrations to ensure that the migration scripts will apply correctly before applying them to your actual development database. This is a safeguard to prevent errors from corrupting your main development database.
Why Use a Shadow Database?
-
Validation of Migrations: It allows Prisma to simulate the migration process, ensuring that everything can be applied without errors.
-
Safe Migration Iterations: Using a shadow database helps maintain the integrity of your primary development database by avoiding partial or erroneous migrations.
-
No Impact on Production Data: During development, you might frequently iterate on migrations, and using a shadow database ensures that these experimental changes do not impact your primary data.
Problem Highlighted by the Error
The error you're encountering indicates that the
pg_trgmextension is not installed in the shadow database environment. This extension is required because the migration involves operations dependent ongin_trgm_ops.Resolving the Issue
-
Ensure
pg_trgmis Enabled in the Shadow Database:Since Prisma automatically creates and manages the shadow database, make sure that your setup includes steps to enable necessary extensions in that environment.
-
Default Configuration:
By default, Prisma creates a shadow database with a name based on your main database name, typically appending
-shadow. However, this can vary based on your configuration. -
Prisma Configuration:
In the
schema.prismafile, you can explicitly define the shadow database URL if you need specific control, like using a different database altogether or avoiding overlap with real data:datasource db { provider = "postgresql" url = env("DATABASE_URL") shadowDatabaseUrl = env("SHADOW_DATABASE_URL") // Define this in your .env or `bun` environment configuration }Set
SHADOW_DATABASE_URLto a PostgreSQL instance which has thepg_trgmextension enabled. -
Manage Database Setup:
Similar to your main database, you need to have the extensions like
pg_trgmset up for the shadow database. You can do this through initialization scripts or manually using SQL commands. If using Docker, ensure the initialization process covers both the main and shadow databases:# For a Docker setup, run: docker-compose exec 1mi-postgres psql -U root -d your_shadow_db_name -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
Properly configuring the environment for your shadow database will allow Prisma to successfully validate migrations without hitting dependency-related errors.
-