sailfork / t3code-github / .repos/ effect/ packages/ sql/ clickhouse/ test/ Client.test.ts

import * as Clickhouse from "@clickhouse/client"
import { ClickhouseClient, ClickhouseMigrator } from "@effect/sql-clickhouse"
import { assert, describe, it } from "@effect/vitest"
import { Effect, Fiber, Stream } from "effect"
import * as Reactivity from "effect/reactivity/Reactivity"
import * as SqlClient from "effect/sql/SqlClient"
import * as Statement from "effect/sql/Statement"
import { TestClock } from "effect/testing"
import { Readable } from "node:stream"
import { vi } from "vitest"

let closeCalls = 0
let connectImmediately = false
const commandCalls: Array<Record<string, unknown>> = []
const insertCalls: Array<Record<string, unknown>> = []
let insertImpl: ((options: Record<string, unknown>) => Promise<unknown>) | undefined
let queryImpl: ((options: Record<string, unknown>) => Promise<unknown>) | undefined

vi.mock("@clickhouse/client", () => ({
  createClient: vi.fn(() => ({
    ping: () => connectImmediately ? Promise.resolve({ success: true }) : new Promise(() => {}),
    query: (options: Record<string, unknown>) => queryImpl ? queryImpl(options) : new Promise(() => {}),
    insert: (options: Record<string, unknown>) => {
      insertCalls.push(options)
      return insertImpl ? insertImpl(options) : new Promise(() => {})
    },
    command: (options: Record<string, unknown>) => {
      commandCalls.push(options)
      return Promise.resolve({})
    },
    close: () => {
      closeCalls++
      return Promise.resolve()
    }
  }))
}))

