Open History to see the last commit
1import { PgliteClient } from "@effect/sql-pglite"
2import { assert, describe, layer } from "@effect/vitest"
3import { Deferred, Effect } from "effect"
4import { SqlClient } from "effect/sql"
5import { TestClock } from "effect/testing"
6
7const ClientLayer = PgliteClient.layer({})
8
9const setup = Effect.fnUntraced(function*(table: string) {
10 const sql = yield* PgliteClient.PgliteClient
11 yield* sql.unsafe(`CREATE TABLE IF NOT EXISTS ${table} (id SERIAL PRIMARY KEY, name TEXT)`)
12 yield* sql.unsafe(`TRUNCATE TABLE ${table} RESTART IDENTITY`)
13 return sql
14})
15
16describe("PgliteClient transactions", () => {
17 layer(ClientLayer, { timeout: "30 seconds" })((it) => {
18 it.effect("withTransaction commit", () =>
19 Effect.gen(function*() {
20 const sql = yield* setup("tx_commit")
21 yield* sql.withTransaction(sql.unsafe(`INSERT INTO tx_commit (name) VALUES ('hello')`))
22 const rows = yield* sql.unsafe<{ name: string }>(`SELECT name FROM tx_commit`)
23 assert.deepStrictEqual(rows, [{ name: "hello" }])
24 }))
25
26 it.effect("withTransaction rollback", () =>
27 Effect.gen(function*() {
28 const sql = yield* setup("tx_rollback")
29 yield* sql.unsafe(`INSERT INTO tx_rollback (name) VALUES ('hello')`).pipe(
30 Effect.andThen(Effect.fail("boom")),
31 sql.withTransaction,
32 Effect.ignore
33 )
34 const rows = yield* sql.unsafe(`SELECT * FROM tx_rollback`)
35 assert.deepStrictEqual(rows, [])
36 }))
37
38 it.effect("withTransaction with BeginTransaction sets the isolation level", () =>
39 Effect.gen(function*() {
40 const sql = yield* PgliteClient.PgliteClient
41 const level = sql`SHOW transaction_isolation`
42 assert.deepStrictEqual(
43 yield* sql.withTransaction(level).pipe(
44 Effect.provideService(SqlClient.BeginTransaction, "BEGIN ISOLATION LEVEL SERIALIZABLE")
45 ),
46 [{ transaction_isolation: "serializable" }]
47 )
48 assert.deepStrictEqual(yield* sql.withTransaction(level), [{ transaction_isolation: "read committed" }])
49 }))
50
51 it.effect("nested transaction commits both", () =>
52 Effect.gen(function*() {
53 const sql = yield* setup("tx_nested_commit")
54 const stmt = sql.unsafe(`INSERT INTO tx_nested_commit (name) VALUES ('hello')`)
55 yield* stmt.pipe(Effect.andThen(() => stmt.pipe(sql.withTransaction)), sql.withTransaction)
56 const rows = yield* sql.unsafe<{ total: number }>(
57 `SELECT count(*)::int AS total FROM tx_nested_commit`
58 )
59 assert.strictEqual(rows.at(0)?.total, 2)
60 }))
61
62 it.effect("nested transaction rollback via savepoint", () =>
63 Effect.gen(function*() {
64 const sql = yield* setup("tx_nested_rollback")
65 const stmt = sql.unsafe(`INSERT INTO tx_nested_rollback (name) VALUES ('hello')`)
66 yield* stmt.pipe(
67 Effect.andThen(() => stmt.pipe(Effect.andThen(Effect.fail("boom")), sql.withTransaction, Effect.ignore)),
68 sql.withTransaction
69 )
70 const rows = yield* sql.unsafe<{ total: number }>(
71 `SELECT count(*)::int AS total FROM tx_nested_rollback`
72 )
73 assert.strictEqual(rows.at(0)?.total, 1)
74 }))
75
76 it.effect("releases completed nested transaction locks", () =>
77 Effect.gen(function*() {
78 const sql = yield* PgliteClient.PgliteClient
79 const locks = sql`SELECT count(*)::integer AS count FROM pg_locks
80 WHERE pid = pg_backend_pid() AND locktype = 'transactionid'`
81 yield* sql.withTransaction(Effect.gen(function*() {
82 yield* sql`CREATE TEMP TABLE savepoint_locks (value INTEGER) ON COMMIT DROP`
83
84 yield* sql.withTransaction(sql`INSERT INTO savepoint_locks VALUES (1)`)
85 assert.deepStrictEqual(yield* locks, [{ count: 1 }])
86
87 const error = yield* sql.withTransaction(
88 sql`INSERT INTO savepoint_locks VALUES (2)`.pipe(Effect.andThen(Effect.fail("rollback")))
89 ).pipe(Effect.flip)
90 assert.strictEqual(error, "rollback")
91 assert.deepStrictEqual(yield* locks, [{ count: 1 }])
92
93 assert.deepStrictEqual(yield* sql`SELECT value FROM savepoint_locks`, [{ value: 1 }])
94 }))
95 }))
96
97 it.effect("preserves successful concurrent nested transactions", () =>
98 Effect.gen(function*() {
99 const sql = yield* setup("tx_nested_concurrent")
100 const firstStarted = yield* Deferred.make<void>()
101 const firstInserted = yield* Deferred.make<void>()
102
103 yield* sql.withTransaction(
104 Effect.all([
105 sql.withTransaction(
106 Effect.gen(function*() {
107 yield* Deferred.succeed(firstStarted, undefined)
108 yield* Effect.sleep("100 millis")
109 yield* sql.unsafe(`INSERT INTO tx_nested_concurrent (name) VALUES ('first')`)
110 yield* Deferred.succeed(firstInserted, undefined)
111 })
112 ),
113 Deferred.await(firstStarted).pipe(
114 Effect.andThen(sql.withTransaction(
115 Deferred.await(firstInserted).pipe(
116 Effect.andThen(Effect.fail("rollback"))
117 )
118 ))
119 )
120 ], { concurrency: "unbounded" }).pipe(Effect.catch(() => Effect.void))
121 )
122
123 const rows = yield* sql.unsafe<{ name: string }>(`SELECT name FROM tx_nested_concurrent`)
124 assert.deepStrictEqual(rows, [{ name: "first" }])
125 }).pipe(TestClock.withLive))
126 })
127})