Can t we add custom indexes to our tables out of schema file Prisma #orm-help

Can’t we add custom indexes to our tables out of s...

Yilmaz Ugurlu

10/07/2021, 3:34 PM

Can’t we add custom indexes to our tables out of schema file? It says here: https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#index

While you cannot configure these option in your Prisma schema, you can still configure them on the database-level directly.

Bu when I do and run

migrate

I get:

Copy code

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[*] Changed the `event` table
  [+] Added index on columns (created_at, event_name)
  [+] Added index on columns (created_at, event_name)
  [+] Added index on columns (created_at, event_name)

So, what am I doing wrong here? Any idea?

Ryan

10/08/2021, 5:43 AM

@Yilmaz Ugurlu 👋 You need to add them to the migration files created. If you’re using Migrate, here’s what you can do: 1. Create the migration using

prisma migrate dev --create-only

2. Add the indexes to the created

.sql

file 3. Run

prisma migrate dev

to apply the migration and the indexes will be added.

Yilmaz Ugurlu

10/08/2021, 7:31 AM

It kind of worked but also it didn’t. Prima created another migrations and added

Drop index

commands.

Copy code

-- DropIndex
DROP INDEX "api_an_idx";

-- DropIndex
DROP INDEX "user_an_idx";

Yilmaz Ugurlu

10/08/2021, 7:31 AM

the file only contains these

Yilmaz Ugurlu

10/08/2021, 7:32 AM

Copy code

model Event {
  id         String   @id @default(uuid()) @db.VarChar(40)
  createdAt  DateTime @default(now()) @map(name: "created_at")
  event_name String   @db.VarChar(64)
  entity     Json

  @@map(name: "event")
}

this is my schema btw.

Ryan

10/08/2021, 7:35 AM

What’s the custom index that you’re adding?

Yilmaz Ugurlu

10/08/2021, 7:41 AM

Copy code

CREATE EXTENSION if not exists btree_gin;

create index user_an_idx
    on event using gin (created_at, event_name, (entity -> 'user'));

create index api_an_idx
    on event using gin (created_at, event_name, (entity -> 'api_key'));

Yilmaz Ugurlu

10/08/2021, 7:42 AM

I even added

@@ignore

to pass client code creation, that I can access to this table by raw sql.

Yilmaz Ugurlu

10/08/2021, 7:44 AM

this is postgresql btw.

Yilmaz Ugurlu

10/08/2021, 9:22 AM

I’ve fixed like this This is the schema

Copy code

model Event {
  createdAt  DateTime @default(now()) @map(name: "created_at")
  event_name String   @db.VarChar(64)
  entity     Json
  @@index(fields: [createdAt, event_name], map: "user_an_idx")
  @@index(fields: [createdAt, event_name], map: "api_an_idx")

  @@map(name: "event")
  @@ignore
}

run

prisma migrate dev --create-only

then added:

Copy code

CREATE EXTENSION if not exists btree_gin;

drop index if exists user_an_idx;
drop index if exists api_an_idx;

-- index for user events
create index user_an_idx
    on event using gin (created_at, event_name, (entity -> 'user'));
-- index for api events
create index api_an_idx
    on event using gin (created_at, event_name, (entity -> 'api_key'));

Now reflected all the changes without dropping the index.

💯 1

Yilmaz Ugurlu

10/08/2021, 9:22 AM

thanks.

👍 1

3 Views

Open in Slack

Previous Next