The best way to control Claude Code, Codex and more from any computer or phone
| 1 | import * as Clickhouse from "@clickhouse/client" |
| 2 | import { ClickhouseClient, ClickhouseMigrator } from "@effect/sql-clickhouse" |
| 3 | import { assert, describe, it } from "@effect/vitest" |
| 4 | import { Effect, Fiber, Stream } from "effect" |
| 5 | import * as Reactivity from "effect/reactivity/Reactivity" |
| 6 | import * as SqlClient from "effect/sql/SqlClient" |
| 7 | import * as Statement from "effect/sql/Statement" |
| 8 | import { TestClock } from "effect/testing" |
| 9 | import { Readable } from "node:stream" |
| 10 | import { vi } from "vitest" |
| 11 | |
| 12 | let closeCalls = 0 |
| 13 | let connectImmediately = false |
| 14 | const commandCalls: Array<Record<string, unknown>> = [] |
| 15 | const insertCalls: Array<Record<string, unknown>> = [] |
| 16 | let insertImpl: ((options: Record<string, unknown>) => Promise<unknown>) | undefined |
| 17 | let queryImpl: ((options: Record<string, unknown>) => Promise<unknown>) | undefined |
| 18 | |
| 19 | vi.mock("@clickhouse/client", () => ({ |
| 20 | createClient: vi.fn(() => ({ |
| 21 | ping: () => connectImmediately ? Promise.resolve({ success: true }) : new Promise(() => {}), |
| 22 | query: (options: Record<string, unknown>) => queryImpl ? queryImpl(options) : new Promise(() => {}), |
| 23 | insert: (options: Record<string, unknown>) => { |
| 24 | insertCalls.push(options) |
| 25 | return insertImpl ? insertImpl(options) : new Promise(() => {}) |
| 26 | }, |
| 27 | command: (options: Record<string, unknown>) => { |
| 28 | commandCalls.push(options) |
| 29 | return Promise.resolve({}) |
| 30 | }, |
| 31 | close: () => { |
| 32 | closeCalls++ |
| 33 | return Promise.resolve() |
| 34 | } |
| 35 | })) |
| 36 | })) |
| 37 | |
| 38 | describe("ClickhouseClient", { concurrent: false }, () => { |
| 39 | it("infers DateTime parameters without empty parentheses", () => { |
| 40 | const sql = Statement.make(Effect.void as any, ClickhouseClient.makeCompiler(), [], undefined) |
| 41 | const date = new Date("2024-01-15T14:30:00Z") |
| 42 | |
| 43 | assert.deepStrictEqual(sql`SELECT ${date}`.compile(), ["SELECT {p1: DateTime}", [date]]) |
| 44 | }) |
| 45 | |
| 46 | it.effect("preserves an explicit nullable parameter type and null value", () => |
| 47 | Effect.gen(function*() { |
| 48 | connectImmediately = true |
| 49 | const sql = yield* ClickhouseClient.make({ url: "http://localhost:8123" }) |
| 50 | |
| 51 | assert.deepStrictEqual(sql`SELECT ${sql.param("Nullable(Int32)", null)}`.compile(), [ |
| 52 | "SELECT {p1: Nullable(Int32)}", |
| 53 | [null] |
| 54 | ]) |
| 55 | }).pipe(Effect.provide(Reactivity.layer))) |
| 56 | |
| 57 | it("binds plain objects shaped like ClickHouse params as String parameters", () => { |
| 58 | const sql = Statement.make(Effect.void as any, ClickhouseClient.makeCompiler(), [], undefined) |
| 59 | const paramLike = { _tag: "Custom", kind: "ClickhouseParam", paramA: "Int32", paramB: 1 } |
| 60 | |
| 61 | assert.deepStrictEqual(sql`SELECT ${paramLike}`.compile(), ["SELECT {p1: String}", [paramLike]]) |
| 62 | }) |
| 63 | |
| 64 | it("preserves fractional JavaScript numbers in inferred parameters", () => { |
| 65 | const sql = Statement.make(Effect.void as any, ClickhouseClient.makeCompiler(), [], undefined) |
| 66 | const [query] = sql`SELECT ${1.5}`.compile() |
| 67 | |
| 68 | assert.strictEqual(query, "SELECT {p1: Float64}") |
| 69 | }) |
| 70 | |
| 71 | it("uses the ClickHouse dialect for dialect-specific fragments", () => { |
| 72 | const sql = Statement.make(Effect.void as any, ClickhouseClient.makeCompiler(), [], undefined) |
| 73 | |
| 74 | assert.strictEqual( |
| 75 | sql.onDialect({ |
| 76 | sqlite: () => "sqlite", |
| 77 | pg: () => "pg", |
| 78 | mysql: () => "mysql", |
| 79 | mssql: () => "mssql", |
| 80 | clickhouse: () => "clickhouse" |
| 81 | }), |
| 82 | "clickhouse" |
| 83 | ) |
| 84 | }) |
| 85 | |
| 86 | it.effect("exposes the underlying ClickHouse client", () => |
| 87 | Effect.gen(function*() { |
| 88 | connectImmediately = true |
| 89 | const createClient = vi.mocked(Clickhouse.createClient) |
| 90 | createClient.mockClear() |
| 91 | const sql = yield* ClickhouseClient.make({ url: "http://localhost:8123" }) |
| 92 | |
| 93 | assert.strictEqual(createClient.mock.results.length, 1) |
| 94 | assert.strictEqual(sql.client, createClient.mock.results[0].value) |
| 95 | }).pipe(Effect.provide(Reactivity.layer))) |
| 96 | |
| 97 | it.effect("closes the client when the connection check times out", () => |
| 98 | Effect.gen(function*() { |
| 99 | connectImmediately = false |
| 100 | closeCalls = 0 |
| 101 | const fiber = yield* Effect.forkDetach( |
| 102 | ClickhouseClient.make({ url: "http://localhost:8123" }).pipe(Effect.scoped) |
| 103 | ) |
| 104 | yield* Effect.yieldNow |
| 105 | yield* TestClock.adjust("5 seconds") |
| 106 | const result = fiber.pollUnsafe() |
| 107 | |
| 108 | assert.isDefined(result) |
| 109 | assert.strictEqual(closeCalls, 1) |
| 110 | }).pipe(Effect.provide(Reactivity.layer))) |
| 111 | |
| 112 | it.effect("parameterizes the query id when cancelling a query", () => |
| 113 | Effect.gen(function*() { |
| 114 | connectImmediately = true |
| 115 | commandCalls.length = 0 |
| 116 | const queryId = "id' OR 1 = 1 --" |
| 117 | const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" }) |
| 118 | const fiber = yield* client.withQueryId(client.unsafe("SELECT 1"), queryId).pipe(Effect.forkScoped) |
| 119 | yield* Effect.yieldNow |
| 120 | |
| 121 | yield* Fiber.interrupt(fiber) |
| 122 | |
| 123 | assert.deepStrictEqual(commandCalls, [{ |
| 124 | query: "KILL QUERY WHERE query_id = {queryId:String}", |
| 125 | query_params: { queryId } |
| 126 | }]) |
| 127 | }).pipe( |
| 128 | Effect.scoped, |
| 129 | Effect.provide(Reactivity.layer) |
| 130 | )) |
| 131 | |
| 132 | it.effect("parameterizes the query id when cancelling an insert", () => |
| 133 | Effect.gen(function*() { |
| 134 | connectImmediately = true |
| 135 | commandCalls.length = 0 |
| 136 | const queryId = "id' OR 1 = 1 --" |
| 137 | const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" }) |
| 138 | const fiber = yield* client.withQueryId( |
| 139 | client.insertQuery({ table: "test", values: [] }), |
| 140 | queryId |
| 141 | ).pipe(Effect.forkScoped) |
| 142 | yield* Effect.yieldNow |
| 143 | |
| 144 | yield* Fiber.interrupt(fiber) |
| 145 | |
| 146 | assert.deepStrictEqual(commandCalls, [{ |
| 147 | query: "KILL QUERY WHERE query_id = {queryId:String}", |
| 148 | query_params: { queryId } |
| 149 | }]) |
| 150 | }).pipe( |
| 151 | Effect.scoped, |
| 152 | Effect.provide(Reactivity.layer) |
| 153 | )) |
| 154 | |
| 155 | it.effect("passes column selection to insertQuery", () => |
| 156 | Effect.gen(function*() { |
| 157 | connectImmediately = true |
| 158 | insertCalls.length = 0 |
| 159 | insertImpl = () => Promise.resolve({ executed: true, query_id: "" }) |
| 160 | const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" }) |
| 161 | |
| 162 | yield* client.insertQuery({ table: "people", values: [{ name: "Alice" }], columns: ["name"] }) |
| 163 | yield* client.insertQuery({ table: "people", values: [{ name: "Bob" }], columns: { except: ["id"] } }) |
| 164 | |
| 165 | assert.strictEqual(insertCalls.length, 2) |
| 166 | assert.strictEqual(insertCalls[0].table, "people") |
| 167 | assert.strictEqual(insertCalls[0].format, "JSONEachRow") |
| 168 | assert.deepStrictEqual(insertCalls[0].columns, ["name"]) |
| 169 | assert.deepStrictEqual(insertCalls[1].columns, { except: ["id"] }) |
| 170 | }).pipe(Effect.provide(Reactivity.layer))) |
| 171 | |
| 172 | it.effect("writes the ledger with commands and runs migrations as queries", () => |
| 173 | Effect.gen(function*() { |
| 174 | connectImmediately = true |
| 175 | commandCalls.length = 0 |
| 176 | const queries: Array<string> = [] |
| 177 | queryImpl = (options) => { |
| 178 | queries.push(String(options.query)) |
| 179 | return Promise.resolve({ json: () => Promise.resolve({ data: [] }) }) |
| 180 | } |
| 181 | const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" }) |
| 182 | const applied = yield* ClickhouseMigrator.run({ |
| 183 | loader: ClickhouseMigrator.fromRecord({ |
| 184 | "1_init": client`CREATE TABLE init` |
| 185 | }) |
| 186 | }).pipe(Effect.provideService(SqlClient.SqlClient, client)) |
| 187 | |
| 188 | const select = `SELECT migration_id, name, created_at FROM "effect_sql_migrations" ORDER BY migration_id DESC` |
| 189 | assert.deepStrictEqual(applied, [[1, "init"]]) |
| 190 | assert.deepStrictEqual(queries, [select, "CREATE TABLE init", select]) |
| 191 | assert.strictEqual(commandCalls.length, 2) |
| 192 | assert.match(String(commandCalls[0].query), /ENGINE = MergeTree/) |
| 193 | assert.match(String(commandCalls[1].query), /^INSERT INTO "effect_sql_migrations"/) |
| 194 | }).pipe( |
| 195 | Effect.ensuring(Effect.sync(() => { |
| 196 | queryImpl = undefined |
| 197 | })), |
| 198 | Effect.provide(Reactivity.layer) |
| 199 | )) |
| 200 | |
| 201 | it.effect("retries a failed migration and the ones after it on the next run", () => |
| 202 | Effect.gen(function*() { |
| 203 | connectImmediately = true |
| 204 | commandCalls.length = 0 |
| 205 | queryImpl = (options) => { |
| 206 | const ledger: Array<{ migration_id: unknown; name: unknown }> = [] |
| 207 | for (const command of commandCalls) { |
| 208 | if (String(command.query).startsWith(`INSERT INTO "effect_sql_migrations"`)) { |
| 209 | const params = Object.values(command.query_params as Record<string, unknown>) |
| 210 | for (let i = 0; i < params.length; i += 2) { |
| 211 | ledger.unshift({ migration_id: params[i], name: params[i + 1] }) |
| 212 | } |
| 213 | } |
| 214 | } |
| 215 | const data = String(options.query).startsWith("SELECT migration_id") ? ledger : [] |
| 216 | return Promise.resolve({ json: () => Promise.resolve({ data }) }) |
| 217 | } |
| 218 | const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" }) |
| 219 | let attempts = 0 |
| 220 | const run = ClickhouseMigrator.run({ |
| 221 | loader: ClickhouseMigrator.fromRecord({ |
| 222 | "1_first": client`CREATE TABLE first`, |
| 223 | "2_second": Effect.suspend(() => attempts++ === 0 ? Effect.fail("rejected") : Effect.void), |
| 224 | "3_third": client`CREATE TABLE third` |
| 225 | }) |
| 226 | }).pipe(Effect.provideService(SqlClient.SqlClient, client)) |
| 227 | |
| 228 | const error = yield* Effect.flip(run) |
| 229 | assert.strictEqual(error.message, `Migration "2_second" failed`) |
| 230 | assert.deepStrictEqual(yield* run, [[2, "second"], [3, "third"]]) |
| 231 | }).pipe( |
| 232 | Effect.ensuring(Effect.sync(() => { |
| 233 | queryImpl = undefined |
| 234 | })), |
| 235 | Effect.provide(Reactivity.layer) |
| 236 | )) |
| 237 | |
| 238 | it.effect("kills a streamed query only when the stream is not read to the end", () => |
| 239 | Effect.gen(function*() { |
| 240 | connectImmediately = true |
| 241 | commandCalls.length = 0 |
| 242 | // A minimal stand-in for the row batches emitted by `ResultSet.stream()` |
| 243 | queryImpl = () => |
| 244 | Promise.resolve({ |
| 245 | stream: () => |
| 246 | Readable.from( |
| 247 | [[{ a: 1 }], [{ a: 2 }]].map((rows) => rows.map((row) => ({ json: () => Promise.resolve(row) }))), |
| 248 | { objectMode: true } |
| 249 | ) |
| 250 | }) |
| 251 | const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" }) |
| 252 | |
| 253 | const all = yield* Stream.runCollect(client`SELECT a FROM t`.stream).pipe(client.withQueryId("full-id")) |
| 254 | const first = yield* Stream.runCollect(client`SELECT a FROM t`.stream.pipe(Stream.take(1))).pipe( |
| 255 | client.withQueryId("take-id") |
| 256 | ) |
| 257 | |
| 258 | assert.deepStrictEqual(all, [{ a: 1 }, { a: 2 }]) |
| 259 | assert.deepStrictEqual(first, [{ a: 1 }]) |
| 260 | assert.deepStrictEqual(commandCalls, [{ |
| 261 | query: "KILL QUERY WHERE query_id = {queryId:String}", |
| 262 | query_params: { queryId: "take-id" } |
| 263 | }]) |
| 264 | }).pipe( |
| 265 | Effect.ensuring(Effect.sync(() => { |
| 266 | queryImpl = undefined |
| 267 | })), |
| 268 | Effect.provide(Reactivity.layer) |
| 269 | )) |
| 270 | }) |