firebase / firebase/firebase-tools

[dataconnect] @index directive should support operator classes (gin_trgm_ops) — every deploy proposes DROP on manually-created pg_trgm indexes

Open
#10,571 3 comments 1 reaction 1 assignee Claimed by @itsrakhil View on GitHub
api: dataconnect type: feature request
Dominant language
TypeScript
Stars
4.5k
Forks
1.3k
Avg merge
1d 12h
Merged PRs (30d)
84

Description

### Issue

The `@index` directive in `schema.gql` doesn't support PostgreSQL operator classes (e.g. `gin_trgm_ops`). For fuzzy-search use cases requiring `pg_trgm` trigram indexes, the only path today is to create them directly in Postgres via raw SQL (`CREATE INDEX foo ON tbl USING gin (col gin_trgm_ops)`).

Because those indexes aren't expressible in `schema.gql`, every subsequent `firebase deploy --only dataconnect` proposes `DROP INDEX foo` as a "destructive change" and aborts.

### Reproduction

1. In Postgres: `CREATE EXTENSION pg_trgm; CREATE INDEX my_trgm_idx ON paper USING gin (title gin_trgm_ops);`
2. Add nothing related to the index in `schema.gql`.
3. Run `firebase deploy --only dataconnect --force`.
4. Observe:

```
⚠ dataconnect: PostgreSQL schema is incompatible with the SQL Connect Schema.
Those SQL statements will migrate it to be compatible:

/** Destructive: drop index "my_trgm_idx" from table: "paper" */
DROP INDEX "public"."my_trgm_idx"

Error: Command aborted. Your database schema is incompatible with your SQL Connect schema.
```

### Why `schemaValidation: COMPATIBLE` doesn't help

The docs at https://firebase.google.com/docs/sql-connect/manage-schemas-and-connectors explicitly list `DROP SCHEMA / DROP TABLE / DROP COLUMN` as the operations COMPATIBLE protects. `DROP INDEX` is not in that list, and verified empirically: with `schemaValidation: COMPATIBLE` set and `--force` passed, the deploy still aborts on index-drop proposals.

### Use case

Trigram indexes are how mature platforms (Algolia, Typesense internally, Elasticsearch with the `wildcard` field type) deliver typo-tolerance / fuzzy search on text columns. There's no SQL Connect-native alternative — `@searchable` is FTS-only (no edit-distance), and Native SQL (Preview) addresses query side, not schema.

### Prior art

Prisma had the same issue: https://github.com/prisma/prisma/issues/7515. Their resolution allowed `@@index([col(ops: raw("gin_trgm_ops"))], type: Gin)` in the Prisma schema, which made migrate aware of the index without it being the literal one it manages.

### Proposed solutions (any of these would work)

1. **Extend `@index`** to accept an `ops` argument: `@index(type: GIN, ops: "gin_trgm_ops")`. Mirrors Prisma's resolution exactly.
2. **A `@preserveIndex(name: String)` directive** at the `@table` level — declares "an index of this name exists, please don't touch it".
3. **A `dataconnect/sql/` migration directory** where arbitrary SQL DDL lives alongside the GraphQL schema. Schema-diff would mark anything created via these files as managed.

Option 1 is closest to existing primitives and would be the smallest API surface change.

### Current workaround (not great)

Patching the deploy workflow to grep stdout for `Destructive: drop index .*_trgm_idx` and bypass the abort if those are the only destructive proposals. Brittle (depends on CLI output wording) but it's the only path that doesn't silently break the indexes.

### Environment

- `firebase-tools` 15.8.0
- Cloud SQL Postgres 16
- `dataconnect.yaml` with `schemaValidation: "COMPATIBLE"` set correctly at the `postgresql` level

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.