Blog
What Clean Architecture Bought Me: a Firebase SQL Connect Trial
A Saturday morning, fresh coffee, a new directory in the monorepo: packages/on_court_tennis_sql_connect/. The first line in the first file I typed read:
import 'package:firebase_data_connect/firebase_data_connect.dart';
That import was the start of a database swap. Firebase Data Connect — recently rebranded as Firebase SQL Connect — is Postgres under the bonnet with a GraphQL layer on top. I wanted to know whether it could be the storage layer for On Court Tennis, the app I’ve been building, in place of Firestore. Not in production. Not even in beta. Just enough to know what it felt like.
The interesting bit isn’t that I tried. It’s that the trial was a Saturday-morning job.
The On Court Tennis codebase is a Flutter monorepo. The domain layer — entities, usecases, repository interfaces — lives in its own package. Every concrete implementation of those repositories lives in a separate package, today named packages/on_court_tennis_firebase. The repository interfaces are the contract; everything behind them is replaceable.
I made that choice two years ago when I started the project. Clean architecture pays in coins minted years before you spend them. Keeping Firestore behind interfaces had no immediate payoff — the alternative was a slightly smaller codebase and a faster first version. The payoff was the morning I created a sibling package, dropped in the same interfaces, and started pointing them at SQL Connect instead.
A compile-time bool.fromEnvironment('USE_SQL_CONNECT') flag selects which DI config the app boots with. It has to be compile-time, not Remote Config — DI runs before Remote Config exists. Chicken-and-egg avoided.
The repository-pattern bet had paid off. Now the actual work began.
The catch with moving from Firestore to Postgres is that you have to design a schema, and an idiomatic Postgres schema for this app turns out to be unusual.
A Competition has competitors. A competitor is polymorphic — sometimes a Player, sometimes a Pair, sometimes a Team. In Firestore that’s easy: a competitorId string and a type discriminator, resolved at the application layer. In a relational schema you have a choice: model the polymorphism with discriminated joins to three tables, or keep competitorId as a plain string and resolve it in the domain layer.
I went with the second. The schema stays clean; the domain layer absorbs the complexity it always did.
For that to work, every primary key has to be a string. And here’s where the trial almost ended.
Pair.pairId is not a UUID. It’s sorted([player1Id, player2Id]).join('-'). The same two players, in either order, should resolve to the same pair row — otherwise match history fragments and the head-to-head stats break. The ID isn’t an opaque identifier; it’s an invariant.
Postgres-flavoured GraphQL wanted me to declare every key as UUID! @default(expr: "uuidV4()"). Every guide I read said the same thing. But the moment Player.playerId is a UUID and Pair.pairId is a String, the polymorphic competitorId column has to pick which one to join to. UUID joins to Player and Team; string joins to Pair. There is no column type that joins to both.
For an hour I assumed I’d boxed myself in. The clean way out was to make Pair.pairId opaque and store the player-pair invariant somewhere else — a unique constraint, maybe, or a generated column. I didn’t like it. The Pair ID being meaningful is part of how the domain reasons about pairs.
I tried something the docs didn’t promise would work:
type Player @table(name: "players", key: "playerId") {
playerId: String! @default(expr: "uuidV4()")
}
A String! column with a uuidV4() default — even though @default(expr: ...) is documented for UUID! columns. SQL Connect compiled it without complaint. The generated migration read:
"player_id" text NOT NULL
DEFAULT replace((uuid_generate_v4())::text, '-'::text, ''::text)
A text column with a hyphen-stripped UUID as its default. Polymorphism preserved. Deterministic Pair IDs preserved. Postgres still generates IDs on insert for the tables that want it. The whole tension had been about choosing the right scalar.
It took bisecting back to a minimal schema to trust this was real, because the docs don’t mention it. But it works.
Staging now boots on SQL Connect when I flip the compile flag. Production is still firmly on Firestore — the trial is for me, not for users. What I have is a parallel data layer that compiles and runs against the same domain interfaces, with the option of comparing the two side by side as I find out where SQL Connect does well and where it strains.
The Saturday morning was cheap because of a decision made two years earlier. That’s the part of clean architecture worth paying for — not the dogma about layer counts, but the optionality you buy yourself when something interesting comes along.
The afternoon was cheap because of a different kind of decision: knowing what the domain actually wanted from its IDs before reaching for the obvious schema. A deterministic Pair ID isn’t a clever trick — it’s the invariant that holds the rest of the data together. Recognising that before committing to UUIDs everywhere is what kept the trial alive.
If your codebase is one architectural choice away from being able to trial new infrastructure without putting production at risk, and you’re not sure which choice that is, that’s a conversation I have most weeks.