Effect SQL Turso Adapter

[braden-w]

What is the problem this feature would solve?

There currently is no Turso/LibSQL adapter in the Effect ecosystem, although there are adapters for other providers such as d1.

What is the feature you are proposing to solve the problem?

Add a Turso/LibSQL Adapter called sql-turso for connections with remote databases on Turso, and perhaps local LibSQL databases on my machine.

This would be similar to how Drizzle has a Turso driver in their drizzle.config.ts as noted here:

https://orm.drizzle.team/learn/tutorials/drizzle-with-turso#setup-drizzle-config-file

And would be added to the collection of existing adapters:

• sql-d1
• sql-mssql
• sql-mysql2
• sql-pg
• sql-sqlite-bun
• sql-sqlite-node
• sql-sqlite-react-native
• sql-sqlite-wasm

What alternatives have you considered?

Not currently using Effect SQL Adapters, instead just wrapping queries with Effect.promise and Effect.tryPromise.

[danielo515]

What will be the minimum to provide it your own codebase meanwhile?

[braden-w]

@danielo515 Sorry for the delay, had school stuff!

An identical API surface to the other SQLite Adapter sand such.

I really like and aspire to write the code in the drizzle example:

import { SqlClient } from "@effect/sql"
import * as SqliteDrizzle from "@effect/sql-drizzle/Sqlite"
import { SqliteClient } from "@effect/sql-sqlite-node"
import * as D from "drizzle-orm/sqlite-core"
import { Config, Effect, Layer } from "effect"

// setup

const SqlLive = SqliteClient.layer({
  filename: Config.succeed("test.db")
})
const DrizzleLive = SqliteDrizzle.layer.pipe(
  Layer.provide(SqlLive)
)
const DatabaseLive = Layer.mergeAll(SqlLive, DrizzleLive)

// usage

const users = D.sqliteTable("users", {
  id: D.integer("id").primaryKey(),
  name: D.text("name")
})

Effect.gen(function*() {
  const sql = yield* SqlClient.SqlClient
  const db = yield* SqliteDrizzle.SqliteDrizzle
  yield* sql`CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)`
  yield* db.delete(users)
  yield* db.insert(users).values({ id: 1, name: "Alice" })
  const results = yield* db.select().from(users)
  console.log(results)
}).pipe(
  Effect.provide(DatabaseLive),
  Effect.runPromise
)

Ideally, I would love to just be able to change the setup:

const SqlLive = SqliteClient.layer({
  filename: Config.succeed("test.db")
})
const DrizzleLive = SqliteDrizzle.layer.pipe(
  Layer.provide(SqlLive)
)
const DatabaseLive = Layer.mergeAll(SqlLive, DrizzleLive)

to a Turso setup. For context, this is what a current Drizzle + Turso setup would look like:

export const client = createClient({ url: env.TURSO_LOCAL_DATABASE_URL });

// or

export const client = createClient({
    url: env.TURSO_REMOTE_DATABASE_URL,
    authToken: env.TURSO_AUTH_TOKEN,
});

export const db = drizzle(client, { schema });

[danielo515]

Sorry for the misunderstanding @braden-w I was not asking to you, my question was more to the Effect experts, asking for a workaround until this is available in effect.

[datner]

According to a maintainer, the libsql client is a superset of better-sqlite3 (https://github.com/tursodatabase/libsql/discussions/1449#discussioncomment-9858764), if that is the case then we might just swap one for the other for a quick win to make sqlite-node compatible with Turso.

@braden-w Are you willing to champion this? It should be a simple change of imports and supporting more properties in the options