Convex vs. Prisma: Type Safety Was Never the Hard Part

Prisma is a type-safe ORM: you own the database, Prisma generates a typed client for it. Convex is a whole backend — reactive database, server functions, file storage, scheduled jobs — queried with TypeScript instead of SQL. The choice is not about type safety, which both deliver. It is about who owns correctness under concurrency.

Every comparison of these two opens with end-to-end types, and that framing hides the decision. Both give you autocomplete on your data. Neither of them, by itself, stops the bug that actually shipped in this codebase: a check-then-act race in a download rate limiter that let a burst of parallel requests walk straight through a limit both tools would have typed perfectly.

The comparison that matters

The row people underweight is "Raw SQL escape hatch." It looks like a power-user nicety until the day correctness requires a database primitive your abstraction has no word for. That day arrived here.

The defect: a type-safe race condition

The download endpoint enforces an hourly per-user cap. The first implementation did the obvious thing, and the obvious thing was wrong:

1. Read the user's download count for the last hour.
2. If it is under the limit, insert an audit row and serve the file.

Two statements, two round trips, no lock. Under concurrency, N parallel requests all read the same count, all pass the < limit check, and all insert. The cap is advisory. This is CWE-367, time-of-check to time-of-use, and it is filed in this repo as supabase/migrations/0007_atomic_download_rate_limit.sql with the root cause written into the header.

Note what would not have caught it. The code was fully typed. A Prisma version would have been equally typed — prisma.downloadEvent.count() followed by prisma.downloadEvent.create() is two awaits with the same gap between them. Convex would have done better here by accident, because its mutations are transactional by default, but "my framework serialized it for me" is a different thing from knowing why it had to be.

The fix collapses count-and-insert into one database call, serialized per user:

create or replace function public.record_download_within_limit(
  p_user_id uuid, p_product_slug text, p_framework text,
  p_version text, p_ip inet, p_user_agent text, p_limit int
) returns int
language plpgsql
security definer
set search_path = public, pg_temp
as $$
declare
  recent int;
begin
  -- Serialize concurrent downloads for THIS user so the count+insert below
  -- is atomic. Transaction-scoped: released automatically on commit. Keyed
  -- on the user-id hash so different users never contend with each other.
  perform pg_advisory_xact_lock(hashtextextended(p_user_id::text, 0));
  -- …count, then conditionally insert, then return the post-insert count
  -- (or -1 when over the limit, in which case NOTHING is inserted).
end;
$$;

Three things in that snippet have no equivalent in either client's query API: a transaction-scoped advisory lock, security definer with a pinned search_path, and a return value that distinguishes "recorded, you are at N" from "refused, nothing written." The last one matters because a rejected download must not be audited as a download — otherwise the limiter poisons its own counter.

The header carries the caveat that makes this honest, and it is the kind of thing an ORM-shaped mental model never prompts you to write down:

Atomicity note: correctness relies on READ COMMITTED isolation (Postgres + Supabase default). Because the lock is acquired before any table read, the SELECT below takes a fresh per-statement snapshot AFTER the prior holder committed. Under REPEATABLE READ / SERIALIZABLE the snapshot could freeze earlier and the second caller could read a stale count — do not run this under a higher isolation level without collapsing count+insert into one statement.

An abstraction that hides the isolation level cannot help you reason about that. Convex hides it by making one global choice for you, which is genuinely safer for the common case and genuinely opaque when you need to know. Prisma hides it behind a transaction option you have to go looking for.

What this codebase actually chose: neither

package.json has no ORM in it. The production dependencies relevant to data are two:

@supabase/ssr
@supabase/supabase-js

That is the whole data layer. No Prisma, no Drizzle, no Convex. Eight versioned SQL migrations in supabase/migrations/, and authorization pushed down into the database itself.

The function inventory is worth reading as a pair, because the split is the point. Three security definer RPCs do every write that has to be atomic — create_order_with_slots, record_download_within_limit, refund_order — and one security invoker helper only reads:

