Five Things Firebase Data Connect Doesn't Tell You

“Invalid SQL statement.”

That’s the whole error. No table name, no column, no hint about which part of the request the server disliked. The first time staging booted on Firebase Data Connect — the Postgres-backed layer Firebase has lately been rebranding as SQL Connect — and I saved a player, that’s what came back. Over the following weeks I saw the same four words for at least three completely different root causes.

Back in May I wrote about the Saturday morning that started this: a trial data layer for On Court Tennis, sitting behind the same repository interfaces as Firestore, selected by a compile-time flag. The trial grew into a proper migration. These are the five walls I hit turning it into one — none of which the documentation mentions.

1. Never call a generated builder method with null

The Dart SDK generates builder methods for optional mutation variables, backed by an Optional<T> with two states: UNSET (you never called the method; the field is omitted from the request) and SET (you called it; the field is serialised — even if you passed null).

So this seemingly harmless line:

builder.playerId(p.playerId.isEmpty ? null : p.playerId);

sends "playerId": null when the id is empty, and the server rejects it. UNSET and SET-null are different requests.

It bit me twice in one day. From my own notes, paraphrased:

Me: Calling .limit(null) marks the Optional SET-null, which sends "limit": null. DC rejects it. Skip the builder call when you don’t want a limit.

Also me, four hours later: Calling .playerId(null) marks the Optional SET-null, which sends "playerId": null. DC rejects it. Skip the builder call when you don’t have an id.

Same root cause, two different surface errors. The rule that went into the codebase comments: never pass null to a generated builder. If you want the field unset, don’t call the method — wrap it in an if.

2. Split _upsert into Create and Upsert when the key is auto-generated

Even with the builders fixed, player_upsert kept failing. The mutation took an optional $playerId, and when the variable was omitted, GraphQL semantics substituted null inside the data block — so Data Connect saw data: { playerId: null, ... }. An _upsert needs a non-null key to compute ON CONFLICT, and you get the generic error again.

The fix is structural: one CreatePlayer mutation using _insert with no key in the data block (the column default fires), and one UpsertPlayer where the key is required and non-null. The Dart side routes on id presence. And strip optional id arguments from your _insert mutations too — they’re the same landmine waiting for the first caller to pass null.

3. JSONB inputs need Any, not String

The longest wall — an afternoon of bisecting. avatarColour: String @col(dataType: "jsonb") works fine for reads: the column comes back as a JSON string and you decode it yourself. But the moment that column appears in an _insert mutation, the SQL generator chokes — even when no value is supplied.

The bisect that found it: strip CreatePlayer to its four required fields — works. Add the optional strings back — works. Dates — works. JSONB columns — fails. Three or four hours, but an unambiguous smoking gun.

The right input scalar is Any, which becomes AnyValue in the generated Dart and tells Data Connect to treat the value as a JSON tree rather than a string to cast. The if-guard from wall one still applies to the builder call.

4. Schema type changes are destructive

Change a column’s type and firebase deploy --only dataconnect generates fresh CREATE TABLE statements — no DROP, no IF NOT EXISTS — and bails with relation "players" already exists. The way through is a manual DROP TABLE ... CASCADE under the firebaseowner role, then dataconnect:sql:migrate --force. Acceptable in staging; plan for it before you have production data.

Two tooling footnotes from the same trench. dataconnect:sql:shell is not psql — no \dt, no inline comments, and it wants spaces around =. And dataconnect:sdk:generate silently exits 0 when run from the repo root instead of firebase/ — a no-op that cost me an hour before I noticed the file timestamps weren’t moving.

5. String! with a uuidV4() default actually works

The trick that kept the whole schema viable: @default(expr: "uuidV4()") is documented for UUID! columns but works on String! too, compiling to a hyphen-stripped UUID default in Postgres. That’s what let polymorphic string foreign keys coexist with server-generated ids — the full story is in the May post.

---

Staging now runs entirely on Data Connect behind the flag; production is still on Firestore while the two run side by side. None of these walls changed my view of the platform — they’re the tax you pay on young infrastructure, and each one is cheap once you know it’s there.

The transferable lesson is the bisect. When a platform hands you one generic error for every failure, stop reading the message and start shrinking the request: strip to the required minimum, add fields back in batches from least to most suspicious. Three steps usually find the culprit.

If you’re weighing up a young piece of infrastructure and want a second pair of eyes on where the walls are likely to be, that’s a conversation I have most weeks.