The fake test database taxes your real types

The model would not even build in the test project, and the error made no sense until I remembered which database the tests were running against.

Production was Postgres with a vector extension. Tests were in-memory SQLite, because that is fast and needs no setup. The problem is that one of my columns was a vector type that only exists because of the extension, and SQLite has never heard of it.

A column that only exists on one engine#

The idea behind an in-memory database for integration tests is decent. Real database semantics, no external service, tests that run in milliseconds. It works well right up to the point where production depends on something the fake engine cannot provide.

My prod-only column was the whole point of the feature. It stored an embedding as a vector so I could do similarity search using the extension’s index. SQLite has no such type, so when the test project tried to build the model, it failed on a column type it could not map.

I had two cooperating hacks to get the tests green again.

The first was to gate the extension behind a provider check. If the provider was Postgres, register the vector type. If it was anything else, skip it. That stopped the model from demanding a type the test engine did not have.

The second was to store the vector as a serialised JSON or text column through a value converter, so the same field could round-trip on both engines. On Postgres it was a real vector. On SQLite it was a string that looked like a list of numbers.

// provider decides whether the column is a real vector or just text
if (provider == "postgres")
    entity.Property(x => x.Embedding).HasColumnType("vector(768)");
else
    entity.Property(x => x.Embedding)
          .HasConversion(v => Serialise(v), s => Deserialise(s));

Tests went green. I felt clever for about a day.

What the green tests quietly threw away#

Here is the part the passing suite did not tell me. The moment the value converter was in effect, the column was a string, and a string has no vector index.

So every similarity search that ran against the converted column could not use the index. It silently fell back to pulling every row into memory and computing the distances in code. On a handful of rows in a test, you never notice. On a real table it is the exact slow path the extension existed to avoid. For a user that is the difference between a search that answers instantly and one that grinds for seconds as the data grows, and the passing suite would never once warn you.

A fake test database does not just skip a prod-only feature. It quietly bills you for it by taxing away the fast path you were paying the extension to give you.

The tests were not wrong about correctness. The similarity results were the same numbers either way. What the tests hid was that the indexed query, the actual reason I added the extension, was never exercised anywhere the converter applied.

Be honest about the trade#

A provider-conditional model is a reasonable way to keep a mixed-engine test suite green. I would do it again. But be honest with yourself about what the condition trades away.

In my case the trade was the indexed query path, which was the entire value of the prod-only feature. Green tests told me the code was correct. They said nothing about whether it was still fast, because the thing that made it fast did not exist on the engine the tests used.

The fix was to stop pretending one suite could cover both. I kept the fast SQLite suite for everything that did not touch the vector, and routed the real similarity search to a smaller suite running on an actual Postgres with the extension. That suite is slower and needs a real database, but it is the only one that tests the thing I actually built.