Feature: add @effect/sql-kysely package

[ecyrbe]

Type

• [x] Refactor
• [x] Feature
• [ ] Bug Fix
• [ ] Optimization
• [ ] Documentation Update

Description

Add support for Kysely as a DB query builder.

Since kysely is built on pure typescript types, i added no @effect/schema overload, like for @effect/sql-drizzle . There are implementations out there doing it, but it's because they don't have effect commit implementation, so they rely on effect/sql schema and resolver to effectify kysely.

many implementations

• add support for @effect/sql mssql backend (required mssql backend refactoring)
• add support for @effect/sql mysql backend
• add support for @effect/sql postgres backend
• add support for @effect/sql sqlite backend
• add support for all kysely drivers with a special kysely Dialect Tag

Example usage

import * as SqliteKysely from "@effect/sql-kysely/Sqlite"
import * as Sqlite from "@effect/sql-sqlite-node"
import { Config, Console, Context, Effect, Exit, Layer } from "effect"
import type { Generated } from "kysely"

export interface User {
  id: Generated<number>
  name: string
}

interface Database {
  users: User
}

class SqliteDB extends Context.Tag("SqliteDB")<SqliteDB, SqliteKysely.EffectKysely<Database>>() {}

const SqliteLive = Sqlite.client.layer({
  filename: Config.succeed(":memory:")
})

const KyselyLive = Layer.effect(SqliteDB, SqliteKysely.make<Database>()).pipe(Layer.provide(SqliteLive))

Effect.gen(function*(_) {
  const db = yield* SqliteDB

  yield* db.schema
    .createTable("users")
    .addColumn("id", "integer", (c) => c.primaryKey().autoIncrement())
    .addColumn("name", "text", (c) => c.notNull())

  const result = yield* db.withTransaction(
    Effect.gen(function*() {
      const inserted = yield* db.insertInto("users").values({ name: "Alice" }).returningAll()
      yield* Console.log(inserted)
      const selected = yield* db.selectFrom("users").selectAll()
      yield* Console.log(selected)
      const updated = yield* db.updateTable("users").set({ name: "Bob" }).returningAll()
      yield* Console.log(updated)
      return yield* Effect.fail(new Error("rollback"))
    })
  ).pipe(Effect.exit)
  if (Exit.isSuccess(result)) {
    return yield* Effect.fail("should not reach here")
  }
  const selected = yield* db.selectFrom("users").selectAll()
  yield* Console.log(selected)
}).pipe(
  Effect.provide(KyselyLive),
  Effect.runPromise
)

Related

Effect has prior external library integration with @effect/sql-drizzle , that's why i propose to also integrate kysely.

It also has a minor change to MSSQL placeholder format. Since Kysely use numbered placeholders and Tedious library supports them, i made a change for mssql to be compatible.

• Related Issue #
• Closes #

[changeset-bot[bot]]

🦋 Changeset detected

Latest commit: 54d68314bdc0b6eb6f0b132a692cd184e0c0c716

The changes in this PR will be included in the next version bump.

This PR includes changesets to release 2 packages

Name	Type
@effect/sql-mssql	Patch
@effect/sql-kysely	Patch

Not sure what this means? Click here to learn what changesets are.

Click here if you're a maintainer who wants to add another changeset to this PR

[ecyrbe]

Docgen generation seems to fail due to "export * " for types. I'll take a look tonight to check why.

[ecyrbe]

Docgen generation seems to fail due to "export * " for types. I'll take a look tonight to check why.

hopefully las commit should fix docgen errors

[mikearnaldi]

@ecyrbe following twitter discussion please add a note in the readme that warns against "future proof" and suggesting to users to use this at their own risk

[ecyrbe]

@ecyrbe following twitter discussion please add a note in the readme that warns against "future proof" and suggesting to users to use this at their own risk

done

[mikearnaldi]

@ecyrbe would you mind rebasing pls so we can merge?

[TylorS]

I'm probably a bit late to the game here, but FWIW, I have a fairly different approach to this integration which may be of some value in the discussion. https://github.com/TylorS/effect-sql-kysely. The /test folder is probably most useful in terms of usage as its not really entirely ready for public consumption (though it is being utilized).

The key differences are:

1.) @effect/sql's Client interface is implemented atop of Kysely's connections instead of as "just" a query builder 2.) Kysely's client interface is injected in the Sql.schema.* and Sql.resolver.* interfaces 3.) Integrates with @effect/schema for Kysely's ColumnType and its derivatives

There's probably plenty of reasons it wouldn't be utilized as-is, and I'm definitely not upset if it's disregarded entirely, but I figured I'd share either way as its fairly different from what's in this PR and doesn't really have any means of breaking unless the public API from Kysely is broken explicitly.

[mikearnaldi]

/rebase