libSQL

SQL.LibSQL runs SQL on a libSQL database, such as Turso. Queries are Effects, and failures are SqlErrors.

Install the driver:

pnpm

pnpm add @effect/sql-libsql

bun

bun add @effect/sql-libsql

npm

npm install @effect/sql-libsql

yarn

yarn add @effect/sql-libsql

Connect

Connect a Turso database with Turso.Connect and pass it to SQL.LibSQL:

src/api.ts

import * as Cloudflare from "alchemy/Cloudflare";
import * as SQL from "alchemy/SQL/LibSQL";
import * as Turso from "alchemy/Turso";
import * as Effect from "effect/Effect";
import * as HttpServerResponse from "effect/http/HttpServerResponse";
import { Db } from "./db.ts";

export default class Api extends Cloudflare.Worker<Api>()(
  "Api",
  { main: import.meta.url },
  Effect.gen(function* () {
    const conn = yield* Turso.Connect(Db);
    const sql = yield* SQL.LibSQL(conn);

    return {
      fetch: Effect.gen(function* () {
        const users = yield* sql`SELECT * FROM users`;
        return yield* HttpServerResponse.json({ users });
      }),
    };
  }).pipe(Effect.provide(Turso.ConnectHttp)),
) {}

The connection opens on the first query and closes when the request ends. See Connection lifecycle.

Connect to any libSQL server

const sql = yield* SQL.LibSQL({
  url: "libsql://my-db.example.com",
  authToken: Redacted.make(token),
});

url and authToken can be plain values or Effects.

Queries

Values you put in ${} are sent safely as parameters:

const user = yield* sql`SELECT * FROM users WHERE id = ${id}`;

yield* sql`INSERT INTO users ${sql.insert({ name, email })}`;

Set the row type with sql<{ id: number; name: string }>\…``.

Transactions

yield* sql.withTransaction(
  Effect.gen(function* () {
    yield* sql`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${from}`;
    yield* sql`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${to}`;
  }),
);

If any statement fails, none of the changes are kept.

Errors

const users = yield* sql`SELECT * FROM users`.pipe(
  Effect.catchTag("SqlError", () => Effect.succeed([])),
);

Provide as a service

Write code against the generic SqlClient, then give it the database with SQL.LibSQLLayer:

import * as SqlClient from "effect/sql/SqlClient";

const makeUsers = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
  return {
    find: (id: number) => sql<User>`SELECT * FROM users WHERE id = ${id}`,
  };
});

const users = yield* makeUsers.pipe(Effect.provide(SQL.LibSQLLayer(conn)));

Where next

Turso connections shows how to connect a database. Drizzle: libSQL adds typed schemas. Migrations runs SQL on deploy.