Build your research spike as running code: 8 blockers a doc missed

Every engineering org has a shelf of design documents that confidently described systems nobody ever built. The format’s weakness is structural: a document is where you write down what you believe, and the whole reason you’re writing it is that you don’t know yet. On 12 August 2026 Simon Willison published a small library that answers the same class of question in a better format, and it is worth copying the format even if you never touch the library.

What is a research spike?

A research spike is time-boxed investigative work whose deliverable is an answer to a feasibility question — not a feature. It ends when the question is answered, and the code it produces is explicitly disposable. The term comes from Extreme Programming, where the point was always to buy information rather than functionality.

An executable research spike answers that question with running code plus a written conclusion, so a reviewer can rerun the claim instead of trusting it. The code is the evidence; the document is the interpretation.

TL;DR

Ship your next feasibility investigation as running code with a RESEARCH.md beside it, not as a design doc. alchemy-utils 0.1a0 is a public worked example: it asks whether the sqlite-utils API can sit on SQLAlchemy Core across SQLite, PostgreSQL and DuckDB, answers yes, and documents eight concrete blockers that only appear once code runs — six of them in DuckDB alone. The payoff isn’t the library, which is explicitly alpha; it’s that the spike converts “probably feasible” into “three to five weeks, and here is the list.”

Why the cheaper artifact is now the code

The design doc won by default for thirty years because writing was cheap and implementing was expensive. You wrote the doc precisely so you wouldn’t have to build the thing twice.

That ratio has moved. A throwaway implementation of a well-specified API surface is now a day’s work with a coding agent — the alchemy-utils README says outright it was built using GPT-5.6 Sol Ultra and Codex — while the cost of good judgement about what to build has barely moved at all. When the expensive half of the pair gets 10× cheaper and the other half doesn’t, you reorder the work. Spike first, document second, and let the document report measured results instead of predicted ones.

This is not an argument against writing things down. alchemy-utils ships a RESEARCH.md that is, structurally, a design doc — conclusion, trade-offs, recommended plan, estimate. The difference is that every claim in it was tested before it was typed. It’s the opposite of the spec-driven approach, and both are defensible; what isn’t defensible any more is a doc that predicts and then never gets checked.

A design doc records what you believe. An executable spike records what happened. Only one of those survives contact with DuckDB.

The worked example, in one paragraph

sqlite-utils has a table-first Python API — db["people"].insert(...), .upsert(), .columns_dict, .pks — that is pleasant enough that people keep wishing it worked on PostgreSQL. The spike’s question: can that API sit on SQLAlchemy Core and behave the same on three engines? The answer shipped as an installable package, pip install alchemy-utils with [postgresql] and [duckdb] extras, plus a CLI (tables, schema, columns, indexes, foreign-keys, rows, get, count, insert, upsert, update) that accepts a SQLite path or any SQLAlchemy URL. The API contract was derived from sqlite-utils 4.1.1 at commit 43d5d33, and the whole thing is Apache-2.0 on Python 3.10+.

That paragraph is the part a design doc could have written. Everything below it is the part only running code produced.

The eight blockers

Two are cross-engine semantics. Six are DuckDB.

#	Blocker	Engine	What it forced
1	SQLAlchemy’s generic insert() has no on_conflict_do_update()	Cross-engine	Per-dialect insert objects; upsert is not a portable operation
2	One multi-values upsert with duplicate keys: PostgreSQL rejects it, DuckDB keeps the first value	Cross-engine	Ordered upserts inside a single transaction, for consistent last-write-wins
3	duckdb-engine renders an integer primary key as SERIAL, which DuckDB rejects	DuckDB	—
4	IDENTITY plus a primary-key constraint is unsupported in the tested release	DuckDB	A DuckDB Sequence as the server default, with RETURNING to keep last_pk portable
5	Primary-key reflection returns no constrained columns	DuckDB	Ordered keys read from table_constraints and key_column_usage
6	Index reflection returns an empty list and emits a warning	DuckDB	duckdb_indexes()
7	Native JSON reflects as VARCHAR, defeating SQLAlchemy’s JSON decoder	DuckDB	Native types read from duckdb_columns()
8	UPDATE row counts are not reliable	DuckDB	update() validates existence with get() before issuing the update

Blockers 3 through 8 are all against duckdb-engine 0.17.0, whose own README keeps a caveats section — the incompleteness is known, but “reflection is incomplete” in a caveats list and “your primary keys come back empty” in a test run are not the same information.

Read the distribution, not just the count. A design doc that said “support SQLite, PostgreSQL and DuckDB” would have carried three engines as three equal line items. In practice, 75% of the integration risk sat in one of them, and none of it sat in the two everyone assumed would be hard. No amount of careful reading produces that number. Running the tests produces it in an afternoon.

Where the portability line actually fell

The other thing you can only learn by building: where the abstraction seam goes.

SQLAlchemy Core turned out to own more than expected — identifier quoting, Python and SQLAlchemy type declarations, single and compound constraints, ordinary INSERT/UPDATE/SELECT, transaction contexts, server-side RETURNING for generated keys, and reflected columns, nullability, primary keys and foreign keys on SQLite and PostgreSQL. That let the shared Table class own record normalization, schema inference, chaining, last_pk, primary-key validation and the entire public API.

What it didn’t own is the list above, so each engine got its own Database subclass and the shared Table never inspects a dialect name.

The spike’s own conclusion is the sentence worth stealing: the architecture should not attempt to make SQLAlchemy itself disappear. That is a design decision, arrived at by trying the opposite first. It is also the kind of sentence that reads as obvious in a document and expensive in a codebase, which is exactly why you want it discovered on day three rather than in month two of the gap between a working prototype and a production system.

