1import { NodeFileSystem } from "@effect/platform-node"
2import { SqliteClient } from "@effect/sql-sqlite-node"
3import { assert, expect, it } from "@effect/vitest"
4import { Duration, Effect, FileSystem, Layer } from "effect"
5import * as PersistedQueueTest from "effect-test/persistence/PersistedQueueTest"
6import * as SqlCleanupTest from "effect-test/persistence/SqlCleanupTest"
7import { PersistedQueue, Persistence } from "effect/persistence"
8import { Reactivity } from "effect/reactivity"
9import * as SqlClient from "effect/sql/SqlClient"
10import { TestClock } from "effect/testing"
11
12const ClientLayer = Effect.gen(function*() {
13 const fs = yield* FileSystem.FileSystem
14 const dir = yield* fs.makeTempDirectoryScoped()
15 return SqliteClient.layer({
16 filename: dir + "/test.db"
17 })
18}).pipe(
19 Layer.unwrap,
20 Layer.provide([NodeFileSystem.layer, Reactivity.layer])
21)
22
23const testLayer = (layer: Layer.Layer<Persistence.BackingPersistence, never, SqlClient.SqlClient>) =>
24 layer.pipe(Layer.provideMerge(ClientLayer))
25
26const suite = (
27 name: string,
28 layer: Layer.Layer<Persistence.BackingPersistence, never, SqlClient.SqlClient>,
29 options: {
30 /**
31 * A crafted `getMany` key which, if request keys are interpolated into SQL
32 * without parameterization, escapes the `IN` list and
33 * reads the `sqli_victim` store's entries from the `sqli_attacker` store.
34 */
35 readonly injectionProbe: string
36 }
37) =>
38 it.layer(testLayer(layer))(`Persistence (${name})`, (it) => {
39 it.effect("set + get", () =>
40 Effect.gen(function*() {
41 const persistence = yield* Persistence.BackingPersistence
42 const store = yield* persistence.make("test_store")
43 yield* store.set("key1", { name: "Alice" }, undefined)
44 const value = yield* store.get("key1")
45 expect(value).toEqual({ name: "Alice" })
46
47 // test upsert
48 yield* store.set("key1", { name: "Alice" }, undefined)
49 }))
50
51 it.effect("setMany + getMany", () =>
52 Effect.gen(function*() {
53 const persistence = yield* Persistence.BackingPersistence
54 const store = yield* persistence.make("test_store_2")
55 yield* store.setMany([
56 ["key1", { name: "Alice" }, undefined],
57 ["key2", { name: "Bob" }, undefined],
58 ["key3", { name: "Charlie" }, undefined]
59 ])
60 const values = yield* store.getMany(["key1", "key2", "key3", "key4"])
61 expect(values).toEqual([
62 { name: "Alice" },
63 { name: "Bob" },
64 { name: "Charlie" },
65 undefined
66 ])
67 }))
68
69 it.effect("getMany with duplicate keys", () =>
70 Effect.gen(function*() {
71 const persistence = yield* Persistence.BackingPersistence
72 const store = yield* persistence.make("test_store_duplicate_keys")
73 yield* store.set("key", { value: 1 }, undefined)
74
75 expect(yield* store.getMany(["key", "key"])).toEqual([{ value: 1 }, { value: 1 }])
76 }))
77
78 it.effect("remove", () =>
79 Effect.gen(function*() {
80 const persistence = yield* Persistence.BackingPersistence
81 const store = yield* persistence.make("test_store_2")
82 yield* store.setMany([
83 ["key1", { name: "Alice" }, undefined],
84 ["key2", { name: "Bob" }, undefined],
85 ["key3", { name: "Charlie" }, undefined]
86 ])
87 yield* store.remove("key2")
88 const valuesAfter = yield* store.getMany(["key1", "key2", "key3"])
89 expect(valuesAfter).toEqual([{ name: "Alice" }, undefined, { name: "Charlie" }])
90 }))
91
92 it.effect("expires", () =>
93 Effect.gen(function*() {
94 const persistence = yield* Persistence.BackingPersistence
95 const store = yield* persistence.make("test_store_3")
96 yield* store.setMany([
97 ["key1", { name: "Alice" }, undefined],
98 ["key2", { name: "Bob" }, undefined],
99 ["key3", { name: "Charlie" }, Duration.seconds(10)]
100 ])
101 let values = yield* store.getMany(["key1", "key2", "key3"])
102 expect(values).toEqual([{ name: "Alice" }, { name: "Bob" }, { name: "Charlie" }])
103 yield* TestClock.adjust(Duration.seconds(5))
104 values = yield* store.getMany(["key1", "key2", "key3"])
105 expect(values).toEqual([{ name: "Alice" }, { name: "Bob" }, { name: "Charlie" }])
106 yield* TestClock.adjust(Duration.seconds(5))
107 values = yield* store.getMany(["key1", "key2", "key3"])
108 expect(values).toEqual([{ name: "Alice" }, { name: "Bob" }, undefined])
109 }))
110
111 it.effect("isolation between stores", () =>
112 Effect.gen(function*() {
113 const persistence = yield* Persistence.BackingPersistence
114 const storeA = yield* persistence.make("test_store_a")
115 const storeB = yield* persistence.make("test_store_b")
116
117 yield* storeA.set("shared-key", { name: "Alice" }, undefined)
118 yield* storeB.set("shared-key", { name: "Bob" }, undefined)
119
120 expect(yield* storeA.get("shared-key")).toEqual({ name: "Alice" })
121 expect(yield* storeB.get("shared-key")).toEqual({ name: "Bob" })
122
123 yield* storeA.clear
124
125 expect(yield* storeA.get("shared-key")).toEqual(undefined)
126 expect(yield* storeB.get("shared-key")).toEqual({ name: "Bob" })
127 }))
128
129 it.effect("getMany treats keys containing single quotes as data", () =>
130 Effect.gen(function*() {
131 const persistence = yield* Persistence.BackingPersistence
132 const store = yield* persistence.make("test_store_quotes")
133 yield* store.set("it's-a-key", { name: "Alice" }, undefined)
134
135 // Keys must be bound as parameters, not
136 // interpolated into the `IN` list as raw string literals.
137 const values = yield* store.getMany(["it's-a-key", "missing'key"])
138 expect(values).toEqual([{ name: "Alice" }, undefined])
139 }))
140
141 it.effect("getMany preserves store isolation for crafted keys", () =>
142 Effect.gen(function*() {
143 const persistence = yield* Persistence.BackingPersistence
144 const victim = yield* persistence.make("sqli_victim")
145 yield* victim.set("secret", { secret: "victim-data" }, undefined)
146
147 const attacker = yield* persistence.make("sqli_attacker")
148 yield* attacker.set("innocent", { name: "Mallory" }, undefined)
149
150 // The probe is a no-op key when keys are parameterized. If keys are
151 // interpolated into raw SQL it escapes the `IN` list and exfiltrates
152 // the victim store's `secret` entry into the attacker's results.
153 const values = yield* attacker.getMany(["secret", options.injectionProbe])
154 expect(values).toEqual([undefined, undefined])
155 expect(yield* attacker.get("secret")).toEqual(undefined)
156 }))
157 })
158
159suite("table-per-store", Persistence.layerBackingSqlMultiTable, {
160 // Expands to `... WHERE id IN ('secret', '') UNION SELECT id, value FROM
161 // effect_persistence_sqli_victim WHERE ('1'='1') AND (expires IS NULL OR
162 // expires > ?)`, appending every victim row to the attacker's result set.
163 injectionProbe: `') UNION SELECT id, value FROM effect_persistence_sqli_victim WHERE ('1'='1`
164})
165suite("single-table", Persistence.layerBackingSql, {
166 // Expands to `... WHERE store_id = 'sqli_attacker' AND id IN ('secret', '')
167 // OR store_id='sqli_victim' AND ('1'='1') AND (expires IS NULL OR expires >
168 // ?)`, bypassing the store isolation filter.
169 injectionProbe: `') OR store_id='sqli_victim' AND ('1'='1`
170})
171
172PersistedQueueTest.suite(
173 "sql-sqlite-node",
174 PersistedQueue.layerStoreSql().pipe(Layer.provide(ClientLayer))
175)
176
177it.layer(ClientLayer)("Persistence SQL cleanup", (it) => {
178 it.effect("deletes expired entries in batches", () =>
179 Effect.gen(function*() {
180 const sql = (yield* SqlClient.SqlClient).withoutTransforms()
181 const table = sql("effect_persistence")
182 const expiredCount = sql<{ readonly count: number }>`
183 SELECT COUNT(*) AS count FROM ${table} WHERE store_id = 'expired'
184 `.pipe(Effect.map((rows) => rows[0].count))
185 yield* sql`
186 CREATE TABLE ${table} (
187 store_id TEXT NOT NULL,
188 id TEXT NOT NULL,
189 value TEXT NOT NULL,
190 expires INTEGER,
191 PRIMARY KEY (store_id, id)
192 )
193 `
194
195 const entries = Array.from({ length: SqlCleanupTest.expiredEntryCount }, (_, i) => ({
196 store_id: "expired",
197 id: String(i),
198 value: "{}",
199 expires: SqlCleanupTest.expiredAtEpoch
200 }))
201 yield* sql`INSERT INTO ${table} ${sql.insert(entries)}`.unprepared
202 yield* sql`
203 INSERT INTO ${table} (store_id, id, value, expires)
204 VALUES ('live', 'live', '{}', NULL), ('live', 'future', '{}', ${SqlCleanupTest.futureExpiresAt})
205 `
206
207 yield* Layer.build(Persistence.layerBackingSql).pipe(TestClock.withLive)
208
209 const expired = yield* SqlCleanupTest.waitForCount(expiredCount, (count) => count === 0)
210 assert.strictEqual(expired, 0)
211 const live = yield* sql<{ readonly count: number }>`
212 SELECT COUNT(*) AS count FROM ${table} WHERE store_id = 'live'
213 `
214 assert.strictEqual(live[0].count, 2)
215
216 const indexes = yield* sql<{ readonly count: number }>`
217 SELECT COUNT(*) AS count FROM sqlite_master
218 WHERE type = 'index'
219 AND name = 'effect_persistence_expires_idx'
220 `
221 assert.strictEqual(indexes[0].count, 1)
222 }), { timeout: SqlCleanupTest.testTimeout })
223})