Skip to content
Back to search

sqlloom

Find race conditions in your SQL transactions: runs every interleaving on a real Postgres at every isolation level and shows the one that corrupts your data.

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/anime5hka--sqlloom/badge.svg)](/listings/anime5hka--sqlloom)

HTML

<a href="/listings/anime5hka--sqlloom"><img src="/api/listings/anime5hka--sqlloom/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

Install instructions from the README

As written by the repo's author. findsafeskills has not verified these steps.

# 1. A throwaway Postgres (any Postgres 12+ works; sqlloom only touches its own sqlloom_* schemas)
docker run -d --name sqlloom-pg -e POSTGRES_PASSWORD=sqlloom -p 54329:5432 postgres:17-alpine
export SQLLOOM_URL=postgres://postgres:sqlloom@localhost:54329/postgres

# 2. Install and run an example
npm install --save-dev github:Anime5hka/sqlloom
npx sqlloom node_modules/sqlloom/examples/on-call.mjs

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

README.md · README · 317 lines
  1. <h1 align="center">sqlloom 🧶</h1>
  2. <p align="center">
  3. <b>Find the race conditions in your SQL transactions before production does.</b><br>
  4. sqlloom runs every interleaving of your transactions on a real Postgres, at every isolation level,<br>
  5. and shows you the exact one that corrupts your data.
  6. </p>
  7. <p align="center">
  8. <img src="https://img.shields.io/badge/postgres-12%2B-336791" alt="postgres 12+">
  9. <img src="https://img.shields.io/badge/node-%3E%3D20-339933" alt="node >= 20">
  10. <img src="https://img.shields.io/badge/types-included-3178c6" alt="TypeScript types included">
  11. <a href="LICENSE"><img src="https://img.shields.io/badge/license-MIT-blue" alt="MIT"></a>
  12. </p>
  13. <p align="center">
  14. <img src="assets/demo.svg" alt="sqlloom finding a write-skew race at read committed and repeatable read" width="900">
  15. </p>
  16. "Is `READ COMMITTED` enough here, or do I need `FOR UPDATE`? Or `SERIALIZABLE`?" Most of us answer that by reasoning about it, or by guessing, because the race only happens when two requests land in the same millisecond. sqlloom answers it the same way a model checker would. It takes your transaction code, including the `if` statements between the queries, and runs **every possible interleaving** of the queries against a real Postgres, once per isolation level. Then it tells you which levels are unsafe, which are safe if you retry, and the shortest interleaving that breaks.
  17. <p align="center">
  18. <a href="#quick-start">Quick start</a> ·
  19. <a href="#write-a-scenario">Write a scenario</a> ·
  20. <a href="#what-it-catches">What it catches</a> ·
  21. <a href="#how-it-compares">How it compares</a> ·
  22. <a href="#cli">CLI</a> ·
  23. <a href="#api">API</a> ·
  24. <a href="#faq">FAQ</a>
  25. </p>
  26. ## Quick start
  27. ```sh
  28. # 1. A throwaway Postgres (any Postgres 12+ works; sqlloom only touches its own sqlloom_* schemas)
  29. docker run -d --name sqlloom-pg -e POSTGRES_PASSWORD=sqlloom -p 54329:5432 postgres:17-alpine
  30. export SQLLOOM_URL=postgres://postgres:sqlloom@localhost:54329/postgres
  31. # 2. Install and run an example
  32. npm install --save-dev github:Anime5hka/sqlloom
  33. npx sqlloom node_modules/sqlloom/examples/on-call.mjs
  34. ```
  35. ## Write a scenario
  36. A scenario is a setup, two or more transactions written as plain async functions, and (optionally) an invariant. Here's the classic write skew: at least one doctor must stay on call.
  37. ```js
  38. // on-call.mjs
  39. export default {
  40. setup: `
  41. CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
  42. INSERT INTO doctors VALUES ('alice', true), ('bob', true);
  43. `,
  44. transactions: {
  45. alice: goOffCall('alice'),
  46. bob: goOffCall('bob'),
  47. },
  48. invariant: async (db) => {
  49. const [{ n }] = await db.query('SELECT count(*)::int AS n FROM doctors WHERE on_call');
  50. if (n === 0) throw new Error('nobody is on call');
  51. },
  52. };
  53. function goOffCall(name) {
  54. return async (db) => {
  55. const [{ n }] = await db.query('SELECT count(*)::int AS n FROM doctors WHERE on_call');
  56. if (n < 2) throw new Error('you are the last one on call'); // app logic between queries
  57. await db.query('UPDATE doctors SET on_call = false WHERE name = $1', [name]);
  58. };
  59. }
  60. ```
  61. ```sh
  62. npx sqlloom on-call.mjs # exit code 1 if your level (default: read committed) is unsafe
  63. ```
  64. sqlloom wraps each function in `BEGIN … COMMIT` (or `ROLLBACK` if it throws) on its own connection. Every `db.query` and every `COMMIT` is a step, and sqlloom chooses which transaction takes the next step.
  65. ### Or in plain SQL
  66. No app logic between the queries? Then a `.sql` file is enough:
  67. ```sql
  68. -- setup
  69. CREATE TABLE accounts (id int PRIMARY KEY, balance int NOT NULL);
  70. INSERT INTO accounts VALUES (1, 100);
  71. -- transaction alice
  72. UPDATE accounts SET balance = balance - 80 WHERE id = 1 AND balance >= 80;
  73. -- transaction bob
  74. UPDATE accounts SET balance = balance - 50 WHERE id = 1 AND balance >= 50;
  75. -- invariant
  76. SELECT balance >= 0 FROM accounts; -- every row of every invariant query must be true
  77. ```
  78. ```sh
  79. npx sqlloom withdraw-atomic.sql
  80. ```
  81. Use one statement per line-ending `;`. Dollar-quoted function bodies are not supported, so put those in `-- setup` of a `.mjs` scenario.
  82. ### No invariant? It still finds the bug
  83. Every result is compared with the **serial executions** of the same transactions (alice then bob, bob then alice). If the final tables, or a value a transaction returned, can't be explained by *any* serial order, that's an anomaly. A lost update needs no invariant at all:
  84. ```
  85. ✗ read committed: the result matches no serial order of the transactions
  86. Shortest interleaving (2 context switches, replay "0.1.1.1.0.0"):
  87. 1 alice │ SELECT balance FROM accounts WHERE id = 1 → 100
  88. 2 bob │ SELECT balance FROM accounts WHERE id = 1 → 100
  89. 3 bob │ UPDATE accounts SET balance = 50 WHERE id = 1 → UPDATE 1
  90. 4 bob │ COMMIT
  91. 5 alice │ UPDATE accounts SET balance = 20 WHERE id = 1 → UPDATE 1
  92. 6 alice │ COMMIT
  93. alice committed → 20 · bob committed → 50
  94. accounts: [{"id":1,"balance":20}]
  95. serial alice → bob: accounts: [{"id":1,"balance":20}] · alice committed → 20 · bob failed: insufficient funds
  96. serial bob → alice: accounts: [{"id":1,"balance":50}] · bob committed → 50 · alice failed: insufficient funds
  97. ```
  98. Both withdrawals succeeded, but only one of them shows up in the balance.
  99. ### Your ORM code, unchanged
  100. Using Prisma, Drizzle, Kysely, Knex or another library on top of node-postgres? Don't rewrite anything as SQL. `db.pg` looks like a `pg.Client` and a `pg.Pool`, so you can hand it to the ORM and call **the function your app already has**:
  101. ```js
  102. import { drizzle } from 'drizzle-orm/node-postgres';
  103. import { checkout } from '../src/orders.js'; // your real code, with its own db.transaction()
  104. export default {
  105. setup: `...`,
  106. transactions: {
  107. alice: (db) => checkout(drizzle(db.pg), 'alice', 1, 1),
  108. bob: (db) => checkout(drizzle(db.pg), 'bob', 1, 1),
  109. },
  110. invariant: async (db) => { /* sold <= stock */ },
  111. };
  112. ```
  113. ```
  114. isolation level schedules result
  115. read committed 24 (all) ✗ UNSAFE 22 of 24 break the invariant ◂ yours
  116. repeatable read 24 (all) ✓ safe 22 of 24 abort a transaction (40001/40P01): retry it in your code
  117. serializable 24 (all) ✓ safe 22 of 24 abort a transaction (40001/40P01): retry it in your code
  118. ✗ read committed: invariant failed: sold 2 items, had 1 in stock
  119. 1 alice │ select "id", "stock" from "products" where "products"."id" = 1 → [[1,1]]
  120. 2 bob │ select "id", "stock" from "products" where "products"."id" = 1 → [[1,1]]
  121. 3 bob │ update "products" set "stock" = 0 where "products"."id" = 1 → UPDATE 1
  122. ...
  123. ```
  124. Adding `.for('update')` to the select makes it safe at read committed. Full example: [`examples/orm/drizzle-checkout.mjs`](examples/orm/drizzle-checkout.mjs).
  125. | ORM | Pass this to your code | Example |
  126. |---|---|---|
  127. | Drizzle | `drizzle(db.pg)` | [drizzle-checkout.mjs](examples/orm/drizzle-checkout.mjs) |
  128. | Prisma 7 | `new PrismaClient({ adapter: new PrismaPg(db.pg, { schema: db.schema }) })` | [prisma/checkout.ts](examples/orm/prisma/checkout.ts) |
  129. | Kysely | `new Kysely({ dialect: new PostgresDialect({ pool: db.pg }) })` | [test/orm.test.js](test/orm.test.js) |
  130. | Knex 3.3+ | `knex({ client: 'pg', connectionPool: db.pg, version: '17' })` | [test/orm.test.js](test/orm.test.js) |
  131. | raw `pg` | `db.pg.query(...)`, `db.pg.connect()` | |
  132. Prisma pins every table to a schema, so it needs `db.schema` (sqlloom's private schema for the run). Prisma's `P2034` write-conflict error counts as a retryable abort, like `40001`. Knex's `version` option skips its `select version()` probe, which would otherwise show up as an extra step in every transaction.
  133. The ORM's own `BEGIN`/`COMMIT`/`ROLLBACK` are answered by sqlloom, which already runs the function inside a transaction at the level under test. A level your code asks for (`{ isolationLevel: 'serializable' }`) is ignored the same way: sqlloom tests all three, and `--level` marks the one you run. Savepoints (nested transactions) go to Postgres as usual.
  134. ### In your test suite
  135. ```js
  136. import { test } from 'node:test'; // or vitest / jest
  137. import { assertSafe } from 'sqlloom';
  138. import transfer from './scenarios/transfer.mjs';
  139. test('transfers are safe at the isolation level we run in production', async () => {
  140. await assertSafe({ ...transfer, level: 'read committed' }); // throws with the timeline if not
  141. });
  142. ```
  143. ### Is your retry loop enough?
  144. At repeatable read and serializable, Postgres prevents some anomalies by aborting a transaction with `40001`, and your app is expected to retry it. `--retries 2` (or `{ retries: 2 }`) models that loop: an aborted transaction starts again inside the same schedule and sees whatever the others committed.
  145. ```
  146. $ npx sqlloom examples/on-call.mjs --retries 2
  147. isolation level schedules result
  148. read committed 20 (all) ✗ UNSAFE 18 of 20 break the invariant ◂ yours
  149. repeatable read 20 (all) ✗ UNSAFE 18 of 20 break the invariant
  150. serializable 20 (all) ✓ safe 18 of 20 needed a retry
  151. ```
  152. With retries, serializable is fully safe: the retried doctor sees that the other one already left and refuses. Retries can't save repeatable read here, because nothing gets aborted.
  153. ## What it catches
  154. All of these are in [`examples/`](examples). Run them all with `npm run examples`.
  155. | Scenario | read committed | repeatable read | serializable |
  156. |---|---|---|---|
  157. | [withdraw](examples/withdraw.mjs): read balance, check, write it back | ✗ lost update | ✓ retry | ✓ retry |
  158. | [withdraw-for-update](examples/withdraw-for-update.mjs): the same with `SELECT … FOR UPDATE` | ✓ | ✓ retry | ✓ retry |
  159. | [on-call](examples/on-call.mjs): "at least one doctor on call" (write skew) | ✗ | ✗ | ✓ retry |
  160. | [booking](examples/booking.mjs): count bookings, check capacity, insert (phantom) | ✗ overbooked | ✗ overbooked | ✓ retry |
  161. | [signup](examples/signup.mjs): check-then-insert username, no `UNIQUE` | ✗ duplicate | ✗ duplicate | ✓ retry |
  162. | [withdraw-atomic.sql](examples/withdraw-atomic.sql): check and write in one `UPDATE … WHERE balance >= 80` | ✓ | ✓ retry | ✓ retry |
  163. | [counter.sql](examples/counter.sql): read into a temp row, write back | ✗ lost update | ✓ retry | ✓ retry |
  164. **✓ retry** means the level is safe, but Postgres will abort one of the transactions in some interleavings (`40001` serialization failure or `40P01` deadlock), so your code has to retry it. sqlloom counts how often that happens.
  165. ## How it compares
  166. | | Tests your app's transactions (with the logic between queries) | Every interleaving, not a sample | Every isolation level side by side | No expected output to write |
  167. |---|:-:|:-:|:-:|:-:|
  168. | Load tests / concurrent integration tests | ✅ | ❌ the race needs the same millisecond | ❌ | ✅ |
  169. | [Jepsen](https://jepsen.io/) / Elle | ❌ they test the database | ❌ random workloads | ✅ | ✅ |
  170. | Postgres [isolation tester](https://github.com/postgres/postgres/tree/master/src/test/isolation) | fixed SQL steps only | ✅ | one per spec | ❌ |
  171. | [Hermitage](https://github.com/ept/hermitage) | ❌ documentation of anomalies | hand-picked schedules | ✅ | – |
  172. | **sqlloom** | ✅ JS functions, Prisma, Drizzle, Kysely, Knex | ✅ | ✅ | ✅ invariant or serial-equivalence check |
  173. ## How it works
  174. 1. **One connection per transaction.** Each schedule starts from a fresh schema built by your `setup`. Every transaction gets its own connection and its own `BEGIN ISOLATION LEVEL …`.
  175. 2. **You write code, sqlloom holds the queries.** When a transaction calls `db.query`, the query is parked, not sent. sqlloom picks one parked transaction, sends its query, and waits for the answer. The first schedule picks in declaration order, which runs the transactions one after another.
  176. 3. **Lock waits are real.** If a query blocks on a lock, sqlloom sees it in `pg_stat_activity`, records who it is waiting for (`⏳ waits for alice`), and carries on with the other transactions. Deadlocks are detected by Postgres itself. sqlloom lowers `deadlock_timeout` for its own sessions when it's allowed to.
  177. 4. **Every interleaving.** The choices form a tree, and sqlloom walks all of it depth-first (`20 (all)` in the report). If the tree is bigger than `--max`, the report says `sampled` instead.
  178. 5. **Two checks per schedule.** Your invariant runs, and the final state plus the return values are compared against every serial order.
  179. 6. **Shortest story.** Of all the failing schedules, the report shows the one with the fewest context switches, plus a `replay` token that re-runs exactly that interleaving.
  180. 7. **Fast enough to keep in CI.** Schedules run in parallel, each in its own schema with its own connections, and `synchronous_commit` is off for these throwaway sessions. Two-transaction scenarios take well under a second, and three transactions with 600+ interleavings take about 5 s.
  181. ## Use it from AI coding agents
  182. [`skills/sqlloom/SKILL.md`](skills/sqlloom/SKILL.md) is an agent skill. It teaches Claude Code (and other agents that read `SKILL.md`) how to turn the transaction code in your repo into a scenario, choose a useful invariant, read the matrix and suggest the smallest fix:
  183. ```sh
  184. mkdir -p ~/.claude/skills/sqlloom && curl -fsSL https://raw.githubusercontent.com/Anime5hka/sqlloom/main/skills/sqlloom/SKILL.md -o ~/.claude/skills/sqlloom/SKILL.md
  185. ```
  186. Then ask: *"can two concurrent checkouts oversell the last item?"*
  187. ## CLI
  188. ```
  189. sqlloom <scenario.mjs | scenario.sql> [--url <url>] [--level rc|rr|serializable] [--levels rc,rr,serializable] [--max <n>] [--json]
  190. ```
  191. | | |
  192. |---|---|
  193. | `--url` | Postgres URL. Default: `$SQLLOOM_URL`, then `$DATABASE_URL` |
  194. | `--level` | The isolation level your app runs at (marked `◂ yours`). The exit code is `1` if it is unsafe |
  195. | `--levels` | Which levels to test (default: all three) |
  196. | `--max` | Max schedules per level (default 5000) |
  197. | `--retries` | Re-run transactions that Postgres aborted (`40001`/`40P01`) up to n times, like your app's retry loop |
  198. | `--json` | Machine-readable report |
  199. ## API
  200. ```ts
  201. import { check, assertSafe, formatReport } from 'sqlloom';
  202. const report = await check({
  203. url, // default $SQLLOOM_URL / $DATABASE_URL
  204. setup, // SQL string, or async (db) => { ... }
  205. transactions, // { name: async (db) => result, ... } — 2 or more
  206. invariant, // optional: async (db) => { throw / return false if broken }
  207. levels, // default ['read committed', 'repeatable read', 'serializable']
  208. level, // your production level (used by assertSafe, marked in the report)
  209. maxSchedules, // default 5000 per level
  210. serializable, // compare with serial executions (default true)
  211. snapshot, // custom "final state" for that comparison (default: all tables)
  212. retries, // re-run aborted (40001/40P01) transactions up to n times (default 0)
  213. parallel, // worker schemas (default min(4, CPUs))
  214. replay, // run exactly one schedule from a report's replay token
  215. });
  216. console.log(formatReport(report, { color: true }));
  217. ```
  218. `db.query(sql, params)` returns the rows array, with `.rowCount` and `.command` attached. `db.pg` is the same connection shaped like a node-postgres client/pool, for ORMs. Full types are in [`src/index.d.ts`](src/index.d.ts).
  219. ## FAQ
  220. **Why not just use SERIALIZABLE everywhere?** Sometimes that's the right answer, and sqlloom will tell you so. But serializable means some of your transactions get aborted under contention, so you need a retry loop (`--retries` checks that it's enough). Most apps also run on the default read committed, where a single `FOR UPDATE` or a one-statement `UPDATE … WHERE` is the cheaper fix. sqlloom shows you all three options side by side for *your* code.
  221. **How is this different from Jepsen / Elle / isocheck?** Those test the *database*: they run random workloads and check whether it really provides the isolation it promises. sqlloom assumes Postgres works and tests *your transactions*. It enumerates their interleavings instead of sampling, and points at the exact one that breaks your invariant.
  222. **And from Postgres's isolation tester or Hermitage?** Postgres's [isolation tester](https://github.com/postgres/postgres/tree/master/src/test/isolation) can also run every permutation of fixed SQL steps, but it is an internal tool for Postgres's own regression tests, and its specs compare against expected output. [Hermitage](https://github.com/ept/hermitage) is a set of hand-written schedules that document anomalies. sqlloom runs your app logic between queries, works out the interleavings itself, needs no expected output, and plugs into your test runner.
  223. **Does it need a superuser?** No. It needs permission to create schemas. As a superuser it also shortens `deadlock_timeout` for its own sessions, which makes scenarios that deadlock run faster.
  224. ## Caveats
  225. - **Point it at a disposable database.** sqlloom creates and drops its own `sqlloom_*` schemas and never touches anything else. Your `setup` SQL is still yours, though, so a test database is the right place for it.
  226. - **Granularity is one statement.** sqlloom interleaves whole statements. Races *inside* a single statement are left to Postgres. That matches how your app talks to the database.
  227. - **Keep it deterministic.** `now()`, `random()`, and sequences that skip values when a transaction aborts can make a state differ from every serial run. If you use them, pass a `snapshot` that leaves them out (for example, select the business columns only).
  228. - **2–3 transactions with a handful of queries each.** The number of interleavings grows quickly. That's where these bugs live anyway.
  229. ## Roadmap
  230. - [x] Plain `.sql` scenario files for transactions without app logic
  231. - [x] ORM code as-is: Prisma 7, Drizzle, Kysely, Knex and other node-postgres clients (`db.pg`)
  232. - [ ] MySQL / MariaDB
  233. - [x] Retry loops as part of the scenario (`--retries`)
  234. - [ ] Partial-order reduction to skip interleavings that can't differ
  235. - [ ] Python API
  236. ## See also
  237. [raceloom](https://github.com/Anime5hka/raceloom) does the same kind of search for async JavaScript and Python code, using fakes instead of a database. [shakeout](https://github.com/Anime5hka/shakeout) finds flaky tests in a whole existing suite with seeded timing jitter.
  238. ## License
  239. [MIT](LICENSE)

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.