The output that actually matters: a priced estimate

Here is the part most spikes skip and this one gets right. RESEARCH.md ends with a scoped estimate in three bands:

• Demonstrated now — create, insert, insert_all, upsert, upsert_all, update, schema inference, generated integer IDs, compound keys, and the introspection contract, on all three engines.
• A documented v0.1 — roughly three to five weeks for an experienced engineer, across seven named work items: publish the compatibility contract, add streaming chunks, centralize value adaptation for dates/decimals/UUIDs/JSON, add a transaction API with savepoints, complete create options, run CI across engine versions, and port compatibility tests from sqlite-utils itself.
• Broad sqlite-utils parity — transforms, hash IDs, extracts, conversions, FTS, trigger details — a multi-month effort, to be scoped separately.

Be honest about what that estimate is: one experienced engineer’s judgement, written down, not a measurement. Its value isn’t precision. Its value is that it is falsifiable and attached to a named list of seven things — so when week six arrives, you can point at which item ran long instead of arguing about whether the estimate was ever real.

Compare that to how the same decision usually gets made: someone asks whether we could put this API on Postgres, three people say “probably, but DuckDB might be weird,” and the question stays open for a quarter. That’s the same information content as blocker 3, delivered six weeks later and with no test behind it.

How to run a research spike this week

1. Write the question as a yes/no with a stake. Not “investigate multi-engine support” but “can the same table API behave identically on SQLite, PostgreSQL and DuckDB without dialect checks in the shared layer?” A spike with no falsifiable question becomes a hobby project.

2. Time-box it before you start, in days. The deliverable is a decision. Code volume is not progress.

3. Test against the real engines, not one. The entire finding here — the 6/2/0/0 split — exists only because all three were exercised. A spike run against your favourite engine reports your favourite engine’s opinion.

4. Keep the compatibility source in view. This spike read sqlite-utils 4.1.1 at a pinned commit and lifted its highest-value tests as the contract. Deriving the contract from an existing implementation beats inventing one.

5. Write RESEARCH.md as you go, in four parts: conclusion first, what was genuinely portable, where adapters were required, and the priced plan. If you write it afterwards you will write the version you wish had happened.

6. Say the estimate out loud, with bands. Demonstrated / hardened / full parity. One number with no bands invites everyone to hear the one they wanted.

7. Label the artifact. “An executable research spike,” in the first line of the README, “not a published compatibility promise.” That sentence is what stops the spike from being deployed by an optimist.

If your team runs agents on this kind of work, the same rules that make a repo legible to a human make it legible to the agent — the project context file you give the agent is where the pinned versions, the test command and the “this is a spike” label belong.

The three ways this goes wrong

The spike ships. Someone finds pip install alchemy-utils and puts it in a pipeline, because installable reads as supported. The defence is labelling — alpha version string, an explicit non-promise in the README, and a RESEARCH.md that names what’s missing. This spike does all three, including the detail that the name sqlalchemy-utils was already taken by an established project, so even the package name is a deliberate collision-avoidance choice rather than a land grab.

The spike becomes the product. The demonstrated core looks close enough to done that the seven hardening items get skipped. They are the boring ones — streaming, transactions, value coercion for dates and decimals, CI across engine versions — which is exactly why they’re the ones that bite in production, the same way an orchestrator choice looks simple until you meet the operational half.

The spike answers a question nobody asked. If the conclusion can’t change a decision someone is about to make, you built a demo. Write the decision down first.

Frequently asked questions

How is a spike different from a prototype or an MVP?

A prototype is aimed at users and an MVP is aimed at a market; both are meant to survive. A spike is aimed at your own uncertainty and is meant to be thrown away. If you find yourself protecting spike code from deletion, it has become a prototype, and it now needs the tests, error handling and support you skipped on purpose.

How long should a research spike take?

Time-box it to the smallest window that can produce a decision — usually a few days. What matters is the estimate and the risk list, not the code volume. alchemy-utils shows the shape: a small spike whose value is the conclusion that a documented v0.1 is three to five weeks, and full parity is a separately-scoped multi-month effort.

Do AI coding agents make design docs obsolete?

No, but they change which artifact is cheaper to produce first. Agents have collapsed the cost of a throwaway implementation far more than the cost of judgement, so the sensible order is now spike first and document second, with the document reporting measured results. You still need someone senior to decide what the results mean.

Can I use alchemy-utils in production?

Not yet, and its author doesn’t claim otherwise — 0.1a0 is an alpha that implements a subset of sqlite-utils and describes itself as a spike rather than a compatibility promise. Treat it as a reference for the pattern and for the DuckDB findings. If you need the real thing today, use sqlite-utils on SQLite and SQLAlchemy directly elsewhere.

What did the spike find about DuckDB specifically?

Six of the eight documented blockers were DuckDB-specific, against duckdb-engine 0.17.0: integer primary keys rendering as SERIAL, IDENTITY-plus-primary-key being unsupported, primary-key reflection returning nothing, index reflection returning an empty list, native JSON reflecting as VARCHAR, and unreliable UPDATE row counts. The workarounds route through DuckDB’s own catalog functions — duckdb_columns(), duckdb_indexes(), information_schema — rather than through SQLAlchemy’s reflection.

Sources

• Simon Willison — alchemy-utils 0.1a0, 12 August 2026
• simonw/alchemy-utils — README.md and RESEARCH.md (conclusion, portability findings, per-engine adapters, and the production plan and estimate)
• sqlite-utils documentation — the API contract the spike ports
• duckdb-engine — things to keep in mind

Blocker counts and the three-band scope estimate are taken directly from RESEARCH.md at the 0.1a0 release; the 6/2/0/0 engine split is my own tally of the blockers it documents.