create or replace function public.downloads_in_last_hour(uid uuid)
returns int language sql stable security invoker
set search_path = public, pg_temp as $$
  select count(*)::int from public.download_events
  where user_id = uid and created_at > now() - interval '1 hour';
$$;

That read-only helper is exactly what the racy implementation called. It is correct, cheap, and stable — and calling it, then deciding, then writing is the bug. The fix did not change this function; it added one that refuses to separate the three steps. Worth sitting with, because "the query was fine" is usually true in a TOCTOU defect, and it is why no amount of type generation finds it.

Six policies are the entire authorization boundary:

create policy "own profile"   on public.profiles          for select using ((select auth.uid()) = id);
create policy "own orders"    on public.orders            for select using ((select auth.uid()) = user_id);
create policy "own slots"     on public.entitlement_slots for select using ((select auth.uid()) = user_id);
create policy "own downloads" on public.download_events   for select using ((select auth.uid()) = user_id);
create policy "own deliveries"      on public.download_deliveries for select using ((select auth.uid()) = user_id);
create policy "own refund requests" on public.refund_requests    for select using ((select auth.uid()) = user_id);

Read-own-only, enforced by Postgres, on every path. A forgotten where user_id = ? in application code is a data leak; a forgotten one here returns zero rows. This is the strongest argument for owning your SQL that this repository has, and it is an argument against Convex specifically: Convex has no row-level security layer beneath your functions, so every authorization check lives in function code you must not forget to write.

The counterpart is that the business logic that can be pure, is. src/lib/entitlements.ts is a dependency-free module — no database, no network — so the rules that decide what a buyer owns are unit-testable without a database at all. That split is available under any of these tools and is worth more than the choice between them.

When each one is right

Choose Prisma when the database is yours and must stay yours: an existing Postgres, a compliance boundary, a team that already writes SQL, or a read path where you need query shapes an ORM's generator won't produce. You keep $queryRaw for the day correctness needs a primitive — which is the day this post is about.

Choose Convex when there is no backend yet, the app is genuinely reactive (collaborative editors, live dashboards, presence), and you would otherwise be building WebSocket plumbing by hand. Transactional-by-default mutations remove a whole class of the bug above for free, and that is not a small thing. Accept that your data lives in their store and your queries are not portable.

Choose neither when your read path doesn't touch a database at all. This storefront's public pages — 311 blog posts, 111 product pages — are statically generated. Nothing queries Postgres to render them. Only the authenticated surfaces do: the dashboard, /api/download, the LemonSqueezy webhook, and the auth callback. A reactive backend solves a problem a static site does not have.

Mistakes and troubleshooting

Frequently asked questions

Is Convex a replacement for Prisma? Not a drop-in one. Prisma is a client for a database you chose; Convex replaces the database too. Migrating from Prisma to Convex means moving your data into their store and rewriting queries as TypeScript functions — there is no Prisma adapter for Convex.

Does Prisma work with Supabase? Yes, it is Postgres. But Prisma connects as a database role, which means row-level security policies keyed on auth.uid() do not apply the way they do through the Supabase client. If RLS is your authorization boundary, as it is here, that is a real architectural consequence rather than a configuration detail.

Does Convex need a separate auth provider? It integrates with external providers rather than shipping a full auth product, so expect to wire one in. Factor that into the "no backend to run" promise.

Why does this project use raw SQL instead of either? Because the two hardest requirements were atomicity and read-own-only authorization, and both are database features. Four RPCs and six policies express them directly; an ORM would have wrapped them, and a reactive backend would have replaced the database that provides them.

Templates in this post

ASoc Pip is a forex-trading marketing site with a live-rates dashboard preview and a 3-tier pricing table — the page whose real version is the reactive read path Convex is built for. ASoc Press is a blog, news and magazine template with editor's picks, galleries and video and audio feeds, a content-heavy read path that wants static generation, not a live query. ASoc Quest is a dark-theme games-storefront landing page with deals, top sellers and platform rows, where entitlements and downloads are the schema problem this post's RPCs solve.

Browse the full sets: Next.js landing page templates, Tailwind landing page templates.