firebase / firebase/firebase-tools
[dataconnect] @index directive should support operator classes (gin_trgm_ops) — every deploy proposes DROP on manually-created pg_trgm indexes
- 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
Assessment
This issue has not been assessed yet.