describe("ClickhouseClient", { concurrent: false }, () => {
  it("infers DateTime parameters without empty parentheses", () => {
    const sql = Statement.make(Effect.void as any, ClickhouseClient.makeCompiler(), [], undefined)
    const date = new Date("2024-01-15T14:30:00Z")

    assert.deepStrictEqual(sql`SELECT ${date}`.compile(), ["SELECT {p1: DateTime}", [date]])
  })

  it.effect("preserves an explicit nullable parameter type and null value", () =>
    Effect.gen(function*() {
      connectImmediately = true
      const sql = yield* ClickhouseClient.make({ url: "http://localhost:8123" })

      assert.deepStrictEqual(sql`SELECT ${sql.param("Nullable(Int32)", null)}`.compile(), [
        "SELECT {p1: Nullable(Int32)}",
        [null]
      ])
    }).pipe(Effect.provide(Reactivity.layer)))

  it("binds plain objects shaped like ClickHouse params as String parameters", () => {
    const sql = Statement.make(Effect.void as any, ClickhouseClient.makeCompiler(), [], undefined)
    const paramLike = { _tag: "Custom", kind: "ClickhouseParam", paramA: "Int32", paramB: 1 }

    assert.deepStrictEqual(sql`SELECT ${paramLike}`.compile(), ["SELECT {p1: String}", [paramLike]])
  })

  it("preserves fractional JavaScript numbers in inferred parameters", () => {
    const sql = Statement.make(Effect.void as any, ClickhouseClient.makeCompiler(), [], undefined)
    const [query] = sql`SELECT ${1.5}`.compile()

    assert.strictEqual(query, "SELECT {p1: Float64}")
  })

  it("uses the ClickHouse dialect for dialect-specific fragments", () => {
    const sql = Statement.make(Effect.void as any, ClickhouseClient.makeCompiler(), [], undefined)

    assert.strictEqual(
      sql.onDialect({
        sqlite: () => "sqlite",
        pg: () => "pg",
        mysql: () => "mysql",
        mssql: () => "mssql",
        clickhouse: () => "clickhouse"
      }),
      "clickhouse"
    )
  })

  it.effect("exposes the underlying ClickHouse client", () =>
    Effect.gen(function*() {
      connectImmediately = true
      const createClient = vi.mocked(Clickhouse.createClient)
      createClient.mockClear()
      const sql = yield* ClickhouseClient.make({ url: "http://localhost:8123" })

      assert.strictEqual(createClient.mock.results.length, 1)
      assert.strictEqual(sql.client, createClient.mock.results[0].value)
    }).pipe(Effect.provide(Reactivity.layer)))

  it.effect("closes the client when the connection check times out", () =>
    Effect.gen(function*() {
      connectImmediately = false
      closeCalls = 0
      const fiber = yield* Effect.forkDetach(
        ClickhouseClient.make({ url: "http://localhost:8123" }).pipe(Effect.scoped)
      )
      yield* Effect.yieldNow
      yield* TestClock.adjust("5 seconds")
      const result = fiber.pollUnsafe()

      assert.isDefined(result)
      assert.strictEqual(closeCalls, 1)
    }).pipe(Effect.provide(Reactivity.layer)))

  it.effect("parameterizes the query id when cancelling a query", () =>
    Effect.gen(function*() {
      connectImmediately = true
      commandCalls.length = 0
      const queryId = "id' OR 1 = 1 --"
      const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" })
      const fiber = yield* client.withQueryId(client.unsafe("SELECT 1"), queryId).pipe(Effect.forkScoped)
      yield* Effect.yieldNow

      yield* Fiber.interrupt(fiber)

      assert.deepStrictEqual(commandCalls, [{
        query: "KILL QUERY WHERE query_id = {queryId:String}",
        query_params: { queryId }
      }])
    }).pipe(
      Effect.scoped,
      Effect.provide(Reactivity.layer)
    ))

  it.effect("parameterizes the query id when cancelling an insert", () =>
    Effect.gen(function*() {
      connectImmediately = true
      commandCalls.length = 0
      const queryId = "id' OR 1 = 1 --"
      const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" })
      const fiber = yield* client.withQueryId(
        client.insertQuery({ table: "test", values: [] }),
        queryId
      ).pipe(Effect.forkScoped)
      yield* Effect.yieldNow

      yield* Fiber.interrupt(fiber)

      assert.deepStrictEqual(commandCalls, [{
        query: "KILL QUERY WHERE query_id = {queryId:String}",
        query_params: { queryId }
      }])
    }).pipe(
      Effect.scoped,
      Effect.provide(Reactivity.layer)
    ))

  it.effect("passes column selection to insertQuery", () =>
    Effect.gen(function*() {
      connectImmediately = true
      insertCalls.length = 0
      insertImpl = () => Promise.resolve({ executed: true, query_id: "" })
      const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" })

      yield* client.insertQuery({ table: "people", values: [{ name: "Alice" }], columns: ["name"] })
      yield* client.insertQuery({ table: "people", values: [{ name: "Bob" }], columns: { except: ["id"] } })

      assert.strictEqual(insertCalls.length, 2)
      assert.strictEqual(insertCalls[0].table, "people")
      assert.strictEqual(insertCalls[0].format, "JSONEachRow")
      assert.deepStrictEqual(insertCalls[0].columns, ["name"])
      assert.deepStrictEqual(insertCalls[1].columns, { except: ["id"] })
    }).pipe(Effect.provide(Reactivity.layer)))

  it.effect("writes the ledger with commands and runs migrations as queries", () =>
    Effect.gen(function*() {
      connectImmediately = true
      commandCalls.length = 0
      const queries: Array<string> = []
      queryImpl = (options) => {
        queries.push(String(options.query))
        return Promise.resolve({ json: () => Promise.resolve({ data: [] }) })
      }
      const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" })
      const applied = yield* ClickhouseMigrator.run({
        loader: ClickhouseMigrator.fromRecord({
          "1_init": client`CREATE TABLE init`
        })
      }).pipe(Effect.provideService(SqlClient.SqlClient, client))

      const select = `SELECT migration_id, name, created_at FROM "effect_sql_migrations" ORDER BY migration_id DESC`
      assert.deepStrictEqual(applied, [[1, "init"]])
      assert.deepStrictEqual(queries, [select, "CREATE TABLE init", select])
      assert.strictEqual(commandCalls.length, 2)
      assert.match(String(commandCalls[0].query), /ENGINE = MergeTree/)
      assert.match(String(commandCalls[1].query), /^INSERT INTO "effect_sql_migrations"/)
    }).pipe(
      Effect.ensuring(Effect.sync(() => {
        queryImpl = undefined
      })),
      Effect.provide(Reactivity.layer)
    ))

  it.effect("retries a failed migration and the ones after it on the next run", () =>
    Effect.gen(function*() {
      connectImmediately = true
      commandCalls.length = 0
      queryImpl = (options) => {
        const ledger: Array<{ migration_id: unknown; name: unknown }> = []
        for (const command of commandCalls) {
          if (String(command.query).startsWith(`INSERT INTO "effect_sql_migrations"`)) {
            const params = Object.values(command.query_params as Record<string, unknown>)
            for (let i = 0; i < params.length; i += 2) {
              ledger.unshift({ migration_id: params[i], name: params[i + 1] })
            }
          }
        }
        const data = String(options.query).startsWith("SELECT migration_id") ? ledger : []
        return Promise.resolve({ json: () => Promise.resolve({ data }) })
      }
      const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" })
      let attempts = 0
      const run = ClickhouseMigrator.run({
        loader: ClickhouseMigrator.fromRecord({
          "1_first": client`CREATE TABLE first`,
          "2_second": Effect.suspend(() => attempts++ === 0 ? Effect.fail("rejected") : Effect.void),
          "3_third": client`CREATE TABLE third`
        })
      }).pipe(Effect.provideService(SqlClient.SqlClient, client))

      const error = yield* Effect.flip(run)
      assert.strictEqual(error.message, `Migration "2_second" failed`)
      assert.deepStrictEqual(yield* run, [[2, "second"], [3, "third"]])
    }).pipe(
      Effect.ensuring(Effect.sync(() => {
        queryImpl = undefined
      })),
      Effect.provide(Reactivity.layer)
    ))

  it.effect("kills a streamed query only when the stream is not read to the end", () =>
    Effect.gen(function*() {
      connectImmediately = true
      commandCalls.length = 0
      // A minimal stand-in for the row batches emitted by `ResultSet.stream()`
      queryImpl = () =>
        Promise.resolve({
          stream: () =>
            Readable.from(
              [[{ a: 1 }], [{ a: 2 }]].map((rows) => rows.map((row) => ({ json: () => Promise.resolve(row) }))),
              { objectMode: true }
            )
        })
      const client = yield* ClickhouseClient.make({ url: "http://localhost:8123" })

      const all = yield* Stream.runCollect(client`SELECT a FROM t`.stream).pipe(client.withQueryId("full-id"))
      const first = yield* Stream.runCollect(client`SELECT a FROM t`.stream.pipe(Stream.take(1))).pipe(
        client.withQueryId("take-id")
      )

      assert.deepStrictEqual(all, [{ a: 1 }, { a: 2 }])
      assert.deepStrictEqual(first, [{ a: 1 }])
      assert.deepStrictEqual(commandCalls, [{
        query: "KILL QUERY WHERE query_id = {queryId:String}",
        query_params: { queryId: "take-id" }
      }])
    }).pipe(
      Effect.ensuring(Effect.sync(() => {
        queryImpl = undefined
      })),
      Effect.provide(Reactivity.layer)
    ))
})