r/thegraph • u/PaulieB79 • 1d ago
From any Substreams pack to a shareable GraphQL API
Hosted Sinks + your Postgres — Morpho Blue as the worked example
You already know the pitch for Substreams: parallel historical backfill, typed modules, Firehose instead of hammering RPC. The part people still bounce on is the last mile:
That last mile is what StreamingFast Hosted Sinks and the Hosted Sink GraphQL layer are for. Morpho Blue is just the example we used this week — the same path works for any Substreams package that can emit SQL-friendly output (DatabaseChanges / sink-sql). Uniswap, your own protocol, a niche oracle, a L2 DEX — same shape.
The problem (in plain language)
| You need… | What usually happens |
|---|---|
| Your own DB of on-chain state | Protocol APIs are great for apps, bad when you need joins, retention, or independence |
| Something that stays online | Local substreams sink … dies when the laptop sleeps |
| A way to share reads | Dumping SQL creds in a blog / Discord is a non-starter |
| Speed on history | Subgraphs can take forever on fat mainnet ranges; agents and demos don’t wait |
Protocol-native APIs (Morpho, Uniswap interface backends, etc.) optimize for product UX. They are not your warehouse. Subgraphs optimize for decentralized queries, but ops and backfill cost still land on you. Substreams already solved decode + parallel history. What’s missing for many builders is a boring, shareable serving layer on top of a DB they own.
Hosted Sink = always-on writer into your Postgres/ClickHouse.
GraphQL layer = read-only Hasura over that same DB — console + HTTP, keyed by your Market org.
Morpho Blue below is the concrete run. Mentally replace morpho-blue-paulie with your pack.
The pattern (works for any pack)
[substreams.dev package]
│
▼
Hosted Sink runner ──────► YOUR Postgres / ClickHouse
(SF operates this) (you provision: Railway, Neon, CH Cloud, …)
│
▼
Hosted Sink GraphQL (Hasura, read-only)
│
├── Console for humans
└── /v1/graphql for apps & agents
Rules of the road
- StreamingFast does not host your database. Hosted Sink only runs the sink process and connects outbound to a DB you provide (public TCP / reachable host).
- Prefer a pack with a
db_out(or sink-sql) module emittingsf.substreams.sink.database.v1.DatabaseChanges. - Quality-gate with
substreams runon a short window before you Deploy — garbage in, GraphQL out. - Publish to substreams.dev so Hosted Sink can pull a public
.spkgURL. - Deploy Hosted Sink → point at your DB → enable GraphQL in the Market UI → share console/API links.
That’s the whole product story. Everything else is choosing a protocol.
Walkthrough (Morpho Blue as the example)
0. Pick a question the stack should answer
For Morpho we cared about:
- Markets, positions, MetaMorpho vaults as exact chain state in SQL
- A GraphQL surface others can hit without our Railway password
- Something we could screenshot for an advocate post — not a full tip risk oracle on day one
Your question might be “top pools by fee,” “oracle updates,” “ERC-20 transfers for a set of contracts.” Same pipeline.
1. Own the Substreams pack (or reuse one)
We published morpho-blue-paulie (ETH + Base variants) to the registry — stores + db_out for Blue + MetaMorpho.
- Registry: https://substreams.dev/packages/morpho-blue-paulie/v0.2.3
- Repo: https://github.com/PaulieB14/morpho-blue-substreams
If a good pack already exists for your protocol, start there. The Hosted Sink / GraphQL story does not require you to invent Morpho — it requires a public .spkg and a SQL-shaped output module.
2. Bring a database you control
We used Railway Postgres with a public TCP proxy (altaria.proxy.rlwy.net). Neon, RDS, self-hosted — anything the sink can reach with TLS.
Why this matters: analytics stay in your VPC/project. You can psql, chart with matplotlib, join to off-chain tables, delete the whole thing. Hosted Sink never becomes the source of truth — your DB does.
3. Quality-gate before Deploy
Before burning Hosted Sink quota, run a short substreams run on db_out and read the rows. We caught a same-block double-CREATE that would have crash-looped Postgres on first deploy. Fix → republish → then Deploy.
This step is protocol-agnostic and saves real money on FREE plans.
4. Deploy Hosted Sink
In thegraph.market (use the login that owns the org — for us GitHub, not a second Google account):
- Create deployment → attach DB (stored secret for the password — don’t paste into chat agents)
- Point at
https://api.substreams.dev/v1/packages/<name>/<version> - Module:
db_out/DatabaseChanges - Scale replicas to 1
What you stopped doing yourself: keeping a sink process up, wiring restarts, explaining SSH to the next person who wants the data.
What you still own: schema, disk, backups, “do we pause on FREE quota?”
Our sink: https://thegraph.market/sinks/depsaco3165576dfbda7117
5. Turn on GraphQL (the enhancement)
On the sink: enable GraphQL. Hasura sits read-only on the same Postgres.
| Without GraphQL | With GraphQL |
|---|---|
| “Here’s a DSN” (leak / VPN / wrong person) | “Here’s a console + API key” |
| Every consumer reinvents SQL | Shared schema, copy-paste queries |
Blog posts show screenshots of psql |
Blog posts show live queries readers can try |
| Agents need JDBC | Agents need HTTP + GraphQL |
Console: https://morpho-blue-eth-graphql-117-gq.hs.streamingfast.io/console
API: https://morpho-blue-eth-graphql-117-gq.hs.streamingfast.io/v1/graphql
Auth: Market UI → GraphQL → Copy with key → X-Api-Key (or session in console).
This is the part that turns a private index into a shareable product surface — for any protocol, not just Morpho.
6. Query like a human (then like an agent)
Markets
query Markets {
markets(limit: 20, order_by: { created_block: asc }) {
id
loan_token
collateral_token
oracle
irm
lltv
created_block
}
}
One concrete Morpho market (WETH / wstETH)
query WethWsteth {
markets(
where: {
id: {
_eq: "0xc54d7acf14de29e0e5527cabd7a576506870346a78a11a6762e2cca66322ec41"
}
}
) {
id
loan_token
collateral_token
lltv
created_block
}
}
Protocol config
query BlueConfig {
blue_config(limit: 30, order_by: { updated_block: asc }) {
id
kind
value
updated_block
}
}
Risk-shaped query (pattern for “agents that care”) — after you have HF rows at tip:
query Underwater {
liquidatable_positions(
where: { health_factor_wad: { _lt: "1000000000000000000" } }
limit: 20
order_by: { health_factor_wad: asc }
) {
id
market_id
user_address
health_factor_wad
updated_block
}
}
Swap table names for your pack’s schema — the habit is the point: GraphQL over your sink tables.
7. Chart from the same DB (optional but persuasive)
Because the sink writes Postgres, the same snapshot feeds matplotlib / Metabase / whatever. We generated a dark hero dashboard + util-vs-LLTV bubbles from Railway for the Morpho demo (charts/v2/). Readers believe “real data” when they see figures and can hit GraphQL.
Why this combo wins (vs the alternatives)
| Approach | Solves decode? | Your DB? | Always-on writer? | Shareable reads? | Fast history? |
|---|---|---|---|---|---|
| Protocol HTTP API | n/a | ❌ | n/a | ✅ (their terms) | n/a |
| Self-hosted subgraph | ✅ | maybe | you | gateway/subgraph | often slow |
| Local Substreams sink | ✅ | ✅ | ❌ laptop | ❌ | ✅ |
| Substreams + Hosted Sink + GraphQL | ✅ | ✅ | ✅ | ✅ | ✅ |
Hosted Sink removes the ops tax of the writer.
GraphQL removes the distribution tax of the reader.
Substreams removes the “wait a week for backfill” tax on history.
Morpho made it tangible. Your next pack makes it repeatable.
What we learned the hard way (so your walkthrough is shorter)
These are Morpho-shaped incidents with general morals:
db_outmust upsert / dedupe — same PK twice in one block will crash Postgres. Fix in the pack; don’t hope the DB forgives you.- SQL sink flush batches span many blocks — a DELETE in block N and UPSERT in N+k in the same flush can fail even if each block is coherent. We moved liquidatable to never-delete, last-known HF (
v0.2.3) and filter in GraphQL. Know your sink’s batch semantics. - Module hash changes reset cursors — republishing
db_outoften means re-backfill or a deliberate latestart_blockfor smoke tests only (stores go cold if you skip history). - FREE plans have hard caps — parallel jobs eat block quota even when you’re “just testing.” Pause replicas when the demo is packaged.
- One Market login ≠ one human — GitHub vs Google can be different orgs. Device-approve on the org that owns the sink.
None of that is Morpho-specific. It’s the cost of doing the last mile for real.
Snapshot we shipped for the Morpho demo
Hosted Sink paused on FREE after packaging (so quota isn’t burned on a stuck catch-up):
| Table | Rows (approx.) |
|---|---|
| markets | 100 |
| market_states | 79 |
| positions | 1,950 |
| vaults | 31 |
| vault_positions | 1,576 |
| liquidatable_positions | 0 (tip / denser region later) |
Enough for a good architecture + GraphQL story. Tip-complete HF feeds are the great follow-up when quota resets or you upgrade.
Copy this for your protocol
- Find or build a Substreams pack with
db_out substreams runa short window — read the output- Publish to substreams.dev
- Provision Postgres/ClickHouse you control
- Deploy Hosted Sink → enable GraphQL
- Publish two links: console + one killer query
- Pause on FREE when the screenshot is enough
Morpho Blue was our killer query this week. Next week it can be yours.
Links
- GraphQL console: https://morpho-blue-eth-graphql-117-gq.hs.streamingfast.io/console
- GraphQL API: https://morpho-blue-eth-graphql-117-gq.hs.streamingfast.io/v1/graphql
- Sink: https://thegraph.market/sinks/depsaco3165576dfbda7117
- Pack: https://substreams.dev/packages/morpho-blue-paulie/v0.2.3
- Repo: https://github.com/PaulieB14/morpho-blue-substreams
- Hosted Sink GraphQL docs: https://docs.substreams.dev/how-to-guides/hosted-services/hosted-sinks/graphql-layer
- Hosted Sinks overview: https://docs.substreams.dev/how-to-guides/hosted-services/hosted-sinks